OBIEE Interview Questions

1. What is the default location of a repository file? 
A)C:\OracleBI\server\Repository

2. How many repository files can be loaded to a BI Server simultaneously? Assum e its a single server with single instance of BI Server running just to keep things easy 
A)BI Server only can handle one repository file in online mode

3. If you have more than 3 repository files mentioned in your NQSConfig.ini fil e as default, which one gets loaded to the memory when the BI Server is started? Ex: Star = SamplerRepository1.rpd, DEFAULT; Star = SamplerRepository2.rpd, DEFAULT; Star = SamplerRepository3.rpd, DEFAULT;
A)Oracle BI server will not be started.

4. How do you import Essbase Cubes into your repository? 
A.)1.Open the repository with the admin tool.
2.Go to File>Import from Multi-dimensional.
3.Enter your credentials and click OK, select your cube and import.

5. Whats XMLA and where is it used for in OBIEE context? 
A.)Extended multi lanaguage analysis ,in previous version named as xmla nothing but a cube.

6. 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?
A.) It cannot be changed.

7. What kind of joins would you perform in the physical layer of the repository file when opened with Administration tool? 
A.)Physical joins and Complex joins.

8. What are the minimum services needed to load a repository file onto memory a nd view a dashboard which has reports that have been refreshed on a scheduled basis? 
A.) All services are needed.

9. Can you use an OLTP backend database for creating a dashboard? 
A.) Yes

10. How many tables are needed in minimum to pass through the Consistency Check ing in Logical Layer (Business Mapping and Modeling Layer) of the repository?
A.)One dimension and Fact Table

11.What is a complex join in OBIEE and why, where is it used? 
A.)The complex join in the BMM is it will dynamically select which Logical table sources to join together from the Logical tables. In Pyhsical layer

12. Is it mandatory to have hierarchies defined in your repository? If Yes, whe re does it help? If No, what happens in the reports? 
A.)Yes it is mandatory.

13. How do you create outer joins in physical layer? 
A.)We cannot create outer joins in Physical layer.

14. What does Consistency Checking perform; What are the minimum criteria to pass consistency checking for a given repository? 
A.)Need One Dimension and fact in BMM and appropraite folders in Presentaion layer

15.Does OBIEE store physical sql ? How is physical sql generated in OBIEE envir onments? 
A.)Yes it stores.We can check in mange sessions and Nqquery log.

16. Are there any occasions where physical sql is not generated when running ag ainst a backend database like Oracle, SQL Server or any other relational database? 
A.It will effect due to Log level.

17.What is the best default logging level for production users? 
A.)Log level 2

18.What is the difference between logging level 1 and 2?

• A.)Level 1 Logs the SQL statement issued from the client application and logs el apsed 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 user ID, 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, pre sentation catalog (called Subject Area in Answers) name, SQL for the queries iss ued against physical databases, queries issued against the cache, number of rows returned from each query against a physical database and from queries issued ag ainst the cache, and the number of rows returned to the client application. 

19. What are the different places (files) to view the physical sql generated by an Answers report? 
A.) Manage sessions and NQQuery log.

20.)Where does the BI Server logs its start, stop and restart times in the file system? 
A.)In NQServer log (C:\OracleBI\server\Log)

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

22.Same as question 22 but what happens if you import each table seperately? 
A.)Keys will be affected but not the joins.

23.If Table 1 and Table 2 are dragged from physical layer to BMM layer, which ta ble becomes a Fact Table and which table becomes a Dimension Table?
A.)Table with primary key becomes Dimension and table with foreign key becomes F act table.

24.What if the tables (Table 1 and Table 2) are not joined, then what happens i n BMM layer?
A.)Both acts like Fact table in BMM.

25.How many server instances can coexist in an OBIEE cluster? 
A.)There are two server instances:

• Master server. A master server is a clustered Oracle BI Server to which the Admi nistration Tool connects for online repository changes. In the NQClusterConfig.I NI file, the parameter MASTER_SERVER specifies the Oracle BI Server that functio ns as the master server. 
• Slave server. A slave server is a clustered Oracle BI Server that does not allow online repository changes. It is used in load balancing of ODBC sessions to the Oracle BI Server cluster. If the master server is ever down, the Administration Tool will connect to an available slave server, but in read-only mode.

26.Aggregation rules are set on top of or Both)
A.)Logical Columns.

