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.
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
