OBIEE Interview Questions Part - 1

These list of questions are collected from various blogs:
 
 1.       What is the default location of a repository file and Catalog?

RPD Path: D:\OBIEE11g\instances\instance5\bifoundation\OracleBIServerComponent\coreapplication_obis1\repository

C:\OracleBI\server\Repository

Catalog Path: D:\OBIEE11g\instances\instance5\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalog

2.       How many repository files can be loaded to a BI Server simultaneously? Assume it’s a single server with single instance of BI Server running just to keep things easy :)

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 file as default, which one gets loaded to the memory when the BI Server is started?(NOT SURE)

Ex:

Star = SamplerRepository1.rpd, DEFAULT;

Star = SamplerRepository2.rpd, DEFAULT;

Star = SamplerRepository3.rpd, DEFAULT;

--Oracle BI server will not be started

--Last rpd will be loaded into BI server.

4.    How do you import Essbase Cubes into your repository?

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.     What’s XMLA and where is it used for in OBIEE context?

Extended multi language 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?

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?

Physical joins and Complex joins.

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

All services are needed.

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

Yes

10. How many tables are needed in minimum to pass through the Consistency Checking in Logical Layer (Business Mapping and Modelling Layer) of the repository?

One dimension and Fact Table

11. Can you create new views in the database using OBIEE Administration tool? Can you explain the procedure for doing this?

12. What is a complex join in OBIEE and why, where is it used?

The complex join in the BMM is it will dynamically select
which Logical table sources to join together from the Logical tables.

13. Is it mandatory to have hierarchies defined in your repository? If yes, where does it help? If No, what happens in the reports?

Yes it is mandatory. It helps in drilling the data from one level to another level.

14. How do you create outer joins in physical layer?

We cannot create outer joins in Physical alyer.

15. What does Consistency Checking perform; what are the minimum criteria to pass consistency checking for a given repository?

Need One Dimension and fact in BMM and appropriate folders in Presentation layer.

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

Yes it stores. We can check in mange sessions and Nqquery log.

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

It will effect due to Log level.

18. What is the best default logging level for production users?

Log level 2

19. 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 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, presentation catalog (called Subject Area in Answers) name, SQL for the queries issued against physical databases, queries issued against the cache, number of rows returned from each query against a physical database and from queries issued against the cache, and the number of rows returned to the client application.

20. What are the different places (files) to view the physical sql generated by an Answers report?

Manage sessions and NQQuery log.

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

In NQServer log (C:\OracleBI\server\Log)

22. 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, it will

23. Same as question 22 but what happens if you import each table separately?

Keys will be affected but not the joins.

24. 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 key becomes Dimension and table with foreign key becomes Fact table.

25. What if the tables (Table 1 and Table 2) are not joined, then what happens in BMM layer?

Both acts like Fact table in BMM.

26. What is the difference between logical table and logical table source?

27. How many LTS (Logical Table Sources) can a logical table have? What’s the minimum and maximum?

28. How many server instances can coexist in an OBIEE cluster?

There are two server instances:

Master server:  A master server is a clustered Oracle BI Server to which the Administration Tool connects for online repository changes. In theNQClusterConfig.INI file, the parameter MASTER_SERVER specifies the Oracle BI Server that functions 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.

29. What’s a dimension only query in OBIEE?

30. Aggregation rules are set on top of …………… columns (Physical Columns or Logical Columns or Both)

Logical Columns

31. What is alternative drill path? How do you set alternative drill path in OBIEE?

32. What are the pre-requisites for using aggregate persistence wizard? What does aggregate persistence wizard create and what does it help with?

Creation and initial population of aggregates, persists them in a back-end database and configures the BI Server metadata layer so that they’re used when appropriate.

33. Can you import data in multiple sheets from an Excel file? How do you import data from an Excel sheet? (Forget about csv files for now)

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 > File > Import > From Database > Select Data source > select the files which you want to import.

34. What are the uses of “Execute Direct SQL” feature in Answers? Is it a good practice to allow this feature for production users?

Check physical connectivity to the database and Check report or dashboard performance (Performance Tuning) ..etc….No,it will overload Production Users.

35. How do you disable “Execute Direct SQL” feature for all the users of your repository?

Answers > Settings > Administration > Manage Privilege.

36. I want to store the value of the last time the repository was updated and show it in the dashboard? What type of variable can be used for this purpose?

37. Is there any way to see a list of all the repository variables defined in your repository using Answers tool?

38. What are Chronological Keys in OBIEE? How are they different from Logical Keys?

