OBIEE Interview Questions and Answers

1. Explain the Architecture of OBIEE and what each components do?

 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 .

2. How to get sql from obiee for reports?

 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.

3.  How will you do sort in Reports in OBIEE Answers.

 Click modify and then click sort (order by icon) on the relevant column in the criteria pane.

4.  How will you do different types of narrative Reports in OBIEE?

 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.

5. How will you create Interactive Dashboards ?

 By clicking Administration and Manage dashboards and by adding column selector also by using view selector etc.and also by using prompts.

6. What is write-back in obiee ?

 In Reports of Answer you can give a column as updatebale and then view the reports,this option is called write back.

7. How will you execute Direct SQL in OBIEE?

 By clicking Direct Database Request below the subject area in Answers.

8. How will you create report from two subject areas

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

9. How will you Port changes for dashboards,reports,rpd from development to production?

 For the RPD use the Merge option in Admin Tool and for dashboards and reports use Content Accelerator Framework.

10. What are the different types of variables in OBIEE? explain

 There are two types of variables in obiee.

a.Repository variable.

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.

11. How will you enable or disable caching in the system level and table level?

 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.

12. How will you go about adding additional column to the repository in the presentation layer?

 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.

13. How will effect the changes for a report , if for certain users only the column heading in the report should be changed?

  Using session variables for that user.

14. What is a table alias in obiee? and where and how will you create it.

 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.

15. Have you created Hierarchy in OBIEE. if so where and how?

 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.

16. What is Level Based Metrics.How will you create it?

 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.

17. What are the different layers of OBIEE Repository?

 Physical Layer, Business Model and Mapping Layer, and Presentation Layer.

18. What is Authentication? How many types of authentication.

 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

19. What are the different types of security you have worked in OBIEE?

 Object Level and data level.

20. What is a bridge table ?

 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.

21. You come Morning to the office. and all the users are complaining about the obiee reporting system is very slow(dasboards). what is your approach to resolve the issue?

 Mainly check NQServer.log and in Admin Tool -> click Session Manager. check if there is any bottleneck and accordingly resolve.

22. What are the different log files in OBIEE?

 Following are the different log files inOBIEE

               NQServer.log , NQQuery.log, NQSAdminTool.log etc.

               There are mainly 25 Configuration files available in obiee.

23. What are the Key Configuration Files in OBIEE?

 NQSConfig.ini, NQSCluster.ini, odbc.ini, instanceconfig.xml

24. How will change port of obiee answers?

 By changing its port in instanceconfig.xml

25. What is ClusterServices in OBIEE ? Why its used?

 To Scale up the Performance by clustering and distributing the services across multiple Servers ClusterServices are used.

26. Can you change the location of the OBIEE Repository?

 No, Not in Standalone OBIEE Install. you can give shared location in NQSCluster.ini if the OBIEE is clustered.

27. How many BI Services Node can be clustered together?

 16

28. Can you run multiple rpd’s in a Single OBIEE Instance?

 No . you should create another instance in the same server , then its possible.

29. How will you implement security in obiee,so that subjects areas accessed by one group are not accessed by another group.

 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.

30. What is SCD type 1 and 2.

 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.

31. What is Star Schema and SnowFlake Schema?

 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.

32. How to startup OBIEE in Linux?

./run-sa.sh start #starts obi server

./run-saw.sh start #start saw server

./run-sch.sh start #start scheduler

33. How you generally Approach to ur Analytics Project?

 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.

34. How we are going to decide which schema we are going to implement in the data warehouse?

 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.

35. What are the Challenges You Faced while making of Reports?

 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.

36. What you will do when your Report is not Fetching Right Data?

 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.

37. How analytics Process Your Request When you Create your Requests?

 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.

38. From where u Get the Logical Query of your Request?

 The logical SQL generated by the server can be viewed in BI Answers.

39. What are the Major Challenges You Faced While Creating the RPD??????

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.

40. What are Global Filter and how thery differ From Column Filter?

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.

41. How to hide Certain Columns From a User?

 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.

42. How to Enable Drills in a Given Column Data?

 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.

43. How the users Created Differs From RPD/Answers/Dashboards Level?

 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.

44. How Online/Offline mode impact Development and Delpoyment????

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.

45. What is Guided Navigation?

 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.

OBIEE Interview Questions and Answers

