Product:

Planning Analytics 2.0.9.11

Microsoft Windows 2019 server

Issue:

The TM1 Admin service is not starting. This program is not found running in task manager d:\program files\ibm\cognos\tm1_64\bin64\tm1admsd.exe.

There are no error in the log file at d:\program files\ibm\cognos\tm1_64\bin64\tm1admsrv.log.

You get this error when you start the service:

“The IBM Cognos TM1 Admin Server x64 service on Local Computer started and then stopped. Some services stop automatically if they are not in use by other services or programs.”

Solution:

Check if a other application is using the port needed by the TM1 admin service (tm1admsdx64).

TM1 admin service uses the ports:

5495 Tm1 architect contact with TM1 Admin service
5498 Tm1 architect contact with TM1 Admin service (ssl)
5895 TM1 Admin Server (REST API)
5898 TM1 Admin Server (REST API)

Checking which application is using a port:

  1. Open the command prompt – start >> run >> cmd or start >> All Programs >> Accessories >> Command Prompt.
  2. Type netstat -aon | findstr '[port_number]'. Replace the [port_number] with the actual port number that you want to check and hit enter.
  3. If the port is being used by any application, then that application’s detail will be shown. The number, which is shown at the last column of the list, is the PID  (process ID) of that application. Make note of this.
  4. Type tasklist | findstr '[PID]'. Replace the [PID] with the number from the above step and hit enter.
  5. You’ll be shown the application name that is using your port number.

 

You can use DOS command NETSTAT -ANO to get a list of used ports as shown above.

Then you can check what service is using that process id (pid) with above command.

If you can, stop the other application/process and then try to start TM1 admin service again.

But in most cases, you need to reboot the full windows server, to clear any runaway processes.

More information:

https://learn.microsoft.com/en-us/troubleshoot/windows-server/performance/determine-which-program-use-block-tcp-ports

https://veerasundar.com/blog/how-to-check-which-application-is-using-which-port/

https://www.ibm.com/support/pages/how-restart-tm1-admin-server-service-after-crash-or-hang-without-impacting-running-tm1-servers

If the tm1admsrv.exe is blocking the start of TM1 admin service, it is because the TM1 has itself started the TM1ADMSRV as a background process – to keep the Tm1 system up, after a crash of the TM1 admin service.

Product:

Planning Analytics 2.0.9.3
Microsoft Windows 2019 server

Issue:

Login to TM1 client give error, like cafe or pax or pafe.

The PMHub service parameter was not specified or is not one of the configured locations.

Solution:

Update the pmhub.html file with the server name of the PAW server to the line var pmhubURLs =.

Open pmhub.html from folder D:\Program Files\ibm\cognos\analytics\webcontent\bi

In later version of PAL (TM1) the pmhub.html file that are in use is in the webcontent\bi folder instead of the webcontent folder.

Add the url to your PAW server without portnumbers.

