6 Replies Latest reply on Nov 12, 2019 4:29 PM by Hesh

    Session Variables vs Junk dimension vs ....

    Hesh
    Hesh Nov 11, 2019 5:34 PM

      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

        • 1. Re: Session Variables vs Junk dimension vs ....
          Gianni Ceresa
          Gianni Ceresa Nov 11, 2019 5:54 PM (in response to Hesh)

          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.

          • 2. Re: Session Variables vs Junk dimension vs ....
            Hesh
            Hesh Nov 11, 2019 6:28 PM (in response to Gianni Ceresa)

            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

            • 3. Re: Session Variables vs Junk dimension vs ....
              Gianni Ceresa
              Gianni Ceresa Nov 11, 2019 7:53 PM (in response to Hesh)

              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.

              1 person found this helpful
              • 4. Re: Session Variables vs Junk dimension vs ....
                Hesh
                Hesh Nov 12, 2019 4:44 AM (in response to Gianni Ceresa)

                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

                • 5. Re: Session Variables vs Junk dimension vs ....
                  Gianni Ceresa
                  Gianni Ceresa Nov 12, 2019 10:03 AM (in response to Hesh)

                  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.

                  • 6. Re: Session Variables vs Junk dimension vs ....
                    Hesh
                    Hesh Nov 12, 2019 4:29 PM (in response to Gianni Ceresa)

                    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