Chronological key is the key which uniquely identifies the data at particular level. Chronological key is mostly used in time dimensions where time series functions are used.
Where as logical key is the key which is used to define the unique elements in each 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 are used for drill down and which are used as primary keys.

39. You want to use a database built-in function bypassing the functions defined in OBIEE? What OBIEE function helps in achieving this? What’s the syntax for this function?

40. What are the different ways to authenticate a user in OBIEE system? Can OBIEE authenticate a user passing through multiple authentication methods?

OBIEE Supports four types of authentication:

1) LDAP Authentication: Users are authenticated based on credentials stored in LDAP. This is the BEST method to do authentication in OBIEE and it supports company’s Single Sign On (SSO) philosophy as well.

2) External Table Authentication: If users belongs to multiple groups ,that information 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: Users are directly crated in OBIEE and are authenticated against those credentials. This is the worst authentication method if company has larger then few people using the system. Believe me Maintenance 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 authentication (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

41. How do you resolve M: M relationship between tables in OBIEE? Explain a scenario where this would help?

By creating driving table, E.g. DIM_BOOK çFACT_BOOK_AUTHORèDIM_AUTHOR

42. Does OBIEE support ragged hierarchies? What is the procedure to import ragged hierarchies from Essbase?

OBIEE 11g supports ragged hierarchies.

43. You are trying to open a repository using Admin tool and when you click to say “Open Online”; a dialogue box pops up saying “Your rpd is available in read-only” mode. How can you edit this repository by opening online?

We can avoid this error by deleting the .log and .sav in repository directory and restarting the services.

44. How do you set up usage tracking in OBIEE? Is the Usage Tracking mechanism in OBIEE configurable to capture new identified metrics by your department?

45. What is the default configuration for caching in NQSConfig.ini file? How method does the OBIEE use for clearing its cache?

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.

46. Table 1 and Table 2 are joined together in an Answers query. Table 1 is defined as cacheable and Table 2 is defined as not cacheable? What happens to the resultset used in the Answers tool? Is the result cached or not cached?

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

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.

48. Two people (Developer A and Developer B) are assigned to the same project inside MUD and opened the same project simultaneously. Developer A made some changes to the project and merged his changes to the original repository. Developer B also made some changes and committed his changes to the original repository? Does the MUD environment preserve both Developer A and Developer B changes?

49. In MUDE, Can two resources checkout the same project simultaneously?

No

50. In MUDE, what happens if the physical tables are shared between projects?

51. What are the different types of utilities provided in OBIEE? Explain any two utilities used in your previous projects and what are they used for?

RPD Documentation—Tools–>Utilities—>Repository Documentation

We can do dashboard and report documentation by CATALOG MANAGER.

52. 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? (Remember we are not talking about cache in BI Server only) How does a dashboard request gets served from all available caches?

A.)To clear Cache:

If its presentation server then go to Settings > Manage Session > Close all Cursors

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 cache will never save cache again in rpd.

Physical table—->general—>Cacheable

53. Is it better to cache a fact table or dimension table or both in the BI Server level? Why? (Forgetother caches from above question)

Better to cache fact table

 54. You recently changed the columns of your presentation catalog as your manager wants to enforce naming standards for all customer facing apps. What happens to all the dashboard requests written prior to this change? Do they function properly or do they appear broken? If ‘Yes, they will function’ how does they work? ‘If Not, reports appear broken’ what can you do to fix this? Give examples.

If Alias table is available for Presentation table then all the reports work fine.

55. What are the different hierarchy types available in OBIEE? When would you use them?

A.) Level-based hierarchies (structure hierarchies): Consists of an ordered set of 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 named 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 member’s don’t have values for certain higher levels.

56. What is a federated query? How does OBIEE develop these federated queries?

Federated queries are queries where data is being brought from multiple databases 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 multiple databases.

57. What is in-memory query? How to implement this in OBIEE?

I don’t think OBIEE does in-memory queries. It does compensate for this by using features like caching. Also, caching being present in two places for OBIEE like presentation cache and server cache.

58. Can you create a Master Detail Report in OBIEE and how would you do it?

Yes. Changes in master report reflect into the detailed report. It uses master-detail event in interaction tab.

59. How do you deal with a situation like this when data is coming from a snowflaked data warehouse?

Fact  >—— Dimenion 1 >———-< Dimension 2 >————< Dimension 3

Dimension 1 and Dimension 2 is a M:M relationship and the same for Dimension 2 and Dimension3.

60. How do you resolve a M:M relationship other than using a bridge table?

61. What are logical keys? Why would you need to create them? Does the physical key get automatically converted to logical key when the table is moved from physical layer to business model?