OBIEE 11g Interview Questions & Answers.

1. Tell me your experience in OBIEE, along with your roles & responsibility in projects?

• I'm bla bla bla.!! I'm working for bla blah company from this much of time period as an OBIEE consultant/developer. So far, I have been involved in multiple implementations of OBIEE 11g as well as 10g also (If you so.!!). The main domains I have worked on are this & this. (Tell only the domains on which you have worked on, e.g. Health Care, Data Mining, and Retail). Apart from this, I have some exposure on OBI Apps installations also.
• Here, even you can mention like on how many projects you have implemented from the scratch. If you have BI Publisher experience for any particular project, mention it along with the project name/domain.

2. What is Business Intelligence?

• Business Intelligence, on the other hand, is simply the art and science of presenting historical data in a meaningful way (often by using different data visualization techniques).
• Raw data stored in databases turns into valuable information through the implementation of Business Intelligence processes.

3. What is OBIEE?

• OBIEE is an application that implements the concept of business intelligence by the means of slicing & dicing the huge volumes of data across numbers of data sources, and as a result, it creates a platform of business information that enables users to query valuable information as a web application which a centralized and highly interactive.
• From  a pure technical perspective, OBIEE utilizes Kimball data modelling philosophy in its implementation that enables users to analyse and monitor their business information through the uses of reports, charts, alerts, etc.

4. What are your roles and responsibilities in your current project?

• As we know, OBIEE is typically the 'front end' of data warehouse process since most of the data that OBIEE deals with come from analytic database, I worked on designing schema for a data warehouse.
• As an OBIEE developer, the responsibilities like repository configuration and reports generation based on the client requirements.
• At the end, we need to implement the security
• Here, you can also mention out of box utilities if you had any like External Table Utilization, BI bursting, etc.

5. Can you explain the architecture of OBIEE?

You can follow the following link to answer this.
http://obieenow.blogspot.in/2013/05/obiee-architecture-and-its-components.html


6. Explain about OBIEE Repository (rpd) three layer architecture?

• Physical Layer: The Physical layer defines the data sources to which Oracle BI Server submits queries. It also defines the relationships between physical databases and other data sources that are used to process multiple data source queries.

• Business Model and Mapping Layer: The Business Model and Mapping (BMM) layer of the Oracle BI repository defines the business―or logical―model of the data and specifies the mapping between the business model and the Physical layer schemas. Business models are always dimensional, unlike objects in the Physical layer, which reflect the organization of the data sources. The BMM layer can contain one or more business models. Each business model contains logical tables, columns, and joins.

• Presentation Layer: Provides a way to present a customized view of a business model to users. It exposes only the data that is meaningful to users. Here, we can organize the data in a way that aligns with the way users think about the data.

7. What is connection Pool in OBIEE?

The connection pool is an object in the Physical layer that describes access to the data source. It contains information about the connection between the Oracle BI Server and that data source.

The Physical layer in the Administration Tool contains at least one connection pool for each database. When you create the Physical layer by importing a schema for a data source, the connection pool is created automatically. You can configure multiple connection pools for a database. Connection pools allow multiple concurrent data source requests (queries) to share a single database connection, reducing the overhead of connecting to a database.

8. What is the difference between OBIEE 10g & 11g?

• OBIEE 11g uses weblogic server as an application server where as OBIEE 10g used OC4J.
• A new column called the hierarchical column is introduced.
• We have only one join. i.e. New Join
• Session variables initialize when they are actually used in OBIEE 11g unlike OBIEE 10g where they were initialized as soon as a user logs in.
• OBIEE 11g now supports Ragged and Skipped hierarchy.
• Introduced Parent-Child hierarchy as well.
• In OBIEE 11g, there are some changes in terminology as well.
• iBots becomes Agents.
• Answers renamed as Analysis.
• Charts renamed as Graphs.
• As per the Security point of view, there are many changes in OBIEE 11g implementation. Some of like,

