Dynamic Cubes: Aggregate Cubes & Parent Child Dimensions

[andy_mason_84]

Hi All,

I am having some issues regarding aggregate tables on a cube with a parent-child dimension.

We have found that when your cube has a Parent-Child Dimension then in the aggregate table you must include a record for EVERY member in the hierarchy (every node in the tree).

In practice this increases the size of the aggregate table considerably. You could easily have 3 times as many hierarchy members as bottom-level members linked to facts. That would quadruple the size of the aggregate table. In some cases that might even give the aggregate table more records than the fact table, making it useless as an aggregate table!

In the main fact table you don't need to do this. As long as the bottom level links to a fact the other levels are rolled up correctly. Why can't aggregate cubes work the same way?

Unless I'm missing something I don't think parent-child dimensions are usable when Aggregation Cubes are required.

Has anyone got any experience of creating aggregate cubes when Parent - Child dimensions?

[bus_pass_man]

The point of including a parent-child dimension in a aggregate is so you can use it in queries which can be routed to aggregate tables.

And no, you would not end up with more records in the aggregate table than the fact table.  The worst you'd get would be exactly the number of records and that's only if you include the leaf level of every dimension of your cube in the aggregate table.

A way to think about it is to pretend that the thing is just a single level hierarchy. 

You're essentially putting that single level hierarchy into the aggregate table.  In itself there's no possibility of aggregating the hierarchy to higher levels ('cause there aren't any) but by including the parent-child dimension in the aggregate table you get to use that aggregate table in a query which qualifies as being routed to the aggregate table. 

With a parent-child hierarchy, you can assign values from a dimension table to any member within the hierarchy; in other words to leaf members and non-leaf members. Data for non-leaf members can also be obtained by rolling up (aggregating) data from leaf members.  If you think of things like salary your manager's salary isn't the sum of his employee's salary so being able to do this is not quite unimportant.

So it isn't necessarily a simple problem of why doesn't Cognos just aggregate the leaf members.


hope that helps.