Product:
Planning Analytics 2.0.9
Planning Analytics Workspace 55
Microsoft Windows 2016 server
Problem:
What to add cube security for new cognos groups from file.
You have created some cognos groups – GroupA and GroupB and filled them with Active Directory users.
You have added the group into TM1 Architect, to see that they are visible. This is done by right click on the Tm1 application and select security – clients/groups.
You have tested to add manually in TM1 architect, the values in the security cube.
Solution:
This can be solved in many ways, this is one example.
You have a text file with the groups and the new values. Here you add the other groups and there values you want to be setup.
Columns are cube to update, cube, cognos group, access rights.
Go to PAW. Login to your TM1 Instance. Go to Processes and right click and select Create Process.
Enter a name, in our example ImportSecurityTI.
Click Create.
Click on file.
Drag you text file to the drop area, to load the file into the system.
This will copy the file to a folder under your data folder.
Click Next. Select the delimiter you have in your file. Here we use comma.
Click preview.
Here we have a simple file, all columns are strings and we keep the default variables values of V1 to V4.
Click validate and save. Click on script.
Now enter code similar to this to make it populate the cube;
#Section Prolog
#****Begin: Generated Statements***
#****End: Generated Statements****
#——————————————————–
# setup the file to import
#——————————————————–
DataSourceType=’CHARACTERDELIMITED’;
# ASCII for comma is 44 http://www.asciitable.com/
DatasourceASCIIDelimiter=Char(44);
DatasourceASCIIHeaderRecords=0;
# ASCII for quates is 34
DatasourceASCIIQuoteCharacter=Char(34);
# place the file in below folder and paw will find the file
DatasourceNameForServer=’model_upload\CubeSecurity3.txt’;
# full path to the file and name – this is for Tm1 architect to find the file
DatasourceNameForClient=’C:\Program Files\ibm\cognos\tm1_64\samples\tm1\24Retail_CAM\data\model_upload\CubeSecurity3.txt’;
# set default values
sNAMESPACE= ‘CAMID(“:’;
sEND = ‘”)’;
sCUBE= ‘}CubeSecurity’;
#Section Metadata
#****Begin: Generated Statements***
#****End: Generated Statements****
#——————————————————–
# remove the ### for the debug lines to write variables to text file
#——————————————————–
### ASCIIOutput (‘c:\temp\debugout1.txt’, v1, v2, v3, v4 );
# check if string contain : (colon)
# SCAN(find , in string)
nSTART= scan ( ‘:’,v1);
if (nSTART <> 0);
# remove all before
# SUBST(string, beginning, length)
v1 = subst (v1, nSTART +1, (LONG( v1) – nSTART));
endif;
# add CAMID to the group (column 3)
# check that it does not already have : (colon)
nSTART= scan ( ‘:’,v3);
if (nSTART = 0);
# add value before to look like this “CAMID(“:GroupA”)”
# SUBST(string, beginning, length)
v3 = sNAMESPACE | v3 | sEND;
endif;
### ASCIIOutput (‘c:\temp\debugout2.txt’, v1, v2, v3, v4 );
# write values to the cube
# CellPutS (String, Cube, element1, element2, elementn )
CellPutS(v4,v1,v2,v3);
#Section Data
#****Begin: Generated Statements***
#****End: Generated Statements****
#Section Epilog
#****Begin: Generated Statements***
#****End: Generated Statements****
Click on validate – save – run buttons.
If all apostrophes are correct it should work fine.
More information:
nSTART= scan ( ‘:’,v1);
if (nSTART <> 0);
This will find the position in variable v1 where there are a colon. If there is none, then the value in nSTART is zero. At if we test that if not zero then do next line.
v1 = subst (v1, nSTART +1, (LONG( v1) – nSTART));
Here we replace variable v1 with a part of its content, we take one character to the right from the nSTART position and until end of string ( length of sting minus the start position).
v3 = sNAMESPACE | v3 | sEND;
The pipe character is to add strings together in TI processes. We add the predefined variables sNAMESPACE and sEND around the variable v3, to get it to look correct.
Concatenating Data in TM1 – How to Concatenate Variables in a TI or Rule
If you do not add the groups in security dialog before you run the script you get this error:
Process completed with errors
“24Retail_CAM:}CubeSecurity”,”Capital”,”GroupA”,”WRITE”,Data Source line (1) Error: MetaData procedure line (26): Invalid key: Dimension Name: “}Groups”, Element Name (Key): “CAMID(“:GroupA”)”
If you see this, please wait longer.
