IBM Planning Analytics
  • 07 May 2022
  • 10 Minutes to read
  • Dark
    Light

IBM Planning Analytics

  • Dark
    Light

Overview

ReportWORQ supports IBM Planning Analytics Perspectives and Planning Analytics for Excel (PAfE) based reports. These reports use proprietary custom Excel formulas to extract data, attributes, and metadata from Planning Analytics. The information provided to the formulas instructs ReportWORQ on how to extract data from the system, for example a DBRW formula lists a server, cube, and dimension intersection to extract a single value from the source cube.  ReportWORQ helps you automate these reports by using ReportWORQ Parameters to change cell values which in turn change the input to these formulas.  For example changing a cell from US to Canada further instructs a DBRW formula to extract different data from the system.  After sheets are created and cell values are updated using ReportWORQ parameters, ReportWORQ then calculates the sheet to extract the data from the system.  ReportWORQ Parameters provide support for sourcing information from IBM Planning Analytics subsets or MDX Queries.  ReportWORQ reads and writes data to Planning Analytics via the Planning Analytics REST API. The list of IBM Cognos Planning Analytics versions that are supported by ReportWORQ match the versions fully supported by IBM (refer to the support lifecycle information provided by IBM): https://www.ibm.com/support/pages/lifecycle/

A Planning Analytics On Premise or IBM Cloud system connection must be defined in order to use this datasource, then a Perspectives or PAfE report which uses that system can be utilized by ReportWORQ.

Planning Analytics Connections

There are multiple options for connecting to Planning Analytics that are supported by ReportWORQ. All of these connection types are also available to ReportWORQ and the Planning Analytics Excel utilities that are available in the ReportWORQ Excel add-in. Select the Settings button from the ReportWORQ Excel ribbon to see the Planning Analytics connections that are available to those Excel tools, :

Th option displays both the license information for the Excel tools and all configured Planning Analytics connections:

Planning Analytics - On Premise

Planning Analytics On-Premise connections are defined based on the authentication options supported by your Planning Analytics server instances (Databases):

TM1 Security Mode 1 (Native Only)TM1 Security Mode 3 (Integrated Only)TM1 Security Mode 5 (CAM)

Note how the Local connection interface automatically adjusts to reflect the authentication options that are supported by the specified Planning Analytics server Database instance. ReportWORQ uses the specified Address, Port number, and the SSL setting to query the server and determine the supported connection options.

API Access

ReportWORQ communicates with Planning Analytics through the REST API.  To enable the Planning Analytics REST API, please specify an HTTPPortNumber configuration in the tm1s.cfg file and ensure that this port is opened in any firewalls between ReportWORQ users and your Planning Analytics server: https://www.ibm.com/support/knowledgecenter/en/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_inst.2.0.0.doc/t_ug_cxr_odata_config.html

Security Modes

ReportWORQ supports all Planning Analytics Server IntegratedSecurityMode settings except IntegratedSecurityMode=4 (IBM Cognos Security) when storing ReportWORQ metadata in Planning Analytics control cubes (see Database Configuration for more information on ReportWORQ database storage options), since that mode does not allow the creation of Planning Analytics user groups needed by ReportWORQ.  Customers planning to use IBM Cognos Security with Planning Analytics and ReportWORQ should use IntegratedSecurityMode=5 (IBM CIognos Security with Planning Analytics security groups).  Cognos Express users should not need to alter this Planning Analytics setting since Cognos Express already defaults to using IntegratedSecurityMode=5.

PLEASE NOTE:
Additional configuration is required in order for ReportWORQ to use Planning Analytics Integrated Login (IntegratedSecurityMode=2 or IntegratedSecurityMode=3). Please refer to Prerequisites for Integrated Login Support for more information on the requirements and configuration steps.

Creating a Connection

  • Select the Settings > Connections tab
  • Select the New Connection button to create a new connection
  • Select Planning Analytics On-Premise and then enter the following values:
    • Admin Host : The server name that will be used to locate available databases.
    • Database: The name of the Planning Analytics system which is listed in the tm1s.cfg file.
    • Address: The machine name or IP address of the server hosting Planning Analytics.
    • Port: The REST API port is listed as the HTTPPortNumber setting in the tm1s.cfg file.
    • Credentials: Optionally provide credentials to store for this connection.  Namespace is only required for Security Mode 5.

 Entering the Admin Host name, then selecting Load will populate the Database dropdown list with all available databases .  ReportWORQ does not require or use an Admin server outside of this connection interface. 