27.What are the pre-requisites for using aggregate persistence wizard? What does aggregate persistence wizard create and what does it help with? 
A.)Creation and initial population of aggregates, persists them in a back-end da tabase and configures the BI Server metadata layer so that they re used when appropriate.

28. Can you import data in multiple sheets from an Excel file? How do you impor t data from an Excel sheet? (Forget about csv files for now) 
A.)Yes we can import data from multiple sheets from an excel file Steps to follow: 1.Create system DSN for Excel file 2.After creating ODBC Connection for system DSN , Go to Administration tool > Fi le > Import > From Database > Select Data source > select the files which you wa nt to import.

29. What are the uses of Execute Direct SQL feature in Answers? Is it a good prac tice to allow this feature for production users? 
A.)Check physical connectivity to the database and Check report or dashboard per formance (Performance Tuning) ..etc .No,it will overload Production Users.

30. How do you disable Execute Direct SQL feature for all the users of your reposi tory?
A.)We can disable Execute Direct SQL by the path below: columns (Physical Columns or Logical Columns Answers > Settings > Administration > Manage Privilege .

31. What are Chronological Keys in OBIEE? How are they different from Logical Keys? 
A)

• Chronological key is the key which uniquely identifies the data at particular level. 
• chronological key is mostly used in time dimensions where time series fu nctions are used. 
• Where as logical key is the key which is used to define the unique elements in e ach logical level.
• A logical level may have more than one level key. When that is the case, specify the key that is the primary key of that level.
• It is used to specify the columns which is used for drill down and which is used as primary keys. 

32.What are the different ways to authenticate an user in OBIEE system? Can OBI EE authenticate a user passing through multiple authentication methods? 
A.)OBIEE Support four types of authentication.

1)LDAP Authentication : Users are authenticated based on credentials stored in L DAP.This is the BEST method to do authentication in OBIEE and it supports compan y s Single Sign On (SSO) philosophy as well.
2)External Table Authentication : If users belongs to multiple groups ,that info rmation is being pulled from the data base scheme table.This usually implemented with LDAP Authentication
3)Database Authentication: The Oracle BI Server can authenticates user based on database logins. If a user has read permission on a specific database.Oracle BI Presentation Services authenticates those users
4)Oracle BI Server User Authentication: User are directly crated in OBIEE and ar e authenticated against those credentials. This is the worst authentication meth od if company has larger then few people using the system. Believe me Maintenanc e is a nightmare here.

Two authentication methods are usually combined together.like LDAP and external table authentication. And as i said earlier LDAP and/or LDAP with external table are BEST authenticati on(Again each case is different) Oracle BI Server User Authentication is not quite popular as it has its support/ maintenance issue associated ,once system grows beyond certain users

33.Does OBIEE support ragged hierarchies? What is the procedure to import ragged hierarchies from Essbase?
A.)OBIEE 11g supports ragged hierarchies.

34. You are trying to open a repository using Admin tool and when you click to s ay Open Online ; a dialogue box pops up saying Your rpd is available in read-only mod e. How can you edit thisrepository by opening online? 
A.)We can avoid this error by deleting the .log and .sav in repository directory and restarting the services.

35.What is the default configuration for caching in NQSConfig.ini file? How meth od does the OBIEE use for clearing its cache? 
A.)To disable in rpd In NQSConfig.INI file(OracleBI\server\Config) set ENABLE = NO under Cahce section. If you make it as NO cahce will never save cahce again in rpd.

36.What is MUDE/ MUD in OBIEE? On what basis would you create projects? 
A.)

• Oracle BI repository development environment is not set up for multiple users . 
• A more efficient development environment would permit developers to modify a repository simultaneously and then check in changes. 
• This can be done by setting up the multi user environment using the Oracle BI Administration Tool to support concurrent development. 

37.In MUDE, Can two resources checkout the same project simultaneously? 
A.)NO

38.What are the different documentation mechanisms available in Admin tool? How do you create documentation for your Answers users against all objects available in your subject areas?
A.)RPD Documentation Tools >Utilities >Repository Documentation We can do dashboard and report documnention by CATALOG MANAGER.

