Metadata Dictionary in OBIEE 10.1.3.4.1

Metadata Dictionary in OBIEE 10.1.3.4.1

I wanted to try out the new feature ‘Metadata Dictionary ’and expose them in Answers. This could be a real useful entity for Power Users of OBIEE who could trace down a Presentation Column in Answers till the Database Column.

Steps:

1. Logon to the RPD in Offline mode and generate the metadata using Tools->Utilities->Generate Metadata Dictionary. Specify a directory when Prompted to store the dictionary. A folder named ‘dictionary’ will be created in the specified location. The ‘dictionary’ folder will contain a sub-folder with name =
2. Go to your Analytics Virtual Directory and place the ‘dictionary’ folder generated in step 1 inside it. If you are using OC4J, the virtual Directory would be C:\OracleBI\oc4j_bi\j2ee\home\applications\analytics\analytics\res
3. Restart your Servers and Go to Answers. You will see a new Icon to the right of every Subject Area. The same Icon will also appear besides each folder.
   
4. 
5. Click on the Icon and a new window will open displaying the metadata. The URL should look like

http://localhost:9704/analytics/res/dictionary/OracleBIAnalyticsApps/PresentationCatalog/PRCAT_Loyalty___Financials0313.xml

OBIEE and Ubuntu

OBIEE Supports only Redhat, Oracle Linux and Suse.However I wanted to try if it works for Ubuntu

Ubuntu

Downloaded Ubuntu 9 Desktop Edition from Internet.
The downloaded file is a .iso file.
Burn this file to a blank CD. Burn Image feature has to be used.
Once burnt, make sure its burnt properly. If you see a single .iso file , then its not correct.We should be seeing multiple files and directories in the CD.
Go to the setup of your desktop and change the option to boot from CD Drive.
Put the CD onto the drive and restart the computer.
Computer boots form CD
There will be an option displayed to check the integrity of the burnt CD. Click that.
If the CD is OKAYed in the above step, proceed to install.
If you are going to use multiple OS, then select a partition in which you will be installing Ubuntu.Select File system . I selected Ext4.
Start the Install.
Success :-)

OBIEE

Logon to your Ubuntu OS
Download the OBIEE latest version for Linux from Oracle.com.
Unzip and Untar the file and keep it ready.
Make sure you have the GCC C++ Complier already Installed. It should be available by default in Ubuntu Desktop Edition.If installed, the GCC C++ runtime version 3.4.3 libraries are in one of the following locations:
■ /usr/lib/libstdc++.so.6
■ /lib/libstdc++.so.6
Go to Console.
CD to /home/joseph/Desktop/Media/RH_Linux/Server/Oracle_Business_Intelligence. <<>>
Type sh setup.sh
This will start the Installation Window.
Mention your options like Location for JDK, OC4j password etc and click next, next,next.
This was the last place that I could reach. The window shows 100% for a very long time. I even waited for a full night. Its dead.

OBIEE is not compatible with Ubuntu. I would be happy if somebody could prove otherwise.

URLs to call OBI Reports and Dashboards

The need to call OBI Reports from other websites is a much useful and widely used feature in recent BI projects. This post will show you some basic URLs which can be used to call an Answers Request or Dashboard from a Portal page or website.

1. URL to call a dashboard

http://localhost:9704/analytics/saw.dll?Dashboard&PortalPath=/shared/Sample%20Sales/_portal/Test&nquser=Administrator&nqpassword=Administrator

The above URL is very simple and self-explanatory. Anyways Let me divide and explain each part

http://localhost:9704/analytics/saw.dll?Dashboard //the basic URL to access your default dashboard. Replace localhost with the exact web server if the Presentation Services is running on a different web server.

PortalPath=/shared/Sample%20Sales/_portal/Test // specifies the path of the dashboard. %20 denotes a single space character in 'Sample Sales' - the group folder. You can ignore this and simply use PortalPath=/shared/Sample Sales/_portal/Test

nquser=Administrator&nqpassword=Administrator // specifies the UserId and Password for an RPD user

OBI Best Practices

OBI Physical Layer

