How can I switch a list that is already been used in more then 50 modules and a hundred of formulas?
We had an update on our distribution channel list, so we need to create two more hierarchy levels. The problem is that this list is already been used in more than 50 modules and a hundred of line items. We're having some troubles to implement this. Someone knows if there is an easy way to do that or we need to delete all formulas and "Applies to" so we can do this? Thanks
Best Answer
-
Hello,
There are two basic contexts for updating dimensions (from a model building perspective):
1. The lowest level members are remaining intact (i.e. you are only adding new levels above the lowest level)
2. You are replacing the lowest level members with a lower/more granular hierarchy level (this often impacts data, so is more complex)
If the lowest level members are remaining the same, then the only fallout from adding levels relates to formulas that are expecting particular levels to be in a paricular place. For example, formulas using the Parent function would break if the new level is inserted where the Parent Function is pointing to.
Take the following hierarchy for example:
L1 Region
L2 Country
L3 Account (Customer)
Let's say that you are adding L2 (above) as a new level. These are the steps I'd take:
1. Add L2 Country (Parent Hierarchy is L1, load parent/child relationships)
2. Review modules that use L3 for any line items that include formulas dependent on L1 being the parent of L3. Temporarily remove those formulas.
3. Redirect L3 from having the L1 parent hierarchy to having L2 as the parent hierarchy. At this time, all of the items in L3 will temporatily become orphans (this doesn't affect model data).
4. Load new L3 Parents
5. Reload formulas from step 2 (but with adjusted logic.. in our example, they might be need to reference Parent(Parent( instead of just Parent(.
If the lowest level list is changing, this could be highly consequential to the model as a a whole, and may even require the restructure & reloading of data. For example, if data is loaded at the Line of Business (LOB) Level, but you are adding a level below Line of Business called "Sub-Line Of Business (Sub-LOB)"... then any modules that need to be changed to show data at the SubLOB level (but are currently at the LOB level) would need to have data reloaded, or remapped or otherwise linked in at a lower formula level. This could be a potentially large impacts to a model. Notwithstanding, if a complete rebuild is not warranted, I use the following method:
1. Create a copy of each affected module
2. In the copy, update the dimension and any related downstream formula impacts
3. Reload data into the new module & reconcile totals, to the extent needed
3. Unhook the old module from the model, and hook in the replacement module. In other words, existing modules that pull data from the old module need to be updated to pull data from the new/replacement module.
4. Delete the old module when its no longer referenced anywhere in the model.
5. If appropriate, rename the replacement modules (so that their names match the old module names)
Creating a copy of the module you are changing allows you to work without having to delete existing formulas until you are ready! When you update existing formulas, you can conduct at least some unit testing/validation immediatey.
Good luck!
Paul
5