39.You are running a dashboard report and check whether it is using cache? What are the different caches that can be used to serve its customer faster? (rememb er we are not talking about cache in BI Server only) How does a dashboard reques t gets served from all available caches? 
A.)To clear Cache: if it s presentation server then
go to Settings > Manage Session > Close all Curso rs if it s bi server.. go to Admin tool(rpd) > Manage > Cache > Action > Purge To disable in rpd In NQSConfig.INI file(OracleBI\server\Config) set ENABLE = NO under Cahce section.
If you make it as NO cahce will never save cahce again in rpd. Physical table ->general >Cacheable

40. Is it better to cache a fact table or dimension table or both in the BI Serv er level? Why? (Forgetother caches from above question) 
A.)Better to cache at fact table.

41. You recently changed the columns of your presentation catalog as your manag er wants to enforce naming standards for all customer facing apps. What happens to all the dashboard requests written prior ot this change? Do they function pro perly or do they appear broken? If Yes, they will function How does they work? If N ot, reports appears broken what can you do to fix this? Give examples.
A.)If Alias table is avaliable for Presentaion table then all the reports work fine.

42. What are the different hierarchy types available in OBIEE? When would you us e them?
A.)

• Level-based hierarchies (structure hierarchies):Consists of an ordered set o f two or more levels. 
• Dimensions with parent-child hierarchies (value hierarchies):Consists of values that define the hierarchy in a parent-child relationship and does not contain na med levels Ragged hierarchies:A hierarchy in which all the lowest-level members do not have the same depth. Skip-level hierarchy :A hierarchy in which certain members do not have values for certain higher levels. 

43. How do you create radio buttons on your OBIEE dashboard? 
A.)We should paste the following code in the Text box.

<script type= text/javascript >function getElementsByClass(searchClass,node,tag) { var classElements = new Array(); if ( node == null ) node = document; if ( tag == null ) tag = * ; var els = node.getElementsByTagName(tag); var elsLen = els.length; var pattern = new RegExp( (^|\\s) +searchClass+ (\\s|$) ); for (i = 0, j = 0; i < elsLen; i++) { if ( pattern.test(els[i].className) ) { classElements[j] = els[i]; j++; } } return classElements; } var tables = getElementsByClass( DashboardPromptViewTable ,null, for (var table = 0; table < tables.length; table++){ var stringFunc = ; var selects = tables[table].getElementsByTagName( select ); if ( selects.length == undefined || selects.length == 0 || selects[0].name === atch ){ M table );

if (debug === 1) { document.write( No selects found. Continue to next record. + <BR> );} continue; // This DB Prompt doesn t have any select statements } var spans = getElementsByClass( minibuttonOn ,tables[table], span ); spans[0].style.display= none ; stringFunc = String(spans[0].getElementsByTagName( a )[0].onclick); stringFunc = stringFunc.substr(stringFunc.indexOf( { )+1, stringFunc.length Func.indexOf( { )); 2 string for (var s =0; s < selects.length; s++){ var new_form = document.createElement( form ); var options = selects[s].getElementsByTagName( option ); new_form.name = selects[s].name+ _radio ; new_form.id = selects[s].id+ _radio ; for (var o=0; o<options.length; o++) { var new_input = document.createElement( input ); new_input.type = radio ; new_input.value = options[o].value; new_input.name = selects[s].name+ _radio ; if(selects[s].selectedIndex == o){ new_input.checked = true; } var onClickStringFunc = var sel = document.getElementById(this.name.substr(0,this .name.length-6)); + if ( sel == null){ + return; + } + var opts = sel.getElementsByTagName( option ); + for( var opt = 0; opt < opts.length; opt++){ + if( opts[opt].value == this.value){ + sel.selectedIndex = opt; + } + } ; new_input.onclick = new Function(onClickStringFunc + stringFunc); var new_text = document.createTextNode(options[o].innerHTML); new_form.appendChild(new_input); new_form.appendChild(new_text); } // end options for loop selects[s].parentNode.insertBefore(new_form,selects[s]); selects[s].style.display= none ;} // end selects for loop } // end table foor loop </script>

44.What is a federated query? How does OBIEE develop these federated queries? 
A.)Federated queries are queries where data is being brought from multipledatabases and consolidated/joined in the business layer/logical layer. OBIEE does this quite a lot no matter where the data is. All it needs is a relation between the tables coming from muliple databases.

45.What is in-memory query? How to implement this in OBIEE? 
A.)I dont think OBIEE does in-memory queries. It does compensate for this by usi ng features like caching. Also, caching being present in two places for OBIEE li ke presentation cache and server cache.

