OBIEE Interview Questions & Answers

2012-02-14T17:27:00.000+05:30

Define repository in terms of OBIEE?

Repository stores the Meta data information. The extension of the repository file is “.rpd”. With OBIEE Server, all the rules needed for security, data modeling, aggregate navigation, caching, and connectivity is stored in metadata repositories. Each metadata repository can store multiple business models. OBIEE Server can access multiple repositories

Repository is divided into three layer,
1. Physical – Represents the data Sources
2. Business – model the Data sources into Facts and Dimension and apply business logic
3. Presentation – Specifies the user’s view of the data rendered in OBIEE answers client

What is the end to end life cycle of OBIEE?
OBIEE life cycle:
1. Gather Business Requirements
2. Identify source systems
3. Design ETL to load data to the Data Warehouse
4. Build a repository
5. Build dashboards and reports
6. Define security (LDAP or External table)
7. Based on performance, decide on aggregations and/or caching mechanism.
8. Testing and QA.

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?
Instead of joining fact with fact we can create one logical table (fact) and add the 3 fact tables as logical table source in the BMM layer.

What is connection pool and how many connection pools did you have in your last project?
Connection pool is needed for every physical database.
It contains information about the connection to the database, not the database itself.
Can use either shared user accounts or can use pass-through accounts
We can have multiple connection pools for each group to avoid waiting

What is the purpose of Alias Tables?
An Alias table is a physical table with the type of Alias. It is a reference to a physical table, and inherits all its column definitions and some properties from the physical table. 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.
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:
1. To reuse an existing table more than once in your physical layer (without having to import it several times)
2. To set up multiple alias tables, each with different keys, names, or joins
3. 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?
Using complex join we can define relationship between facts and dimensions in BMM layer.

Did you create any new logical column in BMM layer, how?
Yes. We can create new logical column in BMM layer.
Example: Right click on fact table> select new logical column>give name for new logical column like Total cost.

Can you use physical join in BMM layer?
Yes we can use physical join in BMM layer.

Can you use outer join in BMM layer?
Yes we can. When we are doing complex join in BMM layer in the join properties we have an option to change the type of join to left outer, right outer, full outer or inner.

What is level based metrics?
Level based metrics means, having a measure pinned at a certain level of the dimension.

A LBM is a metric that is defined for a specific level or intersection of levels.
Monthly Total Sales or Quarterly Sales are the examples.

Ex: if you have a measure called “Dollars”, you can create a “Level Based Measure” called “Yearly Dollars” which 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.

What is logging level? Where can you set logging levels?
You can enable logging level for individual users.
We can 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 level of 1 or 2. These two levels are designed for use by Siebel Analytics Server administrators.
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 are different types of variables? Explain each.
There are two classes of variables:
1. Repository variables
2. Session variables
Repository variables-
A repository variable persists from the time the repository is started.
Static: This value does not change until a Siebel Analytics Server administrator decides to change it.
Dynamic: The values of these variables change with the values returned by queries. These variables are linked to an initialization block. An initialization block contains a SQL query. When the repository is started the value returned by the query in the initialization block will be assigned to the dynamic variable.
Session Variables-
Session variables are created and assigned a value when each user logs on(when a new session is started). There are two types of session variables:
1. System: System variables have reserved names, which cannot be used for other kinds of variables (such as static or dynamic repository variables, or for non-system session variables).

Ex: To filter a column on the value of the variable LOGLEVEL set the filter to the Variable NQ_SESSION.LOGLEVEL.
2. Non-system: A common use of non-system variables is for setting user filters. Ex: you could define a non-system 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. For example, to filter a column on the value of the variable ‘SalesRegion’ set the filter to the Variable NQ_SESSION.SalesRegion.

What is Authentication? How many types of authentication do we have in OBIEE?
Authentication is the process by which a system verifies a user with the help of a user ID and password. It checks if user has the necessary permissions and authorizations to log in and access data. There are 4 main types of Authentication in OBIEE:
Operating system authentication
External table authentication
Database authentication
LDAP authentication

