IBM Planning Analytics

Overview

ReportWORQ supports the refresh of Excel-based reports created using the IBM Planning Analytics Perspectives and Planning Analytics for Excel (PAfE) Excel add-ins. These reports use proprietary custom Excel formulas and other Excel settings to help define how to extract data, attributes, and metadata from IBM Planning Analytics (TM1) Server instances. 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 matches the versions actively supported by IBM (refer to the support lifecycle information provided by IBM):

ReportWORQ Parameters can be used to change cell values in Excel reports which can, in turn, change the references to any Planning Analytics formulas and other reporting options to dynamically update Excel reports from Planning Analytics. ReportWORQ additionally supports a couple of Planning Analytics-specific Parameter types: IBM Planning Analytics subsets and MDX Queries.

This topic covers the configuration of Planning Analytics connections as well as the various IBM Planning Analytics features and integration options supported by ReportWORQ:

• Configuring Connections

  • Planning Analytics - On-Premise

  • Planning Analytics - IBM Cloud

  • Planning Analytics - SaaS on AWS

  • Troubleshooting Connections

• Required Security Permissions

• Supported Reporting Features

• Planning Analytics-Specific ReportWORQ Parameters

• ReportWORQ Automation Options with Planning Analytics

  • Cube Monitoring

  • Calling the ReportWORQ REST API from Turbo Integrator Processes

To use IBM Planning Analytics as a data source, at least one IBM Planning Analytics On-Premise or Cloud system connection must be configured in ReportWORQ, and you must also have an existing Perspectives or PAfE report which uses that connection.

Configuration

To add an IBM Planning Analytics connection to the list of available Datasources, ReportWORQ administrators open the Administration screen then click the '+' button beside Datasources, and select the IBM Planning Analytics option:

Three options for connecting to IBM Planning Analytics are supported by ReportWORQ. Which one to use is determined by how your IBM Planning Analytics environment is hosted and managed:

• On-Premise Connections: These are connections to any Planning Analytics instance which you manage, either by installing Planning Analytics on servers in your corporate data center or a cloud environment, such as Amazon Web Services (AWS) or Microsoft Azure.

• IBM Cloud Connections: These are connections to Planning Analytics instances that are hosted and managed by IBM. 

• IBM AWS Cloud Connections: These are connections to Planning Analytics SaaS on AWS instances.

On-Premise Configuration

Planning Analytics On-Premise connections are defined based on the authentication options supported by your Planning Analytics server instances (Databases), specifically the IntegratedSecurityMode setting defined in your database configuration. All security modes are supported in ReportWORQ:

• Native (IntegratedSecurityMode 1 + 2): User Logins are fully managed within Planning Analytics. 

• Windows Integrated (IntegratedSecurity Mode 2 + 3): User logins to Planning Analytics are mapped to Active Directory (Windows Domain) logins.

• Cognos Access Manager / CAM (Integrated Security Mode 4 + 5): User logins to Planning Analytics are managed as part of CAM Security.

Prerequisites

The following information is required as a prerequisite when configuring connections to local IBM Planning Analytics databases:

1. REST API Access Must be Enabled: ReportWORQ communicates with Planning Analytics through the REST API. To enable the Planning Analytics REST API, please configure a valid HTTPPortNumber in the tm1s.cfg file:

2. Basic Database Connection Information:

   1. The address of the Planning Analytics Admin Host service where your Planning Analytics Server database is registered, or

   2. The following Planning Analytics Server database configured settings:

      1. Name (e.g., Planning Sample)

      2. Address

      3. REST API Port Number

      4. SSL Enabled / Disabled

3. Firewall Access Rules: REST API ports (HttpPortNumber setting) must be opened in any firewall between ReportWORQ and your Planning Analytics server. Port 5895 and 5898 must additionally be opened in any firewalls between ReportWORQ and the machine running the Planning Analytics Admin Host service if that service will be used to automatically retrieve settings for a Planning Analytics database connection.

4. Login Credentials & Mode: Verify the IntegratedSecurityMode setting for the database that you wish to connect to, and make sure that you have valid login credentials for that database.

Connection Configuration

The following configuration information will appear after selecting "Self Hosted"  Authentication or when clicking the link for an existing connection in ReportWORQ:

