Skip to Main Content

Analytics Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Session Variables vs Junk dimension vs ....

HeshNov 11 2019 — edited Nov 22 2019

Hi,

OBIEE 12C version - 12.2.1.4.0

long post... Apologies

We have one Budget fact table at week level granularity for an SKU and we have another fact Sales at Day level granularity.

Fact_Sales

DATE_KEY SKU_KEY  Sales_Amt

20191110    18009123     234.55

Fact_Budget

Week_KEY SKU_KEY    Budget

2019-45      18009123     2750.31

The budget needs to split/apportion depending on the day of the week... like following

   

MondayTuesdayWednesdayThursdayFridaySaturdaySunday
10161211191418

Currently this is achieved using Variables at RPD level along with a CASE statement in Analysis like following.

Select 10 as D1, 16 as D2 ... from dual;

Case when WK_DAY=1 then D1* when WK_DAY=2 then D2 End

Now the business would like to enhance this and we need few more different splits for different weeks like Week-23 needs different split and Week-32 need different like following ...

    

WeekMondayTuesdayWednesdayThursdayFridaySaturdaySunday
Other10161211191418
2019-2317171414121115
2019-32188014201822

The Variables along with CASE may not look appropriate now( neither it was earlier ). What I am thinking is to add a Junk dimension with DATE_KEY, WEEK_KEY, APP_VAL.

Appreciate your thoughts!

Regards

Hesh

This post has been answered by Gianni Ceresa on Nov 12 2019
Jump to Answer

Comments

james_sutherland
This should work if using TopLink/EclipseLink.
1 - 1