var pmhubURLs = [“http://tm1webserver.company.com:9510″,”http://tm1webserver.company.com”,”http://pawserver.company.com”,”http://pawserver”];

Save the file after you added the paw server name.

 

More Information:

https://www.ibm.com/support/pages/pmhub-service-parameter-was-not-specified-or-not-one-configured-locations

Product:

Planning Analytics 2.0.9.3

Microsoft Windows 2019 server

Issue:

You have SSO with Active Directory through use of Cognos Analytics on the same server.

After some time you are logged out of TM1 Architect, and get below error message:

Error -2 occurred pinging your Cognos Password

You will now be disconnected from name.

Solution:

Update your Internet Options settings by adding the server name to the trusted internet zone.

  • In Windows control panel – click on Internet Options.

    The Internet Options dialog opens.

  • Click the Security tab.
  • Click Trusted Sites and then click the Sites button.
  • Enter the URL of the Cognos Analytics server in the Add this Web site to the zone box.
  • Click Add.
  • Close the Trusted Sites dialog box.

 

More information:

https://code.cubewise.com/blog/determine-the-version-of-ibm-planning-analytics

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_inst.2.0.0.doc/t_configuringinternetexplorer7_n5050a.htm

Product:

TM1

Issue:

How create a rule?

Solution:

Check out this from https://exploringtm1.com/3-types-of-tm1-rules-all-developers-should-know/

 

 

3 types of rules which every consultant should know:

  • Allocation/Phase/Spread Rule – e.g. Allocate/phase/spread our budgeted sales across States based on the Actual Sales ratio.
  • Rolling Value Rule – e.g. Opening (Measure) is equal to the Closing of the prior period. Often used in the Balance Sheet or Depreciation rules.
  • Averaging Rule (C Level) – e.g. Averaging Percentages or Rates up all hierarchies within the cube.

As with TM1, and Platform Software in general, there are a million ways to do anything, so don’t worry if we don’t follow the methodology you are familiar with. That being said, these 3 TM1 rules are a great guide for any developer!

Allocation/Phase/Spread Rule – How to Spread a value Across Periods

This is a common requirement often seen in budgeting rules to allocate/phase/spread an annual budget across months based on Calendar Days, Working Days or Last Year’s Actual Values for the given account.

Here is a sample rule which will phase an annual budget across months based on the number of working days in each month.

[{'Jul','Aug','Sep','Oct','Nov','Dec','Jan','Feb','Mar','Apr','May','Jun'},'Budget','$'] = N:
   IF( ['Annual'] <> 0 
     ,IF( DB('General Ledger', !Year, 'Annual', !Scenario, !Department, !Account, 'Phasing') @= 'Even Phasing' 
       ,['Annual'] \
          DB('Assumption', !Year, 'All Months', 'Actual', 'Unspecified Department', 'Working Days') *
          DB('Assumption', !Year, !Month, 'Actual', 'Unspecified Department', 'Working Days')
       , CONTINUE )
     , CONTINUE ) ;

There is a number of different ways to write this. For example, I could exclude the {} Months from my rule filter (scope) and filter using an ELISANC within an IF Statement to check that the month element of the current cell being calculated is a descendant of the ‘All Months’ Element but that would clutter the rule tracer when/if used later on the ‘Annual’ Element.


Feeder for Allocation/Spread Rule

Don’t you need a complex feeder with a rule like this? No. We only have to calculate a month if there is a value in the ‘Annual’ month (which is a posting element for annualised data) within the same year. Which means our feeder can simply be:

[‘Annual’,’Budget’,’$’] => [‘All Months’];

If you were writing this longhand, it would look like this:

[‘Annual’,’Budget’,’$’] => [‘Jan’], [‘Feb’], [‘Mar’], [‘Apr’] … [‘Dec’];

Rolling Value TM1 Rule

Calculating a Balance Sheet, Net Book Value or Depreciation? This rule logic is bound to come up. This methodology is going to be slightly different depending on how you have your Time Dimension(s) set up within your cube.

Firstly there are even more possible solutions here, but we are aiming for a sustainable example. This means we will be avoiding DIMNM(DIMIX()-1) in favour of using attributes to help move around periods.

We’ll take a customer subscription calculation as our example. We’ll assume the cube for this rule has a separate Year and Month dimension.

Measures Dimension for Rolling Rule

Given a measures dimension which looks like this:

  • Closing Subscriptions
    • Opening Subscriptions
    • New Subscribers
    • Subscriber Churn (Displayed as a positive sign, aggregated with a -1 Weighting)

Attribute used on Month Dimension

We then have a clever little attribute table on the Month Dimension:

Attributes on the Month dimension for a rolling TM1 Rule
Sample Attribute Table to assist Rolling Value Rules.

Rolling Rule

A TM1 rule can then be written which looks like this:

[{'Jul','Aug','Sep','Oct','Nov','Dec','Jan','Feb','Mar','Apr','May','Jun'},'Forecast','Opening Subscriptions'] = N: DB('Subscription'
    ,STR(NUMBR(!Year) - ATTRN('Month',!Month,'Prior Year Component'),4,0)
    ,STR(NUMBR(ATTRS('Month',!Month,'Month Number')) - ATTRN('Month',!Month,'Prior Month Component'),2,0)
    ,!Scenario,!Department,!Product,'Closing Subscriptions');

This is what I would class as a bare-bones rule for Rolling a Value. This should go back as far as the Year dimension’s elements will go and has not potential to create a circular reference like DIMNM(DIMIX()-1) methodology.

If you want to post an opening amount into the first month and first year within your TM1 cube you can use an additional check to see if the generated Year exists using the DIMIX function, if it doesn’t a STET will make the cell editable.

[{'Jul','Aug','Sep','Oct','Nov','Dec','Jan','Feb','Mar','Apr','May','Jun'},'Forecast','Opening Subscriptions'] = N:
     IF( DIMIX('Year', STR(NUMBR(!Year) - ATTRN('Month',!Month,'Prior Year Component'),4,0)) = 0 
         ,STET
         ,DB('Subscription'
            ,STR(NUMBR(!Year) - ATTRN('Month',!Month,'Prior Year Component'),4,0)
            ,STR(NUMBR(ATTRS('Month',!Month,'Month Number')) - ATTRN('Month',!Month,'Prior Month Component'),2,0)
                 ,!Scenario,!Department,!Product,'Closing Subscriptions')
     );

Feeder for Rolling Rule

Then, the feeders for this involve the same amount of coding, but the theory may be daunting for people still learning. This is because where the rule went back across time periods to get the value, the feeder has to go forwards across time periods to push the value into the rule calculation cell.

[{'Jul','Aug','Sep','Oct','Nov','Dec','Jan','Feb','Mar','Apr','May','Jun'},'Forecast','Closing Subscriptions'] =>
      DB('Subscription'
          ,STR(NUMBR(!Year) + ATTRN('Month',STR((NUMBR(ATTRS('Month',!Month,'Month Number'))-13)-1,2,0),'Prior Year Component'),4,0)
          ,STR(NUMBR(ATTRS('Month',!Month,'Month Number')) + ATTRN('Month',STR((NUMBR(ATTRS('Month',!Month,'Month Number'))-13)-1,2,0),'Prior Month Component'),2,0)
              ,!Scenario,!Department,!Product,'Opening Subscriptions');

This could be written simpler if we didn’t piggyback the same “Prior Year” Attributes and instead added new “Next Year” Attributes. The above example feeder has a minimalistic approach to attributes but is paying for it in rule complexity.

I am also using a filter of each month because I have other N level elements in my month dimension which I don’t want this rule applied to.


Averaging Rule (C Level)

C  Level (Consolidation Level) TM1 rules which do averaging are very similar to normal rules but the reason we have them listed is that people don’t realize until they have to write one that a (non-zero value) countermeasure is needed and you need to use a separate measure to perform the calculation in most cases.

Legacy Method

For averaging a value based on a counter.

['Average Price'] = C:
  ['Price'] \ ['Product Count'];
  ['Product Count'] = N:
  IF( ['Price'] > 0 , 1 , 0 );

Feeder:

['Price'] =>
  ['Product Count'],
  ['Average Price'];

I’m using price and not units for my product counter as I want to average all prices regardless of if the product is sold in a specific period. However, if I wanted an average price pro-rata units sold I would back solve my revenue equation like so.

['Average Price'] = C: ['Subscription Revenue'] \ ['Closing Subscriptions'];

Feeder:

['Closing Subscriptions'] => ['Average Price'];

New Function Method

Averaging based on the data within a measure. Here we use the ConsolidatedAvg function.

['Average Price'] = ConsolidatedAvg (2, 'Subscription', !Year, !month, !Scenario, !Department, !Product, 'Price');

Feeder:

[‘Price’] => ['Average Price'];

The first argument of the ConsolidatedAvg function can be set as either:

  • 0 – consider all cells while averaging.
  • 1 – use weightings
  • 2 – ignore blank cells (zero values) while averaging
  • 3 – use weights and ignore blanks

 

To learn more about TM1 – go to https://tm1explorers.com/webinars/

Product:

TM1

Issue:

Set security with code?

Solution:

Check out this from https://exploringtm1.com/cell-security-in-tm1-the-complete-guide/

TM1’s security can be as simple or as complex as you need. We tend to start with the broadest possible definition of security and then refine it down to the specific, to the cell if required. TM1 cell security places an overhead for your administrator to manage as it can get complex, not only within a cube but also with the interaction of it with element or dimension security. This guide will take you through how to create Cell Security the right way, that minimises the overhead on your server and administrator.


Standard “Create Cell Security Cube” Method

When you right-click on a cube and select Security, you are prompted to Create a Cell Security Cube. If you do this, it will create the cube that replicates the primary cube, but with the addition of the }Groups dimension. Let’s say you have a GL cube with Time, Version, Entity, Cost Centre, Account, and Measure. Using the automated method will give you those plus }Groups.

