OBIEE Interview Questions

OBIEE Interview Questions

 Dear folks,

These are just something that I have found to be some of the very general questions coming around from many different interviewers for the last 3 quarters. I have divided them by complexity and hope these questions help you in bossting your confidence.
Remember that “Practice and Experience are the best learning methods for your next job” but getting to know what kind of questions that people ask for is also good.
We will start with easy questions first and slowly gear towards the complex questions that may make your head spin
To make this more relevant, lets assume you are running 0n OBIEE 10.1.3.4 version
1. What is the default location of a repository file?
2. How many repository files can be loaded to a BI Server simultaneously? Assume its a single server with single instance of BI Server running just to keep things easy
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?
Ex:
Star = SamplerRepository1.rpd, DEFAULT;
Star = SamplerRepository2.rpd, DEFAULT;
Star = SamplerRepository3.rpd, DEFAULT;
4. How do you import Essbase Cubes into your repository?
5. Whats XMLA and where is it used for in OBIEE context?
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?
7. What kind of joins would you perform in the physical layer of the repository file when opened with Administration tool?
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?
9. Can you use an OLTP backend database for creating a dashboard?
10. How many tables are needed in minimum to pass through the Consistency Checking in Logical Layer (Business Mapping and Modeling Layer) of the repository?
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?
13. Is it mandatory to have hierarchies defined in your repository? If Yes, where does it help? If No, what happens in the reports?
14. How do you create outer joins in physical layer?
15. What does Consistency Checking perform; What are the minimum criteria to pass consistency checking for a given repository?
16. Does OBIEE store physical sql ? How is physical sql generated in OBIEE environments?
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?
18. What is the best default logging level for production users?
19. What is the difference between logging level 1 and 2?
20. What are the different places (files) to view the physical sql generated by an Answers report?
21. Where does the BI Server logs its start, stop and restart times in the file system?
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?
23. Same as question 22 but what happens if you import each table seperately?
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?
25. What if the tables (Table 1 and Table 2) are not joined, then what happens in BMM layer?
26. What is the difference between logical table and logical table source?
27. How many LTS (Logical Table Sources) can a logical table have? Whats the minimum and maximum?
28. How many server instances can coexist in an OBIEE cluster?
29. Whats a dimension only query in OBIEE?
30. Aggregation rules are set on top of …………… columns (Physical Columns or Logical Columns or Both)
Lets get to questions that are little more complex in my opinion
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?
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)
34. What are the uses of “Execute Direct SQL” feature in Answers? Is it a good practice to allow this feature for production users?
35. How do you disable “Execute Direct SQL” feature for all the users of your repository?
36. I want to store the value of the last time the respository 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?
39. You want to use a database built-in function bypassing the functions defined in OBIEE? What OBIEE function helps in achieving this? Whats the syntax for this function?
40. What are the different ways to authenticate an user in OBIEE system? Can OBIEE authenticate a user passing through multiple authentication methods?
41. How do you resolve M:M relationship between tables in OBIEE? Explain a scenario where this would help?
42. Does OBIEE support ragged hierarchies? What is the procedure to import ragged hierarchies from Essbase?
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?
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?
46. Table 1 and Table 2 are joined together in an Answers query. Table 1 is defined as cachable and Table 2 is defined as not cachable? 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?
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 commited 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?
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?
52. 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?
Please comment if you would like to add any new questions or suggestions to this list.
Until next time, Kudos to all OBIEE evangelists
53. What are parameterized nested loop joins (PNLP)? Where are they used? Can you configure a PNLP in your physical or logical layer?
54. 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?
55. Is it better to cache a fact table or dimension table or both in the BI Server level? Why? (Forget other caches from above question)
56. 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 ot this change? Do they function properly or do they appear broken? If ‘Yes, they will function’ How does they work? ‘If Not, reports appears broken’ what can you do to fix this?  Give examples.
57. What are the different hierarchy types available in OBIEE? When would you use them?
58. How do you create radio buttons on your OBIEE dashboard?
59. Can you create a Master Detail Report in OBIEE and how would you do it?
60. 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.
61. How do you resolve a M:M relationship other than using a bridge table?
62. Lets say that you have three tables joined to each other which have been set to be cacheable at physical layer with Table 1 set at cache persistence time 45 min, Table 2 with 60 min and Table 3 with 30 min. You ran your answers request at 9 AM and again at 9:15 AM and again at 9:45 AM. Is the result set the same for all these 3 runs at different times? If so, Why? If Not, why not? There are transactions going on and data is being updated in these tables almost every 10 min.
63. Lets say you are on your local box with a rpd and want to make sure that it can be edited only in offline mode. How can you accomplish this? Is this possible? What settings would you change?
64. Assume there is no MUDE in your environment. Three developers have been working on three seperate projects and they have developed their rpds. As a Server Admin, you were asked to promote these three rpds. What are the next steps for you as an admin to take care of to move them to QA and production? Are there any OBIEE tools that can be handy with this situation?
65. How do you get this type of interaction in your dashboard report? When clicked on a report column, I want multiple options for drill down. Remember that I did not create any hierarchies in my rpd.

