Rollup design issue in building hierarchy

[Rams]

Hi Everyone,

I got a scenario where the budget values in the data file having some numbers against the parent as well, like below

Data File.PNG (5.49 KiB) Viewed 4782 times

If I skip the consolidation load, then cube default rollup is different then what it usually has,

Cube Rollup.PNG (5.07 KiB) Viewed 4782 times

so I have decided to roll up everything to show as a single measure "value=allocated+unallocated" to get that, I have to create a dummy element on each level to store the unallocated & allocated. At one point, I'm also making the complex hierarchy, rather than simple structure, like below

Cube View.PNG (8.25 KiB) Viewed 4782 times

So ultimately, rollup in the cube is what I need rather than loading on the consolidation level, did anybody have this scenario before, is there a better way of doing it or some workarounds to get it. please let me know your thoughts.

Thanks in Advance!!

[Wim Gielis]

Hi

Either you use dummy elements (my preferred option), either you load the complement on to one of the children (maybe on a different measure), ...
There is no real workaround other than these, unless I'm overlooking the obvious.

[BariAbdul]

Thanks Wim,Could you please elaborate further with an example.

[Wim Gielis]

Thanks Wim,Could you please elaborate further with an example.

Euh, didn't Rams explain what he was doing in the opening post?

I think some part of the overall solution will also depend on the layout of the data source (file) or relational table (columns) and so on.
If unconvenient, you could store the values for the consolidated cells in a different cube, and a second process can then add the difference between the main cube and the desired totals, to cells at the lowest level. Whether dummy elements or not.

[BariAbdul]

Thanks Wim,Appreciate it.

[Wim Gielis]

You're welcome!

[Rams]

Thanks Wim!, you are absolutely right loading the consol data in flat and bring it back in hier. Also I had a quick brainstrom session with business to understand the design that, there will not be any unallocated left in parent level, means whatever parent got everything will be distributed to their childs, looks like below.

Data File 2.PNG (4.88 KiB) Viewed 4683 times

So that gives me an another option like below,

1. Created a cube(A) to hold the number in consol level, just like loading the data file directly to reflect the exact budget numbers in the existing system. I call that as Cube(A) Non-Rollup.

2. Created another cube(A) exactly the same like above, but not loaded in the consol level so it will rollup ideally. I call that as Cube(A) Rollup

3. Final cube(B) has to show only the value measure with other detailed information, so Cube(A) Rollup will feed the "value" measure to Cube(B).

Cube View 2.PNG (25.94 KiB) Viewed 4683 times

It means Cube(A) Non-Rollup has budget on divisional level which has unallocated info, but Cube(B) is to show only the project level allocation. That works the way I want!!

Cheers!!