Rule error on dimension rebuild

[shorsted]

Hi

I wonder if anyone has any suggestions of workarounds on an error we are getting when rebuilding a dimension?

We have an account dimension which has multiple hierarchies on it. 2 of the hierarchies come from Oracle and others are being created from manually maintained txt files. The account dimension is used in 6 cubes, 4 of which have rules linked to consolidated account elements.

Occasionally we need to rebuild the hierarchies on the dimension so we are trying to create a chore that can be run that wil refresh all hierarchies, the problem we are having is that the rebuild gives us error log files which say that there are errors with the rules - due to the elements that the rule refers to being deleted.

Ideally we would run the whole thing within one process but as the hierarchies are from different sources we do it in a number of processes. Firstly removing all consolidations, then rebuilding the oracle hierarchies and finally rebuilding the manual hierarchies. Once the consolidations have been blown away though we then get the errors on the rules

We're considering a number of options:

1. We maintain an attribute on the accounts dimension which tells us if the element is used in a rule, these elements are then changed to N elements rather than deleted at the first stage. The hierarchy rebuilds would then correct them but as the element isn't deleted we wouldn't get an error. The downside of this, is we have an additional attribute to maintain

2. We use the "RuleLoadFromFile" function to first load empty rule files into the 4 cubes and then at the end load the actual rules files back in. The downside of this is that we will need to remember to archive the original rux files via a batch file to prevent them being overwritten so that we can then reload them

I wondered if anyone had any other suggestions?

Many thanks

Sarah Horsted

[Martin Ryan]

Of those two I'd go with option two.

A third option is not to delete the elements in the first place. Rather run through and destroy the hierarchy by deleting all the consolidation relationships (but not the elements themselves) and rebuild the hierarchy later.

Martin

[shorsted]

Thanks Martin,

I'm also thinking that option 2 might be better. If we didn't delete the elements as you suggested, we could incur problems if elements from hierarchies are removed in oracle as they would still be sitting in tm1?

Kind regards
Sarah Horsted

[George Regateiro]

I'm also thinking that option 2 might be better. If we didn't delete the elements as you suggested, we could incur problems if elements from hierarchies are removed in oracle as they would still be sitting in tm1?

What we do is delete the consolidations and then readd as was suggested. Then there is a final process that collects all N level nodes that do not have a parent into a hierarchy that the users do not have access to. We do not let the hierarchy build processes delete n level nodes from TM1. Those are all reviewed by an administrator before they are deleted. Found that to be the safest way to avoid any data being lost.

[ScottW]

Depending on the size of the cubes and complexity of the rules option 2 will definitely be more CPU and time intensive as it involves reprocessing all rules and feeders for the cubes.

Rather than deleting all consolidations in the account dimension I would suggest unwinding all consolidations. (break the link to all children leaving "orphan consolidations"). Then rebuild the hierarchies from all the sources, then you could run a clean-up process to delete any left over consolidations with no children. Note you won't actually need any additional attributes to do this.

However there's a caveat, which is that depending on the nature of your rules and if the consolidations used in the rules are significantly different post rebuild then you may need to reprocess feeders or some calculations might not be fed. This is probably not that likely, but you never know.

[Steve Vincent]

I do something very similar. The way i managed it was to build a dummy dimension. I have about 6 TIs that build different parts of the hierarchy in to my dummy dim, then the final TI removes all the elements in my real dim in the prolog and copies the whole dummy dim in the metadata. As long as you do the destroy / rebuild in one TI you won't loose data and you won't cause the rule errors that you're getting.