Fact and Dimension from single source Table obiee

Fact and Dimension from single source Table

Normally in OLTP systems to generate a adhoc report may get a single source table which should act as Fact and Dimension.

For example a table contains Order Status, Order Date and its value ,cost.

We can achieve this in three ways.

1 ) Create Alias of main table and make it as dim and fact and join it both
(self join).

2) Import the table to physical layer and create two logical tables where source is above table and join it accordingly

3) Create a opaque view write your own sql which should have key column to join (self join - same as 1)




1)  In below example I have taken SAMP_REVENUE which has both dim and fact columns created  alias   Dim - Status and  Fact - Revenue and joined these in physical layer based on key  'Bill Day Dt' pull to BMM layer.

 

Lets create a report and check the obiee generated query  which takes the source SAMP_REVENUE_F and joining it based on bill_day_dt (self join) 

Above method will have performance issues to do self-join between the same table.

2) In this example don't create any alias of table(Dim - Fact) . 

Create new logical table Dim - Status and pull the dimension columns from physical table SAMP_REVENUE_F

Create new logical table Fact- Revenue and pull measure columns from same physical table SAMP_REVENUE_F

Create the same report as above now check the sql generated

 

There is no Self-join it directly hits main table SAMP_REVENUE_F  for better performance  go with 2nd approach , 3rd approach is similar to 1st one where you have opaque view instead of Alias.