66. Lets say that you want to include a prompt to your dashboard with Start Date, End Date and some measures and dimension attributes. You want to use ‘SQL Results’ feature to automatically populate Start Date and End Date with Start Date as trunc(sysdate – 1) and End Date as trunc(sysdate). What would you do? Will you encounter any errors? How do you rectify this problem?
67. How many business models can a presentation catalog refer to? How many presentation catalogues be created from a single business model?
68. How can we create nested presentation folders (nested presentation tables) in your presentation catalog? Lets say we have ‘Facts’ all lumped together in one folder and sub divide these facts as Facts – Logical and Facts – Strategic  folders? How would you create this nested structure in presentation catalog?
69. What are logical keys? Why would you need to create them? Does the physical key gets automatically converted to logical key when the table is moved from physical layer to business model?
70. Lets say you have a report with 4 dimensional attributes and 2 fact measures in the report. Whats the default sort behavior of OBIEE when you try to run the report? On what column/columns does it sort? How do you know this?
71. In the above scenario, Is it better to have atleast one column defined in your criteria manually or just leave it without any sort criteria mentioned? Whats the difference in performance?
Until next time, Kudos to all OBIEE evangelists out there
Always, remember that these are just for your reference and to play around with the tool for different scenarios and get to know the maneuvers available with this tool. Always, practice these scenarios and thats the only way you could register them in your permanent memory of your brain  

What are the different flavors of Oracle BI Publisher available?
BI Publisher that can work in conjunction with OBIEE.
BI Publisher that can work in conjunction with Hyperion EPM.
Bi Publisher that can work in conjunction with Oracle Smart Space.
What security mechanisms does BI Publisher allow?
You can have guest access enabled on your BI Publisher Server to view the reports or else enable SSO to access reports.
How can you integrate BI Publisher to an oracle portal or some other portal software?
BI Publisher reports can be accesssed using URL and when the SSO is enabled, it is seemless.
How do I access BI Publisher reports using URL?

BI Publisher Report Bursting:

What is report bursting?
Rather than giving a definition, think of what it does as explained below and build up the definition yourself.
Using BI Publisher’s bursting feature you can split a single report based on a key in the report data and deliver the report based on a second key in the report data. Driven by the delivery key, you can apply a different template, output format, delivery method, and locale to each split segment of your report. Example implementations include:
• Invoice generation and delivery based on customer-specific layouts and delivery preference
• Financial reporting to generate a master report of all cost centers, bursting out individual cost center reports to the appropriate manager
• Generation of payslips to all employees based on one extract and delivered via e-mail
 How does the BI Publisher bursting work?
BI Publisher will build the delivery XML data set based on the sql query that was used in creating the result set. The delivery data in this XML document is used as a mapping table for each Deliver By element.
As you know, for report bursting to work, you need to supply two result set elements that determines the following:
The Split By element is the data element from the report file that you wish to split the report by. For example, to split a batch of invoices by each invoice, you may use an element called CUSTOMER_NAME.
 The Deliver By element is the data element from the report file by which to determine the delivery method. In the invoice example, it is likely that each invoice will have delivery criteria determined by customer, therefore the Deliver By element may be CUSTOMER_ID.
 How does the delivery XML look like in BI Publisher?
<ROWSET>
<ROW>
<KEY></KEY>
<TEMPLATE></TEMPLATE>
<TEMPLATE_FORMAT></TEMPLATE_FORMAT>
<LOCALE></LOCALE>
<OUTPUT_FORMAT></OUTPUT_FORMAT>
<DEL_CHANNEL></DEL_CHANNEL>
<PARAMETER1></PARAMETER1>
<PARAMETER2></PARAMETER2>
<PARAMETER3></PARAMETER3>
<PARAMETER4></PARAMETER4>
<PARAMETER5></PARAMETER5>
<PARAMETER6></PARAMETER6>
<PARAMETER7></PARAMETER7>
<PARAMETER8></PARAMETER8>
<PARAMETER9></PARAMETER9>
<PARAMETER10></PARAMETER10>
</ROW>
</ROWSET>
where
• KEY is the Delivery key and must match the Deliver By element. The bursting
engine uses the key to link delivery criteria to a specific section of the burst data.
• TEMPLATE – is the name of the Layout template to apply. Note that the value is the
Layout name (for example, “Invoice”), not the template file name (for example,
invoice.rtf).
• TEMPLATE_FORMAT – is the format of the layout template. Valid values are:
• RTF
• PDF
• ETEXT
• XSL_FO
• LOCALE – is the template locale, for example, “en-US”.
• OUTPUT_FORMAT – is the output format. Valid values are:for example: pdf, html,
excel.
• HTML
• PDF
• RTF
• EXCEL
• DEL_CHANNEL – is the delivery method. Valid values are:
• EMAIL
• FAX
• FILE
• FTP
• PRINT
• WEBDAV

