Distributing String Values ​​Within a Cube

Post Reply
biggie
Posts: 9
Joined: Wed Jul 03, 2024 9:50 am
OLAP Product: IBM TM1
Version: 2.0.88
Excel Version: 2019

Distributing String Values ​​Within a Cube

Post by biggie »

Hi all. I'm new to IBM TM1, I've been working with it for a little over two months. I solved past problems and difficulties by reading IBM documentation or viewing topics on the forum. Please help me solve the following problem.
I have two cubes 01_Approval and 01_Approval_General.
Cube dimensions 01_Approval: Year, Period, }Clients (system dimension), Rates
Cube dimensions 01_Approval_General: Year, Period, Rates
Both cubes have the same dimensions, except that 01_Approval has the }Clients dimension.
Also, the Rates dimension contains elements (R_01, R_02, R_03, R_04).
Using rules, I need to fill the cells of the cube 01_Approval on the slice [Rates:{P_01, P_02}] from the cube 01_Approval_General for all elements of the }Clients dimension (cube 01_Approval).

I have written the following rules.
Cube 01_Approval:

Code: Select all

FEEDSTRINGS;
SKIPCHECK;

[Rates:{P_01, P_02}] = S: DB('01_Approval_General', !Year, !Period, !Rates);
Cube_01_Approval_Tech:

Code: Select all

FEEDSTRINGS;
FEEDERS;

[Rates:{P_01, P_02}] => DB('01_Approval', !Year, !Period,  'Admin', !Rates);
This works, the values ​​are transferred correctly, but if I enable zero suppression (cube 01_Approval), then it only works for the calculated cells (green) of the Admin element (because a feeder is written for it in Cube_01_Approval_Tech).

How to solve this problem? My ideas:
1. Write feeders for all users. This is bad because when adding a new user (for example, a new employee), it will be necessary to change the cube feeders.
2. Pass values ​​only for the Admin element and then, inside the 01_Approval cube, distribute the values ​​in the }Clients:Admin slice to all other elements (except Admin).

Thank for help! :)
declanr
MVP
Posts: 1824
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Distributing String Values ​​Within a Cube

Post by declanr »

If this wasn't a control dimension; you could add a consolidation to the }clients dimension and feed that - feeding a consolidation is essentially just shorthand for feeding every N-level element under the consolidation. However, I would not recommend it with a control dimension (you can add consolidations to control dimensions but it could cause unforeseen issues.)

In this particular instance; it looks like other than the measure dimension (rates) you only have a year and period dimension; so it could be quite a small cube dependent on how many other measures there are.
If it is a small cube AND there are not any/many other rule calculations in it; then you could probably get away with turning off Skipcheck altogether... I would recommend fine-tuning the rule to only work on future/forecast rates and use a TI to populate historic/actual periods (assuming they don't need live "real-time" updates.)

Alternatively; create your own duplicate of the }clients dimension (with an "All Users" consolidation) which you would use in the cube instead of the }clients dimension and feed that consilidation.

Other alternative is - does it really need to be fed?
If you construct the views in a sensible way you can normally get away without feeding some cells; for example in another scenario you populate a rate for all products. But you only need to see the product if it has a volume in that period; so you make sure that the volume is also shown alongside the rate and then the suppression will work on the rows because of the volume cell being fed.
In YOUR particular scenario; you are wanting to feed the rate for EVERY single client anyway - so you don't need to suppress to hide clients. That leaves only year/period combinations - you can probably define those combinations via subsets (or an MDX view on the tuples) and leave suppression turned off altogether.

Welcome to the world of TM1 - (until feederless rules make their way into the product) understanding feeders is one of the primary concepts that everyone should focus on early.
Something that often gets overlooked for a long time into people's careers is the step of at least asking the question of "do I really need to feed it?"
In this case maybe you do; but maybe you could save yourself a headache by realising it's actually not necessary.
Declan Rodger
MarenC
Regular Participant
Posts: 415
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Distributing String Values ​​Within a Cube

Post by MarenC »

Hi,

To me it wasn't clear why the "01_Approval" cube even needs the }Clients dimension?

