5 Replies Latest reply on May 19, 2016 12:01 PM by Thomas Dodds

    MONTH_WID to Month or Calculating volume on monthly basis based on month_wid

    Helan Kumar

      I have month_wid and volume columns, i need to bring 12 columns(user request ) for adhoc reporting as Jan Volume, Feb Volume .... etc

      Data in month_wid will be like 201501, 201502 ... etc

       

      How to proceed on this ?

      Note : This fact table is not joined to any time dimensions, both month_wid and volume are coming from same table. This table is connected to a dimension which has Shipping Year column.

        • 1. Re: MONTH_WID to Month or Calculating volume on monthly basis based on month_wid
          Thomas Dodds

          "This fact table is not joined to any time dimensions" --- that's why any approach will probably be inefficient ... you should always have if atr the least a rudimentary time dimension from which to translate keys into textual items for use.

           

          USE OF INFORMATION DRIVES DESIGN

          • 2. Re: MONTH_WID to Month or Calculating volume on monthly basis based on month_wid
            JustTheFacts

            Thomas is correct, the design is sub-optimal, but you have a problem, let's try to solve it. There are a couple of ways to do it.

             

            Brute Force: If you require twelve measures, "Jan Volume", "Feb Volume", etc., you can create them in your logical fact table by using a CASE statement on the last two digits of your month_wid. So for example, your measure formulas would be:

                 Jan Volume: CASE WHEN MONTH_WID MOD 100 = 1 THEN Volume ELSE 0 END

                 Feb Volume: CASE WHEN MONTH_WID MOD 100 = 2 THEN Volume ELSE 0 END

                 ...etc...

             

            Pivot Table: You may not actually need twelve measures, but one measure "Volume" and a "Month Name" dimension. Then use a pivot table to show volume by month. Your "Month Name" dimension would also use a CASE or SWITCH statement, similar to above. CASE WHEN...1...THEN "Jan" WHEN...2...THEN "Feb"...etc.

             

            Shipping Date Dimension: You could improve your design by creating a Shipping Date dimension which includes Shipping Year, Shipping Month, Shipping Date (assuming this is available), etc. The logical source of this dimension would be your main (fact) table + your dimension table which has Shipping Year. You can pull Year directly, and Quarter, Month, Date can all be calculated using CASE statements. The fact that the logical dimension is actually using your "fact" table as a source is of no consequence. To OBIEE it will look like a proper logical date dimension. At some point in the future you could split out an actual physical Date dimension table and then change your mappings to use that table instead - the model will behave the same either way (with possibly slight performance differences).

             

            Good luck!

            1 person found this helpful
            • 3. Re: MONTH_WID to Month or Calculating volume on monthly basis based on month_wid
              Helan Kumar

              Thanks much for your inputs, i never seen this logic till now MONTH_WID MOD 100. Very helpful !!

               

              I do agree with the Pivot view, but user has 12 columns in the source application and she is expecting same in OBI. they have used normalizer to convert it into rows in ETL. This might have some other purpose not sure.

               

              Apart form Shipping Year, i don't see any other time dimension attributes in the table and there are no WID's populated to join with time dimension.

               

              Any how i'll give a try with the case statements.

              • 4. Re: MONTH_WID to Month or Calculating volume on monthly basis based on month_wid
                3139796

                After writing the case statement for month, how to achieve sort(Jan, Feb... instead of April, August...). As it is getting arranged in alphabetical order

                • 5. Re: MONTH_WID to Month or Calculating volume on monthly basis based on month_wid
                  Thomas Dodds

                  Doing this in the front end is not going to get you far ... you need a proper design where you have the text and you can set that logical column's sort based on the numerical representation of the same.