FAQ's on OBIEE

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.


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?

BI Publisher 11G: Creating a report in BI Publisher 11G - Step by Step Process

BI PUBLISHER 11G DOCUMENTATION

PART -1

Creating a Report:-

-          Create a Data Model to get XML Data.

a)     

Save this Data Model. It will be saved as .xdmz

b)      Next step is to create a Data Set. 

Click ‘OK’ once the query is ready.

If Query has Parameters then it will pop up the following.

               ---->                     

Click “OK”, then it will create Parameter by itself.

c)       Parameters:-

Since here we are going to use LOV’s for BILLING_ELEMENT, first need to create List of Value for this parameter.

                                           

                                                Here the Type is SQL Query, based on the requirement change the Type to Fixed Data.

Go, back to Parameters and change the Parameter type to Menu

In Menu Setting, if you have more than one LOV’s then select the relevant List of Values to the parameter

So, now the Data Model is ready for XML Output

Go to the tool bar click GET XML Output icon.

                                Give the necessary Parameters Values and run it.

The output which shows up is what XML Data.

To Prepare a layout you need to have the XML Data. So, Export it to your local drive.

                                So, the first part is done.

-          Now, we need to prepare a layout. For prepare Layout I prefer going from MS word Add inn. For, this needs to install BI Publisher Desktop .exe file.

Once you done with your installation you will see a Add-Ins on the toolbar

Insert the XML data:-                       

If the data is successfully loaded then it will pop up

Then, go to Field and insert the field wherever required.

         ------------>         

                                This is a sample layout:-

                               

You can preview the results here itself in whatever formats you wish among the drill down.

                                Save this MS Word file as .RTF file.

-          Now the layout is ready, now it is to be uploaded back to BI Publisher 11g.

Go to Catalog and click on New which appears on the left side of the toolbar. Select Report which is nothing but .rtf file.

Then it will ask you to select the Existing Data Model to which you want to upload this .rtf layout

Select the Data Model and Click OPEN.

Upload the .rtf layout Click on Upload.

Give a name and the path where you saved your .rtf file on your local drive.

And Click Upload.

The file will be uploaded. Now, save the whole the data model and .rtf file as a package. This file will be saved as .xdoz

 Now, we need to View the result.

PART – 2

 Downloading and uploading a file.

Go the Catalog to the file which you want to download.

Then click on “More” and then download to save the file in your local drive.

This file will be saved as .xdoz file.  This .xdoz  file contain both the Data model and .rtf file.  

Note:-  (a) .xdoz  =  .xdmz + .rtf. 

(b) When it comes to SharePoint always upload both .xdoz and .xdmz files.

Uploading  a .xdoz file.                

                Go to the Catalog and there you will see an Icon “Upload Resource”.

Browse the file from where you saved.

Save this .xdoz file at destination folder.

                Note:- If just incase you want to overwite existing file, then check the box.

The file is saved.

Click Open to run the report.

Note:- Just in case you want to get the .xdmz file from this, you need to go back to DATA MODEL. So, go to Actions and click on Edit Report. Click on Data Model  which is on the top left side.

The data Model will be opened. Then Click Save As and give the destination folder where you want to save the .xdmz file.

Same process for uploading .xdmz file.

PART – 3

Style Template:-

 It is also like a Sub Template. Like you want a set of Reports Font size to be same for Column Headers and body. Then, instead of doing it for all the reports prepare just one .RTF template by using Style and Formatting.

Upload it.

               

Upload it and save it at destination folder.

Now when you edit the report, you will see View a list beside View Thumnnails. Click View  a List.

Apply the Style Template which you wanna use and also don’t forget to check the box “Apply Style Layout”

NOTE:- 1) some of the styles which can be used like for Table Styles, Header and Footer Content.

2) If a header and footer have been defined in the report layout, they will be overwritten. The header and footer from the style template will be applied.

3) Prefer not to use “Style Templates”. Let us do this work when creating a (.rtf) layout itself.

Part 4:-

Subtemplate:-

Purpose: - This is a common template for all the reports which are in common. You can import this file path into your layout and execute.  Just in case if the Design changes later after building all the reports, then just changing the subtemplate works. No need of changing by going into each and every layout.

Example:-

<?template:Header1?>

<?end template?>

<?template:Header2?>

Generated:

11/13/2011

<?end template?>

