Build a dimension, a hierarchy that does not aggregate RRS feed

  • Question

  • I need to ask a question again...

    I want to build a cube on the output that a SQL SELECT on the management view sys.dm_db_index_physical_stats(,,,,'Detailed') delivers. Specifically I want to build a dimension on the fields table, index and index level. Each table has a couple of indexes usually, and each index typically has 2 or 3 levels of data. Lets ignore the details like heaps or so. These three attributes form a nice hierarchy and a good dimension by which to analyse the index stats.

    Example data would look like this:
    TableName    IndexName     IndexLevel     Page Count (a measure / value)    Fragmentation (another measure)
    tab1              ind1               0                   5000                                            30%
    tab1              ind1               1                   30                                                10%
    tab1              ind1               2                   1                                                  0%

    Building the hierarchy is easy and straightforward, and it works great in the cube, but I wanted to go somewhere with this and tried to make the dimension not aggregate along its axis. For Page Count, a sum over the Levels would make sense maybe, also from index up to table the sum still has some meaning - But the fragmentation value in percent cannot be aggregated at all in any meaningful way at all I think...

    So I wanted to play with a dimension that uses the hierarchy only for navigation and not for aggregating up any values for any measure. But that is harder as I expected.

    On the dimension editor I can set the IsAggregateable property to false for Index or Level. Then I am told that I need to set it to levels above it too - fair enough, that makes sense. But when deploying this causes that I am also asked to set AttributeHierarchyEnabled to false on higher levels, which in turn does not allow to use the attribute at all in a hierarchy.
    I do not know other properties on the dimension, its attributes or hierarchies that I could try.

    So - can this be done at all? And how?
    If this is a setting that is not done on the dimension itself but on the cube (and all cubes) where the dimension is used (maybe using custom mdx calculations?) it should even be possible to do it different for each measure?

    Any pointers how to start here?
    Thanks and best regards

    • Edited by Ralf_from_Europe Tuesday, May 19, 2009 10:46 PM Added some detail info to clarify
    Tuesday, May 19, 2009 10:34 PM


  • I would not change the dimension, instead I would override the measure. The idea would be to create a scope statement which covered all the attributes which you did not want to see aggregated and then to set the measure to NULL.

    Something like the following:

    SCOPE descendants( [Tables].[Hierarchy].[All], [Tables].[Hierarchy].[Index Name],SELF_AND_BEFORE);
      Measures.Fragmentation = null;
    END SCOPE; - please mark correct answers
    Wednesday, May 20, 2009 5:12 AM