Load cube data with WHILE loop

[ival]

Hello,
I have an issue with the following task. I have a cube with a description element in month dimension, so when I open it, I see data in the following format:
Item Jan Feb Mar ....... Dec Description
1 100 100 Chairs
2 50 50 Pens
I want to load data from this cube to another one where description will a separate dimension, so my format will change to the following:
Item Jan Feb Mar ....... Dec
Chairs 100 100
Pens 50 50
In my TI job I create a dimension from description field, that's not a problem. Then I need to load data. One description can be applied to multiple values, so I need to hold it somehow while I create variables for each month value. I think I should use a WHILE loop, but I am making a mistake somewhere in the logic because I am not sure how records are being processed. Would it be possible to publish a sample that will be a little more descritive than TI help?

Thank you.

[Alan Kirk]

Hello,
I have an issue with the following task. I have a cube with a description element in month dimension, so when I open it, I see data in the following format:
Item Jan Feb Mar ....... Dec Description
1 100 100 Chairs
2 50 50 Pens
I want to load data from this cube to another one where description will a separate dimension, so my format will change to the following:
Item Jan Feb Mar ....... Dec
Chairs 100 100
Pens 50 50
In my TI job I create a dimension from description field, that's not a problem. Then I need to load data. One description can be applied to multiple values, so I need to hold it somehow while I create variables for each month value. I think I should use a WHILE loop, but I am making a mistake somewhere in the logic because I am not sure how records are being processed. Would it be possible to publish a sample that will be a little more descritive than TI help?

In a nutshell, what you need is called "aggregation". It means this:
- Create a view of the first cube. I recommend doing that by right clicking on the cube and selecting "Export as Ascii data" rather than doing it through the cube viewer, because you don't want title dimensions or rows or columns. The source should just be in a "flat file"" style format. This view will be the data source for your TI process.

- In the Metadata tab of the TI process, write code to create the elements for your new Description dimension using the DimensionElementInsert function.

- In the Data tab of the process, begin by reading any value that is already in your second cube via CellGetN. Store that value in a variable. For example,

Code: Select all

dbl_ExistingVal = CellGetN ('Cube2', 'Element1', 'Element2', 'Element3');

After that you use a CellPutN to write the aggregate value. For example:

Code: Select all

CellPutN ( Value+ dbl_ExistingVal, 'Cube2', 'Element1', 'Element2', 'Element3');

If you do it that way you don't need a while loop; both the Metadata and the Data tabs will loop through each row of data in your source view, executing the code once for each data row. That effectively creates the loop for you.

[ival]

I already completed this part. I create dimension elements in metadata, and I have CellGetN inside CellPutN function. My problem is that a dimension element in the second cube is the same as a value in the description element in the first cube. How do I match them? How should I command TM1 to load this item with "Chair" description and $100 in January into another cube that has "Chair" as dimension element? Cube view that is getting loaded as a flat file contains only one value per element:
1 Jan 100
1 Feb 100
1 May 50
1 Desc Chair
How do I say that all three values should be loaded into "Chair" element in another cube? This is where I should hold a description by saying that it's valid for all numbers that I have in item 1.
I cannot hardcode anything, it should be a dynamic process.

[Alan Kirk]

I already completed this part. I create dimension elements in metadata, and I have CellGetN inside CellPutN function. My problem is that a dimension element in the second cube is the same as a value in the description element in the first cube. How do I match them? How should I command TM1 to load this item with "Chair" description and $100 in January into another cube that has "Chair" as dimension element? Cube view that is getting loaded as a flat file contains only one value per element:
1 Jan 100
1 Feb 100
1 May 50
1 Desc Chair
How do I say that all three values should be loaded into "Chair" element in another cube? This is where I should hold a description by saying that it's valid for all numbers that I have in item 1.
I cannot hardcode anything, it should be a dynamic process.

Just use a CellGetS on the Data tab to read the corresponding Desc value for each row into a variable.

Thus when you're on the row with the data
1 Jan 100
(that is, using the elements '1' (let's say that the variable name is Dim1) and 'Jan') you use CellGetS to read
Dim1 'Desc'

Dim1's value is a variable driven by the contents of that row, 'Desc' is hard coded.

That will return a "Chair" into the variable. You then use that variable in your CellGetN and CellPutN functions to read and write to the second cube.

[ival]

Thank you! It worked much faster than the loop!