Channel	Parameter1	Parameter2	Parameter3	Parameter4	Parameter5	Parameter6	Parameter7
EMAIL	Emailaddress	cc	From	Subject	Message body	Attachment tru/false(For PDF, always set this parameter to true)	Reply-to
Printer	PrinterGroup	Printer	Number ofcopies	Sides	Tray
Fax	Fax serverName	Fax Number
WEBDAV	ServerName	Username	Password	RemoteDirectory	Remote FileName
File	Directory	File Name
FTP	ServerName	Username	Password	RemoteDirectory	Remote FileName

Do you need BI Scheduler to work for report bursting?
Of Course, Yes.

=====

Combining OBIEE Answer Requests in Presentation Layer

 

Often, the OBIEE repository development is run in one phase and any presentation layer changes and creating new OBIEE requests/ answers is done in the next phase. Does this raise any problems? Of course, Yes. BI is supposed to be developed iteratively and it makes great sense for BI projects. Reason being business often doesnot conform to the final format of how their reports should like. Business Analysts are more concerned about data collection, data modeling and less concerned about presentation in your first phase. Business Stakeholders often does not know the capabilities of your BI tool. We can find multiple reasons to blame each other but one thing for sure is that the business stakeholders will finally look at your iteration 1 reports and likes to add some new features (nice to have) and you are back modifying the reports that you thought are complete.
Lets get back to our initial discussion; Doing iterative development in OBIEE makes sense in multiple ways. Often, the complexity in representing data is handled in your presentation/answers. Is this good? No. Often the business logic should be pushed to your business layer. It makes complete sense.
With a recent review of one of my colleague’s project which was done in traditional waterfall SDLC methodology, the Reports Developer was banging his head to get all the business logic embedded in the answers request. The repository developer finished developing his repository, creating aggregates, creating time awareness and creating hierarchies. RPD developer is out in phase 2 of the project and no repository changes were allowed in this phase. Being an 75/25   onsite/offsite project with resources across the world and everyone being consultants, no body cared about performance, database loads and dashboard performance for the long run.
Finally, this situation lead to a peculiar situation of how do we combine various answers requests. Lets start looking at different ways OBIEE answers can be combined together to make meaningful business reports.
1. In one of my previous blogs, we talked about how we can use one OBIEE answers request to help the other OBIEE answers request either to display or not using OBIEE feature of Guided Navigation. Follow this link http://nerdsofobiee.wordpress.com/2009/10/26/hide-sections-in-dashboards/
2. This is done at the report level while creating your answers request. Lets go through this exercise together. Create an OBIEE answers request as shown below.  There are some pre-defined conditions based on which a report has been developed. Lets look at those conditions.

If you look in here, we are looking at DW_Insert_date to be in top 10 percent and plant code starting with ‘FW’ and base product codes ending with ‘P’ and formula number between 100 and 500. These ae maintained as constants. Consider this as your base report and we shall refer this as base report from now on.

The requirements for the second report always happens to be picking up the formula numbers that are in base report. The question comes down to why not replicate the filters that are in report 1 into report 2. But what if the report 1 is a easy maintenance report where the numbers (constants used in filters keeps changing). So, lets think that there is no other go. In such a situation, we are bound to build our second report based on results in first report.
Follow the diagrams below and you will get there…. take my word for it

Choose the first option “Filter based on Results of another request” in the below diagram.

I have chosen the scenario where the formula number is equal to any values from base report. In the “Saved Request”, browse to the base report and once you browse the base report, your drop down for “Use Values in Column” will be populated with all columns of base report.

As always, kudos to all OBIEE evangelists and report your comments below if this is helpful.
Any further tips you can add in comments is also welcome
Also, if you have few more seconds, can you answer this poll..

=====

OC4J Parameters for BI Publisher Performance Improvement

Recently I heard so many folks talking about OC4J being a problem in production environments when deployed for BI Publisher. Complaints include OC4J shutting down itself when a batch script is used to run multiple jobs nightly. This brings into question whether OC4J is suitable for production environments along with BI Publisher?
While I trust Oracle being good at scalability issues and if you take a deep look inside OC4J how this runs, its mostly the parameters you pass to the underlying JVM from OC4J.
This made me wonder what the default parameters are with a OC4J installation.
Now, I need to find out the config locations of OC4J in my BI Publisher or OBIEE installation.
After a quick search, I came against this file called oc4j.cmd file in \\oracle_bi\oc4j_bi folder. Open this file and you will come across some of the default parameters used in starting the JVM behind OC4J server.
Lets look at the file

