Do parent-child dimensions store aggregations?

Question

Post reply

Do parent-child dimensions store aggregations?

Mark Tralls

Right there with Babe

Points: 746
More actions
September 7, 2004 at 4:25 am

#109237

Hi
In a new system we are designing, we are considering using parent-child tables to maintain dimension hierarchy data.
From these. AS can easily create parent-child dimensions in our MOLAP cubes, but a consultant has said that when processed, no aggregations will be held for the parent-child dimensions.
I have tried to find documentation on this but with no success.
Please can anyone confirm that this is the case, that no aggregations are held for parent-child dims, as if it's true it rules out their use (as the two parent-child dims are our biggest dims).
Also, if anyone knows where this is documented that would be great.
Thanks
Mark

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Jamie Thomson SSChampion Points: 11805 More actions · Answer 1

Its not true. Parent-child dims DO hold aggregations.

There are some caveats though. I've just tried it out and found a situation where this isn't true.

You can only create aggregations in a p-c dimension if non-leaf data is not visible. This is a problem if your non-leaf members have data attributed to them.

Thanks for posting this because I have learnt something tonight that I did not know. Previously I thought p-c dimensions could always hold aggregations. I now know this isn't true.

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson

Mark Tralls Right there with Babe Points: 746 More actions · Answer 2

Thanks for the reply Jamie.

Sorry if I am being a bit thick, but please could you expand on:

"You can only create aggregations in a p-c dimension if non-leaf data is not visible. This is a problem if your non-leaf members have data attributed to them."

Do you mean if the dim does not go down to the bottom level? Is this possible with a p-c dim? Or do you mean if some levels are skipped in the dim?

I have been trying out some small examples today, but have not managed to get any aggregations (if you exclude 1!) on the p-c dim.

Thanks

Mark

Jamie Thomson SSChampion Points: 11805 More actions · Answer 3

Mark,
A worked example is probably better.

Imagine the following scenario. My organisation has a sales team that I am modelling as a parent-child (p-c) dimension.

Lisa is the Sales Director
Mark and Tim are Regional Sales Managers
Sarah and Lee are sales reps. They report to Mark
Gary and Helen are sales reps. They report to Tim
Claire is also a sales rep. She works in a small area so reports directly to Lisa

So our Sales team hierarchy looks like this (allow for formatting when this gets posted and try and imagine some lines drawn between them):

Lisa

Mark Tim Claire

Sarah Lee Gary Helen

OK, now imagine we have 2 scenarios:
1. Only our sales reps make sales
2. Our sales reps make sales. So do the regional sales managers.

Scenario 2 would be an example of data (i.e. sales) being attributed to non-leaf members. In scenario 1 this is not the case, only the leaf members (i.e. the sales reps) of the sales hierarchy actually make any sales.

Basically, in scenario 1 you could have aggregations. You couldn't with scenario 2. Does that help explain it?

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson

qjt Old Hand Points: 380 More actions · Answer 4

I don't agree with Jamie.

In example of Analysis Service,cube of 'HR' in Foodmart 2000 include p-c dimention 'employee'. The non-leaf data is visible in 'employee'.The aggregations can be processed

Jamie Thomson SSChampion Points: 11805 More actions · Answer 5

Sure, the cube can still have aggregations but not on the p-c dimension.

I base this on the fact that I created a cube with 1 dimension - that being a p-c dimension. I was able to create aggregations but when I set the dimension to have "Non-leaf data visible" I got a message saying the aggregations were removed.

HTH

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 6

A few quick tests on my local (dev Edn, using HR cube, focusing on P-C dim for Employees) showed me the following:

1. Set P-C dim to leaf members only, AS still let me rebuild the aggs for the cube/partition. On building, had to set the missing dim members to ignore as there is actually data in the fact associated with non-leaf members.

2. Set P-C dim to hide non-leaf member data, can still build aggs, no problems on the build process of the cube

3. Set P-C dim to show non-leaf member data, can still build aggs, no probs on cube build.

Things I found:

1. Changing the dimension visibility of non-leaf members with data tells you that the aggs will be deleted from the HR cube, and also that the cube needs to be refreshed. It doesn't actually say that you *can't* or *won't* have aggregations if you rebuild them later.

2. Using the Partition Aggregation Utility (see SSABI) showed that in all three cases, aggregations were made that included the employee dim.

