Moving data between Version dimension

[vasek1192]

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 (66.14 KiB) Viewed 1609 times

Data Source:

Variables.png (48.36 KiB) Viewed 1609 times

Prolog:

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_________________;
IF(DIMIX(sDimVersion,pVersion)=0);
	ProcessBreak;
ENDIF;


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';

Data:

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 1609 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]

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]

Hello,

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 ?