Interview questions for OBIEE11g

Q1 Define the components of Physical layer.

The physical layer consists of 3 objects:

1. Database Object : It is the highest level object in the Physical layer. It defines the data source to which Oracle BI Server submits queries. Importing a schema automatically creates a database object for the schema in the Physical layer.

2. Connection pool: For each data source there is atleast one connection pool. The connection pool contains information about the connection between Oracle BI Server and a data source. The connection pool contains the data source name, the no of connections allowed, time out information and other connectivity-related administrative details. Connection pools allow multiple concurrent data source requests(queries) to share a single database connection, reducing the overhead of connecting to a database. You can create multiple connection pools to improve the performance for groups of users.  

It is recommended that you use a dedicated connection pool for initialization blocks. This is because when a user logs in, the init blocks are fired to populate session variables. If same connection pool is used for init blocks as well as data queries, then the limit of max connections will be reached very soon and there may be case where new users may be unable to login.

3. Schema Folder: It contains tables and columns for a physical schema

Q2 Why are aliases used?

Aliases are used to avoid circular references. A common use of aliases is role-playing dimensions where a single dimension simultaneously appears several times in the same fact table. e.g a order date and shipping date in a fact table may both point to same date column in a physical dimension table but each date is derived from a separate alias.

Q3 What is a logical table source?

Logical table source define the mapping between a logical table and physical table. A logical table source can have many physical table sources. In such a case, a logical table source act as a database view querying from more than 1 table. e.g Revenue information may come from different physical tables. This can be represented in a single logical table. The logical table source will be mapped to these multiple tables.

There can also be more than 1 logical table sources for a logical table. So, when data is not duplicated across many physical tables, we can model multiple physical sources under 1 Logical table sources. When data is duplicated in physical sources (e.g same column is present in 2 physical tables), then we can have more than 1 LTS under same logical table. BI Server will decide the most economical source while forming the query.

For each logical table source, we can choose "Aggregation Content, Group By" value in the "Content" tab. This defines grain or the context in which this LTS will be used. It can have 2 values: By Column or By Level. When a dimension hierarchy is not created, we have only 1 option "By Column". We select the column in the drop down which provides the context. When a dimension hierarchy is created, we should select 'By level' and provide the logical level.

Q4. What is an implicit fact column and why it is used?

There may be requirements where a user needs to build a report containing columns only from dimension tables.  However many fact tables may be joined to multiple dimensions. So, when the dimension-only query is run, BI Server may choose most economical fact table and display results. But the user may be expecting results from a join to a different fact table. For such situations, Implicit fact column is used. Its a column that is automatically added to dimension-only queries. It forces the BI Server to select a pre-determined fact table source even if its not the most economical source. It provides a default join path between dimension tables when there are several possible alternatives. Implicit column is set for a subject area in Presentation layer.

Q5. Can we drag contents in a Subject Area from multiple business models?

No. A single subject area must be populated with content from a single business model. It can not span multiple business models. Multiple subject areas can reference the same business model.

Q6. What is Global Consistency Check?

Its a feature of Administration tool that checks whether a repository has met certain requirements such as:

a. All logical columns are mapped directly or indirectly to one or more physical columns.

b. All logical dimension tables have a logical key

c. All logical tables have a logical join relationship to another logical table

d. There are atleast 2 logical tables in the business model: a logical fact table and a logical dimension table.          Both can map to the same physical table

e. There are no circular join relationships.

f.  A subject area exists for the business model

Q7 How logging level is set in OBIEE?

Logging level is set in Repository file through Admin tool. Open the repository online and go to Manage>Identity>BIRepository>Users . Select Action>Set Online User Filter and retrieve the username for which you want to set the logging level. Double click the user name and set the logging level.

If you want to set the same logging level for all the users, then use the system session variable LOGLEVEL. You can set it through an initialization block.

Q8. What are the different types of logical dimensions?

