Dear Readers, Welcome to OBIEE Interview Questions and Answers have been designed specially to get you acquainted with the nature of questions you may encounter during your Job interview for the subject of OBIEE. These OBIEE Questions are very important for campus placement test and job interviews. As per my experience good interviewers hardly plan to ask any particular questions during your Job interview and these model questions are asked in the online technical test and interview of many IT companies.
OBIEE Consists of Presentation Services(Web),Oracle BI (Analytics Engine) Server
The client (Answers) constructs sql and passes it to the Analytic Engine and then the Oracle BI (Analytic Engine) parses the physical sql to the Datasources and retrieve the data back to the Engine and presents to the presentation Services .
There are many ways to get the sql
a. Modify the request and click Advanced in that you get xml code and also the actual sql.
b. In the catalog Manager click Tools –>Create Report .In the Create Report Window –> Click Request SQL and save the sql to the physical path in your PC.
c. Enable Loglevel to 2 in the OBIEE Admin Tool from Mange-> Security and enable the log level to 2 by clicking properties for the user, then go to the NQQuery.log in BI_HOME/OracleBI/Server/Logs.You will find the SQL for that User.
d. By clcking Administration->Manage sessions-> view sql.
Click modify and then click sort (order by icon) on the relevant column in the criteria pane.
By clicking modify request and Narrative View and by giving @1 for the first column result and @2 for the 2nd column and son on and we can also give a heading for No Results by clicking the Narrative view.
By clicking Administration and Manage dashboards and by adding column selector also by using view selector etc.and also by using prompts.
In Reports of Answer you can give a column as updatebale and then view the reports,this option is called write back.
By clicking Direct Database Request below the subject area in Answers.
From the Criteria Pane of the Report Created from First Subject Area
come to the bottom of the page and click combine request. but the options are limited for combining like union etc..
For the RPD use the Merge option in Admin Tool and for dashboards and reports use Content Accelerator Framework.
There are two types of variables in obiee:
This variable is for the whole repository.
b.session variable . session variable can be system variable and non system variable. system variable uses NQ_SESSION. —- (system reserved variable). examples of non system variables are user defined filters etc.
In the NQSConfig.ini file use ENABLE under CACHE Section for System Level
for tables .If you want to enable the cache at table level , open the repository in offline mode(not the current working repository). this should be different from the current repository and click enable or disable the cache . right click the table and properties and click the cache or deselect the cache. then merge the repository with the current working one.
Check whether the table is already existing if so add in physical layer ,then BMM and then Presentation layer, then reload server metadata ,then it will be visible to all users.
Using session variables for that user.
Table alias can be created by right clicking the table in the physical layer then click alias,
Table alias is mostly used for creating self joins.
Yes we can create hierarchy in BMM Layer of OBIEE in dimensions for the dimension tables. this can be done by right clicking the dimension table and click create dimension and then we should manually define the hierarchy and its levels.
Leval-base matrics means, having a measure pinned at a certain level of the dimension. Monthly Total Sales or Quarterly Sales are the examples.
To create a level based measure, create a new logical column based on the original measure (like Sales 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 Month in Time Dimension.
Physical Layer, Business Model and Mapping Layer, and Presentation Layer.
Authentication is the process by which a system verifies, through the use of a user
a. Operaing system autentication
b. External table authentication
c. Database authentication
d. LDAP authentication
Object Level and data level.
If you want to connect two tables where there is no relation ship you can use a thrid bridge table for connecting them which will have common columns in both tables, this is used in BMM Layer.
Mainly check NQServer.log and in Admin Tool -> click Session Manager. check if there is any bottleneck and accordingly resolve.
Following are the different log files inOBIEE
NQServer.log , NQQuery.log, NQSAdminTool.log etc.
There are mainly 25 Configuration files available in obiee.
NQSConfig.ini, NQSCluster.ini, odbc.ini, instanceconfig.xml
By changing its port in instanceconfig.xml
To Scale up the Performance by clustering and distributing the services across multiple Servers ClusterServices are used.
No, Not in Standalone OBIEE Install. you can give shared location in NQSCluster.ini if the OBIEE is clustered.
No . you should create another instance in the same server , then its possible.
Using Security Manger in OBIEE Admin Tool, by creating two user groups and allowing only part of the subject area for one group and other part to the other user group.
SCD is slow changing dimensions. in type 1 we replace the changed dimension with old dimension. in type 2 we use surrogate keys and keep both the records(rows). Advantage of SCD2 is we keep history of the old dimensiont.
Star is one fact and many dimensions where as Snowflake design is one fact and many dim’s and dim’s can have additonal dim relations.
./run-sa.sh start #starts obi server
./run-saw.sh start #start saw server
./run-sch.sh start #start scheduler
Any project should start from defining the scope of the project and the approach
should be not to deviate from the scope.
Then the project should be functionally divided into smaller modules generally done by
project managers along with technical and functional leads.
The functional leads then decide on majorly three things:
According to the defined scope of the project they start gathering requirements while interacting with the clients.
They had a discussion with the technical leads and try to reach a solution.
Technical leads decides what schemas to create and what requirements are going to fulfill by that schema.
Technical leads discuss all this with the developers and try to close requirements.
Simultaneously testing and deployment is planned in a phased manner.
One way is what is mentioned in Question above.
If one ask you to blindly create schemas for the warehouse without knowing any requirements,
You can simply first divide the schemas on the basis of functional areas of an Organization which are similar to the modules in an ERP like sales, finance, purchase, inventory, production, HR etc.
You have to broadly describe the expected analysis an organization would like to do in every module. This way you would be able to complete at least 40-50 % of the requirements. To move ahead, study the data and business and you can create few more schemas.
Making of an report has never been a difficult task. But problem comes when users are reluctant to adopt a new system. Many developers have experienced that if they are not able to create the report in exactly the way they used to see, they will keep asking for the changes. Developers approach should be to first show them what they want to see and then add more information in the report.
This is the biggest problem in report creation and verification. There could be two reasons for report not fetching the right data.
1. Mostly clients do not have correct data in their database and on top of that to correct the results they make some changes at the report level to bring the desired result which you may not e aware of while creating the reports. Clients try to match the data with their existing reports and you never get the correct results. you try to discover the things and at later stage come to know of all these problems and you are held responsible for this delay. Hence always consult the SPOC(Single Point of Contact) and try to understand the logic they have used to generate their reports.
2. If the database values are correct, there there could be a problem with the joins and relations in the schema. You need to discover that analyzing and digging deep into the matter.
Oracle BI server converts the logical SQL submitted by the client into optimised physical SQL which is then sent to the backend database. Also in between it performs various tasks like converting the user operations like user selections to form a logical SQL, checking and verifying credentials, breaking the request into threads(as Oracle BI is a multi threaded server), processes the requests, manages the cached results, again converting the results received from the database into user presentable form etc.
The logical SQL generated by the server can be viewed in BI Answers.
Every now and then there are problems with the database connections but the problem while creating the repository RPD files comes with complex schemas made on OLTP systems consisting of lot of joins and checking the results. Th type of join made need to be checked. By default it is inner join but sometimes the requirement demands other types of joins. There are lot of problems with the date formats also.
Column filter- Simply a filter applied on a column which we can use to restrict our column values while pulling the data or in charts to see the related content.
Global filter- This filter will have impact on across the application.
We can hide a column in Application access level security
Do not add the column in the report
Do not add the column in the presentation layer.
To enable Drill down for a column, it should be included in the hierarchy in OBIEE.
Hyperion IR has a drill anywhere feature where dont have to define and can drill to any available column.
RPD users can do administrator tasks like adding new data source, create hirarchies, change column names where as Answers users may create new charts, edit those charts and Dashboard users may only view and analyse the dashboard or can edit dashboard by adding/removing charts objects.
Online Mode- You can make changes in the RPD file and push in changes which will be immediately visible to the users who are already connected. This feature we may use in production environment.
Offline mode- can be useful in test or development environment.
Guided navigation is a feature of Oracle BI Interactive Dashboards that enables the content and layout of Oracle BI Interactive Dashboard to change dynamically based on changes in the information being analyzed.
Specifically, sections in a dashboard page can be set up and only appear when there is “interesting” information in the data.
There are two types of variables in OBIEE 11g.
Repository variable : This variable is used for the whole repository.
Session variable : session variable are of two types: system variable and non system variable.
System variable uses NQ_SESSION. Examples of non system variables are user defined filters.
Table alias is used for creating self joins. Table alias can be created by right clicking the table in the physical layer then click alias.
Authentication is the procedure by which a system confirms, through the use of a user
Operaing system authentication.
External table authentication.
Complex Join – it uses multiple conditions, such as A.ROW_WID = B.ROW_WID AND / OR A.A_WID = B.B_WID
The key configuration files in OBIEE 11g are NQSConfig.ini, NQSCluster.ini, odbc.ini, instanceconfig.xml