Managing multiple tables with same join cols- OBIEE

1.   Senario

AR Outstanding Payment snapshot table basically captures the customer outstanding payment for each month. Ie: the outstanding payment of the customer is calculated each day and keep a single snapshot record for each customer for each month. This is done by updating the snapshot records for the customer each day with the current snapshot and freezing the records for the month as the month ends.

Example:

Snapshot Date	Customer Name	Outstanding Amount
1/31/2010	Ford	$10.00
1/31/2010	GMC	$20.00
2/28/2010	Ford	$10.00
2/28/2010	GMC	$40.00
3/30/2010	GMC	$50.00
4/3/2010	GMC	$50.00

Table1 : Monthly DSO

Assuming the current date is 03-Apr-2010, there are 2 customers (GMC and Ford) and the customer is the lowest grain of this information,

The first 2 records shows the outstanding payment of Customer at the end of Jan-2010

Now, during  February-2010 the customers made some transaction so that at the end of Februvary the customer Ford have $10 outstanding and GMS $40.

In the month of March-2010, ford paid the outstanding amount and GMC made some more purchase so that the outstanding amount for Ford is Nothing and GMC is $50

In the month of April 2010, till today (3^rd April) GMC didn’t made the payment and holds an outstanding amount of $50

Now: As per the above table

Ø  The outstanding payment for the customer for the Quarter1 – 2010 is

3/30/2010

GMC

$50.00

Ie: this is the out standing payment at the last month of the Q1-2011

Ø  The outstanding payment for the customers for the Quarter2 – 2010 is

4/3/2010

GMC

$50.00

Ie: the Current Months outstanding payment so far.

Now, the scenario is that,

Ø  when a user selects Month and The Outstanding Balance, the user should see the outstanding balances for each customer as shown in the table1

Ø  If the user selects only Quarter and Outstanding payment in the report, the report should show the last month’s outstanding payment for that Quarter

Ø  If the user selects only Year and Outstanding payment in the report, the report should show the last month’s outstanding payment for that Year

We can implement the solution for this in many ways including aggregate (here aggregate means taking the correct record to the next level) the transaction table to Qtr and Year level and report the amount from the aggregate tables or to keep a flag for Qtr level reports, year level reports etc... The below sessions show how we can implement this without using aggregate fact tables or changing / updating data in fact tables

Note: Please note that the below scenario is true only for snapshot tables not true for the normal aggregate functions etc…

2.   Base Work

To implement this solution, a small amount of ETL work is required to populate correct dimensional information for time

Now Lets have a look on the Time Dimension we have

Date	Month	Qtr	Year
01/01/2010	Jan-2010	Q1-2010	2010
…….	……	…..	…..
02/01/2010	Feb-2010	Q1-2010	2010
…….	……	…..	…..
03/01/2010	Mar-2010	Q1-2010	2010
…….	……	…..	…..
04/01/2010	Apr-2010	Q2-2010	2010

Now, we need to generate the below tables with the information shown

1.     For Month – month Level aggregated Time Dimension

Month	Start Date	End Date	Qtr	Year
Jan-2010	1/1/2010	1/31/2010	Q1-2010	2010
Feb-2010	2/1/2010	2/28/2010	Q1-2010	2010
Mar-2010	3/1/2010	3/31/2010	Q1-2010	2010
Apr-2010	4/1/2010	4/30/2011	Q2-2010	2010

Here the Start and End dates are the start and End dates for the month. This need to be generated bt an ETL/MVew etc…

2.     For Quarter – Qtr Level aggregated Time Dimension

Qtr	Start Date	End Date	Year
Q1-2010	1/1/2010	1/31/2010	2010
Q1-2010	2/1/2010	2/28/2010	2010
Q1-2010	3/1/2010	3/31/2010	2010
Q2-2010	4/1/2010	4/30/2010	2010

Here the Start and End dates are

o    If the Quarter is already passed, it should be the start and End Date of the last month of the Quarter

o    If it’s the current Quarter, it should be the start and End Date of current month in the Quarter.

Assuming the current date is 03-Apr-2010, the start date and end date for the quarter should be 4/1/2010 and 4/30/2010

3.     For Year – Year Level aggregated Time Dimension

Year	Start Date	End Date
2009	12/1/2010	12/31/2010
2010	4/1/2010	4/30/2010

Here the Start and End dates are

o    If the Year is already passed, it should be the start and End Date of the last month of the Year

o    If it’s the current Year, it should be the start and End Date of current month in the Year.

Assuming the current date is 03-Apr-2010, the start date and end date for the Year 2010 should be 4/1/2010 and 4/30/2010

The start and end dates of the Qtr and Year should be properly managed for this solution and the snapshot table (fact) should be in the Month level snapshot.

 

3.   OBIEE Work

3.1    Physical Layer

·         Step 1

Create 2 aliases for out fact table

Lets call Our actual fact table as  DSO_SNAPSHOT_FACT

And alias as

Alias 1: DSO_SNAPSHOT_FACT_MTH
Alias 2: DSO_SNAPSHOT_FACT_QTR

Alias 3: DSO_SNAPSHOT_FACT_YEAR

Note: You can implement this solution with out creating the Alias tables in the physical layer with some impact in the solution, but let me explain with the alias tables.

·         Step 2

Join the Time tables with the fact table as shown below (assuming that all other joins for the fact table is already taken care)

Lets call our time tables as