This is a dead-simple way to create a Cell Security cube and from it you can assign cell-level security. However, if you have, say six dimensions in the underlying cube, then you’ll have seven dimensions in the resulting cube. Great flexibility, because you can assign security to any corresponding intersection to the primary cube. Huge overhead through because you have to maintain all those intersections and if you want to go down the path of having rules manage the cell security, then it could have a big hit on performance.


Customised Cell Security Cube

So what do we do? We want a cell security cube that only has the dimensions you need to assign security for the primary cube. If, from our GL cube above, we only need Time, Version, and Account for administering security, then we create a security cube with only those plus the }Groups dimension. Administering a 4 dimension cube is very much easier than a 7 dimension cube!


Create a Custom Cell Security Cube

The only way two create a customised cell security cube is via a special TI. This TI contains just two lines, namely:

CubeName = 'General Ledger';
CellSecurityCubeCreate ( CubeName, '1:1:0:0:1:0');

This has a set of simple binary switches that enable or disable a dimension from the primary cube. So obviously, our primary cube, the General Ledger cube, here has six dimensions, they are referred to here in the exact order they are in the primary cube and are separated by a colon (one of these “:”). Finally, the zero and one switches are contained inside a single inverted comma.

Running this TI will then create a cell security cube with only the required dimensions. So, with our dimensions above, we would end up with Time, Version, Account, and }Groups in the new cube. This corresponds with the 1’s in the command in the TI.