For security mode 2 (Native or Integrated Login) if the username and password are left blank then ReportWORQ will attempt to connect as the active user. For security mode 3 (Integrated Login) you may leave the credentials blank to connect as the active user, or you may specify network credentials to be used, for instance DOMAIN\user in the Username field. In order to create Integrated Login connections you will need to make sure you satisfy the Prerequisites for Integrated Login Support.

Prerequisites for Integrated Login Support

Additional configuration related to Active Directory and Kerberos is required to ensure that ReportWORQ is able to successfully connect to Planning Anayltics using Integrated Login. Before performing the configuration steps described in this section, please confirm that the following are available:

  1. Planning Analytics Integrated Login is already working properly when using Architect or Perspectives
  2. The Planning Analytics Server service is running under an Active Directory domain/service account
  3. Knowledge of the the Url's and IP address(es) of the Planning Analytics Server
  4. Verify that you have access to someone (typically an Active Directory administrator) who can:
    • Create or modify Service Principal Names (SPNs) in Active Directory
    • Update/push policy changes to client desktops, if needed

Once you've confirmed the above, the primary steps required to configure ReportWORQ to use integrated login are to add one or more Service Principal Names (SPNs) using the following structure:

setspn -s HTTP/<Address> <Planning Analytics Service Account Name>

Key:

  • <Address>: The address of the server machine running the Planning Analytics Server, which may be:
    • Fully-qualified name, for example: app01.company.com
    • Machine name, for example: app01
    • IP address, for example: 10.4.121.15
  • <Planning Analytics Service Account Name>: The domain account that the Planning Analytics Server application is running under (the "Log on as" account)
IMPORTANT
The Address(es) specified when defining SPNs must cover any address used when Creating a Connection for ReportWORQ.

Examples:

If your Active Directory domain is called "Corporate.Internal" and your Planning Analytics Server runs on a machine called "PAAppPrd01" under a service account called "TM1Services" running at IP Address "10.10.12.34" you might add the following SPNs:

setspn -s HTTP/PAAppPrd01.Corporate.Internal TM1Services
setspn -s HTTP/PAAppPrd01 TM1Services
setspn -s HTTP/10.10.12.34 TM1Services

Note that SPN updates may not be visible to all client desktops immediately; your Active Directory administrator can help determine whether or not those changes have propagated across all client machines.

Adding SPNs based on an IP Address
You can create SPNs based on the IP address of the Planning Analytics Server. However, in order for this to work from ReportWORQ Client desktops or ReportWORQ Server, each client machine must have this registry setting enabled as described here:

https://docs.microsoft.com/en-us/windows-server/security/kerberos/configuring-kerberos-over-ip

Note that a restart of client machines may be required in order for this setting to take effect.

Planning Analytics - Cloud

Prerequisites

The following is needed to connect to Planning Analytics Cloud:

  • The Welcome Kit provided by IBM.
  • A non-interactive user account.  
    • If the Non-Interactive Account section of the welcome kit is empty then open a support case with IBM and request a non-interactive account.
    • All support requests should go through https://support.ibm.com . Choose “Open a Case” while signed in with your IBM ID.
  • REST API nocanon Configuration Setting
    • NOTE: This is only required when using a Planning Analytics Control Cube as the ReportWORQ Database, or when your reports reference element names containing special characters such as a backslash ("\")
    • When logging the request for non-interactive user(s), you will need to include an additional request or add a separate ticket that says the following:
IBM,

We need "nocanon" added to the ProxyPass in our IBM Cloud environments:

[Customer cloud environment 1]
[Customer cloud environment 2]
[Customer cloud environment 3]
This is needed to address an issue with IBM PA Cloud’s handling of REST API requests, which is documented in case number TS002469510.

Creating a Connection


  • Select the Settings > Connections tab
  • Select the New Connection button to create a new connection
  • Select Planning Analytics Cloud and then enter the following values:
    • Database: The name of the Planning Analytics system which is listed in the User names and passwords section of the Welcome Kit.
    • IBM Cloud URL: The hostname listed in the Setting up an agent in your Cognos Command Center Server section of the Welcome Kit. It is important that you remove the /ccagent/ portion of this url
    • Non-interactive account credentials

Connectivity Errors

If you are having trouble connecting to Planning Analytics, make sure that the URL to the Planning Analytics REST API is correct and accessible. Test connectivity by navigating a browser to the following test URL.

http://{server}:{HTTPPortNumber}/v1/$metadata

Example: http://localhost:9856/api/v1/$metadata

If you see an XML response then you can connect to the server, however if you see an error then check the user, port, SSL setting and firewall. If SSL is enabled you must use https instead of http.

Security Errors

Failed to create ReportWORQ objects because an Administrator is required on first use. Please login as an Administrator to proceed

On first run ReportWORQ must be run as an Administrator so that it can create the necessary ReportWORQ control cubes and update the }DimensionSecurity cube.

