Applied Dimensionality

Finally, we’ve solved problem stated in communities. And Robert allowed me to publish resulting library here as well. See attached zip file.

Problem statement

We have 2 cubes, one containing product groups sales (for example) and the other containing detailed product data. Users should be able to correct overall group totals and correct detailed product distribution.

If group A contains Product 1 and Product 2 and if Total(A) is 100, it’s initially split 50\50.

User should be able to modify Product 1 to 75, yielding result of 75\25. Normal break back won’t work this way, because it’ll see 75\50 as profile for break back and will produce 60\40 as a result.

So you’d need to create an own break back. That’s easy )

Verbal Description of the algorithm

It’s a copy-paste from communities topic, don’t bother re-reading.

1. For example we have a 2 dim cube
``````Total 100
A 70
B 30
``````

I want A to become 800

1. Calculate the amount I need to break-back everywhere else to get total of x + 800 = 100
``````x = 100 - 800 = -700
``````
1. Copy all other initial numbers, except A (set A=0) — that’s potential trouble, you’ll have to insure no-zero data input
``````Total 70
A 0
B 70
``````
1. Break-back -700 across it
``````Total -700
A 0
B -700
``````
1. Sum it up with A amount of 800
``````Total 100
A 800
B -700
``````

Implementation

A working Analyst library is attached so I just want to highlight some details:

• inputValue and value as subtotal. That’s the only way to allow input into break-backed cell.
• transactional logic is implemented in very same way I described it.
• no manual items allocation in dlinks on dimensions other that transactionalLogic_1, only match descriptions and self-updating allocation table. This allows easy updates.
• 3 elements are redundant in attached library (see comments). They are left for clarity of implementation.
• dlists are named according to my own convention, I’ll write about it a bit later

Thanks Robert, it was an interesting task.