OBIEE 11g - Denormalizing physical tables in BMM layer(Snowflake)

In general logical  tables can be demoralized by joining the Dim's and Fact's in general tab of Logical Table in BMM layer.

Let's take example of HR schema physical looks like

 

Employees and JOBS table has metrics Salary , MAX , MIN Salary

 

Above figure shows you how I joined the tables in physical layer

Regions -- Countries-- Locations -- Departments -- Employees - Job History - Jobs

BI Server doesn't know whether we have joined the Dim - Fact in physical layer the Cardinality 1 : N and Join Type will be in BMM layer using Complex Join

I would like to get the all the dimension related columns/tables under a new Logical table

So,we are going de-normalize the physical tables in BMM layer as shown below

1 . Create a new logical table right click on BMM folder

2. Give a 'name' and click on OK.

3. Drag and drop 'Regions'/'countries' Physical table to BMM layer(depends on how you joined in physical layer) under newly created 'Logical Table'

4 . Double click on LTS in general tab click on ADD it will show the physical table relationship (How the tables joined in PK-FK)

 

once you select the table it will add in Joins section as shown below

Repeat the above step to add all the tables

 

If you remember Employees table has metric Salary which is fact we haven't pulled it over New logical dimension table.

Now join both the Dim - Fact(de-normalized)

Create custom tables(folders) in presentation layer 

 

 Sample obiee report 

OBIEE generated query where it joined the way how  we mentioned in General Tab -> Physical tables

select T95.CITY as c1,
     sum(T69.SALARY) as c2
from 
     EMPLOYEES T69,
     JOBS T82,
     JOB_HISTORY T88,
     DEPARTMENTS T63,
     LOCATIONS T95,
     COUNTRIES T58,
     REGIONS T103
where  ( T69.EMPLOYEE_ID = T88.EMPLOYEE_ID and 

T58.COUNTRY_ID = T95.COUNTRY_ID and

 T58.REGION_ID = T103.REGION_ID and 

T63.DEPARTMENT_ID = T69.DEPARTMENT_ID and

 T63.LOCATION_ID = T95.LOCATION_ID and 

T82.JOB_ID = T88.JOB_ID ) 
group by T95.CITY
order by c1