ReportWORQ requires Dimension-level security on this server and it appears that the }DimensionSecurity cube is missing.

Make sure the }DimensionSecurity cube exists or create one if necessary. To create the }DimensionSecurity cube open Architect, Right click on Dimensions and choose Security Assignments. In the security assignments editor make any change and choose OK.  You can then return to the editor and undo that change.  Making a change will create the }DimensionSecurity cube.

ReportWORQ failed to update Dimension Security. Please check to see if a rule is preventing updates to this cube.

If there are any Rules on the }DimensionSecurity cube make sure that they permit changes to the }ReportWORQ_READER and }ReportWORQ_WRITER members:

[‘}ReportWORQ__READER’] = S: STET;

[‘}ReportWORQ__WRITER’] = S: STET;

Allowing non-Admin users access to ReportWORQ. See the ReportWORQ Security non-Admin section below for configuration information.

Failed to create ReportWORQ objects please contact support@reportworq.com for assistance.

ReportWORQ encountered an error trying to update or create ReportWORQ control cubes or security groups and the user was logged in as an Administrator. 

Please send log files to support@reportworq.com for further assistance.

Non-Admin Access

ReportWORQ users require WRITE access to the ReportWORQ Control objects stored in Planning Analytics.  The simple way to accomplish this is for the ReportWORQ user to be a TM1 Admin, however this is not always desired or possible.  If you require non-Admin users to use ReportWORQ then a TM1 Admin will need to manage security access for these users just as they would any other TM1 cubes or dimensions. 

For customers upgrading from ReportWORQ 4.2 or earlier you may already have the }ReportWORQ__WRITER security group, however this group is no longer maintained by ReportWORQ.  You may continue to use this security group and allow ReportWORQ access for non-Admin users by setting WRITE access for all ReportWORQ Control Objects in both the }DimensionSecurity and }CubeSecurity cubes.

For new customers who are starting with ReportWORQ 4.3 you will need create or use an existing group and then set ADMIN access for all ReportWORQ Control Objects in the }DimensionSecurity and WRITE access in the }CubeSecurity cubes.

Note that all ReportWORQ Control Objects begin with }ReportWORQ.

Change Management

ReportWORQ development often takes place in one or more customer Planning Analytics Server environments.  The Planning Analytics Server that is used with ReportWORQ corresponds typically to the Planning Analytics Server containing the data needed in the reports that need to be distributed.  But multiple Planning Analytics Servers can be involved when there are Development, QA, Production, and other environments with Planning Analytics applications in use.

ReportWORQ can fit into your existing change management requirements for Planning Analytics development.  As with migration of Planning Analytics changes, ReportWORQ Reports and Jobs can be created in Development, which is often less-restricted to user updates, and migrated to Production, which is typically more locked down to changes.  This also ensures that changes to ReportWORQ Jobs, for example, are carefully managed to avoid a change to any settings resulting in scheduled jobs suddenly not working due to changes made in production.

Uninstalling the ReportWORQ Control Objects

ReportWORQ creates multiple control objects for storing data, as documented here in ReportWORQ Control Objects. To remove these objects, for instance if you have fully-transitioned to using "Local Storage Mode", first shut down the Planning Analytics server instance which contains them, then delete from the data directory any files that begin with the prefix "}ReportWORQ".  If you intend to reinstall ReportWORQ and continue to use Control Object storage mode then do not delete these control objects.

Supported Planning Analytics Formulas

DBRDIMXELCOMPNELWEIGHTTM1RPTFILTER
DBRWDIMNMELISCOMPSUBSIZTM1RPTTITLE
DBSDIMSIZELISPARTABDIMTM1RPTVIEW
DBSWDNEXTELLEVTM1PRIMARYDBNAMETM1USER
SUBNMDNLEV

ELPAR

TM1RPTROW
DBRADTYPEELPARNTM1RPTELISCONSOLIDATED
DFRSTELCOMPELSLENTM1RPTELLEV

Unsupported Planning Analytics Formulas

  • TM1RPTISEXPANDED
  • VIEW
  • TM1ELLIST
  • TM1INFO
  • ELEMENTFIRST
  • TM1GLOBALSANDBOX
  • DBSA
  • DBSS

Was this article helpful?