cognostm1 cubeview export recent six months data by using ti

[gopal]

Hi all,
How to export recent six months(running six months) data by using ti process .here month (jan,feb,mar...)and year(2012,2013,2014,2015) dimension are saparate .without parameter how to export running six months view data.pls help me regarding this.
Thanks
Gopi

[rmackenzie]

What have you tried - post your code?

[pandinus]

This should get you going.

First:
- Get the current year and month using the TIMVL function or parameters
- Add current year to the subset for the year dimension

Loop from 0 to your maximum number of months -1 and for each month:
- Add the current month minus the loop iterator to the subset for the period dimension
- add 12 to the months if the current month minus your loop iterator becomes 0 or negative
- add year-1 to the year subset if the current month minus your loop iterator becomes 0 or negative for the first time

[mattgoff]

Options:

• Include all months and two years in your datasource view and ItemSkip unneeded months in metadata/data tabs.
• Use two TI processes.
• Set datasource = none and iterate through cells manually in metadata/data tabs.

Unless it's a ton of data, I'd prob just use the first one and call it a day.

[gopal]

i am using the below code.
IF(yYear@='2014'&yMonth=>MONTH(strCurrentDate)-5&yMonth<=MONTH(strCurrentDate));
it is working only current year.
i want prioryear data also.suppose current month is 2015 Jan ,i need last five months data like year 2014 Aug to Dec and Jan 2015.

[gopal]

This should get you going.

First:
- Get the current year and month using the TIMVL function or parameters
- Add current year to the subset for the year dimension

Loop from 0 to your maximum number of months -1 and for each month:
- Add the current month minus the loop iterator to the subset for the period dimension
- add 12 to the months if the current month minus your loop iterator becomes 0 or negative
- add year-1 to the year subset if the current month minus your loop iterator becomes 0 or negative for the first time

Please send me the if you have the logic ,i will check ...

[gopal]

paramSrcCube
paramTgtCube
paramNeedRules

paramSrcCube,""
paramTgtCube,""
paramNeedRules,1.
637,3
paramSrcCube, Enter the Name of Cube
paramTgtCube, Enter the New Name of Cube
paramNeedRules, Do you want to attach Source Cube Rules to the Newly created cube? (Enter Number: 1 for Yes)



#****Begin: Generated Statements***
#****End: Generated Statements****

#######################################################

#This process REPLICATES the provide Cube

#This process also copies the DATA source Cube to newly created Cube.
#Along with it, it optionally attached RULES to newly create cube

#NOTE: This process is valid for the Cube having Maximum 16 Dimensions.

#######################################################

processName = 'util_Cube_Rename';
debugFile = GetProcessErrorFileDirectory | processName | '_' | TimSt(Now, '\Y\m\d\h\i\s') | '_' | paramSrcCube | '.pro' ;
errorFlag = 0;



# Check If Source Cube parameter has been provided
If( Trim( paramSrcCube ) @= '' );
errorFlag = 1;
Msg = 'ERROR: Source Cube value not specified';
AsciiOutput( debugFile, Msg );
ProcessBreak;
EndIf;

# Check If Target Cube parameter has been provided
If( Trim( paramTgtCube ) @= '' );
errorFlag = 1;
Msg = 'ERROR: Target Cube value not specified';
AsciiOutput( debugFile, Msg );
ProcessBreak;
EndIf;


# Check if Source Cube exists

If( CubeExists(paramSrcCube) = 0 );
errorFlag = 1;
Msg = 'ERROR: The Source Cube ' | paramSrcCube | ' does not exists. Process terminating';
AsciiOutput ( debugFile, Msg);
DataSourceType = 'NULL';
EndIf;

# Check if Target Cube does not exists

If( CubeExists(paramTgtCube) = 1 );
errorFlag = 1;
Msg = 'ERROR: The Target Cube ' | paramTgtCube | ' already exists. Overwriting it may result in data loss. Proces Terminating. Please re-run the pro
cess with unique New Cube Name';
AsciiOutput ( debugFile, Msg);
DataSourceType = 'NULL';
EndIf;


