Friday, July 25, 2014

How to create a TM1 view using TI script (Basic)

Data modification in TM1 is much more challenge than Data modification in relational database. The goal is to build a library to address data modification in TM1.  View creation is a very important build block to achieve such functionality, as View can be used as data source in TI process to load data, and View can be used to zero out data. This document is intended to start a approach to create a basic view with two dimensions and each dimension with one element.

Code

###
#paramters:
#pCube: Specifiy cube name
pCube ='SALES';
#pDim1: Specifiy first dimension name
pDim1='Product';
#pElement1: Specifiy element name for pDim1
pElement1='Product1';
#pDim2: Specifiy second dimension name
pDim2='Customers';
#pElement2: Specifiy element name for pDim2
pElement2='Customer 1';


#script:
sView = 'TEST';
sSubset = sView;

# Step 1: create View
If( ViewExists( pCube, sView ) = 1 );
 ViewDestroy( pCube, sView );
EndIf;
ViewCreate( pCube, sView );


# Step 2: create first subset
If( SubsetExists( pDim1, sSubset ) = 1 );
 SubsetDeleteAllElements( pDim2, sSubset );
Else;
 SubsetCreate( pDim1, sSubset );
EndIf;
SubsetElementInsert( pDim1, sSubset, pElement1, 1 );


# Step 3: Assigned the subset to view
ViewSubsetAssign( pCube, sView, pDim1, sSubset );


# Step 4: create second subset and assigned it to view if needed
If( SubsetExists( pDim2, sSubset ) = 1 );
 SubsetDeleteAllElements( pDim2, sSubset );
Else;
 SubsetCreate( pDim2, sSubset );
EndIf;
SubsetElementInsert( pDim2, sSubset, pElement2, 1 );


ViewSubsetAssign( pCube, sView, pDim2, sSubset );


# Step 5: set view parameters
ViewExtractSkipCalcsSet( pCube, sView, 1 );
ViewExtractSkipRuleValuesSet( pCube, sView, 1 );
ViewExtractSkipZeroesSet( pCube, sView, 1 );


Result

Cube


Subset1


Subset2


View

No comments:

Post a Comment