Planning Analytics 2.0.5 workspace
Microsoft Server 2016

You get value in to the TM1 application, from a yearly cost of company car, and you want to know what the monthly cost is. Where you have the values for start date, and the accumulated sum for the month.

Starting solution:
This show part of the solution, to get you to understand the ParseDate function.
You must expand this solution to get a fully working TI process.

You can a find better solutions here:


Login to PAW (Plannig Analytics Workspace) and your application.
Click on the pencil to make it blue so you are in edit mode.
Right click on dimensions to create a new dimension
Enter the name of the dimension and click create
Click OK and click on enter new members
Enter StartDate and click on commit
click on plus sign and select after selection to add a new member of the dimension.
Enter this values – this will be part of our cube to show the result.
Select the Breakdate and right click to set format to Date, repeat for StartDate.
Click on the show members attributes to see the format.
Create a new dimension named Result.Log with only one member.
Right click on cubes to create a new cube of above dimensions.
Mark Result.Event and Result.log and click arrow to move them over.
Enter Result Cube as name of the cube, and click on Create.
Click on the plus sign to add a new sheet. Select a boxed template.
Select the cube and right click and select Add new view
Click on the box and on the icon to change row and columns value. So you get this;
Right click on process to create a new process. Name the process AmountCalculated.
Expand the code window so you see most of it below the cube view.
Click on parameters tab, and click on plus sign to add input parameters. Here we will enter the values to test the process. In your solution you may get this values from a file instead.
Enter above names and start values. (p is to show it is a parameter variable)
Enter script tab, to enter your code.
Enter below code under Prolog End: Generated Statements

# define the cube and dimensions to work with
sDimName = ‘Result.Log’;
sCubename = ‘Result Cube’;

nBreakDateA = ParseDate( pBreakDateA, ‘yyyyMMdd’,0 );
nStartDateA = ParseDate( pStartDateA, ‘yyyyMMdd’,0 );

# write variables values to txt file in servers log folder for debug
asciioutput (‘../logfiles/debuglog1.txt’, str(nBreakDateA,8,2),str(nStartDateA,8,2) , numbertostring(pTotalAmountA));

# check if it is before break date then divide with 12
if (nStartDateA < nBreakDateA);
nMonthAmountA = pTotalAmountA / 12;

# check if the distance is not in the same year
nStartYear = stringtonumber( subst (pStartDateA, 1,4));
nEndYear = stringtonumber( subst (pTotalDateA, 1,4));

# write variables values to txt file in servers log folder for debug
asciioutput (‘../logfiles/debuglog3.txt’, numbertostring(nStartYear),numbertostring(nEndYear) , pStartDateA, pTotalDateA );

if (nStartYear <>  nEndYear);
nTotalDateA = ParseDate( pTotalDateA, ‘yyyyMMdd’,0 );
nResult=  ((YEAR(DATE( nTotalDateA  )) – YEAR(DATE( ( nStartDateA ))) )*12)+ (MONTH(DATE( nTotalDateA )) – MONTH(DATE( ( nStartDateA ))) ) ;

# write variables values to txt file in servers log folder for debug
asciioutput (‘../logfiles/debuglog4.txt’, numbertostring(nStartYear), numbertostring(nEndYear) , numbertostring(nResult)  );

# if total is far from start date to today – you use the same formula  but nResult is total number of months
nMonthAmountA = pTotalAmountA / nResult;
# change here what values you want to use for the calculation
nStartmonth = stringtonumber( subst (pStartdateA, 5,2));
nEndmonth = stringtonumber( subst (pTotaldateA, 5,2));
nOfMonths = nEndmonth – nStartmonth +1;
# calculate value if less than a year
nMonthAmountA = pTotalAmountA / nOfMonths;
Then enter below code as Epilog

# write variables values to txt file in servers log folder for debug
asciioutput (‘../logfiles/debuglog2.txt’, str(nStartmonth,8,2) ,numbertostring(nMonthAmountA) ,numbertostring(pTotalAmountA), numbertostring(nOfMonths));