--> 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?
o Using levelbase matrics.

--> Did you work on a stand alone Siebel system or was it integrated to other platforms?
o Deploying the Siebel analytics platform without other Siebel applications is called Siebel analytics Stand -Alone .If your deployment includes other siebel Analytics Application it called integrated analytics -You can say Stand-Alone siebel analytics

--> How to sort columns in rpd and web?
o Sorting on web column, sort in the rpd its sort order column

--> If you want to create new logical column where will you create (in repository or dashboard) why?
o I will create new logical column in repository.because if it is in repository,you can use for any report.If you create new logical column in dashboard then it is going to affect on those reports ,which are on that dashboard.you can not use that new logical column for other dashboard(or request)

--> What is complex join, and where it is used?
o we can join dimention table and fact table in BMM layer using complex join.when there is SCD type 2 we have to use complex join in Bmm layer.

--> If you have dimension table like customer, item, time and fact table like sale and if you want to find out how often a customer comes to store and buys a particular item, what will you do?
o write a query as -->SELECT customer_name, item_name, sale_date, sum(qty) FROM customer_dim a, item_dim b, time_dim c, sale_fact d WHERE d.cust_key = a.cust_key AND d.item_key = b.item_key AND d.time_key = c.time_key GROUP BY customer_name, item_name, sale_date-->

--> You worked on standalone or integrated system?
o Standalone.