SNAPSHOT_TIME_MONTH

SNAPSHOT_TIME_QTR

SNAPSHOT_TIME_YEAR

Join (complex) should be as shown below

Table 1 (Dim)	Table 2(Fact)	Join
SNAPSHOT_TIME_MONTH	DSO_SNAPSHOT_FACT_MTH	Dim.strat date<=fact.snapshot date AND Dim.end date>=fact.snapshot date
SNAPSHOT_TIME_QTR	DSO_SNAPSHOT_FACT_QTR	Dim.strat date<=fact.snapshot date AND Dim.end date>=fact.snapshot date
SNAPSHOT_TIME_YEAR	DSO_SNAPSHOT_FACT_YEAR	Dim.strat date<=fact.snapshot date AND Dim.end date>=fact.snapshot date

3.2    BMM Layer

·         Step 1

Create a logical table for Snapshot Time. Lets call the Logical folder as “Snapshot Time”

·          Step 2

Bring in the below physical tables as the source for the Snapshot Time logical table and map all cols as available in the tables

SNAPSHOT_TIME_MONTH

SNAPSHOT_TIME_QTR

SNAPSHOT_TIME_YEAR

·         Step 3

Create a Dimensional Hierarchy for the SNAPSHOT TIME table with the level YEAR à QUARTER à MONTH and assign the elements to the hierarchy from the Logical SNAPSHOT TIME table

·         Step 4

Define the Content of each Physical Sources for the Logical SNAPSHOT TIME table as shown below

  

·         Step 6

Create a logical table for the Fact. Lets call the Logical folder as “AR-DSO_SNAPSHOT_Poc”

·          Step 7

Bring in the below physical tables as the source for the AR-DSO_SNAPSHOT_Poc logical table

DSO_SNAPSHOT_FACT_MTH
DSO_SNAPSHOT_FACT_QTR

DSO_SNAPSHOT_FACT_YEAR

·         Step 8

Define the Content of each Physical Sources for the Logical AR-DSO_SNAPSHOT_Poc table as below for the “Snapshot Time” Dimension

·         Step 9

Define the aggregation rule for the measure cols.

3.3    Presentation Layer

·         Step 1

Bring the Fact table and the Snapshot Time table (necessary cols) to the Presentation layer

3.4    OBIEE Answers

Now we are ready with our solution, lets test the same

3.4.1     Scenario 1

Report should show Year and Outstanding Balance. Report created by selecting the Year col from the time dim and the Measure from the fact.

Ie: When we run this report, this should hit the tables DSO_SNAPSHOT_FACT_YEAR and SNAPSHOT_TIME_YEAR  to get the results

Physical Query Fired By OBIEE:

select T2044808.YEAR as c1,

     sum(T2044858.GLOBAL_AR_AMOUNT) as c2

from 

     SNAPSHOT_TIME_YEAR T2044808,

     DSO_SNAPSHOT_FACT T2044858 /* AR_SNAPSHOT_FACT_YEAR */ 

where  ( T2044858.SNAPSHOT_DT between T2044808.START_DATE and T2044808.END_DATE ) 

group by T2044808.YEAR

order by c1

As you can see, the query hits the Year alias fact table (in turn hitting the base table DSO_SNAPSHOT_FACT) and the SNAPSHOT_TIME_YEAR table to get the data.

1.4.2     Scenario 2

Report should show Quarter and Outstanding Balance. Report created by selecting the Quarter col from the time dim and the Measure from the fact.

Ie: When we run this report, this should hit the tables DSO_SNAPSHOT_FACT_QTR and SNAPSHOT_TIME_QTR to get the results

Physical Query Fired By OBIEE:

select T2044801.QUARTER as c1,

     sum(T2044817.GLOBAL_AR_AMOUNT) as c2

from 

     SNAPSHOT_TIME_QTR T2044801,

     DSO_SNAPSHOT_FACT T2044817 /* AR_DSO_SNAPSHOT_QTR */ 

where  ( T2044817.SNAPSHOT_DT between T2044801.START_DATE and T2044801.END_DATE ) 

group by T2044801.QUARTER

order by c1

As you can see, the query hits the Quarter alias fact table (in turn hitting the base table DSO_SNAPSHOT_FACT) and the SNAPSHOT_TIME_QTR table to get the data.

1.4.3     Scenario 3

Report should show Month and Outstanding Balance. Report created by selecting the Month col from the time dim and the Measure from the fact.

Ie: When we run this report, this should hit the tables DSO_SNAPSHOT_FACT_MTH and SNAPSHOT_TIME_MONTH to get the results

Physical Query Fired By OBIEE:

        select T2044794.MONTH as c1,

             sum(T1807937.GLOBAL_AR_AMOUNT) as c2

        from 

     SNAPSHOT_TIME_MONTH T2044794,

     DSO_SNAPSHOT_FACT T1807937 /* AR_DSO_SNAPSHOT_MTH */ 

where  ( T1807937.SNAPSHOT_DT between T2044794.START_DATE and T2044794.END_DATE ) 

group by T2044794.MONTH

order by c1

As you can see, the query hits the Month alias fact table (in turn hitting the base table DSO_SNAPSHOT_FACT) and the SNAPSHOT_TIME_MONTH table to get the data.

/*-------------------------------------------
CREATED_BY : JJN
CREATED_DATE : 16-Mar-2011
----------------------------------------------*/