# create a new dimension element with the date and time as value to make it uniq
sDate = (timSt(now, ‘\Y-\m-\d \h:\i’));
DimensionElementInsertDirect( sDimname,’ ‘, sDate, ‘N’ );
# update the log cube with the values
CellPutN( nStartDateA+21916, sCubename,’StartDate’, sDate );
CellPutN( pTotalAmountA, sCubename,’TotalAmount’, sDate );
CellPutN( nMonthAmountA, sCubename,’MonthAmount’, sDate );
CellPutN( nBreakDateA+21916, sCubename,’BreakDate’, sDate );

# 1 in excel is 1900, but in TM1 that is 1960 – therefor the addition of 21916 days.
Click validate, and correct any ‘ that may be of wrong format.
Save and run.
Enter your values and click OK.
Above error if there is no /logfiles/ folder parallel to the data folder.
Create on folder c:\Program Files\ibm\cognos\tm1_64\samples\tm1\logfiles, if you use a sample TM1 application that do not have a separate log folder.
Connect to the server and check the log files to see variables values.
Click on refresh for the view to see the result.

Things to review:
nBreakDateA = ParseDate( pBreakDateA, ‘yyyyMMdd’,0 );
Above code change the string to a date number, but should be changed so it is not use the 1960 as start date.

if (nStartDateA < nBreakDateA);
Above code check if the date is before a specific date, you can change this to be automatic check for 12 months .

if (nofmonths > 12);
# if total is for from start date to today – you use the same formula
nMonthAmountA = pTotalAmountA / nOfMonths;
Above code check if the difference is more than 12 months, then you can enhance it to calculate the value from January to today date only, if the total is only for this year.

# asciioutput (‘../logfiles/debuglog1.txt’, str(nBreakDateA,8,2),str(nStartDateA,8,2) , numbertostring(pTotalAmountA));
Remark out the debug asciioutput lines from your code.

More information:

Operators in TI IF Statements

Planning Analytics 2.0.5 (PAX)
Microsoft Windows 2016 Server

When you select a TM1 server in list of TM1 connections in Planning Analytics for Excel a HTTP 503 error appears.
If you test the connection to PAW in the options box it is OK.

Possible solution:
Inside the ADMINTOOL for PAW (planning analytics workspace) the value for TM1 Application Server Gateway URI is wrong, it may point to HTTPS when it should point to HTTP.
This if you have changed your setup of TM1WEB(WLP) from HTTPS to HTTP to do some testing.

Copy the URL to a web browser (IE) and test if you can surf there.
Example of URL are:

More information:

Planning Analytics 2.0.5 TM1
Microsoft Windows 2016 terminal server
TM1 package connector version 10.2.6100.8-0

Inside TM1 architect you get a error when you run a TI process that uses the Package connector. Error happens when you start to import data to TM1 with the process.

Error Message:
Error: MetaData procedure line (0): CCognosPackage::BuildDataSource Exception: (TR3117) Unable to retrieve security objects. Please verify credentials.
– CCL-BIT-0005 A socket reported a communication error.
– CAM_Connect=0xffffffff -2113929065CAM-CRP-0321 The GSKit function ‘gsk_environment_init’ failed with the error code ‘9’

Ensure that on the Planning Analytics server, where the TM1 applications are, that you have the GSK*.dll files that come with the TM1 package connector version. If they have been replace to solve other issues, you need to copy them back.

A clean new install of TM1 Package Connector on the PA 2.0.5 server can also help.

More information:

Planning Analytics 2.0.5
Cognos Analytics 11.0.12
Microsoft Windows 2016 server

When starting Excel and selecting Planning Analytics for Excel (TM1) you get a error after the login dialog.

