OBIEE
Interview Questions and Answers FAQ
These questions are
related to what previously known as Siebel Analytics is now known as OBIEE i.e
Oracle Business Intelligence Enterprise Edition.
”
Define repository in terms of Siebel Analytics
o Repository stores
the Meta data information. Siebel repository is a file system ,extension of the
repository file. rpd.
o META DATA
REPOSITORY
o With Siebel
Analytics Server, all the rules needed for security, data modeling, aggregate
navigation, caching, and connectivity is stored in metadata repositories.
o Each metadata
repository can store multiple business models. Siebel Analytics Server can
access multiple repositories
”
What is the end to end life cycle of Siebel Analytics?
o Siebel Analytics
life cycle
1. Gather Business
Requirements
2. Identify source
systems
3. Design ETL to load
to a DW if source data doesn’t exist.
4. Build a repository
5. Build dashboard or
use answers for reporting.
6. Define security
(LDAP or External table…)
7. Based on performance,
decide on aggregations and/or caching mechanism.
8. Testing and QA.
”
What were you schemas? How does Siebel Architecture works? Explain the three
layers. How do you import sources?
o There are five
parts of Siebel Architecture.
1. Clients
2. Siebel analytics
Web Server
3. Siebel analytics
server
4. Siebel analytics
scheduler
5. data sorces
o Metadata that
represents the analytical Model Is created using the siebel Analytics
Administration tool.
o Repository divided
into three layer
1. Physical –
Represents the data Sources
2. Business – models
the Data sources into Facts And Dimension
3. Presentation –
Specifies the users view of the model;rendered in Siebel answer
” If
you have 3 facts and 4 dimension and you need to join would you recommend joining
fact with fact? If no than what is the option? Why you won’t join fact to fact?
o In the BMM layer,
create one logical table (fact) and add the 3 fact table as logical table
source
”
What is connection pool and how many connection pools did you have in your last
project?
o connection pool is
needed for every physical database.
o It contains
information about the connection to the database, not the database itself.
o Can use either
shared user accounts or can use pass-through accounts -Use: USER and PASSWORD
for pass through .
o We can have
multiple connection pools for each group to avoid waitin
”
Purpose of Alias Tables
o An Alias table
(Alias) is a physical table with the type of Alias. It is a reference to a
logical table source, and inherits all its column definitions and some
properties from the logical table source. A logical table source shows how the
logical objects are mapped to the physical layer and can be mapped to physical
tables, stored procedures, and select statements. An alias table can be a
reference to any of these logical table source types.
o Alias Tables can be
an important part of designing a physical layer. The following is a list of the
main reasons to create an alias table:” To reuse an existing table more than
once in your physical layer (without having to import it
several times) ” To
set up multiple alias tables, each with different keys, names, or joins
o To help you design
sophisticated star or snowflake structures in the business model layer. Alias
tables are critical in the process of converting ER Schemas to Dimensional
Schemas.
”
How do you define the relationship between facts and dimensions in BMM layer?
o Using complex join
,we can define relationship between facts and dimentions in BMM layer.
”
What is time series wizard? When and how do you use it?
o We can do
comparison for certain measures ( revenue.,sales etc.. ) for current year vs
previous year, we can do for month or week and day also
o Identify the time
periods need to be compared and then period table keys to the previous time
period.
o The period table
needs to contain a column that will contain “Year Ago” information.
o The fact tables
needs to have year ago totals.
o To use the “Time
series wizard”. After creating your business model right click the business
model and click on “Time Series Wizard”.
o The Time Series
Wizard prompts you to create names for the comparison measures that it adds to
the business model.
o The Time Series
Wizard prompts you to select the period table used for the comparison measures
o Select the column
in the period table that provides the key to the comparison period. This column
would be the column containing “Year Ago” information in the period table.
o Select the measures
you want to compare and then Select the calculations you want to generate. For
ex: Measure: Total Dollars and calculations are Change and Percent change.
o Once the Time
series wizard is run the output will be:
a) Aliases for the
fact tables (in the physical layer)
b) Joins between
period table and alias fact tables
c) Comparison
measures
d) Logical table
sources
o In the General tab
of the Logical table source etc you can find “Generated by Time Series Wizard”
in the description section
o Then you can add
these comparision measures to the presentation layer for your reports.
o Ex: Total sales of
current qtr vs previous qtr vs same qtr year ago
”
Did you create any new logical column in BMM layer, how?
o Yes. We can create new
logical column in BMM layer.
o Example: Right
click on fact table -new lgical column-give name for new logical column like
Total cost.
o Now in fact table
source,we have one option column mapping, in that we can do all calculation for
that new column.
”
Can you use physical join in BMM layer?
o yes we can use
physical join in BMM layer.when there is SCD type 2 we need complex join in BMM
layer.
”
Can you use outer join in BMM layer?
o yes we can.When we
are doing complex join in BMM layer ,there is one option type,outer join is
there.
”
What are other ways of improving summary query reports other than Aggregate
Navigation
and Cache Management
” Indexes
” Join algorithm
” Mat/view query
rewrite
” Web proper report
design its optimal by making sure that it is not getting any addition column or
rows
”
What is level-base matrics?
o Leval-base matrics
means, having a measure pinned at a certain level of the dimension. For
Example, if you have a measure called “Dollars”, you can create a “Level Based
Measure” called “Yearly Dollars” which (you guessed it) is Dollars for a Year.
This measure will always return the value for the year even if you drill down
to a lower level like quarter, month… etc. To create a level based measure,
create a new logical column based on the original measure (like Dollars in the
example above). Drag and drop the new logical column to the appropriate level
in the Dimension hierarchy (in the above example you will drag and drop it to
Year in Time Dim
o A LBM is a metric
that is defined for a specific level or intersection of levels.
o Monthly Total Sales
or Quarterly Sales are the examples.
o You can compare
monthly sales with quarterly sales. You can compare customer orders this
quarter to orders this year
”
What is logging level?Where can you set logging levels?
o You can enable
logging level for individual users; you cannot configure a logging level for a
group.
o Set the logging
level based on the amount of logging you want to do. In normal operations,
logging is generally disabled (the logging level is set to 0). If you decide to
enable logging, choose a logging
o level of 1 or 2.
These two levels are designed for use by Siebel Analytics Server
administrators.
o Set Logging Level
1. In the
Administration Tool, select Manage > Security.
2. The Security
Manager dialog box appears.
3. Double-click the
user.s user ID.
4. The User dialog
box appears.
5. Set the logging
level by clicking the Up or Down arrows next to the Logging Level field
”
What is variable in sieble?
o You can use variables
in a repository to streamline administrative tasks and modify metadata content
dynamically to adjust to a chainging data environment.The Administration Tool
includes a Variable Manager for defining variables
”
What is system variable and non system variable?
o System variables
o System variables
are session variables that the Siebel Analytics Server and Siebel Analytics Web
use for specific purposes. System variables have reserved names, which cannot
be used for other kinds of variables (such as static or dynamic repository variables,
or for nonsystem session variables).
o When using these
variables in the Web,preface their names with NQ_SESSION. For example, to
filter a column on the value of the variable LOGLEVEL set the filter to the
Variable NQ_SESSION.LOGLEVEL.
o Nonsystem variables.
o A common use for
nonsystem session variables is setting user filters. For example, you could
define a nonsystem variable called SalesRegion that would be initialized to the
name of the user.s sales region. You could then set a security filter for all
members of a group that would allow them to see only data pertinent to their
region.
o When using these
variables in the Web, preface their names with NQ_SESSION. For example, to
filter a column on the value of the variable SalesRegion set the filter to the
Variable NQ_SESSION.SalesRegion.
”
What are different types of variables? Explain each.
o There are two
classes of variables:
1. Repository
variables
2. Session variables.
Repository variables.
A repository variable
has a single value at any point in time. There are two types of repository
variables:
static : This value
persists, and does not change until a Siebel Analytics Server administrator
decides to change it.
dynamic:The values
are refreshed by data returned from queries. When defining a dynamic repository
variable, you will create an initialization block or use a preexisting one that
contains a SQL query. You will also set up a schedule that the Siebel Analytics
Server will follow to execute the query and periodically refresh the value of the
variable.
Session Variables
Session variables are
created and assigned a value when each user logs on. There are two types of
session variables:
1.system
2.nonsystem.
”
What are the cache management? Name all of them and their uses. For Event
polling
table
do u need the table in your physical layer?
o Monitoring and
managing the cashe is cache management.There are three ways to do that.
o Disable caching for
the system.(INI NQ config file), Cashe persistence time for specified physical
tables and Setting event polling table.
o Disable caching for
the system.(INI NQ config file :
You can disable
caching for the whole system by setting the ENABLE parameter to NO in the NQSConfig.INI
file and restarting the Siebel Analytics Server. Disabling caching stops all
new cache entries and stops any new queries from using the existing cache.
Disabling caching allows you to enable it at a later time without losing any
entries already stored in the cache.
o Cashe persistence
time for specified physical tables :
You can specify a
cachable attribute for each physical table; that is, if queries involving the specified
table can be added to the cache to answer future queries. To enable caching for
a particular physical table, select the table in the Physical layer of the
Administration Tool and select the option Make table cachable in the General
tab of the Physical Table properties dialog box. You can also use the Cache
Persistence Time settings to specify how long the entries for this table should
persist in the query cache. This is useful for OLTP data sources and other data
sources that are updated frequently, potentially down to every few seconds.
o Setting event
polling table :
Siebel Analytics
Server event polling tables store information about updates in the underlying databases.
An application (such as an application that loads data into a data mart) could
be configured to add rows to an event polling table each time a database table
is updated. The Analytics server polls this table at set intervals and invalidates
any cache entries corresponding to the updated tables.
o For event polling
table ,It is a standalone table and doesn’t require to be joined with other tables
in the physical layer
”
What is Authentication? How many types of authentication.
o Authentication is
the process by which a system verifies, through the use of a user ID and password,
that a user has the necessary permissions and authorizations to log in and
access data. The Siebel Analytics Server authenticates each connection request
it receives.
” Operaing system
autentication
” External table
authentication
” Database
authentication
” LDAP authentication
”
What is object level security?
o There are two types
of object level security: Repository level and Web level
o Repository level :
In presention layar we can set Repository level security by giving permission or
deny permission to users/groups to see particular table or column.
o web
level:thisprovides security for objects stored in the siebel anlytics web
catlog,such as dashboards,dashboards pages,folder,and reportsyou can only view
the objects for which you are authorized. For example,a mid level manager may
not be granted access to a dashboard containing summary information for an
entire department.
”
What is data level security?
o This controls the
type an amount of data that you can see in a report.When multiple users run the
same report the results that are returned to each depend on their access rights
and roles in the organization.For example a sales vice president sees results
for alll regions, while a sales representative for a particular region sees
onlu datafor that region.
”
What is the difference between Data Level Security and Object Level Security?
o Data level security
controls the type and amount of data that you can see in a
reports.Objectlevel
security provides security for objects stored in the siebel analytics web catlog,
like dashboards,dashboards pages,folder,and reports.
”
How do you implement security using External Tables and LDAP?
o Instead of storing
user IDs and passwords in a Siebel Analytics Server repository, you can maintain
lists of users and their passwords in an external database table and use this
table for authentication purposes. The external database table contains user
IDs and passwords, and could contain other information, including group
membership and display names used for Siebel Analytics Web users. The table
could also contain the names of specific database catalogs or schemas to use
for each user when querying data
o Instead of storing
user IDs and passwords in a Siebel Analytics Server repository, you can have the
Siebel Analytics Server pass the user ID and password entered by the user to an
LDAP(Lightweight Directory Access Protocol ) server for authentication. The
server uses clear text passwords in LDAP authentication. Make sure your LDAP
servers are set up to allow this.
” If
you have 2 fact and you want to do report on one with quarter level and the
other with month level how do you do that with just one time dimension?
o Using levelbase
matrics.
”
Did you work on a stand alone Siebel system or was it integrated to other
platforms?
o Deploying the
Siebel analytics platform without other Siebel applications is called Siebel analytics
Stand -Alone .If your deployment includes other siebel Analytics Application it
called integrated analytics -You can say Stand-Alone siebel analytics
”
How to sort columns in rpd and web?
o Sorting on web
column, sort in the rpd its sort order column
” If
you want to create new logical column where will you create (in repository or
dashboard)
why?
o I will create new
logical column in repository.because if it is in repository,you can use for any
report. If you create new logical column in dashboard then it is going to
affect on those reports , which are on that dashboard.you can not use that new
logical column for other dashboard(or request)
”
What is complex join, and where it is used?
o we can join
dimention table and fact table in BMM layer using complex join.when there is SCD
type 2 we have to use complex join in Bmm layer.
” If
you have dimension table like customer, item, time and fact table like sale and
if you want to find out how often a customer comes to store and buys a
particular item, what will you do?
o write a query as
“SELECT customer_name, item_name, sale_date, sum(qty) FROM
customer_dim a,
item_dim b, time_dim c, sale_fact d WHERE d.cust_key = a.cust_key AND d.item_key
= b.item_key AND d.time_key = c.time_key GROUP BY customer_name, item_name,
sale_date”
”
You worked on standalone or integrated system?
o Standalone.
” If
you want to limit the users by the certain region to access only certain data,
what would you do?
o using data level
security.
o Siebel Analytics
Administrator: go to Manage -> Security in left hand pane u will find the
user, groups, LDAP server, Hierarchy
What you can do is
select the user and right click and go to properties, you will find two tabs named
as users and logon, go to user tab and click at permission button in front of
user name you have selected as soon as u click at permission you will get a new
window with user group permission having three tabs named as general ,query
limits and filter and you can specify your condition at filter tab, in which
you can select presentation table ,presentation columns ,logical table and
logical columns where you can apply the condition according to your requirement
for the selected user or groups.
” If
there are 100 users accessing data, and you want to know the logging details of
all the users, where can you find that?
o To set a user.s
logging level
1. In the
Administration Tool, select Manage > Security.
The Security Manager
dialog box appears.
2. Double-click the
user.s user ID. The User dialog box appears.
3. Set the logging
level by clicking the Up or Down arrows next to the Logging Level field
”
How do implement event polling table?
o Siebel Analytics
Server event polling tables store information about updates in the underlying databases.
An application (such as an application that loads data into a data mart) could
be configured to add rows to an event polling table each time a database table
is updated. The Analytics server polls this table at set intervals and
invalidates any cache entries corresponding to the updated tables.
” Can you migrate the
presentation layer only to different server
o No we can’t do only
presentation layer. And ask him for more information and use one of the above
answers
o Create a ODBC
connection in the different serve and access the layer.
o Copy the Rpd and
migrate it to other server
”
Define pipeline. Did you use it in your projects?
o Yes, pipelines are
the stages in a particular transaction. assessment, finance etc.
”
How do you create filter on repository?
o Where condition on
content tab.
”
How do you work in a multi user environment? What are the steps?
o Create a shared
directory on the network for Multi-user Development (MUD).
o Open the rpd to use
in MUD. From Tools->Options, setup the MUD directory to point to the above
directory.
o Define projects
within the rpd to allow multiple users to develop within their subject area or Facts.
o Save and move the
rpd to the shared directory setup in point 1.
o When users work in
the MUD mode, they open the admin tool and start with
o MUD ->Checkout
to checkout the project they need to work on (not use the File open as you would
usually do).
o After completely
the development, user checkin the changes back to the network and merge the changes.
”
Where are passwords for userid? Ldap,external table authentication stored
respectively?
o passwords for userid
are in siebel analytics server repository Ldap authentication in Ldap server
external database in a table in external database
”
Can you bypass siebel analytics server security ?if so how?
o yes you can by-pass
by setting authententication type in NQSCONFIG file in the security section
as:authentication_type=bypass_nqs.instanceconfig.xml and nqsconfig.ini are the
2 places
” Where can you add
new groups and set permissions?
o you can add groups
by going to manage>security>add new groups> You can give permissions to
a group for query limitation and filter conditions.
”
what are the things you can do in the BMM layer?
o Aggrigation
navigation,level base matrics,time series wizard,create new logical
column,comlex join.
”
what is Ragged hierarchy? and how do u manage it
o Ragged Hierarchy is
one of the different kinds of hierarchy.
o A hierarchy in
which each level has a consistent meaning, but the branches have inconsistent depths
because at least one member attribute in a branch level is unpopulated. A
ragged hierarchy can represent a geographic hierarchy in which the meaning of
each level such as city or country is used consistently, but the depth of the
hierarchy varies.
o For example, a
geographic hierarchy that has Continent, Country, Province/State, and City levels
defined. One branch has North America as the Continent, United States as the
Country, California as the Province or State, and San Francisco as the City.
However, the hierarchy becomes ragged when one member does not have an entry at
all of the levels. For example, another branch has Europe as the Continent,
Greece as the Country, and Athens as the City, but has no entry for the
Province or State level because this level is not applicable to Greece for the business
model in this example. In this example, the Greece and United States branches
descend to different depths, creating a ragged hierarchy.
”
What is the difference between Single Logical Table Source and Multiple Logical
Table
Sources?
o If a logical table
in BMM layer has only one Table as the source table then it is Single LTS.
o If the logical
table in BMM layer has more than one table as the sources to it then it is
called Multiple LTS.
o Ex: Usually Fact
table has Multiple LTS’, for which sources will be coming from different Physical
tables.
” Can you let me know
how many aggregate tables you have in your project? On what basis have you
created them?
o As per resume
justification document
”
How do you bring/relate the aggregate tables into the Siebel analytics Logical
layer?
o One way of bringing
the Aggregate Tables into the BMM layer is by bringing them as Logical Table
sources for the corresponding Fact table.
o This is done by
dragging and dropping the aggregate table into the corresponding fact table. After
doing that establish the column mappings and the set the aggregation levels.
”
How do you know which report is hitting which table, either the fact table or
the
aggregate
table?
o After running the
report, go to “Administration” tab and go to click on “Manage Sessions”. There
you can find the queries that are run and in the “View Log” option in the
Session Management you can find which report is hitting which table.
”
Suppose I have report which is running for about 3 minutes typically. What is
the first step you take to improve the performance of the query?
o Find the sql query
of the report in Admin->manage Session-> run the sql query on toad
->read the explain plan output ->modify the SQL based on the explain plan
output
”
Suppose you have a report which has the option of running on aggregate table.
How does the tool know to hit the Aggregate table and for that what the steps
you follow to configure them?
o Explain the process
of Aggregate navigation
” Have you heard of
Implicit Facts? If, so what are they?
o An implicit fact
column is a column that will be added to a query when it contains columns from
two or more dimension tables and no measures. You will not see the column in
the results. It is used to specify a default join path between dimension tables
when there are several possible alternatives.
o For example, there
might be many star schemas in the database that have the Campaign dimension and
the Customer dimension, such as the following stars:
” Campaign History
star. Stores customers targeted in campaign.
” Campaign Response
star. Stores customer responses to a campaign.
” Order star. Stores
customers who placed orders as a result of a campaign.
In this example,
because Campaign and Customer information might appear in many
segmentation
catalogs, users selecting to count customers from the targeted campaigns
catalog would be expecting to count customers that have been targeted in
specific campaigns. ” To make sure that the join relationship between Customers
and Campaigns is through the campaign history fact table, a campaign history
implicit fact needs to be specified in Campaign History segmentation catalog.
The following guidelines should be followed in creating
” segmentation
catalogs:
” Each segmentation
catalog should be created so that all columns come from only one physical star.
” Because the
Marketing module user interface has special features that allow users to
specify their aggregations, level-based measures typically should not be
exposed to segmentation users in a segmentation catalog.
”
What is aggregate navigation? How do you configure the Aggregate tables in
Siebel Analytics?
o Aggregate tables
store precomputed results, which are measures that have been aggregated (typically
summed) over a set of dimensional attributes. Using aggregate tables is a very
popular technique for speeding up query response times in decision support
systems.
o If you are writing
SQL queries or using a tool that only understands what physical tables exist (and
not their meaning), taking advantage of aggregate tables and putting them to
good use becomes more difficult as the number of aggregate tables increases.
The aggregate navigation capability of the Siebel Analytics Server, however,
allows queries to use the information stored in aggregate tables automatically,
without query authors or query tools having to specify aggregate tables in
their queries. The Siebel Analytics Server allows you to concentrate on asking
the right business question; the server decides which tables provide the
fastest answers.
”
(Assume you are in BMM layer) We have 4 dimension tables, in that, 2 tables
need to have hierarchy, then in such a case is it mandatory to create
hierarchies for all the dimension tables?
o No, its not
mandatory to define hierarchies to other Dimension tables.
”
Can you have multiple data sources in Siebel Analytics?
o Yes.
”
How do you deal with case statement and expressions in siebel analytics?
o use expression
builder to create case when…then.. end statement
” Do
you know about Initialization Blocks? Can you give me an example where you used
them?
o Init blocks are
used for instantiating a session when a user logs in.
o To create dynamic
variable you have to create IB to write sql statement.
”
what is query repository tool?
o It is utility of
Seibel/OBIEE Admin tool
o allows you to
examine the repository metadata tool
o for example: search
for objects based on name,type.
o Examine
relationship between metadata objects like which column in the presentation layer
maps to which table in physical layer
”
what is JDK and why do we need it?
o Java Development
Kit (JDK), A software package that contains the minimal set of tools needed to
write, compile, debug, and run Java applets.
”
Oracle doesn’t recommend Opaque Views because of performance considerations, so
why/when do we use them?
o an opaque view is a
physical layer table that consists of select statement. an opaque view should
be used only if there is no other solution.
”
Can you migrate the presentation layer to a different server.
o No we have to
migrate the whole web & rpd files
”
How do you identify what are the dimension tables and how do you decide them
during the Business/Data modeling?
o Dimension tables
contain descriptions that data analysts use as they query the database. For example,
the Store table contains store names and addresses; the Product table contains
product packaging information; and the Period table contains month, quarter,
and year values. Every table contains a primary key that consists of one or
more columns; each row in a table is uniquely identified by its primary-key
value or values
”
Why do we have multiple LTS in BMM layer?What is the purpose?
o to improve the
performance and query response time.
”
what is the full form of rpd?
o there is no full
form for rpd as such, it is just a repository file (Rapidfile Database)
”
how do i disable cache for only 2 particular tables?
o in the physical
layer, right click on the table there we will have the option which says
cacheable
”
How do you split a table in the rpd given the condition. ( the condition given
was Broker and customer in the same table) Split Broker and customer.
o we need to make an
alias table in the physical layer.
”
What type of protocol did you use in SAS?
o TCP/IP
No comments:
Post a Comment