Post reply

SSAS hierarchy change

  • February 24, 2015 at 12:11 pm

    #151402

    I have a user-defined hierarchy in my cube that goes:

    [font="Courier New"]Region --> State --> Operating Unit --> Franchise[/font]

    There are a number of calculations in the cube that use this hierarchy.

    What are the ramifications/What would I break, if I added a level on top of that hierarchy? Something like:[font="Courier New"]

    Country --> Region --> State --> Operating Unit --> Franchise[/font]

    Your input is appreciated.

    Thanks,

    Rob

  • February 25, 2015 at 6:12 am

    #1779875

    It depends on how the hierarchy is being referenced in the calculations. For example if a calculation referenced the hierarchy like this:

    [Your Hierarchy].[Operating Unit].MEMBERS

    then you would be ok. However if the calculation is referencing the hierarchy using any "familial" functions such as

    [Your Hierarchy].[Operating Unit].CURRENTMEMBER.PARENT

    then it is more likely to break it, depending on what's changed.

    My advice would be to create the new hierarchy alongside the old one and test out the current calculations (with appropriate name changes) against it in a query, comparing them to the current calculations against the old hierarchy. If they yield the same result then it's likely they won't need to change.

    I'm on LinkedIn

  • February 26, 2015 at 10:00 am

    #1780315

    Thanks for the advice PB_BI.

    This started out as modifying PPS dashboards so that we could only roll-up to the country level. Simple, I thought, just change the filter to stop at "country" rather than the top level of the hierarchy. Well the existing hierarchy didn't have country, so I had created a separate hierarchy that included country, changed the PPS filters to use the new hierarchy -- but that caused problems. The PPS charts/reports were using cube calculations that used the old hierarchy. Rather than change the calculations to use the new hierarchy (there were a lot of calcs), I changed the existing hierarchy to add the country. Everything works fine -- I did double check to look for .Parent (familial/relative) coding.

    Thanks again,

    Rob

  • February 26, 2015 at 10:14 am

    #1780318

    This clip from Snatch springs to mind:

    https://www.youtube.com/watch?v=MepMN-1fO90

    Glad I could help 😀

    I'm on LinkedIn

Viewing 4 posts - 1 through 3 (of 3 total)

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