• Datasource Name: This is a friendly name that describes this connection, and will be visible to all users of the ReportWORQ application when accessing information related to Planning Analytics (such as parameters). The name must be unique across all defined datasources.

• Database: This is the Database name as defined in your Planning Analytics configuration. You can manually enter the name or select from the list if populated from a Planning Analytics Admin Host service.

Distribution Credentials:

• Username & Password: Distribution credentials are required, and are the same credentials used to authenticate IBM Planning Analytics Microsoft for Excel. If Data Collection credentials are not enabled, they are also used for Data Collection.

Data Collection Credentials:

• Username & Password: Data Collection credentials are optional. When enabled, they limit contributor access to specified areas of the system.

The icon to the right of the Database option can be used to automatically populate the Database drop-down list based on a lookup to a Planning Analytics Admin Host Service:

When you click it, you will be prompted for the Admin Host Service address, for example:

Clicking OK will attempt to load a list of servers, each of which will automatically be populated with the Address, Use SSL, and Port settings that are required below. This helps avoid errors compared encountered manually entering the same information.

• Address: This is the address of the machine where your Planning Analytics Server database(s) are running. This address must be accessible by the ReportWORQ Server and can be: 

  • An IP address (e.g., 10.0.0.123)

  • A machine name (e.g., prod-paserver-01)

  • A fully qualified domain name (e.g., prod-paserver-01.company.com)

• Port: This is the REST API port number for the Planning Analytics database. 

• Use SSL: This defines whether or not ReportWORQ will communicate with your Planning Analytics database using SSL, and is determined by your Planning Analytics Database configuration.

• Username: The username used to log in (leave blank for Integrated Login).

• Password: The password used to log in (leave blank for Integrated Login).

• Namespace: This is the Cognos namespace, which is required when your Planning Analytics Database is configured to use CAM Security.

• Test: Clicking the '✓' button will either display a success message or show error messages with further information on missing or misconfigured settings.

• Save: The 'Save' button saves changes made to the connection.

• The IBM Planning Analytics Datasource can be enabled or disabled by clicking the checkbox.
  
  Advanced Options can be configured by selecting the following toolbar button:
  

  
  
  
  

• PAfE Calculation Mode: This setting tells ReportWORQ how to calculate and resolve Planning Analytics Excel formula results:

  • Enabled: ReportWORQ will calculate formulas like the Planning Analytics for Excel (PAfE) add-in, for instance, by returning #VALUE! errors when invalid or missing element names are referenced.

  • Disabled: ReportWORQ will calculate formulas like the TM1 Perspectives add-in, for instance, by returning blank values when invalid or missing element names are referenced. 

• Enable Proportional Writeback: This enables ReportWORQ to send data to consolidated cells using the Proportional Spread option, and is only available for customers who have licensed the ReportWORQ Data Collection feature.

• Enable Cube Monitor: This is a method for automating ReportWORQ Job Execution from Planning Analytics. Enabling this option will create additional objects on this Planning Analytics Database Instance.

Requirements for Integrated Login Support

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

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. You know the URLs and IP address(es) of the Planning Analytics Server.

4. You have verified 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)

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.

IBM Cloud Configuration

IBM Planning Analytics Cloud connections are managed separately in ReportWORQ because of differences in the way they are configured and managed by IBM.

Prerequisites

The following is needed to connect to IBM Planning Analytics Cloud:

1. The Welcome Kit provided by IBM.

2. A non-interactive user account, which is part of the Welcome Kit

   • NOTE: If the Non-Interactive Account section of the welcome kit is empty then you will need to request a non-interactive account by opening a support case with IBM.

