Best Practice for mapping consolidations -> leafs

[jameswebber]

Guys,
I’m after some advice about best practise.

I want to map a detailed GL cube to a summarised planning GL.

I have created a TI process that exports the GL cube from a view to a csv file, a second process uploads the file into the planning cube.
I have a dimension in my GL cube called account, it has GL code as a unique identifier (principal element name)
I am mapping this to a dimension called plan_acocunt which generally at a much higher level and has string based descriptions as the Principal names.
Most of the consolidated descriptions in account match the ones in plan_account but sometimes there are mapped at a lower level. So to cope with this I created an alias in the plan_account (target) dimension containing the GL code.
In my TI I convert all the account code to a description

Code: Select all

VPlanGLAcc = DimensionElementPrincipalName( ‘Plan_account’, vAccount );

But I have two problems:
1. My second TI errors for every account that doesn’t have either a matching Plan_account name or GL code alias.
(Still uploads where there is a match but creates an error in the log for every account not in plan_account, I have tried if causes with itemskip but had no luck)
2. I can only map by name or 1 code but have come across instances where more than 1 account should roll up into a Plan_account.
If the consolidationed account name = Plan_account name it’s fine but if I have two account codes entries in the csv that need to = 1 plan account code then I’m stuck
I think my best option is to create an alternative hierarchy in my source cube and manage that against my target dimension.
Would that be right?
Obviously I was trying for a more elegant solution, but have come unstuck.

[Alan Kirk]

As I've often said I don't think that there's any one best practice {teeth grind, teeth grind}, just various approaches all of which have strenghts and weaknesses.

The way I would normally approach this (which is a way but not necessarily the One Right Way) would be to use a mapping cube.

One dimension would be the one from your GL cube. The final dimension would contain string elements. An optional third dimension would be a time one, which would allow you to maintain multiple mappings which may change over time.

The data that goes into this cube is simply the name of the planning element that the GL account maps to. The TI does a lookup from the cube as it imports the data.

Strengths: Easy to assign mappings in bulk, easy to update. As long as your planning dimension doesn't change often, it shouldn't need too much work to maintain it.
Weaknesses: No validation on the assigned mapping values. (However any ones which are invalid will generate key errors, and you can minimise this risk by copying the names from the planning cube's dimension.) Also it doesn't take into account the possibility of new elements being inserted into the GL account dimension.

On that last point if the GL dimension is maintained by TI I'd probably modify that to assign a default mapping (say, the mapping of the parent that the account rolls up to) unless you intentionally want to let new accounts error out if they haven't been assigned a mapping.

In this way I'd be interfacing the individual elements and aggregating rather than using values from the consolidations, but only because it allows maximum flaxibility. (In situations where, for instance, a single account within a consolidation may not go to the same planning element as the others in that consolidation.)

[declanr]

I would usually go with the same approach that Alan has mentioned; the only addition I would make is that to remove the issue with not having validation I would add a subset based picklist in, so that you can only select string values based on elements from a subset in your Account Dimension therefore you are presented with all the valid options and have to choose one.

This is dependent on how you update it though as I believe if you were updating via a TI you can "break" the picklist options.

[tomok]

The problem I see here is that you are only thinking about the transfer of Actual balances into the Plan environment/cube. What about transferring the final Plan back into your Actuals environment/cube? Alan's approach is spot on if all you are going to have is a many-to-one transfer. However, moving the Plan back to Actuals is going to be a one-to-many and his approach won't work unless you have a one-to-one mapping. This problem is as old as TM1 is and these are the options I give my clients:

1) Pick a single GL account in each rollup to hold the plan balances. You won't be able to compare plan against actual at a detail level but this is the easiest way to build the model.
2) In the Planning environment, create one or more plan-only leafs underneath each rollup to hold the plan balances. Then you need to create a mapping of each of these to a GL account on a one-to-one basis for when it comes time to move back into the Actuals environment.
3) Add the leafs from step 2 into the account dimension for the Actuals environment too. Then no mapping is necessary. Educate users as to why the plan-only leafs in the dimension are necessary.

[jameswebber]

Thanks guys,
will give these suggestions a go

[Michel Zijlema]

This is dependent on how you update it though as I believe if you were updating via a TI you can "break" the picklist options.

I recently noticed that the In-Spreadsheet Browser also breaks the picklist options.

Michel