Non-standard element weights

Introduction

Anyone who followed even a very basic course in TM1, can manually create a parent-child structure in a dimension. This, a.o., involves determining the weight of the child (numeric element or consolidation) into the parent consolidation. It is fair to say that 1 is by far the most used weight. That is, the parent consolidation is just the simple arithmetic sum of the values of the children. In the illustration below, we have years and months grouped into 1 dimension, and all consolidated elements add up the underlying values.

Other weights are possible too. What about comparing Actuals and Budget data automatically without any rule nor TI process?

Above we see that the variance is automatically calculated as Actuals minus Budget data, since Budget holds a weight of -1 instead 1. Speaking of non-standard weights, the first such weight is 0. If you do not want to add up a certain element into it’s parent, setting the weight to 0 is an option (though probably not the best, since this zero weight is counted for all cubes that make use of that dimension and for all combinations within these cubes). For example, you might want to exclude the impact of a certain cost center or department in a certain cube, but setting a zero weight also changes the historical impact for this cost center or could impact on other measures. You get my point.

Really non-standard weights, but very handy…

Think about loading revenue data in a sales analysis cube, at the euro or dollar. For big revenue values, it makes sense to present the data in thousands (000). This is easy when you look at the screenshot:

You can observe that the weight is set to 0.001. Hence, all data count for only that fraction, effectivvely leading to all (total) figures being 1000 times as small. No rules needed, no TI code needed, all dynamic. AND! Since you do not write any rule for this conversion, you also do not need a feeder. Hence, this is a very elegant and efficient solution, especially in the bigger cubes.

Weeks and months

It is possible to create consolidation structures in a Time dimension where weeks roll up into months. If all weeks/months/years are uniquely named, element weights can be set as 0.20, 0.40, 0.60, 0.80, 1, for instance. Depending on what portion of the week (x days out of 5) belongs to a certain month, the element weight is set as a percentage of the week total. Needless to say, there are other possible solutions to tackle days/weeks/months/years problems.

Weight equals 0, 1 or 3

The only other time I remember using non-standard element weights, was in a small pet model for a soccer competition. Teams can gain 3 points for a victory, 1 point in case of a draw and they get no points when the game is lost. This worked great with the flexible TM1 rules we know, next to that also consolidations with weights 0, 1 and 3 proved to be a good and inventive trick too!

The case of weight 1.3744

Oh wait, there was 1 famous case in which I used the weight of 1.3744. The customer wanted to convert EUR values to USD values in a cube. The cube contains a Year dimension, yet all the figures (even historical ones) needed to be reevaluated at the actual (yearly) rate of 1.3744 (at that time). The customer - that part of a large American company that was outside of the US - was not interested in the historical dollar values. Even more so, all historical euro values should be expressed using the current rate. This proved to be very straightfoward if we think out of the box: I created a consolidated element called USD, and add the n-type element EUR as a child. The weight equals 1.3744. 1 year later, the customer could very easily adapt the element weighting to the new rate. Instant success! No rules needed, no feeders, no Turbo Integrator, no big memory consumption!




Homepage

Section contents

About Wim

Wim Gielis is a Business Intelligence consultant and Excel expert

Other links