Simple way to export cube to file

Product:
Planning Analytics 2.0.9.19
Microsoft Windows 2019 server

Issue:
How export a cube to file?

Suggested solution:

If you have a cube that looks like this, in planning sample, you can export it with a TM1 TI process.

You can write a process to export it that should look like this:

#Section Prolog

# -- get the time --
LastProcessStart = TIMST(NOW, '\Y-\m-\D \h:\i:\s');

# -- set the process as a subset name --
sName = GetProcessName();
ViewName = sName ;
SubName = sName ;

# -- check the folder to save the environment file 
DimName1='SYS_ServerParameters';
Element1 = 'ServerName' ;
Element2 = 'LastUpdated' ;

# -- find the data folder from the path of the logs folders---
sLogDirName1 = LOWER ('Logfiles\' ) ;
sLogDirName2 = LOWER ('Logs\' ) ;
sDataDirName = LOWER ( 'Data\' ) ;
sBackupDirName = LOWER ( 'Config\' );
sENvFileName = 'environment.txt' ;
sLogDirPath = LOWER( GetProcessErrorFileDirectory );

nLyckadScan1 = SCAN (sLogDirName1, sLogDirPath) ;
nLyckadScan2 = SCAN (sLogDirName2, sLogDirPath) ;
IF ( nLyckadScan1 <> 0 );
sDataDirPath = DELET (sLogDirPath, nLyckadScan1, LONG (sLogDirName1)) | sDataDirName;
sEnvDirPath = DELET (sLogDirPath, nLyckadScan1, LONG (sLogDirName1)) | sENvFileName;
ELSEIF ( nLyckadScan2 <> 0 );
sDataDirPath = DELET (sLogDirPath, nLyckadScan2, LONG (sLogDirName2)) | sDataDirName;
sEnvDirPath = DELET (sLogDirPath, nLyckadScan2, LONG (sLogDirName2)) | sENvFileName;
ELSE;
# the log folder does not have this name use this then --
sEnvDirPath = 'D:\Program Files\ibm\cognos\tm1_64\samples\tm1\PlanSamp\' | sEnvFileName ;
ENDIF;

# -- check that the cube exist
cube = 'Systemparameters';
DimName3 = 'Sysparameters' ;
DimName4 = 'Measure Parameter';
DimName5= 'Text' ;

IF ( CubeExists( Cube ) = 0 ) ;
# -- if cube does not exist - stop the process --
ProcessQuit ;
ENDIF;

# build the view - but first destroy it
ViewDestroy ( Cube, ViewName );
SubsetDestroy ( DimName3, SubName );
SubsetDestroy ( DimName4, SubName );

#-- create a view --
ViewCreate ( Cube, ViewName );

#-- create the subsets --
SubsetCreateByMDX ( SubName, '{TM1FILTERBYLEVEL( {TM1SUBSETALL( [' | DimName3 | '] )}, 0)}' );
SubsetCreateByMDX ( SubName, '{TM1FILTERBYLEVEL( {TM1SUBSETALL( [' | DimName4 | '] )}, 0)}' );

ViewSubsetAssign ( Cube, ViewName, DimName3, SubName );
ViewSubsetAssign ( Cube, ViewName, DimName4, SubName );

# -- set the source to skip calculated values --
ViewExtractSkipCalcsSet ( Cube, ViewName, 1);
ViewExtractSkipRuleValuesSet ( Cube, ViewName, 0 );
ViewExtractSkipZeroesSet ( Cube, ViewName, 1 );

# -- Set source --
DataSourceType='VIEW';
DataSourceNameForServer=Cube;
DataSourceCubeview=ViewName;

# -- Change to get comma in the text file
DatasourceASCIIQuoteCharacter='"';
DatasourceASCIIDelimiter = ',';

#Section Metadata

# -- get the servername from dimension --
aServerName = ATTRS( DimName1, Element1 , 'value') ;
vApplication = aServerName | ':' | DimName3 ;

# -- export the values to file --
ASCIIOutput( sEnvDirPath, vApplication ,v1,v2,v3 );


#Section Data



#Section Epilog

ViewDestroy ( Cube, ViewName );
SubsetDestroy ( DimName3, SubName );
SubsetDestroy ( DimName4, SubName );

The TI process need to have a data source like this:

The file will look like below;

You can write a process to import the csv file that can look like this (you have to adjust the code to your environment) :

#Section Prolog

# -- get the time --
LastProcessStart = TIMST(NOW, '\Y-\m-\D \h:\i:\s');

# -- check the folder to load the environment file from
DimName1='SYS_ServerParameters';
Element1 = 'ServerName' ;
Element2 = 'LastUpdated' ;

# -- try to find the data folder from the logs folder ---
sLogDirName1 = LOWER ('Logfiles\' ) ;
sLogDirName2 = LOWER ('Logs\' ) ;
sDataDirName = LOWER ( 'Data\' ) ;
sBackupDirName = LOWER ( 'Config\' );
sEnvFileName = 'environment.txt' ;
sLogDirPath = LOWER( GetProcessErrorFileDirectory );

nLyckadScan1 = SCAN (sLogDirName1, sLogDirPath) ;
nLyckadScan2 = SCAN (sLogDirName2, sLogDirPath) ;
IF ( nLyckadScan1 <> 0 );
sDataDirPath = DELET (sLogDirPath, nLyckadScan1, LONG (sLogDirName1)) | sDataDirName;
sEnvDirPath = DELET (sLogDirPath, nLyckadScan1, LONG (sLogDirName1)) | sEnvFileName;
ELSEIF ( nLyckadScan2 <> 0 );
sDataDirPath = DELET (sLogDirPath, nLyckadScan2, LONG (sLogDirName2)) | sDataDirName;
sEnvDirPath = DELET (sLogDirPath, nLyckadScan2, LONG (sLogDirName2)) | sEnvFileName;
ELSE;
# we chance that the log and data folder are the same
sEnvDirPath = 'D:\Program Files\ibm\cognos\tm1_64\samples\tm1\PlanSamp\' | sEnvFileName ;
ENDIF;


# -- setup the parameter cube --
cube2 = 'Systemparameters';
DimName3 = 'Sysparameters' ;
DimName4 = 'Measure Parameter';
Element5= 'Text' ;

# -- check that that the cube exist
IF ( CubeExists( Cube2 ) = 0 ) ;
IF (DimensionExists( DimName3 ) = 0 ) ;
DimensionCreate (DimName3 ) ;
ENDIF;
IF (DimensionExists( DimName4 ) = 0 ) ;
DimensionCreate (DimName4 ) ;
ENDIF;
# -- create a cube
CubeCreate(Cube2, DimName3 , DimName4 );
ENDIF;

DimensionElementInsertDirect (DimName4, '', Element5 ,'S') ;


# -- set the source to be the file
DatasourceNameForServer = sEnvDirPath ;
DatasourceNameForClient = sEnvDirPath ;

#Section Metadata

#-- check that the application is correct from the file
# SCAN(substring, string)
vLocation = SCAN(':',vApplication ) ;
vServerName = SUBST( vApplication, 1, vlocation - 1 ) ;
aServerName = ATTRS( DimName1, Element1 , 'value') ;
IF (aServerName @<> vServerName);
ProcessQuit;
ENDIF;

# -- add the dimension element from vParameter
# DimensionElementInsertDirect(DimName, InsertionPoint, ElName,ElType);
DimensionElementInsertDirect (DimName3, '', vParameter ,'S') ;

# -- add the lastupdated parameter
DimensionElementInsertDirect (DimName3, '', Element2 ,'S') ;

#Section Data

# -- load the data to the cube 
CellPutS( vValue, cube2, vParameter, vColumn );


#Section Epilog

# -- write the time to cube
CellPutS( LastProcessStart, cube2, Element2, vColumn );

The import TI process need to have a file as data source, similar to this:

Both process demand that you have a dimension (SYS_ServerParameters) that have the name of the application.

This can be done with the process SYS.GET_ServerParameters.

More Information:

https://www.ibm.com/docs/ru/planning-analytics/2.0.0?topic=data-exporting-from-cube

CogKnowHow

Just another WordPress site

Simple way to export cube to file