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
Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday |
10 | 16 | 12 | 11 | 19 | 14 | 18 |
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 ...
Week | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday |
Other | 10 | 16 | 12 | 11 | 19 | 14 | 18 |
2019-23 | 17 | 17 | 14 | 14 | 12 | 11 | 15 |
2019-32 | 18 | 8 | 0 | 14 | 20 | 18 | 22 |
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