Security Task/Object	OBIEE 10g	OBIEE 11g
Define Users and Groups in RPD file using OBIEE Admin Tool	Default	By default, users are defined in embedded WLS LDAP via FMW EM Console, or alternatively, in external LDAP.
Defining security policies	Policies in the catalog and repository can be defined to reference groups within a directory.	Policies are defined in terms of application roles, which map to users and groups in a directory.
"Administrator" user	Unique user with full administrative privileges.	No single user named nor full administrative privileges. Administration can be performed by any user who is member of BIAdministrators group.
Repository Encryption	Available on sensitive elements only - i.e. user passwords, connection pool passwords, etc.	Entire RPD encrypted via a password.
“Everyone” Presentation Server Group	Default	Replaced with AuthenticatedUser role.

9. Have you ever worked with ETL-tools?

As an OBIEE developer, I know the flow of ETL process. I have been involved in designing a data model, but I never worked on that. The ETL people take care of everything.

10. Explain OBIEE Application Development Life-Cycle.

There are 4 phases in the life-cycle

1. Requirement gathering.

Business analyst (BA) who is the mediator between the client and the organization. 

• First Business Analyst is going to interact with the client to know about the client business and pain-areas, from that business analysis BA will prepare the document called as Business requirement document or BRD or FSD(Functional Specification document) or SPEC.

• From the BRD, the development team will prepare the HLD and LLD.

• HLD(High-level-document): Divide the BRD into different modules for requirement analysis, prepared by developers.

• GAP Analysis: This is the comparison between client requirement and reporting tool features like is the tool fulfill all the requirements of the client or not.

• POC(Proof of Concept): This is the dummy or a duplicate model where the development team will work and prepares a sample model which is going to send for the client’s approval. The end product should be same with the POC.

2. Development

There are 2 parts in development,

• ETL development: ETL developers will prepare a data-model with all dimensions and facts. Also build an integrated data warehouse from the heterogeneous data sources.

• Reporting development: Once the DWH is built, the reporters will configure the repository and generate the reports as per the client’s requirement.

3. Testing: 

Testing is the major part in any application or product development. There are 3 types of testing in the OBIEE application development.

• Unit Testing: The developer will test the own stuff.

• Peer-Reviews: Test the application within the organization as the application developed.

• UAT(User Acceptance Test):This is a client test testing, the application will be tested by some other company.

4. Production or Support

The company needs to give the application support for one month at least. After that based on the client’s decision, the development company or some other company will give the production support.

11. What is star and snow-flake schema?

Star schema is the simplest kind of schema where one fact table is present in the centre of the schema surrounded by multiple dimension tables.

In a Star schema design, any information can be obtained just by traversing a single join, which means this type of schema will be ideal for information retrieval.

Snow flake schema is just like star schema but the difference is, here one or more dimension tables are connected with central fact table as well as with the other dimension table. Snowflake schema requires one more join (to connect one more table) to retrieve the same information. This is why snowflake schema is not good performance wise.

12. Why do we use snowflake schema then?  

The reason we do it is, suppose we have another fact table with granularity store, food type and day. This fact will use the key of "type" dimension table instead of "food" dimension table. Unless you have this dimension table in your schema, you won't get the "type" key. This is the reason we need to snowflake the "food" dimension to "type" dimension.

13. What are OLTP and OLAP? Difference?

• On-Line Transaction Processing: It describes processing of short and simple transaction data at operational sites i.e. day to day operations in the Source systems. The Database is designed as Application-oriented (E-R based) i.e. Highly Normalized so as to efficiently support INSERT and UPDATE operations. Data stored in these systems are raw Current (Up-to-date) and Isolated Data, in a much detailed level in flat relational tables.

• On-Line Analytical Processing: It describes processing at the Centralized, Integrated and Consistent Data Warehouse. It acts as the Decision Support System for the Business End Users. The Database is designed as Star/Snowflake Schema i.e. highly de-normalized to support the SELECT operations. Data in these systems are generally Consolidated, Summarized and Historical Data in nature.

14. What is fact and dimension table in data warehousing?

A fact table holds the data to be analysed, and a dimension table stores data about the ways in which the data in the fact table can be analysed. Thus, the fact table consists of two types of columns. The foreign keys column allows joins with dimension tables, and the measures columns contain the data that is being analysed.

15. If you forget administrator password to access your repository, what corrective actions you would be taking? 

• Start your weblogic server.
• Go to C:\Middleware\oracle_common\common\bin
• Run wlst.cmd command
• wls:/offline> connect("weblogic","password","your_server_name:7001")
• listCred("oracle.bi.enterprise","repository.SampleAppLite_BI0001")