# Determine count of existing Dimensions in the Source Cube

Cntr=1;
While (TabDim (paramSrcCube, Cntr) @<> '');
currentDim = TabDim(paramSrcCube, Cntr);
Cntr = Cntr + 1;
End;
dimCount = Cntr - 1 ;






Dim1 = TabDim( paramSrcCube, 1 );
Dim2 = TabDim( paramSrcCube, 2 );
Dim3 = TabDim( paramSrcCube, 3 );
Dim4 = TabDim( paramSrcCube, 4 );
Dim5 = TabDim( paramSrcCube, 5 );
Dim6 = TabDim( paramSrcCube, 6 );
Dim7 = TabDim( paramSrcCube, 7 );
Dim8 = TabDim( paramSrcCube, 8 );
Dim9 = TabDim( paramSrcCube, 9 );
Dim10 = TabDim( paramSrcCube, 10 );
Dim11 = TabDim( paramSrcCube, 11 );
Dim12 = TabDim( paramSrcCube, 12 );
Dim13 = TabDim( paramSrcCube, 13 );
Dim14 = TabDim( paramSrcCube, 14 );
Dim15 = TabDim( paramSrcCube, 15 );
Dim16 = TabDim( paramSrcCube, 16 );



# Create the Target Cube first


If( dimCount = 2 );
CubeCreate( paramTgtCube, Dim1, Dim2 );
ElseIf( dimCount = 3 );
CubeCreate( paramTgtCube, Dim1, Dim2, Dim3 );
ElseIf( dimCount = 4 );
CubeCreate( paramTgtCube, Dim1, Dim2, Dim3, Dim4 );
ElseIf( dimCount = 5 );
CubeCreate( paramTgtCube, Dim1, Dim2, Dim3, Dim4, Dim5 );
ElseIf( dimCount = 6 );
CubeCreate( paramTgtCube, Dim1, Dim2, Dim3, Dim4, Dim5, Dim6 );
ElseIf( dimCount = 7 );
CubeCreate( paramTgtCube, Dim1, Dim2, Dim3, Dim4, Dim5, Dim6, Dim7 );
ElseIf( dimCount = 8 );
CubeCreate( paramTgtCube, Dim1, Dim2, Dim3, Dim4, Dim5, Dim6, Dim7, Dim8 );
ElseIf( dimCount = 9 );
CubeCreate( paramTgtCube, Dim1, Dim2, Dim3, Dim4, Dim5, Dim6, Dim7, Dim8, Dim9 );
ElseIf( dimCount = 10 );
CubeCreate( paramTgtCube, Dim1, Dim2, Dim3, Dim4, Dim5, Dim6, Dim7, Dim8, Dim9, Dim10 );
ElseIf( dimCount = 11 );
CubeCreate( paramTgtCube, Dim1, Dim2, Dim3, Dim4, Dim5, Dim6, Dim7, Dim8, Dim9, Dim10, Dim11 );
ElseIf( dimCount = 12 );
CubeCreate( paramTgtCube, Dim1, Dim2, Dim3, Dim4, Dim5, Dim6, Dim7, Dim8, Dim9, Dim10, Dim11, Dim12 );
ElseIf( dimCount = 13 );
CubeCreate( paramTgtCube, Dim1, Dim2, Dim3, Dim4, Dim5, Dim6, Dim7, Dim8, Dim9, Dim10, Dim11, Dim12, Dim13 );
ElseIf( dimCount = 14 );
CubeCreate( paramTgtCube, Dim1, Dim2, Dim3, Dim4, Dim5, Dim6, Dim7, Dim8, Dim9, Dim10, Dim11, Dim12, Dim13, Dim14 );
ElseIf( dimCount = 15 );
CubeCreate( paramTgtCube, Dim1, Dim2, Dim3, Dim4, Dim5, Dim6, Dim7, Dim8, Dim9, Dim10, Dim11, Dim12, Dim13, Dim14, Dim15 );
ElseIf( dimCount = 16 );
CubeCreate( paramTgtCube, Dim1, Dim2, Dim3, Dim4, Dim5, Dim6, Dim7, Dim8, Dim9, Dim10, Dim11, Dim12, Dim13, Dim14, Dim15, Dim16 );
EndIf;