There are 2 types: dimensions with level based hierarchies(structure hierarchies) and dimensions with parent-child hierarchies(value hierarchies). level-based hierarchies are those in which members of the same type occur only at a single level, while members in a parent-child hierarchies all have the same type.

Level-based hierarchies can inturn be of 3 types: Unbalanced(ragged), skip-level and Time hierarchies.

Unbalanced hierarchy is the hierarchy in which the leaves do not necessarily have the same depth. A skip-level hierarchy is a hierarchy in which there are members that do not have a value for a particular ancestor level. e.g in a Country-State-City hierarchy, the city Chandigarh does not belong to a state. Its a union territory. So you can drill down from Country level(India) to the city level(Chandigarh) directly. Time hierarchies are used for modeling time-series data. 

Q9. What are the different types of aggregation that can be applied to measures?

You can either apply "Default Aggregation" rules or "Dimension-Specific" rules. Usually, the default aggregation rule is specified most of the times for fully additive or non-additive measures.

There may be cases where a measure can be added only for some dimensions(semi-additive measures) but not for others e.g bank balances might be averaged over time but summed over the individual accounts

In such cases, we specify the aggregation as "based on dimensions". Yoy can select the dimension on which the aggregation will be applied. you can also select "Others" to specify the aggregation rule for other dimensions.

Q10 What is the use of setting "Number of Elements"?

The number of elements is used by BI Server when picking aggregate sources. Setting the number of elements is necessary when there are 2 or more aggregate sources that can be used by BI server. Aggregate Sources are selected based on the combination of the fields selected as well as the number of elements of the levels in the logical dimensions to which they map. The number does not need to be exact but ratios of numbers from one logical level to another should be accurate.

Q11 What is the use of Aggregate persistence Wizard?

OBIEE provides an aggregate persistence feature that automates the creation and loading of the aggregate tables and their corresponding OBIEE metadata mappings.

Q12  You have 2 physical tables in your schema: ORDERS_HISTORICAL and ORDERS_RECENT. Both tables contain the same columns. Historical table contains all data prior to a given Order Date. The Recent table contains all the data after the given Order date. How will you model these tables in BMM layer and how the BI server will choose when to select which table?

We will use "Fragmentation Content" feature. In BMM layer, we specify the fragment for both logical table sources. Lets say the given order date is 31-Dec-2013'. We have an PERIOIDKEY column in the tables with value as 20131231. Assume that Business Model name is SupplierSales and Logical Fact tbale name Fact-Sales

So we will specify the fragments as :

For Historical LTS : "SupplierSales"."Fact-Sales"."Order Date" <= '20121231'
For Recent LTS: "SupplierSales"."Fact-Sales"."Order Date" > '20121231'  

Also tick "This source should be combined with other sources at this level"

Now BI server will know to include which table in the final query. If no filter on Order Date is give, BI Server will combine data from both sources automatically (It will do a UNION ALL).

Q13. Define the various kinds of variables in OBIEE

There are 4 classes of variables: Repository, Session, Presentation and Request Variables

A Repository variable has a single value at any point in time. They can be used instead of Constants. Their value persist from the time Orcale BI server is started till the time it is shut down. They are of 2 types: Static and Dynamic. 

Static variables are those variables whose values are initialized once and remains constant. 

Dynamic Variables are the variables whose value is dynamically initialized through an Initialization Block using an SQL query. This value can be periodically refreshed by specifying a refresh schedule. A common use of these variables is to set column filters in reports. e.g to filter a report based on Current Month, we can create a dynamic repository variable CurrentMonth and use it as a filter.

Session variables are created and assigned a value when each user logs on. Like Repository Dynamic variables, their values are assigned by Initialization blocks but unlike repository variables, this initialization is not scheduled. When a user begins a session, Oracle BI Server creates new instances of session variables and initializes them. There are as many instances of session variables as there are active sessions. on BI server. There are 2 types of session variables: System and Non-System