1. "Try to always import tables and columns into Physical layer rather than creating it manually.
2. This will ensure correct data types are set for each column. This is particulary useful when there is confusion between DATE and DATETIME"
3. "For each Physical table there should be a Primary Key and only one.
4. If only composite key is present create a single Physical key and add all the composite key columns in it."
5. "Minimize Opaque Views (Select) in Physical Layer.
6. Create Materialized views in database instead."
7. Always use Foreign Key Joins in the Physical layer
8. Always try to use Number-Number join. This will work faster than a varchar-varchar join.
9. Avoid using CAST functions in the join expression. This will destroy the usability of the Database indexes created on that column.
10. "Avoid any filter conditions in the Join.
11. These filter conditions can in turn be added in the LTS Where clause content filter or as request filter in Reports."
12. "Facts should not be joined together.
13. Use conforming Dimensions instead."
14. "Connection Pool:
15. ""Require fully qualified table names"" should be unchecked
16. ""Enable Connection Pooling"" should be checked
17. ""Execute queries asynchronously"" should be checked"
18. Create a separate Connection Pool for Initialization Blocks
19. Keep Cache persistence time of all tables as Infinite
20. The columns used in Joins should be set to "NOT NULL"
21. "The database Features tab should be set correctly with the Parameters supported by your backend database.
22. If both are not in-sync then lot of processing will be done in the OBI Server instead of the Database. This affects Performance. Pay particular attention to Locale. (They are case-sensitive).Mismatch of Locale can cause the sorting to be done in OBI Server instead of DB  and performance take a bad hit."
23. DERIVED_TABLES_SUPPORTED in database features tab should be checked for Oracle Databases. This will ensure that Proper function shipping will happen to the DB in case of TOP(N) and Rank functions
24. Create Display folders to group tables according to STAR or Releases
25. "Set Different Icons on objects for each Release of the Code.
26. Don't Leave the Description field empty. Write some meaningful descriptions of the object. This will help a lot in later trouble-shooting and Impact Analysis"

OBI BMM Layer

1. Minimize the use of Snow-Flakes. Always go for Star Schemas.
2. Always use Complex joins here. It allows OBI Server to make best decision about the exact Physical SQL to be generated based on Logical Query Path. In contrast to a Physical FK join, which forces a single join path between tables. If joined tables were dragged from Physical Layer, replace FK Joins with complex Joins
3. "Create Dimension Hierarchies for every Dimension in the Business Model
4. Even if a meaningful hierarchy definition cannot be thought of, just create one with the Grand Total Level and Detail Level. "
5. For Dimension Hierarchies the 'Number of Elements at this level' should increase from 1 at Grand Total to the corresponding distinct values at each level. This can be approximate values; need not be the exact ones.
6. Define Keys at each level of the Hierarchy.
7. The Content tab of each of the LTSs in Fact should be set to the related Dimension's Logical Level
8. Combine all attributes that describe a single entity into a single Logical table
9. Never Delete logical columns that map to keys of Physical dimension tables
10. Don't keep unwanted Physical columns in the Logical Layer
11. Give Meaningful Names to the Logical Columns. Avoid assigning a logical column the same name as a logical table or Business Model object.
12. Make proper use of the where clause Content filter of the LTS to minimize number of records returned.
13. Minimize the use of Conditional Checks and 'CASE WHEN' usage in the formula of Logical Columns. This will affect performance. Instead make proper use of the where clause Content filter of the LTS if the condition applies to all the columns/measures in the logical table
14. When Creating a logical column based on other logical columns , make sure all the columns in the expression is from the Same logical table, same LTS
15. Make proper distinction between Count and Count Distinct. If you are counting on a unique value column don't use Count Distinct. This will affect performance
16. Minimize the use of Outer joins within LTS. This is resource consuming. Use default zero ROW_WID records at the database instead.
17. Make sure a particular Report only refers one LTS in a Logical Table. Or the different LTSs should be at the same level
18. Avoid dimensions in Fact tables and avoid measures in Dimension Tables
19. Create Display folders to group tables according to STAR or Releases
20. When using Out-of-the -Box Vanilla RPD, remove unwanted Logical Tables and Hierarchies. This will minimize the time needed for Consistency Check
21. Specify the most Economical Source when there are multiple LTSs for a Dimension
22. Whenever you do Consistency Check, Right Click the Changed Business Model Object and go for Check Consistency rather than using the Global Consistency Check. This will minimize the time needed for Consistency Check
23. Arrange the logical columns alphabetically. This will save time when you revisit.
24. Fix the warnings if any, don't ignore it

OBI Presentation Layer

1. Catalog should map to one BMM Object only
2. Use Parent Folders and Sub folders to group Facts and similar Dimensions together
3. Avoid the use of Aliases when a new Presentation Column is created
4. The Presentation Columns in a table should be sorted alphabetically if no specific order is asked by the customer
5. Get Customer Sign-off of the Presentation layer structure before building reports. This will avoid later replacements of columns which affects the reports constructed.
6. Make proper use of the Permissions in this layer
7. Don't use Double quotes (") in Column name, though its permitted
8. Presentation columns should not have the same name as Presentation Table.
9. Eliminate unneeded objects to reduce user confusion
10. Limit # of objects in folder to 7-12
11. Use Object  description field to convey information to users
12. Keep names short to have space on reports

Datawarehouse

1. Siebel Recommends STAR schema and data warehouse should have it though more effort has to be put in creating those.
2. De-normalize and De-normalize. This would be better rather than more joins happening when a report is run
3. Make sure that indexes are created on the Primary keys, Foreign Keys and the commonly used columns for filtering
4. Create LOV Tables for commonly used columns in Dash. Prompts
5. Create Mini-Dimensions for commonly used columns in Dash. Prompts if LOV is not suitable
6. Complex metrics should be pre calculated in ETL and made readily available as columns
7. Since OLAP involves large data read operations, the Block sizes and Buffer Sizes in database should be set accordingly.

Other Considerations

Coming soon...