Load data into a cube for all versions

[tutak]

I am creating a TI process from an ODBC to load data into a cube which has a version dimension. I want to load this data to all versions available in the cube. I think I need to create a variable for placeholder for version dimension, but I could not figure out what the formula for this variable should be and where exactly I define to put the data to all versions. I am assuming in the data tab, but I don't know what the CellPutN formula should look like.


I wrote this outside the generated script in TI data tab but it did not work. Versions is the dimension in the cube that I need to write this data for all instances.

vPeriod=DATE_MONTH_SHORT_DESC|' '|DATE_YEAR;
vNumber='Billing Days';
CellPutN(BILLDAYS,'Planning',vNumber,vPeriod,'Versions');

My placeholder variable for Version dimension is vVersion (string type) and the formula is vVersion=;Version;' But I don't use this in the data tab as you see above.

Thanks

[jrizk]

Hi. CellPutN is as follows:

CellPutN( value, CubeName, Element of Dim1, Element of Dim2, Element of Dim2 etc)

Assuming BILLDAYS is in your variables tab and is numeric you'd need something like this:

CellPutN(BILLDAYS,'Planning',vNumber,vPeriod,'Actual');
CellPutN(BILLDAYS,'Planning',vNumber,vPeriod,'Budget');

- guessing you might have Actual and Budget elements in your Version dimension. Easy enough to repeat the above for a small dimension like version which may have 3 or 4 elements. For larger dimensions you would need to loop through the dimension to pick up all the leaf elements for your CellPutN.

[tutak]

Hi. CellPutN is as follows:

CellPutN( value, CubeName, Element of Dim1, Element of Dim2, Element of Dim2 etc)

Assuming BILLDAYS is in your variables tab and is numeric you'd need something like this:

CellPutN(BILLDAYS,'Planning',vNumber,vPeriod,'Actual');
CellPutN(BILLDAYS,'Planning',vNumber,vPeriod,'Budget');

- guessing you might have Actual and Budget elements in your Version dimension. Easy enough to repeat the above for a small dimension like version which may have 3 or 4 elements. For larger dimensions you would need to loop through the dimension to pick up all the leaf elements for your CellPutN.

Hi jrizk,

From what you said I understand there is not an easy way of doing this. Especially if your dimension is big or you have more than one dimension to loop through. In my case, I have 2 extra dimensions like this. Versions and locations. For simplification purposes, I did not include locations in my question. But from you answer, it looks like I need to loop in both dimensions and run the CellPutN for all combinations of these dimensions.

can you also give ma an example of loop procedure, so I can identify all the leaf level combinations in the version and location dimension.

Thanks

[jrizk]

Hi. It's not difficult to loop through the dimension - though is there are reason you want to replicate the same data across multiple versions?

eg In the data tab you would have the following - however it is important to remember that this loop will occur for each record returned from your source. There are more efficient ways of addressing this but this gives you and idea of how to loope through a dimension.

DimName = 'Version';
DimCount = DimSiz ( DimName );
i = 1;

While ( i <= DimCount ) ;
DimCount = DimSiz (DimName);
El = DimNm ( DimName , i);
If ( DType(DimName, VersionEl) @='N' );
CellPutN(BILLDAYS,'Planning',vNumber,vPeriod, El');
EndIf ;
i = i + 1 ;
End;

[tutak]

Thanks for the code. What I am trying to achieve is loading some data to a cube that has more dimension than the data set has. Data has two dimensions and value, such as

Time Measure Value
------- ------------ ---------
Feb 2010 Billing Days 21
Mar 2010 Billing Days 22
etc.


My cube though has 4 dimensions; Time, Measure, Versions, Locations. So I am mapping Time and Measure dimensions from source to cube directly, Value goes to data. And I need 2 more dimensions to map. But I am not sure how to handle this. Maybe this clarifies my need.

Really appreciate your help.

[Andy Key]

Any reason why you don't load the data into a cube dimensioned by just Time and Measure, then use rules to access this data from the cube by Time, Measure, Versions and Locations?

[tutak]

Any reason why you don't load the data into a cube dimensioned by just Time and Measure, then use rules to access this data from the cube by Time, Measure, Versions and Locations?

Thanks. Actually today I thought about the same solution, and even tried it and it works well. My only issue is, is this really the best solution to my problem. Is there a way to accomplish what I am trying without going through another cube? If you say this is a common solution to my problem, I will at least move on and check this off from my list. I am still trying to build my expertise around TM1 and this is absolutely a good learning for me.