3. Enable REST API nocanon Configuration Setting

   • This is only required if your reports reference element names containing special characters such as a backslash ("\"), and must be enabled by opening an IBM support case. You will need to include the following information in your support case:

IBM,

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

[Customer cloud environment 1]
[Customer cloud environment 2]
...
[Customer cloud environment n]

This is needed to address an issue with IBM PA Cloud’s handling of REST API requests, which is documented in case number TS002469510.

Connection Configuration

The following configuration information will appear after selecting "IBM Cloud authentication":

These are the connection configuration options:

• Datasource Name: This is a friendly name that describes this connection, and will be visible to all users of the ReportWORQ application when accessing information related to Planning Analytics (such as parameters). The name must be unique across all defined datasources.

• Database: This is the Database name as defined in Planning Analytics Cloud.

• IBM Cloud Url: This is an IBM-provided URL for your IBM Planning Analytics Cloud environment.

• Namespace: This is the namespace provided in your IBM Cloud Welcome Kit, which is typically 'LDAP'

• Username: This is the username for IBM-provided non-interactive login credentials.

• Password: This is the password for IBM-provided non-interactive login credentials.

• Test: Clicking the '✓' button will either display a success message or show error messages with further information on missing or misconfigured settings.

• Save: The 'Save' button saves changes made to the connection.

• The IBM Planning Analytics Datasource can be enabled or disabled by clicking the checkbox.
  
  Data Collection Credentials:
  

  
  
  

• Username & Password: Data Collection credentials are optional. When enabled, they limit contributor access to specified areas of the system.
   
  Advanced Options can be configured by selecting the following toolbar button:
  

  

• PAfE Calculation Mode: This is a setting that tells ReportWORQ how to calculate and resolve Planning Analytics Excel formula results:

  • Enabled: ReportWORQ will calculate formulas like the Planning Analytics for Excel (PAfE) add-in, for instance, by returning #VALUE! errors when invalid or missing element names are referenced.

  • Disabled: ReportWORQ will calculate formulas like the TM1 Perspectives add-in, for instance, by returning blank values when invalid or missing element names are referenced. 
    

• Enable Proportional Writeback: This enables ReportWORQ to send data to consolidated cells using the Proportional Spread option, and is only available for customers who have licensed the ReportWORQ Data Collection feature.

• Enable Cube Monitor: This enables Cube Monitoring, which is a method for automating ReportWORQ Job Execution from Planning Analytics. Enabling this option will create additional objects on this Planning Analytics Database Instance.

IBM AWS Cloud Connections

IBM Planning Analytics SaaS on AWS Cloud connections are managed separately in ReportWORQ because of differences in the way they are configured and managed by IBM and AWS.

Prerequisites

The following is needed to connect to IBM Planning Analytics SaaS on AWS:

1. Create an IBM Cloud API Key:

   1. Select an existing user account in your IBM Planning Analytics SaaS on AWS environment that you would like ReportWORQ to use when refreshing reports. 

   2. Follow IBM's instructions for creating an IBM Cloud API Key for that user account.
      

2. Basic Planning Analytics SaaS on AWS Connection Information:

   1. The name of the Planning Analytics Server database instance(s) that you plan to connect to from ReportWORQ (e.g., Planning Sample).

   2. The AWS Tenant ID where your IBM Planning Analytics SaaS environment is hosted (e.g., A1BCD2EFGHIJK).

   3. The AWS Data Center URL (example) where your IBM Planning Analytics SaaS environment is hosted.

Connection Configuration

The following configuration information will appear after selecting "AWS Cloud" Authentication or when clicking the link for an existing connection in ReportWORQ:

These are the connection configuration options:

• Datasource Name: This is a friendly name that describes this connection, and will be visible to all users of the ReportWORQ application when accessing information related to Planning Analytics (such as parameters). The name must be unique across all defined datasources.

• Database: This is the Database name as defined in Planning Analytics Cloud.

• Data Center Url: This is an AWS/IBM-provided URL for your environment.

• Tenant ID: This is an AWS/IBM-provided URL for your environment.

• API Key: This is the IBM Cloud API Key that was generated in Prerequisites and used to connect to your IBM Planning Analytics database.

• Test: Clicking the '✓' button will either display a success message or show error messages with further information on missing or misconfigured settings.

• Save: The 'Save' button saves changes made to the connection.

• The AWS Cloud Datasource can be enabled or disabled by clicking the checkbox.

Advanced Options can be configured by selecting the following toolbar button:

• PAfE Calculation Mode: This is a setting that tells ReportWORQ how to calculate and resolve Planning Analytics Excel formula results:

  • Enabled: ReportWORQ will calculate formulas like the Planning Analytics for Excel (PAfE) add-in, for instance, by returning #VALUE! errors when invalid or missing element names are referenced.

  • Disabled: ReportWORQ will calculate formulas like the TM1 Perspectives add-in, for instance, by returning blank values when invalid or missing element names are referenced. 

• Enable Proportional Writeback: This enables ReportWORQ to send data to consolidated cells using the Proportional Spread option, and is only available for customers who have licensed the ReportWORQ Data Collection feature.

• Enable Cube Monitor: This enables Cube Monitoring, which is a method for automating ReportWORQ Job Execution from Planning Analytics. Enabling this option will create additional objects on this Planning Analytics Database Instance.

Troubleshooting Connections

The following are suggested troubleshooting steps for IBM Planning Analytics connections for all supported connection types.

On-Premise Troubleshooting

The suggested troubleshooting steps for on-premise (self-hosted) connections are as follows:

1. Attempt to access the "REST API Metadata" and "Planning Analytics Database Information" Urls defined below from a web browser located on the ReportWORQ Server.

2. If either of those tests fails, you should next try the same test from a user desktop that you know can connect to Planning Analytics from Architect, Perspectives, or PAfE.

3. If #1 and #2 fail, please verify:

   1. Connection settings (address, port, and use SSL).

   2. Firewall settings on the Planning Analytics Server.

4. If only #1 fails, please verify:

   1. Firewall settings on the Planning Analytics Server.

   2. Firewall settings on the ReportWORQ Server.

5. If all of the above tests pass, but you are still unable to connect from ReportWORQ, please verify your login credentials from Architect, Perspectives, or PAfE.

The sample URLs for on-premise environments are defined below:

REST API Metadata URL: 

{HttpOrHttps}://{Address}:{Port}/api/v1/$metadata

Planning Analytics Database Information URL: 

{HttpOrHttps}://{Address}:{Port}/api/v1/Server

Where:

• {HttpOrHttps}: "Https" when "Use SSL" is enabled in connection settings, otherwise "Http"

• {Address}: the address specified in connection settings

• {Port}: the REST API port number specified in connection settings

For example, if these are your configured settings for a Planning Analytics Database:

• Address: prod-pa-server-01.company.com

• Use SSL: checked

• Port: 12887

Your test Urls & responses will be:

REST API Metadata Sample

URL: https://prod-pa-server-01.company.com:12887/api/v1/$metadata

Response (only an excerpt of the full response is below):

<?xml version="1.0" encoding="UTF-8"?>
<edmx:Edmx Version="4.0"
	xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx">
	<edmx:Reference Uri="https://oasis-tcs.github.io/odata-vocabularies/vocabularies/Org.OData.Capabilities.V1.xml">
		<edmx:Include Namespace="Org.OData.Capabilities.V1" Alias="Capabilities"/>
	</edmx:Reference>
	<edmx:Reference Uri="https://oasis-tcs.github.io/odata-vocabularies/vocabularies/Org.OData.Core.V1.xml">
		<edmx:Include Namespace="Org.OData.Core.V1" Alias="Core"/>
	</edmx:Reference>
	<edmx:DataServices>
		<Schema Namespace="ibm.tm1.api.v1" Alias="tm1"
			xmlns="http://docs.oasis-open.org/odata/ns/edm">
			<Annotation Term="Core.Revisions">
				<Collection>
					<Record>
						<PropertyValue Property="Version">
							<String>10.2.2.0</String>
						</PropertyValue>
						<PropertyValue Property="Kind">
							<EnumMember>Core.RevisionKind/Added</EnumMember>
						</PropertyValue>
					</Record>
				</Collection>
			</Annotation>
			<Annotation Term="Core.SchemaVersion">
				<String>11.6.0</String>
			</Annotation>
		</Schema>
	</edmx:DataServices>
</edmx:Edmx>

Planning Analytics Database Information Sample

URL: https://prod-pa-server-01.company.com:12887/api/v1/Server 

Response:

{
	"@odata.context": "$metadata#Server",
	"Name": "Prod",
	"ProductVersion": "11.7.00002.1",
	"PortNumber": 12345,
	"ClientMessagePortNumber": 12346,
	"HTTPPortNumber": 12887,
	"UsingSSL": true,
	"SecurityPackageName": "",
	"ServicePrincipalName": "",
	"IntegratedSecurityMode": "Mixed",
	"ClientCAMURI": "",
	"ClientPingCAMPassport": 900
}

IBM Cloud Troubleshooting

The suggested troubleshooting steps for IBM Cloud (IBM fully managed and hosted) connections are as follows:

1. Attempt to access the IBM Cloud-specific "REST API Metadata" and "Planning Analytics Database Information" Urls defined below from a web browser located on the ReportWORQ Server.

2. If either of those tests fails, you should next try the same test from a user desktop that you know can connect to IBM Planning Analytics Cloud from PAfE.

3. If #1 and #2 fail, please verify your connection settings (IBM Cloud Url and Database Name).

4. If all of the above tests pass, but you are still unable to connect from ReportWORQ, you should verify with IBM Support that the IBM PA Cloud Interactive User login credentials and related REST API configuration settings are valid for your IBM Cloud-hosted database.

The cloud-specific sample Urls are defined below:

REST API Metadata: 

https://{IBM Cloud Url}/tm1/api/{Database}/api/v1/$metadata

Planning Analytics Database Information: 

https://{IBM Cloud Url}/tm1/api/{Database}/api/v1/Server

Where:

• {IBM Cloud Url}: the address of your IBM Planning Analytics Cloud environment

• {Database}: the name of the database instance, as defined in IBM Cloud

For example, if these are your configured settings for a Planning Analytics Database:

• IBM Cloud Url: company-prod.planning-analytics.ibmcloud.com

• Database: Prod

Your test Urls & responses will be:

REST API Metadata Sample

Url: https://company-prod.planning-analytics.ibmcloud.com/tm1/api/Prod/api/v1/$metadata

Response (only an excerpt of the full response is below):

<?xml version="1.0" encoding="UTF-8"?>
<edmx:Edmx Version="4.0"
	xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx">
	<edmx:Reference Uri="https://oasis-tcs.github.io/odata-vocabularies/vocabularies/Org.OData.Capabilities.V1.xml">
		<edmx:Include Namespace="Org.OData.Capabilities.V1" Alias="Capabilities"/>
	</edmx:Reference>
	<edmx:Reference Uri="https://oasis-tcs.github.io/odata-vocabularies/vocabularies/Org.OData.Core.V1.xml">
		<edmx:Include Namespace="Org.OData.Core.V1" Alias="Core"/>
	</edmx:Reference>
	<edmx:DataServices>
		<Schema Namespace="ibm.tm1.api.v1" Alias="tm1"
			xmlns="http://docs.oasis-open.org/odata/ns/edm">
			<Annotation Term="Core.Revisions">
				<Collection>
					<Record>
						<PropertyValue Property="Version">
							<String>10.2.2.0</String>
						</PropertyValue>
						<PropertyValue Property="Kind">
							<EnumMember>Core.RevisionKind/Added</EnumMember>
						</PropertyValue>
					</Record>
				</Collection>
			</Annotation>
			<Annotation Term="Core.SchemaVersion">
				<String>11.6.0</String>
			</Annotation>
		</Schema>
	</edmx:DataServices>
</edmx:Edmx>

Planning Analytics Database Information Sample  

Url: https://company-prod.planning-analytics.ibmcloud.com/tm1/api/Prod/api/v1/Server 

Response:

{
	"@odata.context": "$metadata#Server",
	"Name": "Prod",
	"ProductVersion": "11.7.00002.1",
	"PortNumber": 12345,
	"ClientMessagePortNumber": 12346,
	"HTTPPortNumber": 12887,
	"UsingSSL": true,
	"SecurityPackageName": "",
	"ServicePrincipalName": "",
	"IntegratedSecurityMode": "CAM",
	"ClientCAMURI": "https://company-prod.planning-analytics.ibmcloud.com/ibmcognos/cgi-bin/cognosisapi.dll",
	"ClientPingCAMPassport": 900
}

IBM PA SaaS on AWS Troubleshooting

The suggested troubleshooting steps for IBM Planning Analytics SaaS on AWS connections are as follows:

1. Verify your Data Center Url and Tenant Id by logging into your IBM PA SaaS on AWS environment from a web browser. Once logged in you'll see a url like the following: https://<Data_Center_Url>/?accountId=ABC123DEF456&tenantId=<Tenant_Id>&perspective=pa-home

2. Login to IBM Cloud and verify that your API Key has been created and is still valid.

3. If all of the above tests pass, but you are still unable to connect from ReportWORQ, you should contact IBM Support to troubleshoot API access to your IBM PA SaaS on AWS hosted database(s).

Required Security Permissions

Planning Analytics login credentials in ReportWORQ require only the minimum READ access credentials needed to refresh data in any Planning-Analytics-enabled reports. When that feature is enabled, read access may also be required for any Cube Monitoring objects. 

When ReportWORQ Data Collection support is required with Planning Analytics databases, ReportWORQ will additionally need WRITE access to any database cubes and dimensions that need to be populated as part of Data Collection processing.

Administrative permissions are not required for ReportWORQ, with two one-time exceptions:

• Enabling Cube Monitoring requires one-time Administrative permissions to create cube, dimension, and Turbo Integrator processes needed to support this ReportWORQ feature.

• Enable ReportWORQ REST API Integration requires one-time Administrative permissions to create Turbo Integrator processes needed to support this ReportWORQ feature.

Once any administrative operations have been completed, those permissions may be removed from the stored login credentials used by ReportWORQ.

Supported Reporting Features

ReportWORQ Supports the following Planning Analytics reporting modes and Excel formulas: 

Reporting Modes

• Slices (Perspectives) / Custom Reports (PAfE)

• Active Forms (Perspectives) / Dynamic Reports (PAfE)

• Quick Reports (PAfE)

Explorations (PAfE) and In-Spreadsheet Browser (Perspectives) reports are not supported, currently.

Planning Analytics Excel Formulas

The following Planning Analytics Excel formulas are supported in ReportWORQ:

These Planning Analytics Excel formulas are not supported, currently:

• DBSA

• DBSS

• ELEMENTFIRST

• TM1GLOBALSANDBOX

• TM1INFO

• TM1RPTISEXPANDED

Planning Analytics-Specific ReportWORQ Parameters

ReportWORQ has several Parameter types that can be defined based on information in Planning Analytics:

• Planning Analytics Dimension: Used to return all elements from a Planning dimension hierarchy.

• Planning Analytics Subset: Used to return all elements from a named Private or Public Planning Analytics hierarchy Subset.

• Planning Analytics Subset MDX: Used to return elements from a dimension based on an MDX expression, including a special "Repeater" Parameter type that is designed to be used dynamically with other parameters.

More information about these parameter types and other parameter options that can be used in ReportWORQ with Planning Analytics and other datasources are described in more detail in the Creating Jobs topic.

Turbo Integrator Automation Options

Planning Analytics has built-in capabilities, like its Turbo Integrator ETL tool, that are well-suited to automating the execution of ReportWORQ Jobs. There are two Turbo Integrator-related automation supported by ReportWORQ:

• REST API Integration: This is only supported for On-Premise connections, and allows Planning Analytics developers to run ReportWORQ tasks, such as running Jobs, by calling the ReportWORQ REST API from a Turbo Integrator process.

• Cube Monitoring: This is designed for IBM PA Cloud (primarily) or On-Premise connections, and allows Planning Analytics developers to insert ReportWORQ Job execution requests into a cube that ReportWORQ monitors and processes at regular intervals.

Both of these options are documented in more detail below, as are the Turbo Integrator processes and other objects that are created and used by ReportWORQ.

REST API Integration

To enable and use Turbo Integrator integration with the ReportWORQ REST API. which is available only for "IBM Planning Analytics - On Premise" connections, perform the following steps:

1. In the On Premise Configuration Settings, click the link to "Install REST API TI Process". This action adds these two Turbo Integrator Processes to your Planning Analytics datasource:

   • ReportWORQ - REST API Process Input: Used to initiate a Data Collection operation in ReportWORQ, and process any pending input files.

   • ReportWORQ - REST API Run Job Sample 2.1: Used to execute a Job in ReportWORQ, with several Job-specific overrides and settings available as parameter settings.

2. Optionally update the default parameter values in the above Turbo Integrator processes:

   • pAccessToken: This can be set to the value of the ReportWORQ API Token created previously.

   • pApiUrl: This can be set to the configured ReportWORQ Server Url.

3. These processes rely on two accompanying PowerShell scripts that are installed in a Scripts subfolder where ReportWORQ is installed (e.g., C:\Program Files\ReportWORQ\Scripts\). If Planning Analytics is running on a different machine than ReportWORQ then copy these files to the Planning Analytics server in the same folder path. If you do not use this default path then modify the prolog of the Turbo Integrator process and adjust to the path used.

   • ProcessInput.ps1: Needed by "ReportWORQ - REST API Process Input".

   • RunApiJob.ps1: Needed by "ReportWORQ - REST API Run Job Sample 2.1".

4. Test the RunApiJob.ps1 PowerShell script for security policy changes. Right click on the RunApiJob.ps1 file and choose Run with Powershell. If the Powershell window opens and closes then the script is ready to run. If the Execution Policy Change message in the screenshot below appears then choose Y to enable this script.
   

   
   

5. Information about ReportWORQ - REST API Run Job Sample 2.1's additional parameters:

   • pJobName: The ReportWORQ job name to run.

   • pNotificationEmail: Email address used by ReportWORQ to optionally send notifications when the job is complete.

   • pHoldForDelivery: Specify "true" to run the Job with the Hold for Delivery option enabled.

   • pTlsSecurity: This tells the PowerShell script what TLS protocol versions should be used when connecting to ReportWORQ. The default values are normally sufficient on most environments: Tls11, Tls12.

6. ReportWORQ - REST API Run Job Sample 2.0Path Settings:

   • In the Prolog of the process are two directory variable names that need to be updated to existing folders in your environment:

     • PowerShellScriptFolder: This is the folder that contains the RunApiJob.ps PowerShell script file. (e.g., C:\ReportWORQ Scripts\)

     • JobRequestFolder: This is a folder where job requests are written when this process runs. We recommend creating a subfolder below the PowerShellScript folder location (e.g., C:\ReportWORQ Scripts\RunApiJobRequests\).

7. To override ReportWORQ Job parameters, please view instructions and samples on the Epilog tab of the ReportWORQ - REST API Run Job Sample 2.1 TI Process. The Parameter Override logic uses the ReportWORQ API Run Command with the appropriate JSON being constructed through TI.

Cube Monitoring

To enable Cube Monitoring for Planning Analytics On Premise or IBM Cloud connections, perform the following steps:

1. In the On Premise or IBM Cloud connection settings, select the check box to "Enable Cube Monitor". This action adds the following objects to your Planning Analytics datasource:

   1. "}ReportWORQ__Queued Jobs" Cube: This cube, along with its two dimensions, is used by ReportWORQ to track job requests. ReportWORQ polls that cube every few seconds to see if a Job has been queued to run. Jobs are queued to run by adding an element to the "}ReportWORQ__Queued Job Id" dimension and populating the details of the job to be executed. ReportWORQ automatically removes processed job requests from the "Job Id" list. The "ReportWORQ - Add Queued Job" Turbo Integrator process can be used to add requests to this queue cube.

   2. "ReportWORQ - Add Queued Job" Process: This Turbo Integrator process provides a working example of how to create the Job Request entry and can be integrated into other Processes and Chores in Planning Analytics, for instance by requesting that a ReportWORQ Job be run after a data load is complete, or to provide some self-service reporting bursting options for users. 

2. Information about ReportWORQ - Add Queued Job's additional parameters:

   • pJobDefinition: The job definition parameter tells ReportWORQ what to run, and uses the following specification to support optional parameter overrides:

     • Job Name;Job Name!ParamName|ParamOverrideValue,ParamName!ParamOverrideValue

     • Sample pJobDefinition parameter values:

       • Specify a Job without overrides, by name: Monthly Sales Job

       • Specify the path of a Job without overrides: Monthly Reporting/Sales/Monthly Sales Job

         • Tip: the full path of the job name can be obtained by selecting Copy Job Path from the context menu of the job in the Job Editor tree.

       • Override a SalesRep parameter value: Monthly Sales Job;Monthly Sales Job!SalesRep|1001

       • Override the SalesRep and Period parameter values: Monthly Sales Job;Monthly Sales Job!SalesRep|1001,Period|Jan-2023

   • pNotificationEmail: Email address used by ReportWORQ to optionally send notifications when the job is complete.

This mechanism is specifically designed to support IBM Cloud customers where the Planning Analytics Cloud-hosted TI process would not otherwise have the ability to create a file or call a REST endpoint on the locally-hosted ReportWORQ Server. 

No additional configuration in ReportWORQ is needed, once this feature is enabled for a Planning Analytics connection.