Moving data between Version dimension

Post Reply
Posts: 28
Joined: Sun Jan 24, 2021 5:55 pm
OLAP Product: IBM Planning analytics
Excel Version: 2019

Moving data between Version dimension

Post by vasek1192 » Mon Feb 22, 2021 12:18 pm

Hi everyone,

I am attempting to move data between Version dimension inside one cube with 3 dimensions:
-V_Version (1-12),
-T_Cas_M (Months: 1-12) and
-M_Sales (Total_Sales, A_Sales, B_Salaes, Comment)

Each Version contains data for specific month in dimension T_Cas_M. Version 1 contains data for month 1, version 2 for month 2 and so on. The idea is to create process that inserts data into parameter-specified version from previous version. I have created attribute "Prev" in Version dimension containing the previous versions so that I can get it from controll cube.

I have managed to make this work with combination of cellget and cellput, but I was told that I can skip the cellget and simply specify the previous version in sourceview and use cellput only. I have tried this:

Controll Cube:
Controll Cube.png
Controll Cube.png (66.14 KiB) Viewed 79 times
Data Source:
Variables.png (48.36 KiB) Viewed 79 times

Code: Select all

# Definitions of variables
sVerAttrCube = '}ElementAttributes_V_Version2';
sCasAttrCube = '}ElementAttributes_T_Cas_M';

sSourceCube = 'Sales';
sTargetCube = 'Sales';
sSourceView = 'TMP_Sales_Source';
sTargetView = 'TMP_Sales_Target';
nTempFlag = 1;
sDimVersion = 'V_Version2';
sDimCas = 'T_Cas_M';
sDimSales = 'M_Sales';
sElA_Sales = 'A_Sales';
aElB_Sales = 'B_Sales';
aElComment = 'Comment';
aMonth = 'aMonth2';
OutputFile = 'D:\TM1SHARE\08_Outputs\07_ECP2_KONSOLIDACE\Sales_Version_DEBUG.txt';
sElPrevMonth = 'Prev';
sElPrevVer = 'Prev';

#___________________Parameter bound verification_________________;

VersionSearch = CellGetS(sVerAttrCube, pVersion, sElPrevVer);
sMonthSearch = CellGetS(sVerAttrCube, VersionSearch, aMonth);

# Filter creatin for subsets
sVersion2TargetFilter = '{[V_Version2].[' | pVersion | ']}';
sVersion2SourceFilter = '{[V_Version2].[' | VersionSearch | ']}';
sCasSourceFilter = '{[T_Cas_M].[' | sMonthSearch | ']}';
sCasFilter = '{TM1FILTERBYLEVEL( {TM1SUBSETALL( [T_Cas_M]   )}, 0)}';
sSalesFilter = '{TM1SUBSETALL( [M_Sales]   )}';

# Create subsets with defined fileters
SubsetCreateByMDX(sSourceView, sVersion2SourceFilter, nTempFlag);
SubsetCreateByMDX(sSourceView, sCasSourceFilter, nTempFlag);
SubsetCreateByMDX(sSourceView, sSalesFilter, nTempFlag);

# Create Source View
ViewCreate(sSourceCube, sSourceView, nTempFlag);
ViewSubsetAssign(sSourceCube, sSourceView, sDimVersion, sSourceView);
ViewSubsetAssign(sSourceCube, sSourceView, sDimCas, sSourceView);
ViewSubsetAssign(sSourceCube, sSourceView, sDimSales, sSourceView);

# Assigns rules for Sourcde View
ViewExtractSkipZeroesSet(sSourceCube, sSourceView, 0);
ViewExtractSkipCalcsSet(sSourceCube, sSourceView, 1);
ViewExtractSkipRuleValuesSet( sSourceCube, sSourceView, 1 );

#___________________target View_________________;

# Create subsets with filters
SubsetCreateByMDX(sTargetView, sVersion2TargetFilter, nTempFlag);
SubsetCreateByMDX(sTargetView, sCasFilter, nTempFlag);
SubsetCreateByMDX(sTargetView, sSalesFilter, nTempFlag);

# Create Target View
ViewCreate(sSourceCube, sTargetView, nTempFlag);
ViewSubsetAssign(sSourceCube, sTargetView, sDimVersion, sTargetView);
ViewSubsetAssign(sSourceCube, sTargetView, sDimCas, sTargetView);
ViewSubsetAssign(sSourceCube, sTargetView, sDimSales, sTargetView);

# Assign rules for Target View
ViewExtractSkipZeroesSet(sSourceCube, sTargetView, 0);
ViewExtractSkipCalcsSet(sSourceCube, sTargetView, 1);
ViewExtractSkipRuleValuesSet( sSourceCube, sTargetView, 1 );

# Deletes data from Target View
ViewZeroOut(sSourceCube, sTargetView);

DataSourceNameForServer = sSourceCube;
DataSourceNameForClient = sSourceCube;
DatasourceCubeview = sSourceView;
DataSourceType = 'VIEW';

Code: Select all

CellPutN(vnValue, sTargetCube, pVersion, vsMonth, sElA_Sales);
CellPutN(vnValue, sTargetCube, pVersion, vsMonth, aElB_Sales);
CellPutS(NumberToSTring(vnValue), sTargetCube, pVersion, vsMonth, aElComment);

The result is that I get Process Completed with Errors message:
"3","3","Comment","Brezen",Data Source line (3) Error: Data procedure line (0): Cannot convert field number 4, value "Brezen" to a real number

And I transfer data B_Sales from correct version, correct month into correct version and month. Unfortunately it transferes them into every element of Sales dimension: A_Sales, B_Sales, Comment.

Result if I set parameter pVersion to 4. It should look into Version 3 and transfer data from March: A_Sales: 6, B_Sales: 8 and Comment: Brezen. Actual Result:
result.png (79.03 KiB) Viewed 79 times
My guess is he reads all the data from the record and uses the last one he could read (numeric value). But I dont know how to work around this and failed to find sulution in other threads. Any help would be appreciated.

Paul Segal
Community Contributor
Posts: 290
Joined: Mon May 12, 2008 8:11 am
OLAP Product: TM1
Version: TM1 11 and up
Excel Version: Too many to count

Re: Moving data between Version dimension

Post by Paul Segal » Mon Feb 22, 2021 1:02 pm

You might want to have a look at Bedrock, which has a specific TI for this which would save you re-inventing the wheel. If nothing else, it will give you an idea of what the code could or should look like.

Wim Gielis
Posts: 2646
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium

Re: Moving data between Version dimension

Post by Wim Gielis » Mon Feb 22, 2021 1:27 pm


Looking at this topic and also a couple of other topics, I do think that a course on TM1, specifically Turbo Integrator, would be very beneficial.
Exercises like the one you are attempting to do here, are an integral part of the TI courses I teach. I guess this will not be different for other courses out there. You can keep banging your head against the wall very frequently without the necessary instructions and tips and hints.

For instance, if you fail to understand that selecting text cells and forcing them into a numeric value is not really a good thing to do, then this is something that should be solved very quickly instead of writing a topic for half an hour on a forum ;)

Not skipping zeroes in a source view is also a bit weird to me at first sight, though there might be in theory some justifications. You do have a zero out ?
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014 ==> 117 TM1 articles and a lot of custom code
Newest blog article: Avoid circular references

Post Reply