Different Granularities and Different Time Ranges RRS feed

  • Question

  • Here is my scenario:

    Since 2002 we have been recording operational meter data, at an aggregation level (Dimension?) A.  Since 2008, we have been recording the components of that fact data, let's say on a Dimension B.  At a given time sample, fact data along dimension B can sum up to dimension A, with some measurement error (0.1 rounding).  My samples are hourly averages, and I have dimensionalized both sets against a single Time dimension (which I have populated with Years, MonthOfYear, DayOfMonth, Hours, DayOfWeek, etc). 

    I want to be able to create a cube that uses the continuous hourly Time dimension, from 2002-2010, but show the facts of A as long as you are at the outer dimension, and show the dimension attributes of B when you are drilling into the cube.  I would like to see both Total and Sum of my hourly facts.

    Can someone please give me some suggestions on how to structure my Project?  I have the freedom to model it any way I wish.  I have tried a Star schema (put all my facts in the same table) but the difference in granularity did not produce the expected outcome. 


    -- Scott
    Friday, October 29, 2010 12:07 AM

All replies

  • Hi Scott,

    If I am understanding the question correctly, you need one dimension - let's call it DimA, which has a number of levels and a hierarchy, which places your Dimension B on leaves and your Dimension A on one level up from that - this way you can model what you are describing using the same dimension.

    Normally, you would put facts with different granularity in separate fact tables, however, you could also model your dimension DimA to be Parent-Child and then you can store facts against any member in it (inlcuding non-leaf members). Leaves will still aggregate up, so Dimension B will sum up to Dimension A.

    In general, I would try this:

    1. Parent-Child DimA
    2. Fact with a key to both time and DimA, which stores facts on both levels of DimA

    This way, when you browse the cube for periods < 2008, you'll see data only against non-leaf members and nothing on the leaf level (Dimension B), while for post-2008 data, leaves will aggregate to the same level correctly.

    On the flip-side, if you have lots of members in DimA, performance may suffer from utilising P-C dimensions but anything up to a million-2mil members should behave ok.

    Boyan Penev ---
    Friday, October 29, 2010 2:23 AM
  • Hi Scott,

    I would suggest looking at modelling this with separate fact tables, in much the same way you may have Sales Actual measures at a daily level and Sales Targets at a monthly level (have a look at AdventureworksDW). You could then apply a scope statement in the cube to present the separate measures as a single measure (assuming you have no time overlap between the different grain fact tables).

    I would be careful with using a Parent Child dimension, generally the performance is pretty mediocre, more often than not I've seen them implemented only to be ripped out down the track.

    Additionally it seems you have a single time dimension covering Year, Month, Day, Hour, Date etc, this will result in quite a large dimension (87,600 members for 10 years down to hour level, or 5.2M members for 10 years down to minute level), you would likely see much better performance having a separate DimDate (Year, Month, Day) and DimTime (Hour, Minute).


    my blog
    Friday, October 29, 2010 10:02 AM
  • Hi Karl,

    I understood it differently - you are suggesting this based on the assumption that Scott has different granularities on his Time dimension (e.g. some down to minute, some to hour). I thought that he has the same time granularity but different granularity on another dimension and the difference is in the range of Time members which are applicable to each.

    Scott, can you clarify if your facts have different granularity based on Time or something else? In either cae you can follow Karl's suggestion if you think it's more suitable.

    You may also find this msdn page interesting:


    Boyan Penev ---
    • Edited by Boyan Penev Friday, October 29, 2010 2:48 PM added reference
    Friday, October 29, 2010 2:33 PM
  • Hi Boyan,

    Apologies. No I'm not suggesting that Scott has different granularities on his Time dimension, I was just using it as an example. Maybe a better one would be that he previously had his measures at a granularity on the Georgraphy dimension of State and now he has it at City, provided that City is a subset of State and has a user defined hierarchy, he can set the dimension usage accordingly.


    my blog
    Thursday, November 4, 2010 4:48 PM