System Session variables are pre-defined session variables used by BI Server for specific purposes such as authenticating users. They have reserved names. e.g USER which contains the value that the user entered as logon name, GROUP which contains the group name that a user belongs to.

Non-System session variables are created for specific application. A common use is for setting filters. e.g a non-system session variable can be created and initialized to user's sales region. A security filter can then be set to filter rows for a particular user's region.

Presentation Variables are created in Presentation services using dashboard prompts. There are 2 types of dashboard prompts - Column Prompt and Variable Prompt. A presentation variable created as part of column prompt is associated with a column and the values it takes come from column values.

A presentation variable defined with a variable prompt takes the values which the person who created the prompt, provides. This value doesn't comes from a table or a view. The variable prompt just holds a value and compares it to the value passed.

Request Variable A request variable lets you override the value of a session variable but only for the duration of a database request initiated from a column prompt. You can create a request variable as part of the process of creating a column prompt.

A request variable that is created as part of a column prompt is associated with a column, and the values that it can take come from the column values.


Q14 What is the difference between default users - weblogic and BISystemUser ?

weblogic is the administrative user. After installation, a single administrative user is shared between Oracle BI and Weblogic Server. It is the member of BIAdministrators group but it can not impersonate other users

BISystemUser is used to by BI system components to establish a connection to each other. 

Q15 What is Usage Tracking and how it is enabled?

Usage tracking is a method to track and store BI Server usage at the detailed query level. The statistics gathered can be utilized for database performance optimization, aggregation strategies or billing users and departments based on the resources they consume. There are 2 methods for enabling usage tracking:

a. Direct Insertion: Oracle BI Server inserts statistics for every query directly into a relational database table

b. Log file: Oracle BI Server inserts statistics for every query into a log file

In case of Direct Insert,  database table (S_NQ_ACCT) is created using a SQL script and NQSConfig.ini is edited to make [USAGE_TRACKING] parameter ENABLE=YES. Also the parameter DIRECT_INSERT is set as YES.

Q16 Why it is recommended to use separate connection pool for initialization blocks?

When a user logs in, init blocks are run against database to populate session variables. Each will use a connection. If the same connection pool is used for both data queries and init blocks, the limit of max connections will be used up very soon and can result in new users not able to login as init block queries are queuing up. Hence it is recommended to have dedicated connection pool for initialization blocks 

Q17 What is the difference between Filters and Selection Steps?

Filters are applied before the query is aggregated but Selection Steps are applied after the query is aggregated. So, filters may change the resulting values of aggegation but selection step will change on the members or rows displayed.

Q18 What is the significance of Content tab of LTS?

The Content Tab is used to specify the grain at which information is stored in a LTS. For a Fact Table LTS, the content level is set for a measure for each dimension. This helps OBIEE to understand at what level the fact is joined to the dimension. Setting the totals level on the Content tab of the LTS is meaningless and is identical to leaving it blank. This helps OBIEE to join a fact with non-conformed dimension. The content tab helps OBIEE to select the right LTS. e.g it tells OBIEE that a LTS at the Month level is beter than the LTS at the Day level.

Q19 What is the use of BI Java Host system component in OBIEE11g?

BI Java Host gives the Presentation Services the ability to use functionality that is provided in Java libraries to support graph generation, style and skin, charts etc

Q20 What are different types of authentications in OBIEE?

OBIEE supports 4 types of authentication:

a. LDAP Authentication: An LDAP server is used to authenticate users in OBIEE. In weblogic console, an additional authentication provider can be set up using the LDAP details

b. External table Authentication: If the list of users and password are maintained in a database table, then this table can be used for authentication. 

c. Database Authentication: Oracle BI Server can authenticate users based on database logins. If a user has read permissions on specific database, BI Presentation Services can authenticate that user.

d. Oracle BI Server User Authentication: Using Admin tool, a list of users and their passwords can be maintained in BI Repository. The BI server will authenticate the users against this list.