3. Referencing the Perf Guide, as far as I can tell, the only way to forcably exclude a dimension from aggregations is to set the 'Aggregation Usage' value to Custom and then set the 'Enable Aggregations' for each level to 'No' (or Yes if required). Having just tested this on the P-C dim, it looks like you can't set it to custom (to allow for the elimination of levels int he aggs) which in a way makes sense as it's levels are dynamic.

Cheers,

Steve.

Mark Tralls Right there with Babe Points: 746 More actions · Answer 7

Thanks for the clarification Jamie, now I understand what you mean. Also thanks to others that have replied.

But .... I have still not managed to create a cube with aggregations on the p-c dim!

I have created a test cube with a single dim, a p-c customer dim, which links to a fact table. The dim's Member with data parameter is set to leaf members only, as data is only held against customer account number which is at the bottom of the hierarchy. All other levels of the hierarchy will just hold aggregations of the lowest level data.

The dim and cube process fine, but when designing storage, the best I get is "2 aggregations designed (0.0MB, 51%)".

This storage remains the same whatever I set the Member with data parameter to.

I have also played about with Foodmart HR cube that has been mentioned and could not get this to store any aggs on the p-c dim either (but this seems to have data on non-leaf members).

This would lead me to believe that I am either doing something wrong (but, I don't think I am ... it's only 1 dim and a fact table!) or that p-c dims do not store aggs.

If anyone has any further coments, or can send me a small example that holds aggs on a p-c dim I'd be grateful. I need to get a definate answer to this as it greatly affects our new hierarchy maintainence system design and cube solutions.

Thanks

Mark

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 8

How are you determining that there are no aggs being created against the p-c dimension?

The small number of aggregations for the test cube is likely to be a product of the size of the cube (ie single dim). If you would like I can mail you an archive of the HR cube that has aggs against the emp0loyee (p-c) dim.

Steve.

Mark Tralls Right there with Babe Points: 746 More actions · Answer 9

Hi Steve

Thanks for the reply.

I am just going by what the storage design wizard says, all it will go to is the 2 aggs.

Even though the cube only has a single dim, the fact table has 37,000 rows and the p-c customer dim has just under a 1000 members. I would expect this to create some aggregation. If I build a cube with the same fact table and only a time dim I get a few more aggs!

If you remove all the other dims from the HR cube except the p-c employee dim does it still create aggs?

Thanks

Mark

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 10

Hi Mark,

Two things I would suggest initially, download the SSABI (SQL SErver Accellerator for BI) and fire up the Partition Aggregation utility, it will show you what level in what dims the aggregations have been made on. Alternatively you can download the Partition utility from the SQL 2000 Resource kit, but this is the older version of the tool. Secondly go to msdn and take a look at the MSft SQL Server 2000 Analysis Services PErformance Guide. It contains quite a good description of how aggregations are designed, what influences this design and their sizing etc etc.

From what you've outlined earlier, if you have built a test cube with only a single dimension (the p-c), then the two aggregations are based on this dimension. From my interpretation of the perf guide, the aggregation count refers to the number of aggregations to be created, this is not at all indicative of the row count (the number of aggregated rows) within the aggregation. So your agg count of two would be correct as there have been aggregations created on two levels in the dimension ([All] and [p-c level]). This would also account for the addition aggregations created with just a time dim as the count of levels for the time dim will be (should be?) greater than that of the parent child, assuming that the aggregations for parent child can't be made for the 'dynamic' levels that the p-c relationships build. The max potential number of aggregations for the cube is the product of the number of levels from all dims -> 3 dims with 3 levels = 3 X 3 X 3 = 27 aggs. THe potential max records within each agg is determined by the count of members within each of the levels, so (e.g. from perf guide) an aggregation made at the Country, Brand and Quarter levels (from geog, product and time dims respectively), where the levels have 3, 911 and 12 members respectively, the potential aggregation record count would be 32796. It is often less than this due to sparsity.

Hope this helps,

Steve.

Mark Tralls Right there with Babe Points: 746 More actions · Answer 11

Thanks Steve.

I downloaded SSABI, but we are running SP2 on all our servers so I couldn't install. I cannot upgrade to SP3 as the client software has not been tested against it.

I will have to see if I can sort out another machine.

Thanks for pointing me in the right direction. I'll see if SSABI sheds any light on p-c dims.

Thanks

Mark

Ricardo Pinninghoff SSC Rookie Points: 47 More actions · Answer 12

I've found that if in a p-c dimension you not have the [All] level no aggregation for that dimension and de cube will be created.

I hope that hekp some of you.

Ricardo.