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
https://www.ibm.com/docs/en/cognos-tm1/10.2.2?topic=file-parameters-in-tm1scfg
https://exploringtm1.com/how-to-create-a-tm1-model-a-best-practice-guide/
https://cubewise.com/blog/adjusting-server-configuration-planning-analytics/
https://bihints.com/book/export/html/100
https://exploringtm1.com/text-file-export-tm1-asciioutput-functions/
Here we start TM1 instance FAP, in folder c:\tm1data\FAP\config.