Interview Questions OBIEE and Oracle SQL

To Retrieve duplicate records

select * from src_emp where rowid not in (select max(rowid) from src_emp group by emp_no);

For concatenation names for same dept.

select dept_no,listagg(name,';') within group (order by dept_no)
from src_emp
group by dept_no;

 2nd highest salary using dense_rank() and Row_number()

select * from (select e.*, dense_rank() over (order by sal desc) as D from table name e) where D=2;
select * from (select e.*, row_number() over (order by sal desc) as R from table name e) where R=2;

What is Cache Management?
                Monitoring and managing the cache is called cache management.

How many types of Caches we have in OBIEE?
         In OBIEE we have 2 types of Cache:
      1.Oracle BI Presentation Server Cache
      2.Oracle BI Server Cache
             
 Could you please tell me what is the report order of execution and where report result will be stored actually?
           The report order of execution is
1. OBIPS cache
2. OBIS cache
3. DB cache
                 And the report result will be stored in OBIPS cache and OBIS cache

What is advantage of Cache Management?
      We are able to improve the Performance by reducing time to execute it and reducing time to create the physical sequel.

Let me know one thing actually, what are the differences did you observed running a report twice at the time of cache?
        Running a Report First Time:
        1. Logical SQL based on RPD BMM layer
        2. Physical SQL based on Physical layer joins
        3. This Physical SQL executing Connection Pool corresponding DB
        4. Result showing to user and also it stores result in OBIPS Cache and OBIS  
            Cache
      Running a Report Second Time:
   1. Logical SQL based on RPD BMM layer
   2. The logical SQL corresponding cache will be shown to the user


How to skip OBIPS cache for particular report?
                  Advance Tab: By pass OBIPS cache enable
                 
How do I disable cache for only two particular tables?
                  In physical layer, right click on the table there we will have the option
                  Which says cacheable and here you have to deselect the check box
                 
What is cache management? How do you implement auto cache management?
                Cache Management:
               ?Process to maintain cache for performance and update at certain  
                   Duration
              ?Auto cache management can be attained by setting Event Pooling Table

Could you please tell me how do you purge cache by subject area or cache by query?
                Admin Tool: Manage?Cache?Purge  
                You can purge all the cache

Let us assume we have two reports on that one report is cacheable and other is not cacheable, how the request will happened?
? The request will not be cached.

 What is advantages of cache?
                1.  Saves the result of queries in cache files
          2.  Enable OBIS to satisfy subsequent query request without having to  
                Access back-end databases
          3.  Less network Traffic
          4. Improves Performance
What is Disadvantages of cache?
                1. There is chance to get stale data.
                2. Memory wasted

What is the use of Enable BI server cache?
             Enabling the server cache can greatly improve performance by enabling  
             Users who share data visibility to retrieve row sets from queries that  
             Have already been run at the loss of the Possibility of seeing stale data.

If the cache is shareable among all the users are not?
              ? Yes

Tell me some of the parameters did you observed in cache?
                 User, Use count, last used, Repository, Business Model etc.
   
Tell me one thing I have two users X1 and X2 and I want to purge a U1    
 Related cache how it is?
             ?Cache manager window?Select required caches and purge

I have run a three reports i.e.  Customers, Products, calendar with orders Fact and then I want to delete customer table cache, how it is?
           Cache manger window?Physical tab?Navigate to customer table  
           ?Edit?Purge
           Note: Notice that the customer cache will be deleted.

What is purging?
       The process of removing query cache entries is called purging.

What are the purging methods?
                             1. Manual.
                             2. Persistency
                             3. Event Pooling Table
                             4. ODBC functions

What is manual Purging?
                For this manual Purging human interaction is required

 What is Persistency time purging?
                It is useful to purge cache based on fixed time. To provide this setting we  
                need to know accurately the tables refresh frequency.

What is query caching?
            It is used to save the result of query in cache file and reuse for similar Queries.

What are the advantages of query caching?
  1. Improve query performance
  2. Less network traffic
  3. Reduce DB processing
  4. Reduce OBI server processing

What is cache Hints?
           A cache hints means that the server was able to use cache to answer the
          query and did not got to the database at all.


How you are doing the cache purging in your Project?
            In my project all the ETL jobs are running every day mid night, so that in
            my project we set the persistency time for all physical tables one day.

