Testing for double countings in a dimension

[Wim Gielis]

Hello TM1 friends

Does anyone of you use an automated way of checking double countings in TM1 dimensions?

The thing is, if N-type or C-type elements are added twice to other consolidations, the consolidated values will be wrong.
In the absence of correct results outside of TM1 with which to reconcile, we will need to review the dimension elements and dimension structures ourselves.
For large dimensions with lots of consolidations and leaf level elements (like Accounts, Cost Centers, Employees, Projects, ...) this can be a very tedious task.
Every once in a while I receive files from customers that face such issue. I think that I could benefit from a generic routine here.

Potentially every consolidation could be flawed, so we need a generic way of testing this.
For example, this structure could lead to double countings:

Σ Business Unit
╠ Σ Business Centre1
║ ╠ n Employee 1
║ ╠ n Employee 2
║ ╚ n L0_Business Centre1
╠ Σ Business Centre2
║ ╠ n Employee 3
║ ╠ n Employee 4
║ ╚ n L0_Business Centre2
╚ n L0_Business Centre2

... since "L0_Business Centre2" is part of 2 consolidations.
But not only at the top level element in the dimension could we have double countings, it can also appear at lower consolidations in the dimension.

Back in April, I wrote a few simple loops for an article on my personal website on TM1. The code creates a text file with all dimension elements that have more than 1 parent.
This condition of multiple parents is necessary but not sufficient to have double countings:
for example, if the parents are in parallel hierarchies of the dimension.

I started to write Turbo Integrator processes to check for double countings. By doing that, I quickly realized that it isn't that simple at all...
Every numeric element could in theory cause a double counting situation in every consolidation.
By undertaking the exercise, in fact I noticed that I need to generate each unique path from an element to any of the consolidations.
If there is more than 1 such path, we have a double counting situation. In the end I succeeded creating the TI code, but I wanted to reach out and see if this is something important to you and what you use to trace down the problems.

Please note that for now:
- I am aware of the fact that sometimes elements will appear twice in dimension, which is intended AND useful. This might be the case when we 'play around' with the element weights (see below)
- I make abstraction of element weights. A weighting of -1 or 0 will still be marked as a double counting.

Best regards,

Wim

[mvaspal]

Hi Wim,
For such cases we usually build a check cube with two dimensions: the dimension we would like to check and a measure, containing at least two items, Count and Nr of Parents

Then we always create a sum total called 'zTotalCount', all N elements are directly components of this consolidated element, with weight of 1.

Then the rule: Count = N:1

With this, if you stand on Total Employees and zTotalCount, the Count values have to match. If they don't, the nr of parents measure helps us locating the issue. Let's say you have 2 hierarchies; then 'Nr of parents' should be 3 (2 hiers + zTotalCount) for N components and 1 for C components (assuming C elements are totally different in the two hiers), and of course 0 for the sum totals.

However, I think this solution is best for balanced hierarchies.

Matyas

[tomok]

That's not something I would ever attempt in TI just because it is waaaayyy too complicated. Whenever I suspect double-counting I just dump the affected tree to Excel (I have a worksheet with macros that will accept a hierarchy point and then drill down from there, putting the respective elements in rows), sort, and then put a formula in a column that says if this element is equal to the element above it then be 0, otherwise be 1, and then I look for any rows with 0. Those would be my elements that are in the tree twice.

[Wim Gielis]

Hi Wim,
For such cases we usually build a check cube with two dimensions: the dimension we would like to check and a measure, containing at least two items, Count and Nr of Parents

Then we always create a sum total called 'zTotalCount', all N elements are directly components of this consolidated element, with weight of 1.

Then the rule: Count = N:1

With this, if you stand on Total Employees and zTotalCount, the Count values have to match. If they don't, the nr of parents measure helps us locating the issue. Let's say you have 2 hierarchies; then 'Nr of parents' should be 3 (2 hiers + zTotalCount) for N components and 1 for C components (assuming C elements are totally different in the two hiers), and of course 0 for the sum totals.

However, I think this solution is best for balanced hierarchies.

Matyas

Hello Matyas

That's an interesting approach, also it's an approach where you leverage TM1 power (rules, a small lookup cube, ...)
I do think it's still a "manual" approach though (granted, some - if not all - could be automated through TI)
Maybe I'm dreaming but the intended use that I have in mind, is/could be: "okay, I have this cube XYZ, show me the situations that could lead to double countings". Hence, investigate all cube dimensions and their elements.
A couple of days ago at the customer, we faced issues in a cube since the dummy Region element was added twice to the dimension: once below the 'Total Region' element and again under a different consolidation. That 'straightforward' Region dimension was not our first candidate to look at... Neither did we know that it was a double counting problem.

Wim

[Duncan P]

I would make a copy of the dimension with only the leaves in. Then create a cube with the rule

Code: Select all

[] = N: IF( !dim @= !dim_leaves, 1, 0 ); C: IF( 1 = ELISANC( 'dim', !dim, !dim_leaves ), STET, 1 );

then look for non-1s against the consolidated members of dim.

Shouldn't be too hard in TI.

[Wim Gielis]