16. Have you used Time Series functions in OBIEE?

Yes. There are three time series function in OBIEE. These are AGO, TODATE, PERIODROLLING. Time series functions provide the ability to compare business performance with previous time periods, allowing you to analyze data that spans multiple time periods.

• AGO - Calculates aggregated value as of some time period shifted from the current time

• TODATE - Aggregates a measure attribute from the beginning of a specified time period to the currently displayed time

• PERIODROLLING - Performs an aggregation across a specified set of query grain periods, rather than within a fixed time series grain.

17. What are aggregation tables in OBIEE?

As we know, data in fact and dimension sources is stored at the lowest level of detail and that data often needs to be rolled up or summarized during analysis. This can lead to the performance issues if there's a huge amount of data or any run time calculations. So, this aggregate table is a popular technique for speeding up response time. Aggregate tables store pre-computed measures that have been aggregated over a set of dimensional attributes.

18. What is multiple LTS?

Logical tables have a Sources folder that contains one or more logical table sources. These logical table sources define the mappings between the logical tables in the business model and the physical tables in the Physical layer. 

19. What is query repository tool?

• It is utility of Seibel/OBIEE Admin tool
• Allows you to examine the repository metadata tool
• For example: 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

20. 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.

21. Why do we have multiple LTS in BMM layer? What is the purpose?

To improve performance and query response time.

22. How do I disable cache for only a particular table?

In the physical layer, select that particular table. Open Properties of that table. Un-check the cacheble option.

23. What is Authentication and Authorization?

Authentication is process of confirming whether the user is a valid user or not. Is he part of this company? Is he an employee of our external suppliers?

Authorization is process of giving access to different pieces of the OBIEE. One user “User A” can access dashboards, can create iBots, can schedule reports and can do administrative tasks. Another user “User B” can see only part of what “User A” has access to and “User B” has access to some other tabs of the dashboard that “User A” does not. 

Types of Authentication in OBIEE:

• LDAP authentication
• Database authentication
• External table authentication
• Operating system authentication 

24. External Table Authentication? Where does this come into play? 

You can choose to maintain lists of users and their passwords in an external database rather than in the repository. An external database table consisting of user login information has been provided so that you can import this information into the repository and use it to authenticate users during login. The table contains a list of users, their logins and passwords, and the group they belong to. Optionally, the table can also contain the logging level for each user, Web interface information, and the names of specific database catalogs or schema to use for each user when querying data. After this information has successfully been imported, you need to create an initialization block that retrieves this data. This also helps in rpd migration and deployment across multiple environments. 

 25. 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 Siebel analytics 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.

 26. What is data level security?

This controls the type and 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 only data for that region.

27. 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 Siebel analytics web catalog, like dashboards, dashboards   pages, folder, and reports.

28. 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.

29. What is variable in OBIEE?

You can use variables in a repository to streamline administrative tasks and modify metadata content dynamically to adjust to a changing data environment.

There are two classes of variables:

• A repository variable has a single value at any point in time. There are two types of repository variables: static and dynamic. Repository variables are represented by a question-mark icon (?).

• Session variables are created and assigned a value when each user logs on. There are two types of session variables: system and non-system. System and non-system variables are represented by a question-mark icon (?).

Initialization blocks are used to initialize dynamic repository variables, system session variables, and non-system session variables. The icon for an initialization block is a cube.

30. Explain default Security Settings for Oracle BI 11g?

There are changes to how security is managed in Oracle BI 11g. Security is now managed by using WebLogic Administration Console and Fusion Middleware Control.

During installation, three Oracle Business Intelligence security controls are preconfigured with initial values to form the default security model. The security controls include:

• An embedded directory server functioning as an identity store designed to hold all user and group definitions, required to control authentication
• A file-based policy store designed to hold the application role and permission grant mappings to users and groups, required to control authorization
• A file-based credential store designed to hold all user and system credentials, required to control authentication or authorization.

31. What are the new features in OBIEE 11.1.1.7.0?

• Enhancement to Dashboards
• The new default style is FusionFX. 
• The ability to export an entire dashboard or a single dashboard page to Microsoft Excel 2007+.