When will do the cache purging?
            If new data comes into Data warehousing then we need to cache purging
            Otherwise the report don’t shoe the new data. If the report hitting the
           Cache.

 How to disable cache?
               1. Login to EM
               2. Click on Core-Application under BI
               3. Click on capacity management tab
              4. Click on Performance sub tab
              5. Click on Lock and edit configuration
              6. Deselect the cache enable check box
             7. Click on apply button
             8. Click on activate changes button
             9. Restart the services

What is the use of LOG-LEVEL?
            The amount of log information to be specifies for a user, generally we
            can use level-2

How many ways to do cache purging?
         Manually: In Admin tool by using the cache manage to purge the cache
                             entries.
         Automatically: We have four types of ODBC functions, those are useful
                               to purge cache programmatically & those functions are
                 1. SA purge all cache.
                 2. SA purge cache by db.
           3. SA purge cache by table.
           4. SA purge cache by query.
                           It is an accurate method to purge the cache. Normally these            
           functions will be executed by ETL-team using their post load option.

What is Presentation server cache?
           1. The presentation server caches the result of the queries OBIPS users the  
               request key and logical SQL string to determine if subsequent queries can      
               use cache result.
          2. It will be unique for Particular Session

What is SEED-CACHE?
           1. Inserting cache into cache folder is called seed cache.
           2. Seeding is the process of pre calculating the cache with queries that are
                Known to generate cache hits.
           3. Helps improve query performance.

Cloud you please tell me, how many ways to purge the cache in Presentation services?
        1. Refresh Icon Dashboard
        2. Clear my selections
        3. Manage session?session management ?close all cursors

What type of purging methods you are used in Project?
        1. ODBC Functions (Programmatically)
         2. Event Pooling Table
         Note: Tell them to above two methods first priority goes to ODBC

What is Difference between Persistency time and EPT?
           In EPT we have the new records then purging happened, whereas the above
           Persistency is time fixed.

What is Difference between EPT and ODBC functions?
          In EPT stale data is available to overcome this problem we will go for ODBC     functions.

What is Manual Seeding?
           Navigate to the report in the catalog ?click on the report ?then
           automatically cache will be created.

Could you please tell me, how will you perform EPT in your project?
            1. Import table S_NQ_EPT in physical layer
            2. Defining table S_NQ_EPT as event Pooling table.

What are the parameters enabled while cache time?
    1. Enable BI server cache enable, maximum cache entry size=20,
         Maximum cache entries=1000
    2. Global cache, global cache path, global cache size


I want to disable the cache entire environment?
If you want to disable OBI PS cache for entire environment,
We need to change tag in INSTANCECONFIG.XML file LOCATION:instance\instance1\config\oracle BIPS      component\coreapplication_obips1\instanceconfig.xml file
            <Server Instance>
             ___________________
 <cursors>
<ForceRefresh>True/ForeceRefresh>
</cursors>
__________________
<server instance>


While generating a report I don’t want to read the OBIPS cache? How you will do this?
                     If we click report refresh button it will not read OBIPS cache, directly it  
                     will go for OBIS cache


Could you please tell me what the difference between OBIPS and OBIS   
       cache?
               OBIPS cache is temp cache and whereas OBIS server cache will not be  
            deleted with OBI server start or restart

Displaying the OBIEE Reports based on the dropdown selection from Dashboard Prompt

1. Create a new Dashboard Prompt with all the Report names as dropdown list.

• ·             We need to change the Prompt for Column to single quotes.

• ·             Select “Specific Column Values” from “Choice List Values”

• ·             Select a “Default selection” by selecting “Specific Value”

• ·             Set a Presentation Variable as Required.

         

2. Create Intermediate(Dummy) Report for every Report (In the Dropdown) by taking one column by placing a filter as “Report name=Presentation Variable.”

        

3. Create a Dashboard and Integrate the Prompt and the Reports into it to the sections.

4. After Integrating the Reports in the Dashboard we need to set the condition at Section level.

        

5. We need to set the condition for the all Reports Sections by selecting respective Intermediate report.

Visibility of Prompts based on the Users

1.Create a section  and Put the dashboard prompt with new prompts in that section.

2. GO to permission for that section and give the roles as 'granted' for the users account  to have access to that section.

3. Create new filters in the reports for the new prompts as “Is Prompted”.

4. Then the Prompts will work and those will be visible based on the roles.