# Creating DataSource

DataSourceType = 'VIEW' ;
DatasourceNameForServer = paramSrcCube;
DatasourceNameForClient = paramSrcCube;
NValue = 0;
SValue = '0';
Value_is_String = 0;

# Creating Temp View of the target cube

viewNumber = TimSt(Now, '\i\s') ;
tempView = '_' | processName | '_' | viewNumber ;

If (ViewExists ( paramSrcCube, tempView ) = 0 ) ;
ViewCreate( paramSrcCube, tempView );
EndIf;

ViewExtractSkipCalcsSet( paramSrcCube, tempView, 1);
ViewExtractSkipZeroesSet( paramSrcCube, tempView, 1);
DatasourceCubeView = tempView;


573,3

#****Begin: Generated Statements***
#****End: Generated Statements****
574,115

#****Begin: Generated Statements***
#****End: Generated Statements****



# Break the Process if errors are found

If( errorFlag > 0 ) ;
ProcessBreak;
EndIf;

# Copying the Data from Source Cube to Target Cube

If( dimCount = 2 );
elementType = DType( Dim2, v2 );
If( elementType @<> 'S' );
CellPutN( Numbr( v3 ), paramTgtCube, v1, v2 );
Else;
CellPutS( v3, paramTgtCube, v1, v2 );
EndIf;
ElseIf( dimCount = 3 );
elementType = DType( Dim3, v3 );
If( elementType @<> 'S' );
CellPutN( Numbr( v4 ), paramTgtCube, v1, v2, v3 );
Else;
CellPutS( v4, paramTgtCube, v1, v2, v3 );
EndIf;
ElseIf( dimCount = 4 );
elementType = DType( Dim4, v4 );
If( elementType @<> 'S' );
CellPutN( Numbr( v5 ), paramTgtCube, v1, v2, v3, v4 );
Else;
CellPutS( v5, paramTgtCube, v1, v2, v3, v4 );
EndIf;
ElseIf( dimCount = 5 );
elementType = DType( Dim5, v5 );
If( elementType @<> 'S' );
CellPutN( Numbr( v6 ), paramTgtCube, v1, v2, v3, v4, v5 );
Else;
CellPutS( v6, paramTgtCube, v1, v2, v3, v4, v5 );
EndIf;
ElseIf( dimCount = 6 );
elementType = DType( Dim6, v6 );
If( elementType @<> 'S' );
CellPutN( Numbr( v7 ), paramTgtCube, v1, v2, v3, v4, v5, v6 );
Else;
CellPutS( v7, paramTgtCube, v1, v2, v3, v4, v5, v6 );
EndIf;
ElseIf( dimCount = 7 );
elementType = DType( Dim7, v7 );
If( elementType @<> 'S' );
CellPutN( Numbr( v8 ), paramTgtCube, v1, v2, v3, v4, v5, v6, v7 );
Else;
CellPutS( v8, paramTgtCube, v1, v2, v3, v4, v5, v6, v7 );
EndIf;
ElseIf( dimCount = 8 );
elementType = DType( Dim8, v8 );
If( elementType @<> 'S' );
CellPutN( Numbr( v9 ), paramTgtCube, v1, v2, v3, v4, v5, v6, v7, v8 );
Else;
CellPutS( v9, paramTgtCube, v1, v2, v3, v4, v5, v6, v7, v8 );
EndIf;
ElseIf( dimCount = 9 );
elementType = DType( Dim9, v9 );
If( elementType @<> 'S' );
CellPutN( Numbr( v10 ), paramTgtCube, v1, v2, v3, v4, v5, v6, v7, v8, v9 );
Else;
CellPutS( v10, paramTgtCube, v1, v2, v3, v4, v5, v6, v7, v8, v9 );
EndIf;
ElseIf( dimCount = 10 );
elementType = DType( Dim10, v10 );
If( elementType @<> 'S' );
CellPutN( Numbr( v11 ), paramTgtCube, v1, v2, v3, v4, v5, v6, v7, v8, v9, v10 );
Else;
CellPutS( v11, paramTgtCube, v1, v2, v3, v4, v5, v6, v7, v8, v9, v10 );
EndIf;
ElseIf( dimCount = 11 );
elementType = DType( Dim11, v11 );
If( elementType @<> 'S' );
CellPutN( Numbr( v12 ), paramTgtCube, v1, v2, v3, v4, v5, v6, v7, v8, v9, v10, v11 );
Else;
CellPutS( v12, paramTgtCube, v1, v2, v3, v4, v5, v6, v7, v8, v9, v10, v11 );
EndIf;
ElseIf( dimCount = 12 );
elementType = DType( Dim12, v12 );
If( elementType @<> 'S' );
CellPutN( Numbr( v13 ), paramTgtCube, v1, v2, v3, v4, v5, v6, v7, v8, v9, v10, v11, v12 );
Else;
CellPutS( v13, paramTgtCube, v1, v2, v3, v4, v5, v6, v7, v8, v9, v10, v11, v12 );
EndIf;
ElseIf( dimCount = 13 );
elementType = DType( Dim13, v13 );
If( elementType @<> 'S' );
CellPutN( Numbr( v14 ), paramTgtCube, v1, v2, v3, v4, v5, v6, v7, v8, v9, v10, v11, v12, v13 );
Else;
CellPutS( v14, paramTgtCube, v1, v2, v3, v4, v5, v6, v7, v8, v9, v10, v11, v12, v13 );
EndIf;
ElseIf( dimCount = 14 );
elementType = DType( Dim14, v14 );
If( elementType @<> 'S' );
CellPutN( Numbr( v15 ), paramTgtCube, v1, v2, v3, v4, v5, v6, v7, v8, v9, v10, v11, v12, v13, v14 );
Else;
CellPutS( v15, paramTgtCube, v1, v2, v3, v4, v5, v6, v7, v8, v9, v10, v11, v12, v13, v14 );
EndIf;
ElseIf( dimCount = 15 );
elementType = DType( Dim15, v15 );
If( elementType @<> 'S' );
CellPutN( Numbr( v16 ), paramTgtCube, v1, v2, v3, v4, v5, v6, v7, v8, v9, v10, v11, v12, v13, v14, v15 );
Else;
CellPutS( v16, paramTgtCube, v1, v2, v3, v4, v5, v6, v7, v8, v9, v10, v11, v12, v13, v14, v15 );
EndIf;
EndIf;


