Session Variables vs Junk dimension vs ....

Hesh

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

Gianni Ceresa

The first question is: what is your real target?

Budget is at the week level, and now you seem have daily values with little to no link between the days and the week figures as nowhere has been said that the SUM of the daily split will be equal to the weekly figure you have.

So what is the role of that "daily budget" thing? Is it an attribute? Is it a measure? Is it supposed to be a more detailed granularity of your weekly budget?

The answer of what this thing is going to be will drive how it is better/possible to do it.

Hesh

Hi Gianni,

Thank you... the sum of daily budget is equals to the  weekly budget ( for instance 10+16+12+11+19+14+18=100)

We have this static split now we need few exceptions for few weeks like I mentioned for 23 & 32 where the split values for each day will be different from normal week values, but sum of all should be always 100.

Regards

Hesh

Gianni Ceresa

The weekly split being always = 100 sounds like a percentage, so that you can take the weekly budget and make it a daily budget by applying the %.

This is something which would work better at the ETL level: you don't have a fixed split anymore, you start with 2 special weeks but potentially you will have 52 exceptions or directly a daily budget from the budget and forecasting process.

So you would have the most benefits by having the budget at the day level on the ETL side and let OBIEE do aggregations as needed by the analysis, just like it does with the sales figures.

Hesh

Thank you Gianni,

Yes those split numbers are percentages making cumulative as 100.

A new table in ETL sounds a good solution for this. Not sure how much amount of work would be required to make this available at OBIEE level.

Regards

Hesh

Gianni Ceresa

Having a database object with the daily values will make your work in OBIEE easier as you can either change the joins and granularity of the current one or just add the new one as a new source for your current logical fact table. By telling OBIEE that this new database object can go down to the daily level OBIEE will automatically pick the source to use based on the requests: it will stick to the weekly budget table for weekly, month, year figures. And it will switch to the daily budget object when requested at the day level.

That's also why, as a temporary solution waiting for the job in the DB to be done you could also use a view or (if really not having another option) an opaque view in OBIEE with the logic inside. Ideally even a materialized view could make it, it would be refreshed by a trigger when changes in the daily allocation of the weekly budget. (As you see there are tons of options which depends a lot on your internal processes and rules etc.).

I would just not keep that kind of logic (applying the daily split) at the OBIEE level as it's quite heavy to be done often in queries.

Hesh

Hi Gianni,

Thanks again! very thoughtful explanation! ...  I will go with the day level granular table in ETL, lets see how it goes further...

Regards

Hesh