Skip to Content
author's profile photo Former Member
Former Member

BPC Alternate hierarchy rollup issue - require help

Hi All,

We are seeing a rollup issue on Alternate Hierarchies. We had set-up alternate hierarchies on our Country Dimension (DC) to assist some managerial reporting that is only relevant to one of our Operating segments.

To show the problem, I am listing an example of our Country dimension member sheet. I have put "**" delimiter between the pertinent values to show which columns the values land under.

ID*DescriptionPARENTH1PARENTH2FORMULAH1*FORMULAH2

MAIN_HIER*Main Hier* ** ** ** **

D1*DC EuropeMAIN_HIER* ** ** **

A*Country AD1SG1* ** **

B*Country BD1SG1* ** **

C*Country CD1SG2* ** **

D*Country DD1SG3* ** **

D2--DC India*MAIN_HIER* ** ** **

E*Country ED2--ALT_HIER* ** **

C1*Calc Eur* ** *DC.H1.D1+DC.H1.E* **

ALT_HIER*Alt Hier* ** ** ** **

SG1*Sub Reg 1* *ALT_HIER* ** **

SG2*Sub Reg 2* *ALT_HIER* ** **

SG3*Sub Reg 3* *ALT_HIER* ** **

We have our main Country hierarchy that comes under the root parent MAIN_HIER. In addition to that on our main hierarchy we had a calculated node for Calc Europe (C1) which is supposed to be a summation of DC Europe (D1) + Country E (E). We had created an alternate hierarchy to represent a breakdown by more subregions (SG1, SG2, SG3 and Country E) within DC Europe so basically the root node of the alternate Hierarchy (ALT_HIER) should show the same amounts as the calculated node C1 on the main hierarchy.

If we take a higher level Account dimension member like NET_SALES the values tie out but if we take a base member in the Account Hierarchy (e.g one representing Customer Sales), the values don't match with the ALT_HIER values seem being the wrong ones. It almost seems like the rollups of the Alternate Hierarchy are not accurate.

Could you pls. shed some light on if the dimension member set-up above is wrong from any angle.

BTW, we are on BPC 7.0M SP04 Patch 2 and SQLServer version is SQLServer 2005 EE SP3.

Your insights would be greatly appreciated. Thanks in advance.

Regards,

Abhay Shanbhag

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Feb 05, 2010 at 12:59 AM

    Abhay:

    Please try below...

    Add "RollupChildren(Account.Hx.CurrentMember,"+") to all non-base members who don't aggregate properly under FORMULAHx. For instance, I have below hierarchy PARENTH1 for Balance Sheet Total Asset

    BALSHEET

    TotalAsset

    -CurrentAsset

    -NonCurrentAsset

    If CurrentAsset and NonCurrentAsset all roll up fine but TotalAsset doesn't add up. In your Dimension, put MDX formula RollupChildren(Account.H1.CurrentMember,"+") for TotalAsset and BALSHEET under FORMULAH1.

    We have this issue when we created additional hierarchies, and it took us a while to debug before I got this hint from SAP Support. Ever since it works like a charm =)

    Hope this helps

    Brian

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Feb 05, 2010 at 05:59 PM

    Many thanks Brian! Your solution truly works like a charm. Is there a guide or document of such tips for various issues?

    Regards,

    Abhay

    P.S. I am giving you full points

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.