Lets look at the highlighted arguments that are passed to the JVM when instantiating the JVM.
By default, when OC4J is installed as part of OBIEE installation, JVM will try to use heap size of 128 MB permanent memory or it can use upto heap size of 512 MB while running an app. Lets get to know the parameters of JVM used here XX and Xmx.
http://blogs.sun.com/watt/resource/jvm-options-list.html
Based on the blog mentioned above, XX helps in determining “Size of the Initial Permanent Generation” and how high it can go because we specified -XX:MaxPermSize=128m in our cmd file as shown above. On searching for -XX:MaxPermSize this leads to Size of max Permanent Heap Size” it can go to.
Are these parameter values good enough for a OC4J server ? Probably not.
Lets look at the other paramater -Xmx512m which means “set maximum Java heap size of JVM at 512 MB”. This is also probably not a good value for a production server.
Can the OC4J Server crash if BI Publisher tries to run multiple jobs nightly. Most probably 100% failure.
What does it take to fix this issue. As always, use the JVM monitoring tools to see how the heap size is being used over a trial period in QA by running all these jobs. Add additional parameters to the JVM like Xms, increase values for Xmx and size it accordingly.
How do we monitor the JVM usage ? Welcome to the world of Java’s JDK, JVM  and HostSpot Monitoring tools like Jconsole, Jstat.
http://java.sun.com/j2se/1.5.0/docs/tooldocs/#manage
I probably prefer Jconsole because of its wide spread usage and remote monitoring capabilities.  How do we monitor the Server remotely using Jconsole?
Follow this simple tutorial on http://java.sun.com/j2se/1.5.0/docs/guide/management/jconsole.html and please remember to use the graphical version.
Log on to the remote server using the user name, password and port you want to monitor. Which port should we monitor? I would say do ps -ef | grep oc4j on the server to see that else try ps -ef | grep java to see the ports where the JVM is running. I dont have a linux box in hand to attach a screenshot. Thats my bad and your bad luck
Always check the following graph to see how your memory consumption is going on Jconsole.

Also, check whether the garbage collection (gc) has been performed between the various jobs you run for BI Publisher reports or whether gc is run periodically. Better force the gc to run after each successful job of BI Publisher report.
I am sure you know how to force gc collection process as part of your batch jobs
For starters, use this link http://www.devdaily.com/java/edu/pj/pj010008/
Also, this is one more place to search for \\oraclebi\oc4j_bi\j2ee\home\config and look for server.xml file
Open this file and look for javac which is where we send in the parameters for java compiler inside JVM.

By default, we see the javac has Xmx parameter set at 1024 MB heap size in server.xml file as stated in the line above.
<java-compiler name=”javac” in-process=”false” options=”-J-Xmx1024m -encoding UTF8″ extdirs=”C:\Program Files\Java\jdk1.6.0_12\jre\lib\ext;C:\Windows\Sun\Java\lib\ext” />
 If we have more server RAM, try to increase this parameter. Also, you can add more parameters like Xms, Xx if you would like as stated above for JVM parameters.
Next comes to the logs where we can look for when BI Publisher crashes.. Remember that all the java related error log will be logged to javacache.log file. This is set in \\OracleBI\oc4j_bi\j2ee\home\config\javacache.xml file.
Also, the level parameter set inside javacache.xml file can be changed from “FATAL” to other values.
Still not satisfied and want more information on OC4J configuration?  No problem… Search for oc4juserguide.pdf file in your OBIEE installation \oraclebi\oc4j_bi\j2ee\home\default-web-app location.
Open this pdf file and go straight to page 43 of this 266 page document.
Look for ”Setting OC4J runtime options at startup” and look for standalone configuration and runtime options.
This can take you one more level deep to configure your OC4J. Please post comments if you can add more to this article and we appreciate every reader’s colloboration to make this world free of IT consultants
Dont worry.. I am an IT consultant too

OBIEE Services Sequence 

Never heard from Oracle that its always better to follow a particular sequence while starting your OBIEE services? Guess what, its always better to start them in a particular sequence for both your development env or your production env? Why is this important?
This is important in some ways because this helps in clearing the server cache, web cache and also helps you test your latest changes you made.
Anyways.. whats the order? Here you go..
1. Oracle Java Host
2. OC4J
3. Oracle BI Server
4. Oracle Presentation Services
5. Oracle BI Scheduler
I have seen people who had success when the # 2 on this list i.e. OC4J has been started after Presentation Services. Does it work? Yes, Of Course. Does it create any problems? Nope.. You are good.
Similarly, while shutting down use the opposite sequence i.e Oracle BI Scheduler first.. followed by Oracle Presentation Services and so on.
See my Startup and Shutdown Scripts posting to understand how to start these automatically.