0 Replies Latest reply: Apr 5, 2012 4:40 AM by lionel925 RSS

    What is the best data model

      Hello

       

      I 'am struggling a little bit to define the appropriate data model.

      Data comes from a dedicated system and can be easily structured in different maners. To keep it simple let say that

      • I have a fact table aggreageting indicators related to customers
      • On this fact table 1 hierarchy is linked :  Customer (customer sub familly / customer familly)
      • Incators are monthly but they can be aggregated by year

       

      The goal is to produce basic charts listed below

      Chart 1


      20112010VAR€VAr %
      Indicator 1



      Indicator 2



      Indicator 3



       

      Char t2 : Gauge : value of indic1/value of indic2

       

      Chart 3


      Fam Cli 1Fam cli 2Fam cli 3
      Indicator 1


      Indicator 2


       

      I have worked in 2 directions but still facing issues regarding the strucuture of the fact table

      Option 1   fact table = customer number, date, indicator code, value 

                     creation an indicator table (indicatir code, name)

      Option 2   fact table = Customer numbre, date Indicator 1, Inidicator 2, indicator 3

       

      Option 2 is my favorite because it is much more simple to develop, you dont need to define set analysis to select the indicator you would like to show, just pick up the  field. It is fine for Chart 2 and 3 but I can't manage to calculate variation between 2 years (chart 1), it's seem also that it is faster and use less memory

       

      I am currently heading for option 2

       

      Does anybody has a feeling

       

      Lionel