2 Replies Latest reply: Feb 7, 2016 6:22 PM by Mike Davis RSS

    Different aggregation measures in a single fact table?

      Hi,


      This is a follow on to a previous question (https://community.qlik.com/message/965090#965090) as I’ve used the very helpful answers to experiment but have a further question I’d be very grateful for some advice and thoughts on.

       

      As I mentioned in the previous post the data I am using is sales and demand related and I’m trying to get my data model right so that it would provide a good experience in Cliq. My main priority would probably be to favour flexibility in querying over performance if i have to choose.


      I think I’ve decided to go down the route of trying a single fact table that holds the sales and demand measures I have rather than use / rely on synthetic keys.

       

      However, whilst most of the measures are dimensioned at a daily level i have a few for which I have weekly values and some which have no time dimension at all and/or other dimensions.

       

      There are quite a few measures (around 40 or so) and for some of them would be alot of history (millions of rows) whilst others not as many (thousands)

      I’ve tried the suggestion in original post of converting the daily measures to week dimension and that does work but I would like to keep the low daily measure granularities as well for flexibility and drill down.

      (Note - for the weekly measures I don’t have any lower level granularity data - just the agg. measure value)

       

      As an example of the data for say 3 measure types of day/week/none - calling them M1, M4, M7:

      Screenshot 2016-02-06 19.26.56.png

      So I was thinking, in order hold the 3 types of measure in the single fact table I would have a single date column and another column called agg_level (with values of daily, weekly, none) and  it would look as follows:

       

      Screenshot 2016-02-06 19.27.01.png

      And giving me a data model something like:

       

      Screenshot 2016-02-06 19.36.23.png

       

      That would seem to be quite clean but I'm not sure if i'll hit problems with the AGG_LEVEL using this model in cliq?

       

      So, I would really appreciate thoughts and advice on the above.

      Am i on the right track? Is this a valid approach?

      Is there a better way of doing this?

      Any likely pitfalls for this approach or issues to be aware of ?

       

      Really appreciate any thoughts or suggestions.

       

      Thanks,

      Mike

        • Re: Different aggregation measures in a single fact table?
          Stefan Wühl

          I think you are on the right track in general.

           

          You need to remember though, that any selections in a dimension will reduce to your fact table records to the possible values based on selection. Especially when looking at Date and DC_ID, selection in these dimension tables will prevent your M4 and M7 measures to be calculated out-of-the-box though you can work around this using set analysis.

           

          For different granularities like daily and weekly measures, you can look into

          Fact Table with Mixed Granularity

            • Re: Different aggregation measures in a single fact table?

              Hi swuehl,

              Many thanks for the reply.

              The link to "fact table with mixed granularity" is very useful - I think i can see the general premise of how generic keys work. Although it looks like it could be quite complicated to create the fact table. I'll have to have a play with that.

               

              I also searched on 'Set Analysis" and watched the intro/explanation videos on it. I can see how that can be used to restrict to specified sets of values by creating set expressions. I couldn't quite see how it would apply to being able to work around the issue of measures the M4 and M7 measures being excluded for Date and DC_ID dimensions. Would you be able to expand on a bit further on what you meant by that or give an example ? (I see what you mean about how they would reduce the fact table - just not how could work around that with set expression.)

               

              Thanks again for you help!