Maren
biggie
Posts: 9
Joined: Wed Jul 03, 2024 9:50 am
OLAP Product: IBM TM1
Version: 2.0.88
Excel Version: 2019

Re: Distributing String Values ​​Within a Cube

Post by biggie »

MarenC wrote: Mon Jul 15, 2024 7:42 am Hi,

To me it wasn't clear why the "01_Approval" cube even needs the }Clients dimension?

Maren
Hi.
In this case, it is assumed that the 01_Approval_General cube is filled by one employee (administrator/head manager/other).
Next, the Rate values ​​(R_01, R_02) are transferred to cube 01_Approval; employee (user/client) can change only R_03, R_04 elements of Rates dimension (because R_01, R_02 calculated by rules).
Using the }Clients dimension, each employee works only with his own slice of the cube 01_Approval. Also, in the }Clients dimension we created the “Current User” subset, which contains the current authorized user.
This subset is placed in the context of a cube.
biggie
Posts: 9
Joined: Wed Jul 03, 2024 9:50 am
OLAP Product: IBM TM1
Version: 2.0.88
Excel Version: 2019

Re: Distributing String Values ​​Within a Cube

Post by biggie »

declanr wrote: Sun Jul 14, 2024 9:16 pm If this wasn't a control dimension; you could add a consolidation to the }clients dimension and feed that - feeding a consolidation is essentially just shorthand for feeding every N-level element under the consolidation. However, I would not recommend it with a control dimension (you can add consolidations to control dimensions but it could cause unforeseen issues.)

In this particular instance; it looks like other than the measure dimension (rates) you only have a year and period dimension; so it could be quite a small cube dependent on how many other measures there are.
If it is a small cube AND there are not any/many other rule calculations in it; then you could probably get away with turning off Skipcheck altogether... I would recommend fine-tuning the rule to only work on future/forecast rates and use a TI to populate historic/actual periods (assuming they don't need live "real-time" updates.)

Alternatively; create your own duplicate of the }clients dimension (with an "All Users" consolidation) which you would use in the cube instead of the }clients dimension and feed that consilidation.

Other alternative is - does it really need to be fed?
If you construct the views in a sensible way you can normally get away without feeding some cells; for example in another scenario you populate a rate for all products. But you only need to see the product if it has a volume in that period; so you make sure that the volume is also shown alongside the rate and then the suppression will work on the rows because of the volume cell being fed.
In YOUR particular scenario; you are wanting to feed the rate for EVERY single client anyway - so you don't need to suppress to hide clients. That leaves only year/period combinations - you can probably define those combinations via subsets (or an MDX view on the tuples) and leave suppression turned off altogether.

Welcome to the world of TM1 - (until feederless rules make their way into the product) understanding feeders is one of the primary concepts that everyone should focus on early.
Something that often gets overlooked for a long time into people's careers is the step of at least asking the question of "do I really need to feed it?"
In this case maybe you do; but maybe you could save yourself a headache by realising it's actually not necessary.
Thanks for the detailed answer.
If this wasn't a control dimension; you could add a consolidation to the }clients dimension and feed that - feeding a consolidation is essentially just shorthand for feeding every N-level element under the consolidation. However, I would not recommend it with a control dimension (you can add consolidations to control dimensions but it could cause unforeseen issues.)
It seems to me that this option is technically impossible, because IBM cant consolidate string elements (}Cllients elements of string type).
In this particular instance; it looks like other than the measure dimension (rates) you only have a year and period dimension; so it could be quite a small cube dependent on how many other measures there are.
If it is a small cube AND there are not any/many other rule calculations in it; then you could probably get away with turning off Skipcheck altogether...
In this question I gave an example of a minimal cube that demonstrates my problem. Of course, this problem involves a cube with a large number of dimensions and elements.
I would recommend fine-tuning the rule to only work on future/forecast rates and use a TI to populate historic/actual periods (assuming they don't need live "real-time" updates.)
This is a good approach, I use it in other cubes of my IBM model (for me TI is easier than Rules). But this problem requires real-time calculations.
Alternatively; create your own duplicate of the }clients dimension (with an "All Users" consolidation) which you would use in the cube instead of the }clients dimension and feed that consilidation.
Oh, I didn't think about that. Maybe this is a good idea, I will suggest it to my team. We are used to using the }Clients dimension in the cubes of our IBM model - this can become a problem.
Other alternative is - does it really need to be fed?
If you construct the views in a sensible way you can normally get away without feeding some cells; for example in another scenario you populate a rate for all products. But you only need to see the product if it has a volume in that period; so you make sure that the volume is also shown alongside the rate and then the suppression will work on the rows because of the volume cell being fed.
In YOUR particular scenario; you are wanting to feed the rate for EVERY single client anyway - so you don't need to suppress to hide clients. That leaves only year/period combinations - you can probably define those combinations via subsets (or an MDX view on the tuples) and leave suppression turned off altogether.
Sounds good, but I need time to test this.