So, in the subtemplate we have the above two things. Now you need to import this into your layout(as below).

<?import:file:///c:/subtemplate/subtemplate.rtf?>

<?call-template: Header1?>

<?call: Header2?>

So, now if just incase you need to change the header for all the reports, then just changing on sub template is good enough.

Uploading a Subtemplate in BIP 11g:-

And save it at desired Folder. Need to give that folder path in the layouts just in case you want to use that subtemplate.

Errors:-

1)      When you want to modify a already existing layout from report. Needs to Click on Edit and Open. Do the necessary modifications and save it.

But when trying to save it pop ups an error

2)      The following error pops up sometime when I try to upload a layout

3)      When I am trying to use the BIP 11g path of the subtemplate in the Reports, the output does not shows up. Needs to work on this???

4)      When we try to delete the xdoz file, sometimes a pop up shows as below.?????

PART – 5

A)     MISSING COLUMNS:-

Customer Account	Customer Name	IFIFAccount Jurisdiction	Starttime	Billing Element	Billing Element Description	Numeric Value	UOM
F CUSTOMER_ACCOUNT	CUSTOMER_NAME	IFIFACCOUNT_JURISDICTION	TO_CHAR_C_STARTTIME__YYYYMM__	BILLING_ELEMENT	BILLING_ELEMENT_DESCRIPTION	$,0.99($,9.00)	UNIT_OF_MEASURE E
***End of Report***

B)      PARAMETER VALDATIONS(will be useful for hiding the entire table just in case you don’t have any data in it):-

<?choose:?>

<?when:BILLING_ELEMENT=''?> Please Enter Billing Element Value <?end when?>

<?otherwise:?>

<?choose:?>

<?when:count(G_1) > 0?>

BILLING_ELEMENT:

BILLING_ELEMENT

Customer Account	Customer Name	Account Jurisdiction	Starttime	Billing Element	Billing Element Description	Numeric Value	UOM
F CUSTOMER_ACCOUNT	CUSTOMER_NAME	ACCOUNT_JURISDICTION	TO_CHAR_C_STARTTIME__YYYYMM__	BILLING_ELEMENT	BILLING_ELEMENT_DESCRIPTION	$,0.99($,9.00)	UNIT_OF_MEASURE E
***End of Report***

<?end when?>

<?otherwise:?>No Data Found<?end otherwise?>

<?end choose?>

<?end otherwise?>

<?end choose?>

C)      TOTALS AND SUBTOTALS:-

The following are the variables which will be useful when we use Variables (set and get Variables)

INIT_FLAG

SET_FLAG

numeric value

IF_FLAG numeric_value EN

D)     MULTIPLE BILLING_ELEMENTS IN A SINGLE ROW:-

(This is a sample layout, from the report WP_JOCOB_045 for Buyer NCEMC)

LINE NO. (10)	DESCRIPTION (20)	UNIT 2 (50)	SUPPORT FACILITIES (60)	TOTAL (70)
10	F IFBILLING_ELEMENT_DESCRIPTIONend ifEF	F IFNUMERIC_VALUEend ifEF	F IFNUMERIC_VALUEend ifEF	F IFNUMERIC_VALUEend ifEF
20	DIRECT LABOR COST	F IFNUMERIC_VALUEend ifEF	F IFNUMERIC_VALUEend ifEF	F IFNUMERIC_VALUEend ifEF
30	INDIRECT COST	F IFNUMERIC_VALUEend ifEF	F IFNUMERIC_VALUEend ifEF	F IFNUMERIC_VALUEend ifEF
40	TOTAL (L10+L20+L30)	F IFNUMERIC_VALUEend ifEF	F IFNUMERIC_VALUEend ifEF	F IFNUMERIC_VALUEend ifEF
50	PROJECT OWNERSHIP %			F IFNUMERIC_VALUEend ifEF
60	OWNERSHIP COST (L40*L50)			F IFNUMERIC_VALUEend ifEF
70	MONTHLY ADVANCE PAID			F IFNUMERIC_VALUEend ifEF
80	ADJUSTMENT (L60-L70)  (TO OM 100, L40)			F IFNUMERIC_VALUEend ifEF

Note:-  1)  Here for Description I prepared in both ways:

A)     hardcoded(C20, L20-80)

B)      Just in case you want to pull Description from BILLING_ELEMENT_DESCRIPTION, for this you can see C20, L10