Rules

Once the Cell Security cube has been created, then we can assign rules to it. In the rule below we have six blocks. YOU can read the annotation in the rules. Note the last one is a catch-all with the scope of []. This sets it to be for all remaining intersections not caught by the rules above.

# Set Actuals to have Write access to future Weeks only
['Actual'] = S: IF ( ELLEV ( 'Time', !Time) = 0
	,IF ( ATTRN ('Time', !Time, 'FY Week No') <= DB('System Control','Current Week','Value'), STET,'WRITE')
	,CONTINUE
	);

# Set Actuals to be Read for historic Periods (months) up until the most recent completed Month End
['Actual'] = S: IF ( ATTRS ('Time', !Time, 'Monthend Completed') @= 'Yes', STET,'WRITE');

# Set Budget to be all Read only
['Budget'] = S: 'READ';

# Set WEEKS for Active Forecast Versions to Write from Forecast Start Week onwards
[] = S: IF ( ELISANC ( 'Version', 'Active Forecast Versions', !Version ) = 1 & ELLEV ( 'Time', !Time) = 0 
	,IF ( ATTRN ('Time', !Time, 'FY Week No') < DB('System Control','Forecast Start Year-Week','Value'), STET,'WRITE')
	,CONTINUE 
	);

# Set PERIODS for Active Forecast Versions to Write from Forecast Start Week onwards
[] = S:  IF ( ELISANC ( 'Version', 'Active Forecast Versions', !Version ) = 1
	,IF ( ATTRN ('Time', !Time, 'FY Period No') < ATTRN ( 'Time' ,ATTRS ( 'Time', DB('System Control','Forecast Start Year-Week','String'), 'Current Period'), 'FY Period No'), STET,'WRITE')
	,CONTINUE 
	);

# Set ALL else to READ
[] = S: 'READ';

Deleting a Control Cube

Like other Control Objects, these cubes are special and cannot be elated by the normal right-click method. They must be deleted via a TI process as well. The TI just needs to contain the following

CubeName = '}CellSecurity_General Ledger';
CubeDestroy ( CubeName );

Obviously, if you run this, it will delete any data in the cube and any rules you have written against it. So copy the rules out first if you want to re-use them!

Product:

Planning Analytics 2.0.9

Microsoft Windows 2019 server

Issue:

After adding support for HTTPS(SSL) to TM1WEB, the url does not work. You get loged in to CA11, but then you get a error like this:

The TM1Web service parameter was not specified or is not one of the configured locations

If you check the URL (copy it to notepad) you see that the TM1WEB server still are using the old port you had before HTTPS was implemented.

The TM1WEB.HTML file contain the correct value, with the new port for SSL communication, on the Cognos Analytics server.

Check folder c:\Program Files\ibm\cognos\analytics\webcontent\bi\tm1\web.

c:\Program Files\ibm\cognos\tm1web\wlp\usr\servers\tm1web\SERVER.XML have also a correct row for the new port;

<httpEndpoint id="defaultHttpEndpoint" httpPort="-1" httpsPort="9510" host="*" removeServerHeader="true" >

Solution:

tm1web_config.xml in folder c:\Program Files\ibm\cognos\tm1web\webapps\tm1web\WEB-INF\configuration, has the old server value hard-coded to this line ExternalUrl, it should be empty “”.

 <add key="X-Frame-Options" value="2" />
<!-- When performing CAM authentication, optional redirection url override example http://127.0.0.1:9510/tm1web -->
<add key="ExternalUrl" value="" /> 
<!-- LogoutUrl to be executed after logout normally completes-->
<add key="CustomCAMLogoutUrl" value="" />