Error message:
Cognos.Office.Tm1.Connections.Tm1WebException: The remote server returned an error: (503) Server Unavailable. —> System.Net.WebException: The remote server returned an error: (503) Server Unavailable.
at System.Net.HttpWebRequest.GetResponse()
at Cognos.Office.Tm1.Connections.Tm1Request.GetResponse()
— End of inner exception stack trace —
at Cognos.Office.Tm1.Connections.Tm1Connection.HandleWebException(Tm1WebException x, Uri path)
at Cognos.Office.Tm1.Connections.Tm1Connection.GetResponse(Tm1Request request)
at Cognos.Office.Tm1.Connections.Tm1Connection.SendRequest(Tm1Request webRequest, AcceptReturnEnum returns)
at Cognos.Office.Tm1.Connections.Tm1Connection.SendRequest(String method, String path, String postData, AcceptReturnEnum returns, Int32 timeout)
at Cognos.Office.Tm1.Connections.Tm1Connection.Get(String path, AcceptReturnEnum file, Int32 timeout)
at Cognos.Office.Tm1.Rest.Tm1RestCapabilities..ctor(Tm1Connection connection, String server, Int32 timeout)
at Cognos.Office.Tm1.Rest.Tm1RestConnection.RefreshCapabilities(String server, String path)
at Cognos.Office.Tm1.Rest.Tm1RestServer.UpdateCapabilities(String serverName)
at Cognos.Office.Tm1.Rest.Tm1RestDataSource.UpdateCapabilities(String serverName)
at Cognos.Office.Tm1.Tm1DataSource.On_LogOn(String server)
at Cognos.Office.Tm1.Tm1DataSource.Logon(LogonType logonType, DataSourcePropertiesCollection properties)
at Cognos.Office.Framework.Communications.DataSourceManager.LogOn(String dsType, String connectionString, String serverName, IDataSource& source)

Check the values in PAW Admintool, special for the TM1 Application Server gateway URI. If that is wrong, like use HTTP instead of HTTPS when you have switch TM1WEB to use SSL, you will get above error inside PAX. To surf to PAW will still work fine.