That's not something I would ever attempt in TI just because it is waaaayyy too complicated. Whenever I suspect double-counting I just dump the affected tree to Excel (I have a worksheet with macros that will accept a hierarchy point and then drill down from there, putting the respective elements in rows), sort, and then put a formula in a column that says if this element is equal to the element above it then be 0, otherwise be 1, and then I look for any rows with 0. Those would be my elements that are in the tree twice.

Hi Tomok

You're right, it is complicated, no doubt about it. But I managed to do so, it's only that the code is still rudimentary and full of hard-coded stuff I could use some parameters to make the processes more flexible.

Your approach is interesting too; if you extend your macro, I'm sure it could do all the steps at once.

Thanks,

Wim

[mvaspal]

I do think it's still a "manual" approach though

actually, I have not written but usually we do it a bit more complicated and automated, remaining by regions example:
1. we also create a dimension called Region_Check
2. This dimension is in the check cube and not the Region itself
3. When we run the chore for updating the dims, the chore first rebuilds this check dim
4. Then, still in a TI, we check whether the zTotalCount = Total in other hiers
5. If yes, we call the "normal" TIs that rebuild the dims, if no, then we inform users that dims have not been updated
6. In your case, it would have been quite easy to find the issue: open the check cube, then filter descending on nr of parents; N elements with higher than 1 value are incorrect

[Wim Gielis]

Interesting rule, Duncan, I will investigate this over the next few days or weeks. Thanks !

Wim

[jstrygner]

Wim's post finally made me decide to try to write such TI as I also feel a need of having such generic checker.

To be honest I did not analyze in details all posts from others in this thread, but I see all of them suggest creating some additional cubes and/or dimensions.

It is possible to do it using TI only though and it does not seem to be that complicated.

I am attaching a .pro file, so you can refer there for details. I tried to put some comments to explain the steps.
General logic is to check if there are any cases when more than one direct parent appears within the same hieararchy.

I did not test the code widely, so there still may be some unforseen bugs, but I run it on few dims and it seems to work.

Of course additional features could be added like include only hierarchy(ies) provided in parameter (or exclude).

Hope you'll find it useful.

[Duncan P]

This is very good, using the ELISANC as it does and checking all parents against each top ancestor. However it only takes account of the parentage and not of the weightings. It is valid for there to be multiple parents leading to the same ancestor, provided that only one of the paths has a non-zero weight. Calculating composite path weights in TI is very much more tedious than using ELISANC as you need to use ELWEIGHT on each component of the path and multiply the results.

This is why using a temporary cube is so attractive, as the implicit TM1 aggregation engine has already calculated those composite weights and we only have to tease them out by making a cube in which we can individually put a 1 from each leaf up to all the consolidated elements above it.

[jstrygner]

Duncan,

Yes you are right, my code will not consider weigths.
But that was one of Wim's assumptions and also for my business case it was not required to look at them differently as like they would all be just 1.

Please note that for now:
- I am aware of the fact that sometimes elements will appear twice in dimension, which is intended AND useful. This might be the case when we 'play around' with the element weights (see below)
- I make abstraction of element weights. A weighting of -1 or 0 will still be marked as a double counting.

[Duncan P]

I'm sorry. I had interpreted Wim's description of the situation as that the composite weight of each leaf in each ancestor should be 1, and that this could be achieved by playing around with the individual path weights where necessary.

[lotsaram]

Jarek nice work I can see where this would be useful.

Different side of the same problem. Often a reporting dimension may have multiple reporting hierarchies. Ultimately all such hierarchies should contain the same set of leaf elements and so should return the same result at the top level but rollup path under each hierarchy will be different. E.g. fashion retail total SKUs by size, total SKUs by color, total SKUs by Brand, total SKUs by department, etc, etc.

Of course you can always check for internal consistency with cube data that the same result is returned. But that's not so simple and not so generic as part of a dimension update routine. Does anyone have a generic process for such a test (that same set of leaf elements is under distinct top nodes)?

[jstrygner]

Jarek nice work I can see where this would be useful.

Thanks, yes I guess you are guessing correctly

Does anyone have a generic process for such a test (that same set of leaf elements is under distinct top nodes)?

No generic code for now, but I think modifying this one with a small effort would make it work.

Logic would be:
1. Also determine all Top Node Consolidations (TNC), or take delimited list of the ones you want to test from parameter.
2. For each leaf in dimension check if ElIsAnc against each tested TNC is either always 0 or always 1. If at least once it is 0 and at least once 1, you are sure set of elements under tested TNCs is not the same.

Same assumptions on weights as before.

[java_to_tm1]

Ended up putting a couple of ideas together.
1. Treating cases where 'ELWEIGHT is 1 in one consolidation and 0 in another' as a single counting: not double counting.
2. A Control cube with 2 dims (the original dim + a leaf-elements-only copy of that dim) - Duncan's original idea

The key is to look out for any value other than 0,1
Any cell > 1, implies either double ELWEIGHT or double counting.
Havent split these 2 conditions yet.

The attached TI process takes 1 dim as parameter, creates the control dim (Leaf elements) and the control cube (including the rule).

Try it out and let me know if you'd like any changes.