What can you say about this idea:
Pass values ​​​​only for the Admin element and then, inside the 01_Approval cube, distribute the values ​​​​in the }Clients:Admin slice to all other elements (except Admin). I tried this and it seems to work, but there are problems with the feeders inside the cube 01_Approval...

Anyway, your answer is very useful, especially when there is little information about IBM (official documentation, this forum, Cubewise topics).
If you don’t mind, I’ll add one more message to this topic; I need to experiment with feeders and skipchecks for some time.
biggie
Posts: 9
Joined: Wed Jul 03, 2024 9:50 am
OLAP Product: IBM TM1
Version: 2.0.88
Excel Version: 2019

Re: Distributing String Values ​​Within a Cube

Post by biggie »

I tried following idea.

Cube 01_Approval_General:

Code: Select all

FEEDSTRINGS;
SKIPCHECK;

FEEDERS;
[Rates:{'R1', 'R2'}] => DB('01_Approval', !Year, !Period, 'Admin', !Rates);
Cube 01_Approval

Code: Select all

FEEDSTRINGS;
SKIPCHECK;

[Rates:{'R1', 'R2'}, '}Clients':'Admin'] = S:DB('01_Approval_General', !Year, !Period, !Rates);
[Rates:{'R1', 'R2'}] = S:DB('01_Approval', !Year, !Period, 'Admin, !Rates );

FEEDERS;
['Rates':{'R2', 'R1'}, '}Clients':'Admin'] =>['}Clients':'User_1'];

In this case zeros suppression works correctly (the “green” cells are suppressed) only for Admin (feeder for Admin written in 01_Approval_General) and
User_1 (feeder Admin -> User_1 written in 01_Approval).

Next, Im trying to write the following line, thinking that I will setup feeder for all }Clients element, but this does not work (except for the Admin element fed from 01_Approval_General feeder, I think). Why?

Code: Select all

['Rates':{'R2', 'R1'}] => DB('01_Approval', !Year, !Period, !}Clients, !Rates)
MarenC
Regular Participant
Posts: 415
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Distributing String Values ​​Within a Cube

Post by MarenC »

Hi,

Assume this is a Typo because Admin is missing an apostrophe?
[Rates:{'R1', 'R2'}] = S:DB('01_Approval', !Year, !Period, 'Admin, !Rates );
The following will feed itself, so for example User_2 r1 would feed User_2 r1, which obviously wouldn't work.
['Rates':{'R2', 'R1'}] => DB('01_Approval', !Year, !Period, !}Clients, !Rates)
Maren
biggie
Posts: 9
Joined: Wed Jul 03, 2024 9:50 am
OLAP Product: IBM TM1
Version: 2.0.88
Excel Version: 2019

Re: Distributing String Values ​​Within a Cube

Post by biggie »

MarenC wrote: Mon Jul 15, 2024 4:29 pm Hi,

Assume this is a Typo because Admin is missing an apostrophe?
[Rates:{'R1', 'R2'}] = S:DB('01_Approval', !Year, !Period, 'Admin, !Rates );
The following will feed itself, so for example User_2 r1 would feed User_2 r1, which obviously wouldn't work.
['Rates':{'R2', 'R1'}] => DB('01_Approval', !Year, !Period, !}Clients, !Rates)
Maren
The following will feed itself, so for example User_2 r1 would feed User_2 r1, which obviously wouldn't work.
Oh, my bad. You absolutely right
Post Reply