When a user start Excel and PAX, the first connection is to the PAW server.
Then the PAW server will check the TM1S.CFG for value ClientCAMURI=
this URL will be used by PAX to log you in. After you login, the PMHUB.HTML file need to list the URL to PAW to allow you to get back.
You need to check the pmhub.html file in folder c:\Program Files\ibm\cognos\analytics\webcontent and c:\Program Files\ibm\cognos\analytics\webcontent\bi.
Open it in notepad++ and check that the PAW servers name or DNS alias at var pmhubURLs = [“” , “https://centospaw.lab.local”];
When you are back at PAW server in the communication route – pax will use the PAW ADMINTOOLS value in TM1 Application Server gateway URI before it show you are connected. If that value does not point to the CA11 server you get above error.

PAX is not using the PAW ADMINTOOLS value for IBM Cognos BI Gateway URI, instead you have the value  export TM1LoginServerURL=”https://tm1serverhost:12345″ in the paw.env file. The TM1 application that use the HTTPPortNumber = 12345, that value in its tm1s.cfg file is used.

If the value in PMHUB.HTML is wrong you get a error like “Cannot redirect back to planning analytics because the host URL (http://centospaw.lab.local) has not been whitelisted.”

Do not enter the port number for paw server in the PMHUB.HTML file.

Planning Analytics 2.0.4 (TM1)
Microsoft Windows 2016 server

TM1 Architect is connecting the TM1 applications on the wrong network card on the Microsoft Windows server.

Possible solution:
The default network card with the lowest metric value is selected from all network cards to be the default network card. Use Get-NetIPInterface to see metrics.

Start Powershell on the Windows server
Enter: Get-NetIPInterface
This will list the network interfaces on the server.
Check the value for ifIndex and InterfaceMetric (A low value indicates a higher priority.)
The card with lowest value for InterfaceMetric will be used by TM1.
Is this the correct one? or is it for a backup network?

ifIndex InterfaceAlias AddressFamily NlMtu(Bytes) InterfaceMetric Dhcp ConnectionState PolicyStore
——- ————– ————- ———— ————— —- ————— ———–
18 Ethernet 2 IPv6 2800 20 Enabled Connected ActiveStore
1 Loopback Pseudo-Interface 1 IPv6 4294967295 50 Disabled Connected ActiveStore
18 Ethernet 2 IPv4 2800 20 Disabled Connected ActiveStore
12 Ethernet IPv4 1500 10 Enabled Connected ActiveStore
1 Loopback Pseudo-Interface 1 IPv4 4294967295 50 Disabled Connected ActiveStore

Change with Set-NetIPInterface, for example:
Set-NetIPInterface -InterfaceIndex -InterfaceMetric
Set-NetIPInterface -InterfaceIndex 12 -InterfaceMetric 50
Set-NetIPInterface -InterfaceIndex 18 -InterfaceMetric 10
Above will make card 18 to be the new default.
Check the ip address of the card by enter IPCONFIG
Ethernet adapter Ethernet 2:

Connection-specific DNS Suffix . :
Link-local IPv6 Address . . . . . : fe80::a8cd:d4c4:120e:3ed1%18
Autoconfiguration IPv4 Address. . :
Subnet Mask . . . . . . . . . . . :
Default Gateway . . . . . . . . . :

Start TM1 Architect
Expand an TM1 application
See in the right side the IP address that will be used.

More information:

Cognos Planning Analytics Workspace version 30
Microsoft Windows 2016 Server

In a Planning Analytics (TM1) environment where native security is used, you get a error when you try to add content to workspace from the PA databases on left side of screen.

Error Message:
unable to fetch data for content tree. Session can not be established for server: BUDGET 401 (Unauthorized) (“errorMessage”.””.”query”.”https://…..

Possible Solution:
Log out from Workspace
Go into TM1 Architect.
Set a password for all users, do not use blank (empty) as a password. That will not work for workspace. Also ensure that the ADMIN account have a password like apple.

After the user have a password set, exit TM1 Architect and surf to Planning Analytics Workspace again.

Planning Analytics 2.0.4 on Microsoft Windows 2012 server
Planning Analytics Workspace 30 on Linux server
Cognos Analytics 11.0.9 on Microsoft Windows 2012 server

Have setup PA with SSO to CA11 as above link, but when a user surf from Internet Explorer to PAW at address –  the user end up logged into the CA11 portal (cognos connection) instead of the PAW page.

Start PUTTY and login to the Linux server
Enter sudo -su to a root user.
Go to the ../paw folder.
Go to the /paw/config folder.
Enter more paw.env  to see the settings used in Planning Analytics Administration Tool listed on screen. They are saved to this file paw.env
Enter nano paw.env  to be able to edit the file.
Change the IBM Cognos BI Gateway URI and IBM Cognos BI Dispatcher URI fields, to both end with /bi/v1/disp


Two lines in paw.env should look like this;
export IBMCognosGatewayURL=””
export IBMCognosServlet=””

Press CTRL+X to exit the NANO editor in linux.
Press Y to save the changes to paw.env file.

In the paw/scripts folder enter ./ down  to stop the PAW services.
In the paw/scripts folder enter ./ to load the PAW services again.
In the paw/scripts folder enter ./ start  to start the PAW services.
Wait 10 minutes.
Surf to  to test if it works.

This change can also be done in the Planning Analytics Administration Tool, but then you first need to turn off SSO in IIS and change to security mode 1 in tm1s.cfg file.

More Information:

Planning Analytics Workspace 2.0.4
Microsoft Windows 2016 server

When starting the PAW administration tool you get a error when updating settings.

Error Message:
Failed to fetch the status. Ensure the Administration tool is running.

If you from a power shell prompt test the installation by running the command ./paw.ps1 ( from folder d:\install\paw\scripts\) you get this error:

Error: for pa-gateway Cannot start service pa-gateway: failed to create endpoint pa-gateway on network NAT. HNS failed with error: The object already exist.
Encountered errors while bringing up the project.
Execution failed with exit code 1.

Use the scripts/paw.ps1 script instead of Administration tool from power shell prompt.
Enter scripts/paw.ps1 to ensure all services are running and up-to-date.
Enter scripts/paw.ps1 ps to see status of all services.

Above may show that you have a pa-gateway already running.
Check that no other application is using port 80 and 443.
Start IE on the server and surf to http://localhost/ – what does that give?

Stop all PAW with scripts/paw.ps1 down
Then start it again with scripts/paw.ps1

Did it help?

Enter docker ps -a to see all running containers. Any duplicates?
Then to remove a container enter docker stop <container-id> ; then docker rm <container-id>.

Check the log files in folder D:\install\paw\log\pa-gateway\error.log
If you see this; AH00558: httpd.exe: Could not reliably determine the server’s fully qualified domain name, using fe80::5db7:f898:8a1:858c. Set the ‘ServerName’ directive globally to suppress this message. Then that is nothing to be concerned about.

To remove installation of PAW on Microsoft Windows 2016 do this steps in paw folder:
scripts/paw.ps1 stop
scripts/paw.ps1 rm -v
docker rm $(docker ps -a -q)
docker rmi $(docker images -q)
…after that you can install again with ./Start.ps1

You can recreate the docker network with below commands (try this first):
Stop the docker service: stop-service docker
Make sure the hns service is running: start-service hns
Now run this PowerShell command: get-containernetwork | remove-containernetwork
Start the docker service again: start-service docker

If it still doesn’t work, you can also try the same steps except with get-netnat | remove-netnat:

Stop the docker service: stop-service docker
Make sure the hns service is running: start-service hns
Now run this PowerShell command: get-netnat | remove-netnat
Start the docker service again: start-service docker

After that is done, go to the d:\install\paw\scripts folder in power shell.
Start the PAW with ./paw.ps1 start.

Check the file D:\install\paw\config\paw.ps1 contain the values you have set in the administration tool before. Should look similar to this;
# paw.ps1 file generated by the Administration Tool

Surf to servername to see if paw is working e.g.  http://labwin2012ca.lab.local/

To get more detail debug info on screen,
stop the docker service.
Run the Docker daemon in debug mode:
C:\Program Files\Docker\dockerd -D

More Information:


Cognos Analytics 11.0.10
Microsoft Windows 2016 Server

When you try to install the ExternalDiskCache_amd64_en-US.msi you get a error.

Error Message:
IIS version 7.0 or greater is required to install Microsoft External Cache Version 1 for IIS 7

You can resolve this problem by modifying registry.

Open run dialog and enter regedit, to open the Registry Editor.
Find MajorVersion in the path: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\InetStp and HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W3SVC\Parameters.
Open the value MajorVersion and select Decimal radiobutton.
Change Its value from 10 to 9.
Click OK.

After finishing the steps above, you can install External Disk Cache and Microsoft Web Farm Framework successfully.

You can also try download a new version from here

More Information

Planning Analytics 2.0.4
Planning Analytics Workspace 30
Microsoft Windows 2016 Server

You have installed docker, but before you install PAW, you realize that you do not have the needed 50 GB of free space on C drive. How do I tell docker to create the images on the D drive?

Create a folder d:\docker\images
Stop the docker service
Open Notepad and enter this
“data-root”: “d:\\docker\\images”
save the file as c:\programdata\docker\config\daemon.json

docker1 docker2
Start the docker service

Any previous created images in C:\ProgramData\docker\containers\ are not moved.

How Install Docker on Windows 2016 – Get Docker EE for Windows Server
Docker EE for Windows Server 2016 is simple to install.
1. Installation

Open a PowerShell command prompt, and type the following commands:

Install-Module DockerProvider -Force
Install-Package Docker -ProviderName DockerProvider -Force

2. Test your Docker EE Installation
Test your Docker EE installation by running the hello-world container:

PS> docker container run hello-world:nanoserver

Unable to find image ‘hello-world:nanoserver’ locally
nanoserver: Pulling from library/hello-world
bce2fbc256ea: Pull complete
3ac17e2e6106: Pull complete
8cac44e17f16: Pull complete
5e160e4d8db3: Pull complete
Digest: sha256:25eac12ba40f7591969085ab3fb9772e8a4307553c14ea72d0e6f98b2c8ced9d
Status: Downloaded newer image for hello-world:nanoserver

Hello from Docker!
This message shows that your installation appears to be working correctly.

Download a working docker-compose-Windows-x86_64.exe file;

Invoke-WebRequest "" -UseBasicParsing -OutFile $Env:ProgramFiles\docker\docker-compose.exe

Rename it to docker-compose.exe and copy it to the c:\program files\docker folder.

Then after a reboot of the Windows 2016 server, turn off your Anti-Virus software and start your PAW installation from a administrative powershell prompt with the command ./Start.ps1

More about docker:

Installation videos on planning analytics workspace: