Oracle Business Intelligence: OBIEE Interview Questions part 1

OBIEE Interview Questions part 1

What is the difference between a datawarehouse and a datamart?
http://prashanthobiee.blogspot.in/2012/12/datawarehouse-and-datamart.html

What is an ETL?
http://prashanthobiee.blogspot.in/2012/12/etl-plan.html

What is OLTP and OLAP?
http://prashanthobiee.blogspot.in/2012/12/oltp-and-olap.html

What is a star schema and snowflake schema?
http://prashanthobiee.blogspot.in/2012/12/star-schema-and-snowflake-schema.html

What is ODBC and what is the difference between ODBC and OCI?
http://prashanthobiee.blogspot.com/2012/12/odbc-and-oci.html

Explain Slowly changing dimensions
http://prashanthobiee.blogspot.in/2012/11/slowly-changing-dimension.html

How do you import data from an excel file?
http://prashanthobiee.blogspot.in/2012/11/import-excel-worksheet.html

Define repository in terms of OBIEE
Repository stores the Meta data information. OBIEE repository is a file system and the extension of the repository file is .RPD

· 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. BI Server cannot access multiple repositories.

What are the differences between OBIEE 10g and 11g?
http://prashanthobiee.blogspot.in/2012/11/obiee-interview-questions.html

What is a Confirmed Dimension?
http://prashanthobiee.blogspot.in/2012/11/confirmed-dimension.html

What is Factless fact table?
http://prashanthobiee.blogspot.in/2012/12/factless-fact-table.html

What is a degenerate dimension?
http://prashanthobiee.blogspot.in/2012/12/degenerate-dimension.html

What is an Alias table and why is it used?
http://prashanthobiee.blogspot.in/2012/12/alias-table.html

What is a connection pool and explain its properties
http://prashanthobiee.blogspot.in/2012/11/connection-pool.html

What is use of NQSConfig.ini file?
http://prashanthobiee.blogspot.in/2012/12/nqsconfigini.html

Can you change the location of your rpd file in your OBIEE Configuration? If Yes, Where would you mention the new location of this rpd file for BI Server?
In OBIEE 10g you need to specify it in the NQSConfig.ini
In OBIEE 11g repository is managed using EM (Enterprise Manager). We can directly specify the path to load the rpd

If you have more than 3 repository files mentioned in you NQSConfig.ini file as default, which one gets loaded into the memory when BI server is started and Why?

Ex:

Star = SamplerRepository1.rpd, DEFAULT;

Star = SamplerRepository2.rpd, DEFAULT;

Star = SamplerRepository3.rpd, DEFAULT;
SamplerRepository3.rpd will be loaded into the memory because as the server starts reading from the top each time it loads the next repository. So finally the last rpd will be loaded into the server memory

What are the minimum services needed to load a repository file onto memory and view a dashboard which has reports that have been refreshed on a scheduled basis?
OC4J(10g) or Weblogic(11g), BI server, Presentation Server, Scheduler server

What are the different places (files) to view the physical sql generated by an Answers report?
NQQuerylog
Administration > Manage Sessions > View Log

Where does the BI Server logs its start, stop and restart times in the file system?
NQServer.log

You have two tables Table 1 and Table 2 joined by a foreign key in the database? They are imported together from the database into your physical layer. Is this relationship still preserved in the OBIEE physical layer?
Yes

Same as the above question but what happens if you import each table separately?
Keys will be affected but not the joins

If Table 1 and Table 2 are dragged from physical layer to BMM layer, which table becomes a Fact Table and which table becomes a Dimension Table?
Table with primary becomes the dimension table and table with foreign key becomes Fact table

What if the tables (Table 1 and Table 2) are not joined, then what happens in BMM layer?
Both tables act as Fact table

Does OBIEE store physical sql ? How is physical sql generated in OBIEE environments?

Yes, physical SQL is generated by the query compiler during the query processign of the logical sql

Are there any occasions where physical sql is not generated when running against a backend database like Oracle, SQL Server or any other relational database?
When the logging level is 0(zero) or when the query log file reaches maximum limit

What is a complex join in OBIEE and why, where is it used?
http://prashanthobiee.blogspot.in/2012/12/complex-join.html

Is it mandatory to have hierarchies defined in your repository? If Yes, where does it help? If No, what happens in the reports?
In OBIEE 10g, it is not mandatory. These are used for drill down and level based measures
In OBIEE 11g it is mandatory, else you get some warnings and errors

How do you create outer joins in physical layer?
We cannot create

What does Consistency Checking perform; What are the minimum criteria to pass consistency checking for a given repository?
One Dimension table and One Fact table

How do we upload new file into BI server in 10g and 11g?
In 10g using NQSConfig.ini
In 11g using EM(EM manages NQSConfig.ini)

How to bypass server authentication?
You can bypass the authentication in NQSConfig.ini and instance config.xml, BYPASS SERVER AUTHENTICATION=YES

What happens when foreign key join is defined in BMM layer?
If a foreign key join is used in BMM layer, then the BI Server will always select this physical join to create the SQL even when it is not most efficient join

When is a complex join used in physical layer?
Complex join is used in a physical layer when we want to use join expressions. For ex: we want to use greater than or less than operator.

What is an implicit fact column?
http://prashanthobiee.blogspot.in/2012/12/implicit-fact-column.html

What is a chronological key?
http://prashanthobiee.blogspot.in/2012/12/chronological-key.html

What is the best default logging level for production users?

Log Level 0

What is the difference between logging level 1 and 2?
Level 1 Logs the SQL statement issued from the client application and logs elapsed times for query compilation, query execution, query cache processing, and back-end database processing.Logs the query status (success, failure, termination, or timeout). Logs the userID, session ID, and request ID for each query.
Level 2 Logs everything logged in Level 1.Additionally, for each query, logs the repository name, business model name, presentation catalog (called Subject Area in Answers) name, SQL for the queries issued against physical databases, queries issued against the cache, number of rowsreturned from each query against a physical database and from queries issued against the cache,
and the number of rows returned to the client application.

What is the use of 'query repository tool'?
http://prashanthobiee.blogspot.in/2012/12/query-repository-tool.html

What are the different utilities available and explain them in detail

How can I export all the available tables into an excel sheet?
Using the Repository Documentation Utility

Which variable(s) doesn't require an initialization block?
Static Repository Variable

How to model a presentation layer so that all the dimension tables under a Dimension folder and fact tables come under a Fact folder?
To give the appearance of nested folders in Oracle BI Answers, prefix the name of the presentation folder to be nested with a hyphen and a space and place it after the folder in which it nests
Alternatively, you can enter a hyphen and a greater than sign in the description field

If I change a column name in the presentation layer from 'A' to 'A1'. Does the query which includes this column work?
Yes it will work unless you change the column formula

Where can we apply filter in the repository?
In 'where' clause in content tab

What is Authentication and how many types of authentications are available in OBIEE?
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 Server authenticates each connection request it receives. The different authentications available in OBIEE are