Product:
Planning Analytics 2.0.6
Microsoft Windows 2012 server

Problem:
How make a subset of a dimension, where i want all elements that start with BSA* and some other letters, like BSB and BSC?

Suggested Solution:
In your prolog section of the TI process, use UNION, similar to this:

sSubset=’export’;
pDim13=’company’;

StringMDX = ‘ (UNION(
(UNION (
{UNION( { TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [ ‘ | pDim13 | ‘ ] )}, 0)}, “BSA*” ) } ,
{ TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [ ‘ | pDim13 | ‘ ] )}, 0)}, “BSB*” ) } )} ,
{UNION( { TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [ ‘ | pDim13 | ‘ ] )}, 0)}, “BSC*” ) } ,
{ TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [ ‘ | pDim13 | ‘ ] )}, 0)}, “BSD*” ) } )} )) ,
(UNION (
{UNION( { TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [ ‘ | pDim13 | ‘ ] )}, 0)}, “BSE*” ) } ,
{ TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [ ‘ | pDim13 | ‘ ] )}, 0)}, “BSF*” ) } )} ,
{UNION( { TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [ ‘ | pDim13 | ‘ ] )}, 0)}, “BSH*” ) } ,
{ TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [ ‘ | pDim13 | ‘ ] )}, 0)}, “BSJ*” ) } )} ))
)) ‘;

SubsetCreatebyMDX( sSubset ,StringMDX);

 

This can be solved in other ways, this is only a suggestion.

 

More Information:

http://www.wimgielis.com/tm1_mdxstatements_EN.htm

https://www.bihints.com/book/export/html/68

https://www.ibm.com/support/knowledgecenter/en/SS9RXT_10.2.2/com.ibm.swg.ba.cognos.tm1_ref.10.2.2.doc/c_tm1-specificmdxfunctions_n20311.html

Product:
Planning Analytics 2.0.6
Microsoft Windows 2016 server
Planning Analytics Administration Agent version 1.0.36.736

Problem:
When starting the PAA Agent in windows after change from local system to a windows service account, you get a error. The service account works on the other IBM Cognos TM1 services, only the IBM Planning Analytics Administration Agent that does not start.

Error msg in Windows event log can be:
The IBM Planning Analytics Administration Agent service terminated with the following service-specific error:
Incorrect function.

Solution:
Check that the Windows Service account is local administrator on the Planning Analytics server.
The PA Agent needs more local rights to read the files in folder D:\Program Files\ibm\cognos\tm1_64\paa_agent, than the other IBM Cognos TM1 service does.

You can find the message.log file here D:\Program Files\ibm\cognos\tm1_64\paa_agent\wlp\usr\servers\kate-agent\logs, that can contain more error messages.

The python code that can give some of the message is in folder; d:\Program Files\ibm\cognos\tm1_64\paa_agent\wlp\usr\servers\kate-agent\kateagent\scripts\status.py

More information:

https://www.ibm.com/support/knowledgecenter/en/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_inst.2.0.0.doc/c_install_paa_local_on_windows.html

Instructions to configure and start PAA Agent:

1. Open Windows “Services” desktop application
2. Stop “IBM Planning Analytics Administration Agent” service, if running
3. Navigate to <PA install directory>\paa_agent\wlp\usr\servers\kate-agent
4. Open the bootstrap.properties file in a text editor
5. Set the full path of directory containing TM1 databases data directories to SERVERS_DIR. Multiple paths must be separated by semicolon.
E.g. SERVERS_DIR=C:\tm1\samples\tm1\;C:\prod\servers\
6. Save and close the bootstrap.properties file
7. Start “IBM Planning Analytics Administration Agent” Windows service
8. PAA Agent is now ready to use

Product:
Cognos Analytics 11.1.3
Planning Analytics 2.0.6
Microsoft Windows 2016 server

Problem:
When run a CA11 report against a Tm1 Cube, you get this error;
XQE-MD-0007 Unable to establish a metadata connection to data source /content/package
DIM-ERR-1007 Det gick inte att efterfråga kubinformation för datakällan. Kontrollera anslutningssträngen och se till att servern är tillgänglig.

Solution:
The user who runs the CA11 report must have access to data in the TM1 cube.
Go in to TM1 Architect, and right click and select Rights – clients/groups dialog.
Check the user belongs to some of the security groups.
For a quick test, mark your user as ADMIN.
Click OK.
And log out from CA11 and in again, try the same report again.

If you get the below error when you run a Cognos Analytics 11 report;
QE-DEF-0157 The model or package /content/folder does not exist or you are not allowed to use if because of security settings.

