Performing Relational style querying/processing in TM1

[fleaster]

Hi all,
just want to pick a few brains on this one. say I have something simple like a cost centre-product allocation process ie

Table 1 - $ balances by Dept
Dept A = $100 total
Dept B = $200 total

Table 2 - Product allocation by Dept
Dept A - Prod1 = 10%
Dept A - Prod2 = 20%
Dept A - Prod3 = 70%
Dept B - Prod1 = 100%

Table 3 - final product allocation $ by dept
Dept A - Prod1 = $10
Dept A - Prod2 = $20
Dept A - Prod3 = $70
Dept B - Prod1 = $200

...so basically Table 1 x Table 2 = Table 3. Now if this was done in Access/relational database, it is just a simple query - however in tm1/olap it seems to be a bit more complicated. The solutions i have so far are:

(A) TI for "More vs Less Records"
Table 1,2 and 3 are separate cubes. Run a TI process based on Table/Cube 2 ("More records") and reference vs Cube 1 balances ("Less records") to calculate split balances to post into cube 3.
CONS:
-if allocation combinations are missing from Cube 2, they will not generate any balances
-TI must trawl through Every combination in Cube 2, unless there is some filter to exclude combos with $0 balances in Cube 1

(B) Cubes & Rules
Create 3 separate cubes. Cube 3 will calc split balances using Rules based on Cubes 1 and 2.
CONS:
-performance issues where large amts of data are concerned, and the calc is based on multiple complex dimensions

(C) Concatenate fields in 1 Dimension
Concatenate fields in Table 2 into single elements (e.g. "DeptA-Prod1","DeptA-Prod2" etc) in a dimension, and store % in attributes.
CONS:
-same effect as solution #A, but less flexible??

(D) Run relational query externally
Use access/another relational database to run the query externally, then import the data back into tm1
CONS:
-inefficient process with importing/exporting data etc

...this is all I could think of - anyone have any other ideas?

[BigG]

Have you tried rules and found a performance issue - rules shouldnt be too difficult? Dont think the concatenated approach is the best, sounds like a Planning (Ep) set up.

Could you make the views a bit easier for the % allocation by setting up a couple of subsets for each department (with only applicable products). Could build off an attribute if one avaiable.

if you do have some filtering you could apply to views / subsets then maybe you could look into conditional feeders?

Cube 1 has time dimension, cube 2 does not for %'s, while cube 3 has allocated by time again?

BTW, Should Dept B - Prod 1 be $200 in the example?

[Olivier]

I would personally go for either option A or Option B.

The little twist I would add to your options would be to aim to send the result back in Cube 1 and enable the users to see the view pre and post allocations in one cube.
i.e. the data come in a "no product" element and get distributed in each of the product based on your allocation %.
This could remove the need for the cube 3 all together.

If you do it with rules the user from the same view could easily grab the percentage that has generated a specific allocation through the trace calculation ...

The choice between rules and processes is not the easiest one to make.
I have preferred the process approach in my environment to avoid to generate to much inter cube cube dependencies and avoid future performance impacts as the data in the cubes will grow.
My Allocation is meant to be allocating actual data loaded weekly.
I might have taken another approach if it was related to a budgeting module that require more responsive updates based on manual inputs by users.

Hope this helps,

[David Usherwood]

@fleaster, allocations are a standard TM1 task and pretty easy to do with rules. You will need to write proper feeders (normally from input, but try from driver as well).
Introducing the relational idea is confusing calculation with storage.
I do not recommend using TI, because rules are traceable and TI is very hard to trace. But a hybrid approach, freezing the results of rules with a TI, works well, and the frozen number will perform fast without feeding.

[fleaster]

hi all, thanks for the feedback - will need to experiment a bit to see which result works best...

@fleaster, allocations are a standard TM1 task and pretty easy to do with rules. You will need to write proper feeders (normally from input, but try from driver as well).
Introducing the relational idea is confusing calculation with storage.
I do not recommend using TI, because rules are traceable and TI is very hard to trace. But a hybrid approach, freezing the results of rules with a TI, works well, and the frozen number will perform fast without feeding.

David, could you expand on the "freezing the results of rules with a TI" part - I was not aware of any TI functions that allowed freezing of rule calculated cube data.... unless you are suggesting outputting the rule calculated data to values, and reloading this to another cube using TI...?

[David Usherwood]

More or less - but you don't need to push them out to a flat file first - just define a source view including the ruled values (not the C levels) and have a destination cube without the relevant rules (you will need 'KPI' rules in the destination cube). Read the view, write the cube.

[mattgoff]

Option B. I'm not sure why you're concerned about rule performance. In general, doing what you describe via rules is the meat and potatoes of TM1. I have a similar allocation cube with millions of intersections calculated by rules, and it runs fine-- not even close to being one of my performance concerns. Also, unless you're not sharing all of your design constraints, cubes 2 and 3 should be one cube.

Matt

[fleaster]

thanks again for the feedback... ok, would like to throw out a few more thoughts on allocation formats:

Option D: Cube 1 contains $ balances + allocated splits, which are rule calculated from Cube 2 (which contains % splits). Cube 1 and 2 are identical in terms of dimensions
Note: Cube 1 and Cube 2 are identical in terms of dimensions to allow maximum flexibility of allocation methodology
e.g. one method may call for allocations by cost centre, another by GL account+cost centre combination, another by the GL/CC combination + another dimension etc etc

Option E: Cube 1 contains $ balances + allocated splits, which are rule calculated from Cube 2 (which contains % splits). Cube 1 and 2 have different dimension structures
Note: Cube 2 will only contain the dimensions required for a specific allocation calc. A new allocation % will be created for each type of allocation method required, then fed back into Cube 1 via Rules calc

Option F: All data is contained in the 1 Cube under different versions, with post allocation calculated via Rules
e.g. "Preallocated" version contains base $ amts, "Allocation Splits" contains loaded %, "PostAllocation" version = "Preallocated" x "Allocation Splits" via Rules

...let me know any thoughts on the above e.g. performance? sparsity? maintenance? etc

cheers!

Matt

[mattgoff]

Option D: Can (and IMHO *should*) be one cube with the addition of another dim, e.g. "methodology".
Option E: Is Cube 2 a model to %s? If so, you only need this if your allocation model inputs aren't present anywhere else in your overall model.
Option F: Was my original recommendation.

It sounds to me like your requirements aren't final (e.g. Option D allows multiple allocation schemes whereas none of the others do). If I were you, I would take a step back from this exercise and plan out what you want your model to do. Once you have that settled, you can decide the best way to implement it in TM1. You seem to be getting caught up in the best architecture, performance, etc-- these are all side issues that can be worked out later.

Matt

[fleaster]

Thanks for the tips Matt - yes unfortunately we're still working out what we want the model to do + what it may be required to do in future... so to avoid needing to do too many builds, am trying to future-proof it a bit...

One thing for sure is we will be require at least 2-3 different allocation schemes that will need to feed data back into the 1 Cube - hence my indecision about whether to have Option D/F where allocation % may be stored in a relatively sparse cube or Option E, where I will need to create a separate cube to store % for each new allocation methodology...