Moving data between cubes of different dimensionality

[bergstrand]

Hello,

I am developing a TM1-solution where I have come across a bit of a tricky problem with moving data between cubes. I have two cubes with different sets of dimension, as in the simplified model below:

Cube 1:
Dimension 1
Dimension 2

Cube2:
Dimension 1
Dimension 2
Dimension 3

I want to load data from Cube 1 into Cube 2, preferably by using a rule. The tricky part is that I want the destination element in Dimension 3 to be dynamic, based on attributes in Dimension 1 and Dimension 2. How can I achieve this?

Best regards,
Martin Bergstrand

[David Usherwood]

The details are somewhat generic, but I think something on the following lines could work:

Rule
[] = n:DB('Cube 1',!Dim1,!Dim2,DB('a cube with the mapping rules', !dim1,!Dim2));

Feeder
[] => DB('Cube2',!Dim1,!Dim2,'Total for all elements in Dim3');

Of course the challenge is to get the mapping working properly.

[bergstrand]

Thanks for the fast reply!

However I am not sure I understand the rule. Since Dimension 3 does not exist in Cube 1, what does the third argument in the DB-function (the nested DB-function) do? Does it somehow define the area even though it is in the function part of the calculation? (I don't have a ton of experience with TM1 rules development, so please bear with me if I am asking stupid questions. )

Best regards,
Martin

Edit: corrected spelling.

[Steve Rowe]

I think David probably over simplified his approach a little as he is probably sunning himself in Florida somewhere, the key is the mapping cube gives the dim 3 element for a given combination of dim1 and dim2 elements.

In the Mapping Cube, you would have all three dimensions, then (hopefully using rules) you could have a 1 where the intersection of the three dimensions is valid. Say you have an attribute against dim1 and dim2 that gives the dim3 element, then in the mapping cube rules

#Note feeders not required.

[]=N: If ( Attrs ('dim1', !dim1, 'Dim 3 Element')@=!dim3 & Attrs ('dim2', !dim2, 'Dim 3 Element')@=!dim3,
#Then we are a valid point in the mapping cube so flag with a 1
1,
#Else we are not in a valid point so flag with a 0
0);

........
Then in cube 2 rules

Skipcheck;
[]=N: If ( DB('Mapping Cube' , !dim1, !dim2, !dim3)=1 , DB ('cube1', !dim1, !dim2) , 0);

#If you prefer and are using 1 as the flag in the mapping cube, might be faster than an if statement
#[]=N: DB('Mapping Cube' , !dim1, !dim2, !dim3)* DB ('cube1', !dim1, !dim2) ;
#but this simplifies too
#[]=N: If ( Attrs ('dim1', !dim1, 'Dim 3 Element')@=!dim3 & Attrs ('dim2', !dim2, 'Dim 3 Element')@=!dim3, DB ('cube1', !dim1, !dim2) , 0);
#So why bother with the mapping cube...

........
in cube 1

Skipcheck;
Feeders;
[]=>DB('cube2', !dim1,!dim2, 'All elements in dim3');

As David says the tricky part is to build up the mapping relationship in the mapping cube. Depending on the complexity of the mapping relationship I probablly would not bother with the mapping cube and just fold the logic that is split out in the mapping cube into cube2.

You might want to think about moving the data around using TI, as you will have a more responsive system. You also want to think about what you want to happen if the mapping relationship changes over time or for actual versus forecast data. Do you want all the data to be recast or just the data from that point in time forward, this kind of thing should all be considered before you settle on an approach.

HTH

Cheers,

[David Usherwood]

Steve is quite kind, albeit I was posting at 7am EST, so wasn't actually sunning myself. I'm not big on heat for its own sake anyway. Shuttle launch was rather good though - quite a holiday atmosphere, stall selling burgers and beers, then had to untangle ourselves from the traffic and rush to Orlando.

[bergstrand]

Aah, I think I am starting to understand now. I have also considered TI for this task but a rule would be better for the users. My plan is to try with a rule first and see if performance is ok (the server specs are quite ok here). If it gets too slow then TI it is.

I'll give it a try and come back with the result. Thanks very much for your help!

p.s. Shuttle launch sounds like a cool thing to watch. Too bad we only have such tiny little rocket launches here in Sweden.

[bergstrand]

An update on my progress with this issue:

I created the rule and it worked perfectly. The only thing was that, as expected, the performance was quite bad. I have now started to create a TI script for the same task and I have again run into some problems.

I am using a cube view as data source for the TI process and it seems like it only runs through the cells where there is data, leaving out the empty cells. Am I right? If so - is there a way to make TI run through all cells of the view? I have played around a bit with a while-loop looping over all elements of a dimension, but I haven't gotten that to work very well. It would be better if you just could tell TI to run through all the cells.

Best regards,
Martin

[Steve Rowe]

Hi Martin,
This can be done is you uncheck the suppress zeros tick box when define the view to be used. Again use with caution since there are probably a huge amount of blank cells in your view.

Why do you need to process the blank cells? It's a little odd....

Cheers,

[lotsaram]

Look up the ViewExtractSkip... commands in the TI help.

But I'd be a bit stronger than Steve in cautioning and advise you NOT to process blank cells! If your source cube is large (just string a few dimensions with a few hundred or few thousand elements together, add some factorial multiplication and you have a cube of several billion or several trillion cells) even at 20,000 records/second you process could take hours, even days.

Why would you want to do this for starters? If you want to make sure null areas in the source cube are replicated in the target cube the correct (most efficient ) way to do it would be to first zero out the correct area of the target cube and then process the zero suppressed view.

[bergstrand]

Hi Steve,

I found the function myself (you can use the ViewExtractSkipZeroesSet function), and realised just as quickly that the cube I am loading from contains far too many cells to go through them all.

Actually I do not want to go through all cells, but all members of one of the dimensions. What I am trying to do is to re-allocate cost in one dimension based on a cost allocation key (percentage) in a lookup-cube. So I need to loop through all members of that dimension (not only those that contain values in the source view) to allocate cost to those members in the target cube based on the percentage in the lookup-cube. I am now trying to do this using a while-loop, it seems like that should be a possible solution.

Thanks,

[bergstrand]

lotsaram: didn't see your message, but I realise now that even a moderately sized cube contain lots and lots of cells.

[bergstrand]

Another update:

I am trying to create a custom script with a while-loop looping over one of the dimensions. Everything works great until I use the CellPutN command somewhere in my script. The script runs through in perhaps 10 seconds using TextOutput to export the data, but with CellPutN writing directly to the destination cube it runs forever flashing "Starting Display procedure of process" and "Completing Display procedure of process". I have turned off logging of the target cube, but that seems to have no effect on performance.

Questions:
- How can the script run through in seconds using TextOutput but not with CellPutN?
- How can a similar script run in seconds with the same CellPutN line generated by TI?

It feels as if I am lacking some basic knowledge about creating custom TI scripts. Do you have any ideas what I am doing wrong?

Regards,
Martin

[lotsaram]

A sample of your code might be helpful to diagnose what might be wrong.

Have you made sure that Any CellPut or AttrPut commands are on the Data tab and not the Metadata tab?

Have you tried executing with both ASCIIOutput and CellPutN to see if anything is happening?

[bergstrand]

Sorry that I am totally spamming this thread, but I have narrowed down the problem and it is quite interesting:

This code runs forever as described above:

Code: Select all

i = 1;
n = SubsetGetSize(...);
WHILE(i <= n);
   CellPutN(...);
   i = i + 1;
END;

However, this code runs in a few seconds:

Code: Select all

i = 1;
WHILE(i <= 12);
   CellPutN(...);
   i = i + 1;
END;

The subset referenced in the first example has 12 members, so these two should perform pretty much the same (I thought). I tried moving the SubsetGetSize command to the Prolog tab and, *tada*, works like a charm. I didn't think the overhead of having to look up the subset size would cause such a difference in performance. Now i know.

Thank you guys for all help!

Regards,
Martin

[bergstrand]

Ok, now I am very confused again. Even though the described solution above worked for that very simple case, it did not when more complexity was added. I played around a bit and found the following:

This code runs in a few seconds:

Code: Select all

i=1;
while(i <= 12);
   A = SubsetGetElementName(...);
   B = CellGetN(...);
   i = i + 1;
end;
#CellPutN(...);

This code also works fine:

Code: Select all

i=1;
#while(i <= 12);
#   A = SubsetGetElementName(...);
#   B = CellGetN(...);
#   i = i + 1;
#end;
CellPutN(...);

And so does this:

Code: Select all

i=1;
while(i <= 12);
#   A = SubsetGetElementName(...);
#   B = CellGetN(...);
   i = i + 1;
end;
CellPutN(...);

But having all lines in the above code active makes the process run forever. How can this be?

Best regards,
Martin

[Steve Rowe]

Hmmm, welcome to joy of TI programming....

First which tab of the TI is your script

i=1;
while(i <= 12);
# A = SubsetGetElementName(...);
# B = CellGetN(...);
ASCIIOutput('File.cma' , str(i,5,2));
i = i + 1;
end;
CellPutN(...);

Try ASCIIOutput to check what the value of i is during the process.

If that is all of you TI though it looks like a bug, is your version 9.4 fully patched to the latest release or is it 9.4 first release?

[bergstrand]

The script is in the Data tab. I have tried checking the values of i (and n from the previous post) using TextOutput, and everything looks normal. I am using the latest release patched with FixPack1.

[Steve Rowe]

The only thing that I can suggest from this point is to package the server up and report the issue to IBM as a bug.
Not very helpful I know...

For solving your original allocation problem.

It sounds to me like you are processing the totals you want to allocate from the source view and then using the while loop to step through the allocation ratios in order to give you the values that you want.
If you tried it the other way around you might avoid the need for a while loop.
So if you process the allocation ratios as the data source for the TI and then reference the total you want to allocate then you won't need a while loop I think. It may be more fiddly to set up, depends how the totals you need to allocate are set up in the cube.
Anyway hope that helps you have an idea of a different approach.

Cheers,

[GPC]

i had a similar issue; http://forums.olapforums.com/viewtopic. ... 520#p12520