575,29

#****Begin: Generated Statements***
#****End: Generated Statements****



#Delete the TempView

If( ViewExists( paramSrcCube, tempView) = 1 );
ViewDestroy( paramSrcCube, tempView);
EndIf;


If ( errorFlag > 0 ) ;
ProcessQuit;
EndIf;

# Attach Rule to the Replicated Cube

If( paramNeedRules = 1 );
ruleFile = paramSrcCube | '.rux';
If( FileExists( ruleFile ) = 1 );
RuleLoadFromFile( paramTgtCube, ruleFile );
EndIf;
EndIf;


#Deleting Source Cube
CubeDestroy( paramSrcCube );
576,CubeAction=1511DataAction=1503CubeLogChanges=0

[gopal]

rename cube name

[Wim Gielis]

Looking at the provided TI code, how are you going to do an export of data if you nowhere use AsciiOutput or TextOutput?
Again in the code, where do we see that you select months in a months dimension, and years in a years dimension?
By copying code that you find on the internet here, you are not going to solve this problem. There's got to be work from your side too.
Mattgoff's post provides you 3 possible avenues. Choose 1 and implement it.

[rmackenzie]

Mattgoff's post provides you 3 possible avenues. Choose 1 and implement it.

I liked Matt's first suggestion, plus pandinus' suggestion is pretty good too.

