Sometimes you may have to transfer data from source to target in such a way that source cube contain some items which are not data items but need to be used as dimension elements. Based on these items mapping should be performed and data should be transferred.
For example, consider an employee cube:
The above cube contains employee information. We want to transfer the data from this cube to a target cube:
As you can see that Sex and Age, which were in data portion of source cube are in dimension portion of target cube.
Using Rules:
In such a scenario you need to write rule in following way to transfer the data:
[] = S: IF(!Sex @= DB(‘EmployeeInfo’, !Serial, ‘Employee Sex’) & NUMBR(!Age) = DB(‘EmployeeInfo’, !Serial, ‘Employee Age’),DB(‘EmployeeInfo’, !Serial, ‘Employee Name’),continue);
Here is the output that you’d get:
Using TI process:
1. Take 2 new variables of type element in TI. Set their values to:
vSex=CellGetS(‘EmployeeInfo’,Serial,’Employee Sex’);
vAge=NumberToString(CellGetN(‘EmployeeInfo’,Serial,’Employee Age’));
2. Some code for metadata and data portion will automatically be generated in Advanced tab.
3. Now remove the ‘Update Cube’ selection. (so that we could remove the default CellGetS/N command which was added to TI.
4. Put following to update the cell:
If(EmployeeInfo @= ‘Employee Name’);
CellPutS(VALUE,’Employee2′,Serial,vAge,vSex);
EndIf;
This was to ensure that we process each row only once, instead of processing it thrice (1 for each name/sex/age in source cube)
5. Now save and run the TI. You’ll see your data updated as shown above in rules section.
In above, i have assumed that you have basic understanding of TI hence I’ve not put a line by line commentary of what we need to do.
If you still have doubts, let me know in comments section.
Here is the tm1s.cfg configuration when you’ve implemented the system on a distributed environment. you can remove your default tm1s.cfg file content and replace with this one.
[TM1S]
ServerLogging=F
SecurityPackageName=Kerberos
# For third party security configuration, you need to set it to 5
IntegratedSecurityMode=5
UseSSL=F
ServerName=sales_model
#Your model files
DataBaseDirectory=D:\TM1Data\Data_files
LoggingDirectory=D:\TM1Data\Log_Files
#server url on which tm1 files are
AdminHost=176.12.9.165
#port which is open on server for tm1
PortNumber=12876
ClientMessagePortNumber=
Language=ENG
ProgressMessage=True
AuditLogOn=F
AuditLogMaxFileSize=80 MB
#Update per 60 secs.
AuditLogUpdateInterval=60
DefaultMeasuresDimension=T
GroupsCreationLimit=5000
UseSQLExtendedFetch=T
LockPagesInMemory=T
#Set it to number of CPUs you have
MaximumCubeLoadThreads=2
ViewConsolidationOptimization=T
MaximumViewSize=60000
ParallelInteraction=T
ServerCAMURI=http://176.12.9.166:9300/p2pd/servlet/dispatch
ClientCAMURI=http://176.12.9.167:80/ibmcognos/cgi-bin/cognos.cgi
CAMPortalVariableFile=portal\variables_TM1.xml
ClientPingCAMPassport=900
ClientPropertiesSyncInterval=180
IdleConnectionTimeOutSeconds = 900
Here is the tm1s.cfg configuration on localhost. you can remove your default tm1s.cfg file content and replace with this one.
Replace following values:
<your server name>
e.g. sales_model
<data dir path>
e.g. D:\MyFolder\salesModeldir\
# ———————————— File content starts here——————————————
[TM1S]
ServerLogging=F
SecurityPackageName=Kerberos
IntegratedSecurityMode=1
UseSSL=T
ServerName=<your server name>
DataBaseDirectory=<data dir path>
LoggingDirectory=
AdminHost=
PortNumber=12345
ClientMessagePortNumber=
Language=ENG
SaveTime=
DownTime=
ProgressMessage=True
AuditLogOn=F
AuditLogMaxFileSize= 100 MB
AuditLogUpdateInterval=60
MaximumCubeLoadThreads=15
ClientPropertiesSyncInterval=60
# ———————————— File content ends here——————————————
Feeders are weird, and core of any TM1 implementation. You can hell of a lot improve the performance of your server if you’re careful writing feeders. Sometimes I feel crazy while working on them. Learn everyday something new. So I maintain a list of all my learnings.
1. Feeders are required to feed cells which are calculated using formulae.
2. Let’s say A = B * C and C = D * E. If you feed C by E, you can either feed A by C or directly by E.
E => C
Either C => A or E => A
3. Order doesn’t matter in feeders. For example, in above example you could have written:
1)E => C
2)C => A or
1)C => A
2)E => C
4. If a chain of calculation is being performed, you need to feed multiple elements in the calculation tree. For example, in above tree, you have to feed A and C both. you can not leave A.
5. Once you’ve applied feeders and refreshed the cube, the values are stored in memory. Now even if you remove the feeders, you’ll see all values loaded. To revert the values to their pre-feeder state, once you’ve removed your feeders, unload the cube and browse again.
6. If calculation has arithmetic operations comprising addition or subtraction, you need to feed by all elements.
for example in a rule: A = N: c+d+e;
you should feed using all three (c, d and e)
7. If calculation has arithmetic operations comprising multiplication or division, you should feed using sparse elements.
example:
if rule is –
Revenue = N: units_sold * price
Here, the sparse dimension is units_sold, so you should try feeding with units_sold instead of price.
8. Never write a feeder when a rule has been written for consolidated cells.
If you’re getting frequent session timeouts on tm1 web, here is what you need to change to your configuration to stabilize it.
Edit Web.config. It’s in tm1web application directory in IIS. Probably in C:\Inetpub\www\tm1web
Search for the following code:
<!– SESSION STATE SETTINGS
By default ASP.NET uses cookies to identify which requests belong to a particular session. If cookies are not available, a session can be tracked by adding a session identifier to the URL. To disable cookies, set sessionState cookieless=”true”.
–>
<sessionState mode=”InProc” stateConnectionString=”tcpip=127.0.0.1:42424″ sqlConnectionString=”data source=127.0.0.1;Trusted_Connection=yes cookieless=”false” timeout=”120″ />
Change the timeout values.
httpMaxSessionTimeout = 7200
change in tm1s.cfg file :
IdleConnectionTimeout = 900
change in IIS worker process timeout :
IdleTimeout : 7200
Loading ...