TM1 Forum

Hi all,

I have a product dimension with about 60,000 products (leaf level) and some 10,000 consolidations.
There is an alternative hierarchy (PAW speak) to capture where the product originates from (the country).

If I include the alternative hierarchy, then I have some rules that break down and stop functioning.
In particular, a rule that retrieves a cost for a product (DB to a second cube), but the lookup is done on using an intermediate (third) product.
E.g. cost for products A, B, C, is stored at the level of X and Y. A and C map to X, B maps to Y.
That 'mapping' is done in a text measure of the same base cube. So, each product holds a mapping, say, X or Y.
This X or Y is retrieved in the SAME product dimension but in a different cube.

Technically, it's a DB inside a DB. The inner DB yields the X or Y.
If I exclude the alternative hierarchy for source country, it works fine.
If I include the alternative hierarchy for source country, it works fine if I take out the inner DB and replace it with a hard-coded value, like X.
If I include the alternative hierarchy for source country, it does not work if the DB's are nested. While I know for a fact that the inner DB gives us X, for example, the lookup of X in the cost cube returns 0 ! [Hardcoding the X gives the correct result.]

This brings me to the question: in case of a parallel hierarchy (PAW speak), how should we use the DB functions ?
Did IBM already make this available ?

Thanks,

Wim

Hi Wim

Do you possibly have a clash of element names? Is it possible that one of your country consolidations in your Country Hierarchy has eg the same code or an alias that is the same as one of your products or one of your other dimensions? There is a complicated syntax for making clear which you mean. However, if I were you I would add a prefix to the country consolidation elements so that they are unique.

Regards

Paul SImon

Thanks Paul.

Yes, the names are the same, it's by design. This is a POC exercise for the moment.

What I wanted to show to the prospect, is a product dimension with:

- in the main hierarchy in the dimension: 1 principal rollup
- in the main hierarchy in the dimension: 1 alternative rollup by country of origin
- in the alternative PAW hierarchy dimension: 1 alternative rollup by country of origin

The rollup in the PAW hierarchy is constructed by first unwinding (not deleting), then rebuilding based on loops over the Country alternative TM1 rollup in the main hierarchy of the dimension. Such that the 2 country hierarchies are in sync. The names of the elements (consolidated and level 0) are exactly the same in the Country hierarchies.

Like that, I could easily show case the difference and the added value that the PAW alternative hierarchy brings in.
I could rename the consolidated elements in the alternative hierarchy if needed but that seems overkill to me. Adding a suffix to make them unique could be an idea.

However, if you were able to show the syntax in case of like-named elements, that would be greatly appreciated.

Wim

Wim Gielis wrote: ↑Fri Aug 31, 2018 6:42 am Yes, the names are the same, it's by design.

If you have C elements with the same name existing in different hierarchies of the same dimension then if you simply use 'dimension':'element' to reference the element in square brackets (or just 'element within DB()) then TM1 will throw an ambiguous reference error. ( ... makes sense actually since the structure hierarchies are stored in is exactly the same as what we are used to thinking of as "dimensions").

Within square brackets you need to use
[ 'dimension':'hierarchy':'element' ]

and within a DB() you need to use
DB( 'cubeName', 'hierarchy':'element', ...
or
DB( 'cubeName', 'hierarchy':!dimension, ...

Note there is (possibly was, unsure if they fixed it yet) a bug with the square bracket hierarchy reference only working on the left hand side of a rule and not on the right hand side so you have to use the longhand DB() reference.

If you want to apply the same rule to a list of same named elements from different hierarchies then you need to adapt the standard list notation:
[ 'dimension':{'hierarchy'1:'element','hierarchy'2:'element','hierarchy'3:'element'} ] = C:

Chances are if you have a same named C element in multiple hierarchies and you want to apply a rule to it then more than likely you want to apply the SAME RULE for all instances of the element. I find the current way of dealing with it more than a little annoying as it makes it much more difficult to make rules future-proof and maintenance free (e.g. in the case of a new hierarchy being added) as any new hierarchy requires the rule to be edited. What is needed is a !hierarchy notation for a hierarchy wildcard like !dimension acts as a element wildcard to evaluate the hierarchy name in the context of the cell being queried from. This would need to be valid in both square bracket and DB().

.. Yes as Per Paul's point you could get around it by having distinct names per hierarchy but take the classic case of alternate rollups of product which are now in separate hierarchies of "by Vendor", "by Size", "by Color", "by Category", "by Brand", ...etc It makes sense that the top node of each hierarchy just be called "Total Product" provided each is equivalent and contains all leaves just sliced & diced in a different way. It is already obvious to the user what they are looking at by virtue of the hierarchy.