Repository

Repository Basics

Oracle BI Architecture Components - Clients - Oracle BI Repository - Data Sources - Physical Layer Objects - Database Object General Properties - Connection Pool - Physical Table Properties - Alias - Select - View Deployment - BMM Layer Objects - Logical Complex Join - Measures - Presentation Layer Objects - Nested Tables - Aliases - Presentation Catalog - Rename, Reorder, Delete, Rename, Reorder Columns - Consistency Check Manager - Logging Levels - NQQuery Log

Multi Star Schemas

Dimension Only Queries - Implicit Fact Column - OBIEE Behavior - Conformation dimensions – Star Schema - Snow Flake Schema - Multi Star Schema - Slowly Changing Dimensions - Extension Tables - Bridge Table - Helper Tables

Administration Tool Utilities

Wizards and Utilities - Managing Joins - Managing Sessions -Querying Repository Metadata -Replacing Columns or Tables - Externalizing Strings - Renaming Repository Objects – Repository Documentation - Metadata Dictionary

Advanced OBIEE

Clustering of OBIEE Servers - Write back - Upgrade Process of 7.8 - OBIEE Upgrade - Import Option - Repository - Merge - Repository Upgrade - webcat Upgrade - Usage of Saw Migrate Tool in OBIEE - Testing Strategy - Siebel Analytics 7.8 New Features - OBIEE New Features - Differences between Siebel analytics and OBIEE - Usage Tracking - SA System Subject Area - Configuration - Usage

Standard

Why do we create Complex joins in BMM Layer?

All joins created in the BMM Layer should be Complex Joins. Creating a Complex Join between two Logical tables enables the OBIEE Server to pick up the optimum sql.

eg We have a logical dimension table which has two logical table sources with one the main dimension table and the other snow Flaked normalised dimension and a logical fact table which has two logical table sources with one the fact table and the other an Aggregate table.

When a request can be satisfied by a sql using the main dimension table joined to a Fact Table or through an Aggregate table joined to a snow Flaked normalised dimension. Since the number of records are less in the aggregate table the server picks up the Join between the Aggregate table and the snow Flaked normalised dimension. Hence We define only a join between the logical tables and based on the BMM Layer configuration, the server picks up the Optimum Join between the Physical tables.

Where do we specify the Outer Join between two tables in OBIEE?

The Type of the Join is specified in the Logical Table Source in the BMM Layer.

What is the difference between WHERE Clause and Fragmentation Content of a Logical Table Source?

Filters applied in the WHERE Clause will be always always applied whenever the Logical table Source Qualifies

Filters applied in the Fragmentation Content is used by the OBIEE Server to Qualify a logical Table Source if the Filter in the Request matches with the Filter in the Fragmentation content.

Why do we create an alias table in OBIEE?

It is a best Practice to use Alias table for all tables used in a business model. Seperate aliases needs to be made for Seperate Business models.

Seperate Aliases for a Table are made in a single Star Schema, if it is a Role Playing Dimension (eg Open Date(W_DAY_D) and Close Date(W_DAY_D) joined to a Fact)

Alias Tables are also made for a table having Self Join(eg Employee(W_EMPLOYEE_D) and Manager(W_EMPLOYEE_D)

When do we use Complex Joins in Physical layer?

Complex Joins are used in Physical layer when we need to do a calculation on a join condition between two tables.

eg UPPER(W_EMPLOYEE_D.NAME)=W_CUSTOMER_D.EMP_NAME

What is the configuration that needs to be done in rpd to generate an Inline view in the SQL?

A Physical table with type select needs to be created, and the sql of the inline view needs to be put in the Default initialisation string. Physical Table with Type Select are used to generate Inline Views

eg SQL Input

select T1.abc,T2.bac from w_bnj_d T1, (select distinct bac,emp from w_abc_d) T2 where T1.emp_id=T2.emp

The sql "select distinct bac,emp from w_abc_d" will be the sql in the default initialisation string and necessary joins needs to be made in the Physical layer.

How do We create Sub Folders in the Presentation Layer?

Prefix an hyphen and space before the name of the Sub folder.

Can We create a Subject Area from two different Business models?

No, it is not possible.

How do we rename a presentation table so that its alias name is retained?

We need to right click on the column and select rename and then we need to give the new name. Double clicking on it and renaming it does not retain the alias name.

What is the Use of Retaining the Alias Name in the Presentation Layer?

Alias name keeps track of the Previous names of an object.These names are used by the reports having objects referencing to their prevoius names.

eg I have a report Customer.Id, Employee.name from Sales

Now If want to rename the column "Id" under Customer Folder to "Customer Id" without modification of any report, I need to retain the Alias name Id for the Column Customer Id, so that the above report column Customer.Id points to the Customer.Customer Id

Explain the significance of Implicit Fact Column in the Presentation Catalog?

Implicit Fact column is used by the OBIEE Server to define the path when a request confines to only dimension tables. It just adds this implicit fact column to the request to generate the SQL, however this column will not be present in the results.

How do we create a Presentation column that its name should display Western Region for West Users and Region for remaining users?

We need to create a session variable say REG_NM which takes the above names for their groups. For the presentation column we need to uncheck the use logical column name and check the check box of Custom Display name and name it as VALUEOF(NQ_SESSION.REG_NM)

How do we Create two Sub Folders under different Folders with the same name in the Presentation Layer?

Both the Sub Folders will have a hyphen followed by the space with their name and they will be placed below the corresponding folders with different presentation table names. Now for the Second Table we will give the Custom display name as the first table.

Eg Region Folder has to be brought under both Customer and Employee Folder. Presentation Layer will look like

Customer
- Region
Employee
- Emp Region

For - Emp Region presentation table, we will have custom display name as Region.