Skip to Content
0
Former Member
Feb 03, 2011 at 12:10 AM

Confusion about RollupChildren(), Aggregate(), and Sum() MDX formula

120 Views

Dear all:

SITUATION

I encountered some aggregation error in my Finance application a while back, and adding RollupChildren() MDX helped. Now I just discovered another aggregation error in my Sales application, and instead of RollupChildren() MDX, I had to use Sum() MDX.

DATA

When there exists more than 1 hierarchy, aggregation may not function properly hence MDX formula needs to be applied.

System is BPC 7.0 MS platform with SQL 2005

QUESTION

1. How come aggregation starts acting up when I added additional hierarchy?

2. When do I use RollupChildren(), Aggregate(), and Sum() MDX? What are the "golden rules?"

3. For my Sales application, I have the following:

GrossProfit - Parent - INC type - H1

>>> SalesAmt - Base member - INC type - H1

>>> COGS - Base member - EXP type - H1

In theory, I shouldn't need any MDX to aggregate my SalesAmt and COGS to get to my GP. How come in my case I needed the Sum()? Is it because I have 2 hierarchies in my Sales Application's Account dimension?

4. Do we only use these aggregation MDX in Account type dimension? How come it is not needed for my DataSource, Entity, or Time dimension?

Thank you!

Brian