Then it can be that the FM package is deleted.

Click on search icon on the left in Cognos 11, and search for the package name. If you find it, check that the user group the user belongs to have “run” rights, on the package.
More Information:
https://www.ibm.com/support/pages/dim-err-1007-failed-query-cube-information-data-source-when-using-planning-analytics-data-server-connection
https://www.ibm.com/support/pages/dim-err-1007-failed-query-cube-information-data-source-tm1

https://www.ibm.com/support/pages/package-permissions-errors-qe-def-0157

Product:
Planning Analytics 2.0.8 workspace
Microsoft Windows 2016 datacenter

Problem:
When surf to paw (on a windows 2016) server and you enter name and password for the TM1 native login method. The screen flickers, and nothing happens. You see the blank login screen again.
If you surf from a local computer it works fine, it is only when you surf over a VPN tunnel from a partners laptop, you can not login.
If you surf to the PAW (http://servername.domain.com/) from a server, there is no problem – it works fine.

Solution:
Check your antivirus program – so it is not stopping the connection because it is unsecured.

For example; Bitdefender internet Security can have a policy: Feature: Online Threat Prevention
“Privacy threat blocked
An attempt to send your password unencrypted was about to occur on servername. We blocked the connection to stop your private data from being exposed and tampered with.”

Click on Add to exceptions, and add the PAW server to you internet security programs exceptions list.
Other solution is to add HTTPS to you paw server.
https://www.ibm.com/support/knowledgecenter/en/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_inst.2.0.0.doc/t_paw_enable_ssl.html

Ensure also that you are not using the same sub-net as the paw docker network. Normally docker paw network is a 172.x.x.x network.
You update docker network in file C:\ProgramData\docker\config\daemon.json with the following contents:
{
“fixed-cidr”: “192.168.80.0/24”
}

You need to restart docker.

More information:
https://www.ibm.com/support/pages/troubleshooting-planning-analytics-workspace-related-docker-issues
https://www.ibm.com/support/knowledgecenter/en/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_inst.2.0.0.doc/c_paw_trbl_cant_access_over_vpn_ip_address.html

https://docs.microsoft.com/en-us/virtualization/windowscontainers/container-networking/architecture

https://forums.docker.com/t/ip-address-for-container/27454/4

https://www.eiseverywhere.com/file_uploads/b16ad176a58e3a9a360d66f1a4009c4e_Plan_IBMPlanningAnalyticsLocal_SoufianeAzizi.pdf

http://www.pantarhei.at/wp-content/uploads/2018/06/PAW-Local-Distributed-Soufiane-Azizi.pdf

Product:
Planning Analytics 2.0.8
Planning Analytics Workspace 45
Microsoft Windows 2016 server

https://www.ibm.com/support/pages/ibm-planning-analytics-pa-workspace-and-pa-microsoft-excel-fix-list

Problem:
After a reboot of the server, the docker service does not start. If you login to server and click on start in the service manager it will start. Ensure that you wait 5 min before testing with “start”.

Suggested Solution:
Check the format of the c:\programdata\docker\config\daemon.json file.
If it contain illegal characters, the docker service will not start.

If you enter docker ps or docker version to see running containers you get this message:

If you check the C:\ProgramData\docker\panic.log you may see this:

Failed to fire hook: The interface is unknown lock: = it did not get the response it needed in time, but windows service will try again in a few minutes,  so wait an see if it starts.

If you check the windows event log, you may see this:

Open the file c:\programdata\docker\config\daemon.json in NOTEPAD++
ensure it looks correct, and do not have any errors.


Debug parameter will write more information in the Windows Event log for Docker. When you have issues with Docker, check the Windows Event log for any errors.

You can copy the text in the json file to this site, to validate https://jsonformatter.curiousconcept.com/

There can be other causes, for this problem, that the docker service does not start.

Ensure that one network cards is prioritize, and that your DNS is working from this server.

You need also at least 4 cpu and 32 GB ram on the PAW server.

More information:
https://blogs.msdn.microsoft.com/jikuma/2018/03/19/error-pipedocker_engine-access-is-denied-in-the-default-daemon-configuration-on-windows/
https://docs.microsoft.com/en-us/virtualization/windowscontainers/manage-docker/configure-docker-daemon#set-docker-security-group
https://www.reddit.com/r/docker/comments/82myaw/invalid_character_in_string_escape_code/
https://docs.docker.com/v17.09/engine/admin/#start-the-daemon-manually

https://success.docker.com/article/error-streaming-logs-invalid-character-after-object-key-value-pair

Product:
Planning Analytics 2.0.8
Planning Analytics Workspace 45
Microsoft Windows 2012 server

Problem:
When you surf to your PAW, and go to Administrator page, tab – Databases. It says the Agent is not available.

Solution:
Even do you are on the same server. You need to open the Windows Firewall inbound port 9012 to make the PAW have access to the PA Agent.

Go to Control Panel
Go to Administrative tools
Go to Windows Firewall
Click Inbound Rules
Click New Rule
Select Port
Enter 9012 (and the other TM1 ports) at Specific Local ports
Click next for Allow the connection
Click next to keep that the rule apply to all domains
Enter a name like “Cognos TM1”
Click Finish

Now it should work.

When you check a previous made firewall rule it can look like this;

More Information:

https://www.ibm.com/support/knowledgecenter/en/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_prism_gs.2.0.0.doc/c_paw_administer_servers.html
https://www.ibm.com/support/knowledgecenter/en/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_inst.2.0.0.doc/c_tm1_inst_defaultvaluesfortm1installation.html

Product:
Planning Analytics 2.0.6 Workspace
Microsoft Windows 2016 Server

Problem:
How to use OPENSSL to convert certificates from company CA pfx file to the pem format needed by PAW?

Solution:
Download OPENSSL from http://slproweb.com/products/Win32OpenSSL.html
or from https://github.com/git-for-windows/git/releases/tag/v2.23.0.windows.1 – get the file Git-2.23.0-64-bit.exe Run the installation with all default values.
When installing GIT you will get a local openssl tool, that you can access from the command line:
“c:\program files\git\mingw64\bin\openssl.exe”

If you get a certificatechainfile.pfx file that you should use, you can convert it with the following command in CMD:
openssl  pkcs12  -in  c:\temp\your.pfx  -out  c:\temp\good.pem  -nodes

Then you need to open good.pem in Notepad++ and remove the lines not needed, and save it as pa-workspace.pem. It should have this certs in the file;
—–BEGIN RSA PRIVATE KEY—–
(Your Private Key: your_domain_name.key)
—–END RSA PRIVATE KEY—–
—–BEGIN CERTIFICATE—–
(Your Server certificate: your_domain_name.crt)
—–END CERTIFICATE—–
—–BEGIN CERTIFICATE—–
(Your Intermediate certificate: IntermediateCA.crt)
—–END CERTIFICATE—–
—–BEGIN CERTIFICATE—–
(Your Root certificate: TrustedRoot.crt)
—–END CERTIFICATE—–

How setup TLS (SSL) for PAW:

(Do the steps in your TEST environment first, to ensure they work for you.)

Export the root and intermediate certificates first.
Start Internet Explorer and surf to company internal site.
Click on the PAD lock icon.
Click view certificates.
Click Certificate Path tab.

Mark root cert and click view certificate.
Click Details tab.
Click “copy to file” button.
Click Next.

Select Base-64 encoded X.509 (.CER) and click Next.
Browse to your c:\temp folder and enter a name.

Click next and finish.
Repeat above steps for the intermediate cert.

Copy this two cer files to the d:\ibm\paw\config\certs folder.
Rename the cer files to pem.
Start Powershell as administrator.
Go to folder d:\ibm\paw\scripts.
Run .\process_certs.ps1 to include the root cert in the cacerts file.

Stop the paw with command d:\ibm\paw\scripts\paw.ps1 stop.
Go to the d:\ibm\paw\config\ssl folder.
Rename pa-workspace.pem to pa-workspace.pem.org.

If you got a .pfx file from the company that include the privatekey, servercert and  intermediate and root certs. You convert it with this command:
openssl pkcs12 -in your.pfx -out good.pem -nodes

Open good.pem in notepad, and remove lines above the —-BEGIN CERTIFICATE—- but after the —- END line.
Save the file. Now only with the cryptic binary text.

Copy the good.pem file to folder d:\ibm\paw\config\ssl and rename it to pa-workspace.pem

Open d:\ibm\paw\config\paw.ps1 file in notepad++.
Change all HTTP to HTTPS.

Add last in the file, this two lines:
$env:EnableSSL=”true”

$env:ServerName=”yourPAWservername”

Save the file.
Go to the d:\ibm\paw\ folder.
Run ./Start.ps1 to start the PAW administration.
Click on Validate button. Ensure all URL are correct, does they point to correct CA11 or TM1 servers?
Click on the Update button.
Restart PAW, can also be done from powershell with commando  d:\ibm\paw\scripts\paw.ps1

You must add ibmtm1.arm cert to your CA11 servers:
https://www.ibm.com/support/pages/configure-datasource-ibm-planning-analytics-20x-ibm-cognos-analytics-1106

You must add SSL (TLS) cert to your TM1WEB servers:

https://www.ibm.com/support/knowledgecenter/en/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_inst.2.0.0.doc/t_pa_use_ibmkeymgmt.html

https://www.ibm.com/support/pages/how-obtain-planning-analytics-tm1-server-certificate

More Information:

https://knowledge.digicert.com/solution/SO26449.html
https://www.feistyduck.com/
https://www.ibm.com/support/pages/how-transform-pem-and-pfx-keystore-public-key-cryptography-standard-12-pkcs12-keystore
https://www.freecodecamp.org/news/openssl-command-cheatsheet-b441be1e8c4a/
https://www.ibm.com/support/knowledgecenter/en/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_inst.2.0.0.doc/t_paw_enable_ssl.html
https://www.sslshopper.com/article-most-common-openssl-commands.html

Common OPENSSL commands (from SSL Shopper):

These commands allow you to generate CSRs, Certificates, Private Keys and do other miscellaneous tasks.

  • Generate a new private key and Certificate Signing Request
    openssl req -out CSR.csr -new -newkey rsa:2048 -nodes -keyout privateKey.key
  • Generate a self-signed certificate (see How to Create and Install an Apache Self Signed Certificate for more info)
    openssl req -x509 -sha256 -nodes -days 365 -newkey rsa:2048 -keyout privateKey.key -out certificate.crt
  • Generate a certificate signing request (CSR) for an existing private key
    openssl req -out CSR.csr -key privateKey.key -new
  • Generate a certificate signing request based on an existing certificate
    openssl x509 -x509toreq -in certificate.crt -out CSR.csr -signkey privateKey.key
  • Remove a passphrase from a private key
    openssl rsa -in privateKey.pem -out newPrivateKey.pem

Checking Using OpenSSL

If you need to check the information within a Certificate, CSR or Private Key, use these commands. You can also check CSRs and check certificates using SSL Shopper online tools.

  • Check a Certificate Signing Request (CSR)
    openssl req -text -noout -verify -in CSR.csr
  • Check a private key
    openssl rsa -in privateKey.key -check
  • Check a certificate
    openssl x509 -in certificate.crt -text -noout
  • Check a PKCS#12 file (.pfx or .p12)
    openssl pkcs12 -info -in keyStore.p12

Debugging Using OpenSSL

If you are receiving an error that the private doesn’t match the certificate or that a certificate that you installed to a site is not trusted, try one of these commands. If you are trying to verify that an SSL certificate is installed correctly, be sure to check out the SSL Shopper SSL Checker.

  • Check an MD5 hash of the public key to ensure that it matches with what is in a CSR or private key
    openssl x509 -noout -modulus -in certificate.crt | openssl md5
    openssl rsa -noout -modulus -in privateKey.key | openssl md5
    openssl req -noout -modulus -in CSR.csr | openssl md5
  • Check an SSL connection. All the certificates (including Intermediates) should be displayed
    openssl s_client -connect www.paypal.com:443

Converting Using OpenSSL

These commands allow you to convert certificates and keys to different formats to make them compatible with specific types of servers or software. For example, you can convert a normal PEM file that would work with Apache to a PFX (PKCS#12) file and use it with Tomcat or IIS. Use SSL Shopper SSL Converter to convert certificates without messing with OpenSSL.

  • Convert a DER file (.crt .cer .der) to PEM
    openssl x509 -inform der -in certificate.cer -out certificate.pem
  • Convert a PEM file to DER
    openssl x509 -outform der -in certificate.pem -out certificate.der
  • Convert a PKCS#12 file (.pfx .p12) containing a private key and certificates to PEM
    openssl pkcs12 -in keyStore.pfx -out keyStore.pem -nodes

    You can add -nocerts to only output the private key or add -nokeys to only output the certificates.

  • Convert a PEM certificate file and a private key to PKCS#12 (.pfx .p12)
    openssl pkcs12 -export -out certificate.pfx -inkey privateKey.key -in certificate.crt -certfile CAC
    
    https://www.sslshopper.com/ssl-faq.html

Product:
Planning Analytics 2.0.6
Microsoft Windows 2016 Terminal Server

Problem:
On new install of TM1 Architect on a Terminal Server, the list of TM1 instances is not showing up. They show up if you run TM1 architect direct on the TM1 server.

Solution:
Check Windows Firewall one both the Terminal Server and on your TM1 (PAL) server.
Go to Run from the start menu, enter wf.msc and press OK
Add a inbound rule that opens port 5495, 5498.
Save it as Cognos TM1 rule and check if it works again.

If you get a list of TM1 instances, but can not connect to them, then you miss the higher ports you have defined in TM1S.CFG
go back into Windows firewall, and add a port range like 12345-12360 to your cognos firewall rule.
Save and try again.

You need ports 5495, 5498 for TM1 Architect. 5895, 5898 is the Admin Server REST interface port (like HTTPPort for Tm1 servers). 5898 is when using TLS1.2
Then you need the ports you specify in TM1 applications – best to set a range like 12345-12360 then update in every TM1S.CFG file to unique values;
PortNumber=12345
HTTPportnumber=12346
ClientMessagePortNumber=12347

More Information:
https://www.ibm.com/support/knowledgecenter/en/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_inst.2.0.0.doc/c_tm1_inst_defaultvaluesfortm1installation.html
https://www.ibm.com/support/knowledgecenter/en/SSEP7J_11.1.0/com.ibm.swg.ba.cognos.inst_cr_winux.doc/c_reviewthedefaultsettings.html
https://www.ibm.com/support/knowledgecenter/en/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_inst.2.0.0.doc/c_paw_install_free_ports.html
http://cogknowhow.tm1.dk/archives/2055

Port 9300 is for Cognos Analytics (BI dispatcher).
Port 9510 is for TM1WEB and PMPSVC.
Port 5495,5498,5895,5898 is for TM1 Admin service.
Port 9012 is for the TM1 Agent.
Port 80 and 443 is for PAW or CA11 web-gateway ibmcognos.
There are other ports, but they are for communications between servers.

Product:
Planning Analytics Workspace 36
Microsoft Windows 2016 Server

Problem:
When open PAW and create a new book. There are no TM1 servers/applications listed under databases.

If you only miss access to the TM1 instances, then you can see a error like unable to fetch data for content tree. Session can not be established for server: Planning Sample 500 (internal server error) “code”,”Explorer”,”refID”,”message”,”Failed to get children for Server Cubes”,”Cause”…. ConnectTimeoutException….

When you validate the IBM Planning Analytics Workspace Administration Tool, and get time out on the TM1 Admin Server URI – it can be because the Windows Firewall is blocking port 5898.

Solution:
The Microsoft Windows firewall is blocking the traffic to the TM1 Admin service, and the rest api port to the TM1 instances.
Go to Run from the start menu, enter wf.msc and press OK.
This will start the Windows Firewall.
Expand Inbound Rules
Click New Rule
Select Port and click next
Enter the needed ports, can be depending on TM1 instance, below for the standard samples;
9300, 9510, 5898, 9012, 8888, 12354, 8014, 5010, 44312, 8011, 5011, 8010
Click Next
Mark Allow the connection click Next
Click Next to apply rule everywhere
Enter name to Cognos and click Finish

Try again.

If you still get 401 errors, you may need to restart the Windows server to make the Firewall changes kick in.

Port 9300 is for Cognos Analytics (BI dispatcher)
Port 9510 is for TM1WEB
Port 5898 is for TM1 Admin service
Port 9012 is for the TM1 Agent

More information:
https://www.ibm.com/support/knowledgecenter/en/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_inst.2.0.0.doc/c_paw_install_free_ports.html

Product:
Planning Analytics 2.0.6
Planning Analytics Workspace version 36
Microsoft Windows 2016 server

Problem:
How add many users to the PAW?

Solution:
Ensure that SSO and CAM security is working in TM1 (planning analytics).
Check the domain name in Cognos Configuration, this need to be enter in the file.
If the cognos namespace ID is Pacman, then it must be part of the file. It is case sensitive.
Open notepad (or excel) and enter the names like this:
Pacman/Donald Duck,Donald,Duck,administrator,donald.duck@pacman.com,active
Pacman/Daisy Duck,Daisy,Duck,analyst,daisy.duck@pacman.com,active

Save the file as a comma separated list.
Surf to your PAW installation.
Click on Administration
Click on Users
Click on upload users

Click on the icon of the file to browse for the file.
Select and Open the file
Now the users should be added to your PAW, so they can start working.
Please use this to add new administrators, direct after your first setup of PAW.

One user per line, and you must separate columns with comma. Should contain Login ID, First Name, Last Name, Role, Email, Status. Only the 3 first are required.

More information:
https://www.ibm.com/support/knowledgecenter/en/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_prism_gs.2.0.0.doc/t_paw_add_users_locally.html