What is object level security?
There are two types of object level security: Repository level and Web level
Repository level: In presentation layer we can set Repository level security by giving permission or deny permission to users/groups to see particular table or column.
Web level: this provides security for objects stored in the OBIEE web catalog, such as dashboards, dashboards pages, folder, and reports you 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?
This controls the type or 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 all regions, while a sales representative for a particular region sees data for that region.

What is the difference between Data Level Security and Object Level Security?
Data level security controls the type and amount of data that you can see in a report. Object level security provides security for objects stored in the OBIEE web catalog like dashboards, dashboards pages, folder and reports.

How do you implement security using External Tables and LDAP?
Instead of storing user IDs and passwords in a OBIEE 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.
Instead of storing user IDs and passwords in a OBIEE Server repository, you can have the OBIEE 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 base metrics.

If you want to create new logical column where will you create (in repository or dashboard) why?
It would be better if we create a new logical column in repository because if it is in repository you can use it for any report. If you create new logical column in dashboard then it is going to affect only those reports which are on that dashboard. We cannot use that new logical column for other dashboards.

What is complex join, and where it is used?

To join a dimension table and fact table in BMM layer we use a complex join.

If you want to limit the users by the certain region to access only certain data, what would you do?
Using data level security.
In the Admin tool: 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?
Set the 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?
In OBIEE Server event polling tables store information about updates in the underlying databases. These tables are used for cache management. 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 cache entries corresponding to the updated tables.

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

How do you work in a multi user environment? What are the steps?
Create a shared directory on the network for Multi-user Development (MUD).
Open the rpd to use in MUD. From Tools->Options, setup the MUD 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 ->Checkout to check-out the project they need to work on (not use the File open as you would usually do).
After completing the development, users check-in the changes back to the network and merge the changes.

Can you bypass siebel analytics server security ?if so how?
Yes we can by-pass by setting authentication type in NQSCONFIG file in the security section as: authentication_type=bypass_nqs.instanceconfig.xml.

What are the things you can do in the BMM layer?
We apply the business login in the BMM layer like creating new logical columns, Aggregation navigation, level based metrics, time series wizard, complex join.

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 LTSs, for which sources will be coming from different Physical tables.

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 in the content tab of the LTS.

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-> view log then run the SQL query on toad if it takes the same time even in TOAD then modify the query to reduce the query time. If the query runs normally in TOAD then we need to make changes in the repository.

What is aggregate navigation? How do you configure the Aggregate tables in OBIEE?
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.
Ex: If you observe that most of the queries are fired on aggregated values like yearly or monthly data rather that day level data then to speed up the report we can create an aggregate table containing month level and year level data and map it to the respective logical table and set the aggregation levels in the content tab of LTS.

We have 4 dimension tables, in that 2 tables need to have hierarchies then in such a case is it mandatory to create hierarchies for all the dimension tables?
No, it is not mandatory to define hierarchies to other Dimension tables.

Can you have multiple data sources in OBIEE?
Yes, we can have multiple data sources of different types.

Do you know about Initialization Blocks? Can you give me an example where you used them?
Initialization blocks are used for instantiating the value of a variable.
To create dynamic variable you have to create IB to write SQL statement. The result of the query would be assigned as value to the variable.

What is query repository tool?
It is utility of OBIEE Admin tool
It allows you to examine the repository metadata
Examine relationship between metadata objects like which column in the presentation layer maps to which table in physical layer

Ex: we can search for objects based on name, type.

Can you migrate the presentation layer to a different server.
No, we cannot migrate only one particular layer to another server, we have to migrate the complete repository or the web catalog.

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. Ex: the Product table contains product packaging information; and the Period table contains month, quarter, and year values. The tables which contain numeric values used for calculations are used as Fact tables.

Why do we have multiple LTS in BMM layer? What is the purpose?
To improve the performance and query response time.

How do i disable cache for only 2 particular tables?
In the physical layer, double click on a table on the general tab we have an option Cachable we can either enable it or disable it for that 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 create alias tables in the physical layer and use which ever columns are required.

DW & BI Interview Questions

OBIEE Interview Questions & Answers