--> If you want to limit the users by the certain region to access only certain data, what would you do?
o using data level security.
o Siebel Analytics Administrator: 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?
o To set a 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?
o Siebel Analytics Server event polling tables store information about updates in the underlying databases. An application (such as 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 any cache entries corresponding to the updated tables.
--> Can you migrate the presentation layer only to different server
o No we can’t do only presentation layer. And ask him for more information and use one of the above answers
o Create a ODBC connection in the different serve and access the layer.
o Copy the Rpd and migrate it to other server

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

--> How do you create filter on repository?
o Where condition on content tab.

--> How do you work in a multi user environment? What are the steps?
o Create a shared directory on the network for Multi-user Development (MUD).
o Open the rpd to use in MUD. From Tools->Options, setup the MUD directory to point to the above directory.
o Define projects within the rpd to allow multiple users to develop within their subject area or Facts.
o Save and move the rpd to the shared directory setup in point 1.
o When users work in the MUD mode, they open the admin tool and start with
o MUD ->Checkout to checkout the project they need to work on (not use the File open as you would usually do).
o After completely the development, user checkin the changes back to the network and merge the changes.

--> Where are passwords for userid? Ldap,external table authentication stored respectively?
o passwords for userid are in siebel analytics server repository Ldap authentication in Ldap server external database in a table in external database

--> Can you bypass siebel analytics server security ?if so how?
o yes you can by-pass by setting authententication type in NQSCONFIG file in the security section as:authentication_type=bypass_nqs.instanceconfig.xml and nqsconfig.ini are the 2 places
--> Where can you add new groups and set permissions?
o you can add groups by going to manage>security>add new groups> You can give permissions to a group for query limitation and filter conditions.

--> what are the things you can do in the BMM layer?
o Aggrigation navigation,level base matrics,time series wizard,create new logical column,comlex join.

--> what is Ragged hierarchy? and how do u manage it
o Ragged Hierarchy is one of the different kinds of hierarchy.
o A hierarchy in which each level has a consistent meaning, but the branches have inconsistent depths because at least one member attribute in a branch level is unpopulated. A ragged hierarchy can represent a geographic hierarchy in which the meaning of each level such as city or country is used consistently, but the depth of the hierarchy varies.
o For example, a geographic hierarchy that has Continent, Country, Province/State, and City levels defined. One branch has North America as the Continent, United States as the Country, California as the Province or State, and San Francisco as the City. However, the hierarchy becomes ragged when one member does not have an entry at all of the levels. For example, another branch has Europe as the Continent, Greece as the Country, and Athens as the City, but has no entry for the Province or State level because this level is not applicable to Greece for the business model in this example. In this example, the Greece and United States branches descend to different depths, creating a ragged hierarchy.

--> What is the difference between Single Logical Table Source and Multiple Logical Table Sources?
o If a logical table in BMM layer has only one Table as the source table then it is Single LTS.
o If the logical table in BMM layer has more than one table as the sources to it then it is called Multiple LTS.
o Ex: Usually Fact table has Multiple LTS’, for which sources will be coming from different Physical tables.
--> Can you let me know how many aggregate tables you have in your project? On what basis have you created them?
o As per resume justification document

--> How do you bring/relate the aggregate tables into the Siebel analytics Logical layer?
o One way of bringing the Aggregate Tables into the BMM layer is by bringing them as Logical Table sources for the corresponding Fact table.
o 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.

--> How do you know which report is hitting which table, either the fact table or the aggregate table?
o 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?
o Find the sql query of the report in Admin->manage Session-> run the sql query on toad ->read the explain plan output ->modify the SQL based on the explain plan output

--> Suppose you have a report which has the option of running on aggregate table. How does the tool know to hit the Aggregate table and for that what the steps you follow to configure them?
o Explain the process of Aggregate navigation
--> Have you heard of Implicit Facts? If, so what are they?
o An implicit fact column is a column that will be added to a query when it contains columns from two or more dimension tables and no measures. You will not see the column in the results. It is used to specify a default join path between dimension tables when there are several possible alternatives.
o For example, there might be many star schemas in the database that have the Campaign dimension and the Customer dimension, such as the following stars:
--> Campaign History star. Stores customers targeted in campaign.
--> Campaign Response star. Stores customer responses to a campaign.
--> Order star. Stores customers who placed orders as a result of a campaign.
In this example, because Campaign and Customer information might appear in many segmentation catalogs, users selecting to count customers from the targeted campaigns catalog would be expecting to count customers that have been targeted in specific campaigns.
--> To make sure that the join relationship between Customers and Campaigns is through the campaign history fact table, a campaign history implicit fact needs to be specified in Campaign History segmentation catalog. The following guidelines should be followed in creating
--> segmentation catalogs:
--> Each segmentation catalog should be created so that all columns come from only one physical star.
--> Because the Marketing module user interface has special features that allow users to specify their aggregations, level-based measures typically should not be exposed to segmentation users in a segmentation catalog.

--> What is aggregate navigation? How do you configure the Aggregate tables in Siebel Analytics?
o Aggregate tables store precomputed 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.
o If you are writing SQL queries or using a tool that only understands what physical tables exist (and not their meaning), taking advantage of aggregate tables and putting them to good use becomes more difficult as the number of aggregate tables increases. The aggregate navigation capability of the Siebel Analytics Server, however, allows queries to use the information stored in aggregate tables automatically, without query authors or query tools having to specify aggregate tables in their queries. The Siebel Analytics Server allows you to concentrate on asking the right business question; the server decides which tables provide the fastest answers.

--> (Assume you are in BMM layer) We have 4 dimension tables, in that, 2 tables need to have hierarchy, then in such a case is it mandatory to create hierarchies for all the dimension tables?
o No, its not mandatory to define hierarchies to other Dimension tables.

--> Can you have multiple data sources in Siebel Analytics?
o Yes.

--> How do you deal with case statement and expressions in siebel analytics?
o use expression builder to create case when…then.. end statement

--> Do you know about Initialization Blocks? Can you give me an example where you used them?
o Init blocks are used for instantiating a session when a user logs in.
o To create dynamic variable you have to create IB to write sql statement.

--> what is query repository tool?
o It is utility of Seibel/OBIEE Admin tool
o allows you to examine the repository metadata tool
o for example: search for objects based on name,type.
o Examine relationship between metadata objects like which column in the presentation layer maps to which table in physical layer

--> what is JDK and why do we need it?
o Java Development Kit (JDK), A software package that contains the minimal set of tools needed to write, compile, debug, and run Java applets.

--> Oracle doesn’t recommend Opaque Views because of performance considerations, so why/when do we use them?
o 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.

--> Can you migrate the presentation layer to a different server.
o No we have to migrate the whole web & rpd files

--> How do you identify what are the dimension tables and how do you decide them during the Business/Data modeling?
o 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

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

--> what is the full form of rpd?
o there is no full form for rpd as such, it is just a repository file (Rapidfile Database)

--> how do i disable cache for only 2 particular tables?
o in the physical layer, right click on the table there we will have the option which says cacheable

--> 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.
o we need to make an alias table in the physical layer.

--> What type of protocol did you use in SAS?
o TCP/IP