Update the file and save it.

 

More information:

TM1WEB picks up your URL, and sends it to CA11 for login, then CA11 will check if the URL you come from matches the URL listed in the file TM1WEB.HTML, if it is not listed, then you get this error.

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=tls-use-key-management-configure-custom-certificates

https://www.ibm.com/docs/en/SSYKE2_7.0.0/com.ibm.java.security.component.70.doc/security-component/iKeyman.8.User.Guide.pdf

Product:

Planning Analytics

Microsoft Windows 2019

Issue:

When user browse to tm1web site, they get a warning in web browser that “your connection is not private”.

If you check the certificate – by click on the padlock in the browser – windows says the certificate is OK.

Subject Alternative Name Missing The certificate for this site does not contain a Subject Alternative Name extension containing a domain name or IP address.

Certificate Error There are issues with the site’s certificate chain (net::ERR_CERT_COMMON_NAME_INVALID).

Solution:

Order a new certificate for your TM1WEB server that contain a Subject Alternative Name, that is the server-name or DNS alias.

The certificate you have today, if you open it in MMC, under Details tab, there is no line for Subject Alternative Name, only a line for Subject.

 

More Information:

https://www.digicert.com/faq/subject-alternative-name.htm

Security certificate does not specify subject alternative names

Product:

Planning Analytics Workspace

Microsoft Windows 2019 server

Issue:

After patch or restart of the Windows server, sometimes the PAW does not come up.

Suggested solution:

In Daemon.json file shutdown timeout settings update and changed to 600 sec from 180 sec

Found in folder C:\ProgramData\docker\config

Registry key settings update WaitToKillServiceTimeout increase to 20 sec from 5 sec

  • WaitToKillServiceTimeout: Windows normally waits 5 seconds for background services to clean up and close when you tell your computer to shut down. Some applications may change this value when you install them, giving their background services extra time to clean up. Windows forcibly shuts down background services after this period of time. This value controls how many seconds Windows waits before doing so. Windows will automatically shut down if all services close successfully before the timer expires.

It should be created in regedit under this branch:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control

Change the Service type of ‘Docker Engine’ to manual from automatic.

Create a task scheduler so that this ‘Docker Engine’ service should start only after 15 minutes of the server restart.

https://www.maketecheasier.com/start-scheduled-tasks-with-delay-windows/

https://www.technipages.com/scheduled-task-windows

More information:

https://www.ibm.com/support/pages/troubleshooting-planning-analytics-workspace-related-docker-issues

https://exploringtm1.com/how-to-install-planning-analytics-workspace-to-windows-server-2019/

https://www.ibm.com/support/pages/how-configure-planning-analytics-timeout-settings

https://www.howtogeek.com/282062/control-how-long-windows-waits-before-killing-apps-at-shutdown/

Product:

Planning Analytics 11.0.911.20-0

Issue:

After upgrade of Cognos Analytics fix pack, then the login to Tm1Web does not work.

Error: No webpage was found for the web address:  http://servername/ibmcognos/bi/tm1/web/tm1web.html…

HTTP ERROR 404

Solution:

On the CA11 servers, ensure that the folder C:\Program Files\ibm\cognos\analytics\webcontent\bi\tm1\web exist, and contain the tm1web.html file.

The fix pack installation of CA11 can remove files from cognos folders, like variables_tm1.xml

The tm1web.html file should contain a list of the formats that you are going to access the tm1web with.

var tm1webServices = [“http://SystemName:PortNumber“,”https://SystemName:PortNumber“];

 

More information:

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=security-configuring-tm1-web-use-cognos

 

Product:

Planning Analytics

Issue:

When was a version released?

Solution:

List from IBM.

https://www.ibm.com/support/pages/node/6519826

 

 

More Information:

https://www.ibm.com/support/pages/ibm-planning-analytics-microsoft-excel-conformance-requirements

https://www.ibm.com/support/pages/ibm-planning-analytics-tm1-web-conformance-requirements

https://www.tm1forum.com/viewtopic.php?t=16128

End Of Support Summary

  • PA2.0.0 to PA 2.0.8 inclusive: 30 Sep 2022
  • 10.2.2: 30 Sep 2019
  • 10.2.0: 30 Sep 2018
  • 10.1.x: 30 Apr 2017
  • 9.5.x: 30-Sep-2015
  • 9.4.x and Executive Viewer: 30-Sep 2013

https://blog.octanesolutions.com.au/pa-paw-pax-version-conformance

IBM Planning Analytics TM1