• Enhancements to Graphs
• Waterfall graph. A waterfall graph lets you visualize how a value increases or decreases sequentially and cumulatively. 
• Stacked graph as a new subtype to the area graph.
• 100% stacked graph as subtypes to the bar graph and the area graph. A 100% stacked graph, like the stacked graph subtype, lets you compare the parts to the whole. But while the stacked graph shows cumulative total in the units of the measure, the 100% stacked graph always shows units as percentages of the total, and the axis scale is always zero to 100 percent.
• Zoom to data range
• Hide sliders in graph views that listen to master-detail events.

• Enhancements to Views
• A new view type named performance tile. This view type displays a single aggregate measure value in a manner that is both visually simple and prominent.

• Enhancements to Prompts
• Limit values by

We are done with your interview, our HR will get back to you. Thanks..!! ;-)

Purge All Cache OBIEE 11g using Analysis Issue SQL

1. Open Analysis page.

2. Go to Administration tab. (You should logged in with Administrator privileges)

3. Click Issue SQL.

4. Call the below command & hit Issue SQL.

    Call SAPurgeAllCache();

Use following screenshot to see output of issued SQL.

Happy blogging.!! :-)

OBIEE 11g – Lookup Tables – Sparse and Dense Lookups

Lookup table are mainly used for,

1. Resolve code columns and get description/name values from a lookup table.
2. Currency conversion.

There are 2 types of lookup tables.

1. Sparse Lookups – A sparse lookup basically means that the main driving table does not necessarily have corresponding lookup values in the lookup table for all the id values. This can be considered to be an equivalent of a Left Outer Join.

2. Dense Lookups – A dense lookup basically means that the main driving table will have matching lookup values in the lookup table for each of its unique id value. This can be considered to be an equivalent of an inner join.

In short, we can summarise all this in one equation,

Dense lookup = Inner join between Dimension and lookup table
Sparse Lookup = Outer join (with Nulls having a custom name) Dimension and lookup table

To illustrate this in more detail,

Consider the following schema, PLAYERS & TEAM_LKP. You can download this schema from this link.

• PLAYERS table contains all the information of players with TEAM_ID being the unique primary key.

• TEAM_LKP has Team information with TEAM_ID being the primary key. 

The main difference between these 2 tables is, not all players in the main PLAYERS table have a corresponding team assigned in TEAM_LKP table.

1. Import those two corresponding table in Physical layer of your repository.

2. It is mandatory to define a table as a Lookup table, you must define a primary key for that particular table.

3. Take the lookup table to the BMM layer. Here, you will notice that it appears as a fact table with a # symbol in its icon(see arrow below). This is because the lookup table is not joined to any other table and OBIEE assumes it to be a fact table by default.

4. Double click on TEAM_LKP (Here in ref, Lookup table) logical table in the BMM layer and check the “Lookup table” checkbox. In the Keys tab add a key which is based on the ID column.

5. Now, in the Business Model and Mapping layer, lets create a two columns called TEAM_DENSE & TEAM_SPARSE. After that one, lets go to the LTS mapping and apply the following the function for each of them resp. Both column should be derived from, "Derived from existing columns using an expression".

Note: If you have more than one column as a primary key, the order of columns used in the key should match with the column order in the Lookup function.

1. TEAM_DENSE:

Formula:  

LOOKUP( DENSE  "Loopkup Example"."TEAM_LKP"."TEAM" , "Loopkup Example"."PLAYERS"."TEAM_ID")

2. TEAM_SPARSE:

Formula:

 LOOKUP( SPARSE  "Loopkup Example"."TEAM_LKP"."TEAM" , 'Team Not Assigned', "Loopkup Example"."PLAYERS"."TEAM_ID")

6. We now have the 2 lookup columns in the PLAYERS dimension as follows:

7. Take the newly created columns to the presentation layer into the customer dimension display folder and save your work. Check consistency of your repository.

8. Create a sample analysis as follows and verify the result.

9. Notice that, you are getting only those players information whose correspondence value are present in Lookup table i.e TEAM_LKP.

By seeing query log, you can get to know that BI server is applying inner join between TEAM_LKP & PLAYERS table. So, we are not getting any records for Mario & Naymar.

10. Next we create a report with the “SPARSE TEAM”.

11. Notice that, you are getting all players information. The result will be as follows. Notice that, 'TEAM_ID' doesn’t have a corresponding lookup value in TEAM_LKP lookup table and we see the string “Team not assigned”.

Happy blogging. :-)