OBIEE Interview Questions and Answers

How do you implement security using External Tables and LDAP?
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
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?
Using Level-based metrics

Did you work on a standalone Siebel system or was it integrated to other platforms?
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?
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?
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 cannot use that new logical column for other dashboard (or request)

What is complex join, and where it is used?
We can join dimension 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?
Write a query like

“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?
Stand-alone.

If you want to limit the users by the certain region to access only certain data, what would you do?
Using data level security.
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?
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?
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?
No we can’t do only presentation layer. And ask him for more information and use one of the above answers
Create an ODBC connection in the different server and access the layer.
Copy the rpd and migrate it to other server

Define pipeline. Did you use it in your projects?
Yes, pipelines are the stages in a particular transaction. Assessment, finance, etc.,

How do you create filter on repository?
Where condition on content tab in BMM Layer.

How do you work in a multi user environment? What are the steps?
Create a shared directory on the network for Multi-user Development Environment (MUDE).
Open the rpd to use in MUDE. From Tools->Options, setup the MUDE directory to point to the above directory.
Define projects within the rpd to allow multiple users to develop within their subject area or Facts.
Save and move the rpd to the shared directory setup in point 1.
When users work in the MUD mode, they open the admin tool and start with
MUD ->Check-out to check out the project they need to work on (not use the File open as you would usually do).
After completely the development, users check-in the changes back to the network and merge the changes.

Where are passwords for USERID? LDAP, external table authentication stored respectively?
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?
Yes, you can by-pass by setting authentication type in NQSCONFIG file in the security section as: AUTHENTICATION_TYPE = BYPASS_NQS

Where can you add new groups and set permissions?
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?
Aggregation navigation, level base metrics, time series wizard, create new logical column, complex join.

What is ragged hierarchy? And how do you manage that?
Ragged Hierarchy is one of the different kinds of hierarchy.
A hierarchy in which each level has a consistent meaning, but the branches have inconsistent depths because at least one member attributes 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.
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?
If a logical table in BMM layer has only one Table as the source table then it is Single LTS.
If the logical table in BMM layer has more than one table as the sources to it then it is called Multiple LTS.
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?
Justify the answer as per your resume.

How do you bring/relate the aggregate tables into the Siebel analytics Logical layer?
One way of bringing the Aggregate Tables into the BMM layer is by bringing them as Logical Table sources for the corresponding Fact table.
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?
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?
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?
Explain the process of Aggregate navigation

Have you heard of Implicit Facts? If so what are they?
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.
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?
Aggregate tables store pre-computed 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.
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, and then in such a case is it mandatory to create hierarchies for all the dimension tables?
No, it’s not mandatory to define hierarchies to other Dimension tables.

Can you have multiple data sources in Siebel Analytics?
Yes.

How do you deal with case statement and expressions in Siebel analytics?
Use expression builder to create case when…then... End statement

Do you know about Initialization Blocks (IB)? Can you give me an example where you used them?
Initialization blocks are used for instantiating a session when a user logs in.
To create dynamic variable, System and non-system session variable you have to create IB to write SQL statement.

What is query repository tool?
It is utility of Seibel/OBIEE Admin tool
allows you to examine the repository metadata tool
for e.g. search for objects based on name, type.
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?
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?
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?
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?
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?
To improve the performance and query response time.

What is the full form of rpd?
There is no full form for rpd as such; it is just a repository file (Rapid file Database)

How do you disable cache for only 2 particular tables?
In the physical layer, right click on the table. You will get table properties window there we will have the option which says cacheable. You have to unselect the check option so the cache will be disabled for the particular table.

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.
We need to make an alias table in the physical layer.