Gopal - the underlying problem you are facing is that TI doesn't really allow for definition of asymmetric cube views. There might be some sneaky way to do it using MDX via the ODBO data source option, but the performance will probably be dreadful and the parametrization overly difficult. You're way better off with the suggestions above.

[gopal]

#==========
# Constants
#==========
cCubeName = 'Price_And_Cost';
cViewName = 'CurrentMonth';
cSubName = cViewName;
cYear = 'Year';
cMonth = 'Months';
cTime = pMonth | '-' | pYear;
vLocal=0;
#===========================================
# Zero out current months data prior to load
#===========================================
#---------------------------------------
# Create view to be used for zeroing out
#---------------------------------------
# Delete view if it already exists
If(ViewExists(cCubeName, cViewName) = 1);
ViewDestroy(cCubeName, cViewName);
EndIf;
# Create view
ViewCreate(cCubeName, cViewName);
#------------------------------------
# Create subset for version dimension
#------------------------------------
sDimName = 'Months';
# Delete subset if it already exists
If(SubsetExists(sDimName, cSubName) = 1);
SubsetDestroy(sDimName, cSubName);
EndIf;
# Create subset
SubsetCreate(sDimName, cSubName);
# Insert relevant elements into subset
SubsetElementInsert(sDimName, cSubName, 'Jan', 1);
# Assign subset to view
ViewSubsetAssign(cCubeName, cViewName, sDimName, cSubName);
#---------------------------------
# Create subset for time dimension
#---------------------------------
sDimName = 'Months';
# Delete subset if it already exists
If(SubsetExists(sDimName, cSubName) = 1);
#SubsetDestroy(sDimName, cSubName);
EndIf;
# Create subset
#SubsetCreate(sDimName, cSubName);
# Insert relevant elements into subset
#SubsetElementInsert(sDimName, cSubName, cTime, 1);
# Assign subset to view
ViewSubsetAssign(cCubeName, cViewName, sDimName, cSubName);
#----------------------
# Zero out data in cube
#----------------------
ViewZeroOut(cCubeName, cViewName);
#---------------------------
# Clean up views and subsets
#---------------------------
ViewDestroy(cCubeName, cViewName);
sDimName = 'Months';
SubsetDestroy(sDimName, cSubName);
sDimName = 'Months';
SubsetDestroy(sDimName, cSubName);
#data tab
# Retrieve existing value from cube
nCurrentValue = CellGetN(cCubeName, vVersions, vYear, vChannels,vMonths,vPrice_Cost_Measures,vProducts,
numbertostring(vValue));
# Accumulate data
nNewValue = nCurrentValue + vLocal;
# Send new value to cube
CellPutN(nNewValue,cCubeName, vVersions, vYear, vChannels,vMonths,vPrice_Cost_Measures,vProducts,
numbertostring(vValue));

[RJ!]

IF the whole point is to find the last 6mths & add to a subset, then why not use the MDX function "Last Periods"?

Code: Select all

# Build MDX String with CurrDate Variable
MyVar = Number of Periods (in OP case 6)
MyDim = Your Period Dimension
CurrDate = Your start month (assuming its your "cTime" per below
MySub = Name of your Subset (i.e. 'sys_last_6mths')
MyMDX = 'LastPeriods(' | NumbertoString(MyVar) | ', [' | MyDim | '].[' | CurrDate | '])' ;

# Execute Bedrock process to re-insert Elements
Executeprocess( 'Bedrock.Dim.Sub.Create.ByMDX', 'pDimension', MyDim, 'pSubset' , MySub , 'pMDXExpr' ,  MyMDX ) ;

[BariAbdul]

Thanks RJ!,That is brilliant.But Gopal's code has some confusion where he is having view name and subset name as the same,I don't think it is the best practice , as it could create lot's of confusion later part of the code.Thanks