Contents x
    IBM Planning Analytics
    • 16 Jan 2025
    • 28 Minutes to read
    • Print
    • Dark
      Light
    Contents

    IBM Planning Analytics

    • Updated on 16 Jan 2025
    • 28 Minutes to read
    • Print
    • Dark
      Light
    Article summary

    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 report 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 certain Planning Analytics-specific Parameter types (IBM Planning Analytics subsets and MDX Queries).

    This article describes how to configure Planning Analytics connections as well as the various IBM Planning Analytics features and integration options that ReportWORQ supports. It contains the following topics:

    To use IBM Planning Analytics as a data source, at least one IBM Planning Analytics connection (Self Hosted, IBM Cloud, or AWS Cloud with MCSP) must be configured in ReportWORQ, and you must also have an existing Perspectives or PAfE report which uses that connection.

    Datasource Configuration

    ReportWORQ supports three options for connecting to IBM Planning Analytics. Perform the connection procedure that is appropriate for your Planning Analytics environment:

    • Connecting to Self Hosted Instances — 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 a virtual machine on Amazon Web Services (AWS) or Microsoft Azure.

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

    • Connecting to Planning Analytics as a Service on AWS Cloud — These are connections to Planning Analytics SaaS on AWS instances with Managed Cloud Service Provider (MCSP) authentication.

    Connecting to Self Hosted Instances

    Planning Analytics Self Hosted 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 when configuring connections to self hosted IBM Planning Analytics databases:

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

    • Basic Database Connection Information:

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

      • The following Planning Analytics Server database configured settings:

        • Name (e.g., Planning Sample)

        • Address

        • REST API Port Number

        • SSL Enabled / Disabled

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

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

    • Additional Configuration for Integrated Login: If you plan to use Planning Analytics Integrated Login, additional configuration related to Active Directory and Kerberos is required to ensure that ReportWORQ can successfully connect to Planning Analytics.

    To add and configure a Self Hosted IBM Planning Analytics Datasource:

    1. In the ReportWORQ Administration interface, select the add icon beside the Datasources heading.
      The New Datasource Connection pane appears:



    2. Select the IBM Planning Analytics button.



      The Datasource Settings and Distribution Credentials appear:

    3. In the Datasource Name box, provide a meaningful name for the connection.
      This name is visible to all ReportWORQ users when they access information related to Planning Analytics, such as when they use Parameters. The Datasource Name must be unique across all ReportWORQ Datasources.

    4. From the Authentication Type list, select Self Hosted.



      Settings for configuring a Self Hosted connection appear:



    5. Configure the following Datasource Settings as required:

      • Database: The Database name as defined in your Planning Analytics configuration.
        You can type the name, or load a list of database names and then select the correct one.
        Tip: If you select a database from the list, the database Address and Port settings are configured automatically.
        To load a list of database names, select , provide the URL of the IBM Planning Analytics Admin Host, and select OK.


        IMPORTANT: If you plan to use the Planning Analytics distributor and there are multiple Datasources that use the same database, all such Datasources except the one required for the current job must be disabled while the job is running.

      • Address: This is the address of the computer 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: If selected, ReportWORQ uses SSL encryption protocol when communicating with the Planning Analytics database. Whether SSL is required is dictated by settings in the Planning Analytics configuration.

    6. Configure the Distribution Credentials:

      • Use Integrated Login: If selected, Planning Analytics Integrated Login is used to access the database.

      • Username and Password: These are the same credentials used to authenticate IBM Planning Analytics Microsoft for Excel. If using Integrated Login, leave the Username and Password boxes blank.

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

      • Domain: This is the domain name, which is required if using Integrated Login.

      • Use Different credentials for data collection: If selected, Data Collection Credentials appear:



        Note: Data Collection settings apply only if your ReportWORQ implementation includes Data Collection, which is a set of legacy features that remain available only to customers who are already licensed to use them.

    7. Configure the Data Collection Credentials, if applicable:

      • Use Integrated Login: If selected, Planning Analytics Integrated Login is used to access the database.

      • Username and Password: Data Collection credentials are optional. When enabled, they limit contributor access to specified areas of the system. If using Integrated Login, leave the Username and Password boxes blank.

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

      • Domain: This is the domain name, which is required if using Integrated Login.

    8. To confirm that the settings are properly configured, select the Test Connection button.
      If the test fails, edit the settings as required, and then test again. For more information, see Troubleshooting Self Hosted Connections.

    9. In the Datasource Settings area, select the Enable datasource connection checkbox.

    10. Select the Save icon to save and apply the settings.

    To review and configure Advanced Settings:

    1. Select the Advanced Settings button.
      the Advanced Options pane appears:



    2. Configure the advanced options as required:

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

        • Enabled: ReportWORQ calculates formulas like the Planning Analytics for Excel (PAfE) add-in. For example, it returns #VALUE! errors when invalid or missing element names are referenced.

        • Disabled: ReportWORQ calculates formulas like the TM1 Perspectives add-in. For example, it returns blank values when invalid or missing element names are referenced.

      • Enable Proportional Data Collection: This enables ReportWORQ to send data to consolidated cells using the Proportional Spread option.
        Note: Data Collection settings apply only if your ReportWORQ implementation includes Data Collection, which is a set of legacy features that remain available only to customers who are already licensed to use them.

      • 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.

    3. Close the Advanced Options pane.

    4. To confirm that the settings are properly configured, select the Test Connection button.
      If the test fails, edit the settings as required, and then test again.

    5. Select the Save icon to save and apply the settings.

    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 you begin, 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.

    To configure ReportWORQ to use Integrated Login:

    Add one or more Service Principal Names (SPNs) using the following structure,

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

    and replacing placeholders in the command as follows:

    • <Address> is the address of the server computer 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> is 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 the Active Directory domain is named Corporate. Internal and your Planning Analytics Server runs on a computer named PAAppPrd01 under a service account named 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 computers.

    Adding SPNs based on an IP Address

    You can create SPNs based on the IP address of the Planning Analytics Server. However, for this to work from ReportWORQ, the ReportWORQ Server computer must have this registry setting.

    Note that a restart of the ReportWORQ Server computer may be required for this setting to take effect.

    Connecting to IBM Cloud

    This section describes how to add and configure a ReportWORQ Datasource connection to IBM Planning Analytics hosted and managed by IBM on IBM Cloud.

    Prerequisites

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

    • The Welcome Kit provided by IBM.

    • 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, you must request a non-interactive account by opening a support case with IBM.

    • 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. Below is an example of information to include in your support case request:

    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.

    Opening a Support Case with IBM

    All support requests should go through IBM Support. Choose “Open a Case” while signed in with your IBM ID.

    To add and configure a Datasource for Planning Analytics hosted on IBM Cloud:

    1. In the ReportWORQ Administration interface, select the add icon beside the Datasources heading.
      The New Datasource Connection pane appears:



    2. Select the IBM Planning Analytics button.



      The Datasource Settings and Distribution Credentials appear:


    3. In the Datasource Name box, provide a meaningful name for the connection.
      This name is visible to all ReportWORQ users when they access information related to Planning Analytics, such as when they use Parameters. The Datasource Name must be unique across all ReportWORQ Datasources.

    4. From the Authentication Type list, select IBM Cloud.



      Settings for configuring an IBM Cloud connection appear:


    5. Configure the following Datasource Settings as required:

      • Database: This is the Database name as defined in Planning Analytics Cloud.
        IMPORTANT: If you plan to use the Planning Analytics distributor and there are multiple datasources that use the same database, all such datasources except the one required for the current job must be disabled while the job is running.

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

    6. Configure the following Distribution Credentials as required:

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

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

    7. If you want to Use different credentials for data collection, select that option and then provide a Username and Password if required. Data Collection credentials are optional. When enabled, they limit contributor access to specified areas of the system.
      Note: Data Collection settings apply only if your ReportWORQ implementation includes Data Collection, which is a set of legacy features that remain available only to customers who are already licensed to use them.



    8. To confirm that the settings are properly configured, select the Test Connection button.
      If the test fails, edit the settings as required, and then test again. For more information, see Troubleshooting IBM Cloud Connections.

    9. In the Datasource Settings area, select the Enable datasource connection checkbox.

    10. Select the Save icon to save and apply the settings.

    To review and configure Advanced Settings:

    1. Select the Advanced Settings button.
      The Advanced Options pane appears:



    2. Configure the advanced options as required:

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

        • Enabled: ReportWORQ calculates formulas like the Planning Analytics for Excel (PAfE) add-in. For example, it returns #VALUE! errors when invalid or missing element names are referenced.

        • Disabled: ReportWORQ calculates formulas like the TM1 Perspectives add-in. For example, it returns blank values when invalid or missing element names are referenced.

      • Enable Proportional Data Collection: This enables ReportWORQ to send data to consolidated cells using the Proportional Spread option.
        Note: Data Collection settings apply only if your ReportWORQ implementation includes Data Collection, which is a set of legacy features that remain available only to customers who are already licensed to use them.

      • 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.

    3. Close the Advanced Options pane.

    4. To confirm that the settings are properly configured, select the Test Connection button.
      If the test fails, edit the settings as required, and then test again.

    5. Select the Save icon to save and apply the settings.

    Connecting to Planning Analytics as a Service on AWS Cloud

    This section describes how to connect to IBM Planning Analytics as a Service on AWS Cloud with MCSP authentication.

    Before you begin:

    1. Create an API Key for Planning Analytics as a Service with MCSP:

      1. Select the existing user account in your Planning Analytics as a Service environment that you would like ReportWORQ to use when refreshing reports.

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

    2. Gather connection information for Planning Analytics as a Service with MCSP:

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

      • The AWS TenantId where your IBM Planning Analytics as a Service environment is hosted (e.g., A1BCD2EFGHIJK).

      • The AWS Data Center URL where your IBM Planning Analytics as a Service environment is hosted.
        eg: xxx.aws.planninganalytics.ibm.com

    To add and configure a Datasource for IBM Planning Analytics on AWS Cloud:

    1. In the ReportWORQ Administration interface, select the add icon beside the Datasources heading.
      The New Datasource Connection pane appears:



    2. Select the IBM Planning Analytics button.



      The Datasource Settings and Distribution Credentials appear:



    3. In the Datasource Name box, provide a meaningful name for the connection.
      This name is visible to all ReportWORQ users when they access information related to Planning Analytics, such as when they use Parameters. The Datasource Name must be unique across all ReportWORQ Datasources.

    4. From the Authentication Type list, select AWS Cloud (MCSP).



      Settings for configuring an AWS Cloud connection appear:



    5. Configure the following Datasource Settings as required:

      • Database: This is the Database name as defined in Planning Analytics Cloud.
        IMPORTANT: If you plan to use the Planning Analytics distributor and there are multiple datasources that use the same database, all such datasources except the one required for the current job must be disabled while the job is running.

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

      • TenantId: This is an AWS/IBM-provided URI for your environment.

    6. In the Distribution Credentials area, provide the API Key you retrieved earlier. The API Key is used to connect to your IBM Planning Analytics database.

    7. If you want to Use different credentials for data collection, select that option and then provide the required API Key.
      Note: Data Collection settings apply only if your ReportWORQ implementation includes Data Collection, which is a set of legacy features that remain available only to customers who are already licensed to use them.



    8. To confirm that the settings are properly configured, select the Test Connection button.
      If the test fails, edit the settings as required, and then test again. For more information, see Troubleshooting AWS Cloud Connections.

    9. In the Datasource Settings area, select the Enable datasource connection checkbox.

    10. Select the Save icon to save and apply the settings.

    To review and configure Advanced Settings:

    1. Select the Advanced Settings button.
      The Advanced Options pane appears:



    2. Configure the advanced options as required:

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

        • Enabled: ReportWORQ calculates formulas like the Planning Analytics for Excel (PAfE) add-in. For example, it returns #VALUE! errors when invalid or missing element names are referenced.

        • Disabled: ReportWORQ calculates formulas like the TM1 Perspectives add-in. For example, it returns blank values when invalid or missing element names are referenced.

      • Enable Proportional Data Collection: This enables ReportWORQ to send data to consolidated cells using the Proportional Spread option.
        Note: Data Collection settings apply only if your ReportWORQ implementation includes Data Collection, which is a set of legacy features that remain available only to customers who are already licensed to use them.

      • 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.

    3. Close the Advanced Options pane.

    4. To confirm that the settings are properly configured, select the Test Connection button.
      If the test fails, edit the settings as required, and then test again.

    5. Select the Save icon to save and apply the settings

    Troubleshooting Connection Problems

    This section contains recommended troubleshooting steps for IBM Planning Analytics connections for all supported connection types:

    Troubleshooting Self Hosted Connections

    Recommended troubleshooting steps for Self-Hosted connections are as follows:

    1. Try to access the REST API Metadata URL and the Planning Analytics Database Information URL from a web browser on the ReportWORQ Server. For an example of the URL formats, see the Sample URL Formats provided in this section, below these steps.  

    2. If one or both of the URLs mentioned in Step 1 cannot be reached, try to reach them from a user computer that you know can connect to Planning Analytics from Architect, Perspectives, or PAfE.

    3. If one or both of the URLs mentioned in Step 1 still cannot be reached, verify:

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

      2. Firewall settings on the Planning Analytics Server.

    4. If the URLs menitoned in Step 1 cannot be reached from the ReportWORQ Server, but can be reached from a different computer, verify:

      1. Firewall settings on the Planning Analytics Server.

      2. Firewall settings on the ReportWORQ Server.

    5. If you are able to reach the URLs mentioned in Step 1 from the ReportWORQ Server and from a different computer but cannot connect from ReportWORQ, verify your login credentials from Architect, Perspectives, or PAfE.

    Sample URL Formats:

    • REST API Metadata URL Format: <Http or Https>://<Address>:<Port>/api/v1/$metadata

    • Planning Analytics Database Information URL Format: <Http or Https>://<Address>:<Port>/api/v1/Server

    In the URLs above, replace placeholders as follows:

    • <HttpOrHttps>: Use https when Use SSL is enabled in connection settings, otherwise use http.

    • <Address>: Provide the address specified in connection settings.

    • <Port>: Provide the REST API port number specified in connection settings.

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

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

    • Use SSL: checked

    • Port: 12887

    Your test URLs and responses would be as follows:

    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 shown 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
}

    Troubleshooting IBM Cloud Connections

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

    1. Try to access the IBM Cloud-specific REST API Metadata URL and Planning Analytics Database Information URL from a web browser on the ReportWORQ Server. For an example of the URL formats, see the Sample URL Formats provided in this section, below these steps.  

    2. If one or both of the URLs mentioned in Step 1 cannot be reached, try to reach them from a user computer that you know can connect to connect to IBM Planning Analytics Cloud from PAfE.

    3. If one or both of the URLs mentioned in Step 1 still cannot be reached, verify:

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

      2. Firewall settings on the Planning Analytics Server.

    4. If the URLs menitoned in Step 1 cannot be reached from the ReportWORQ Server, but can be reached from a different computer, verify your connection settings (IBM Cloud URL and Database Name).

    5. If you are able to reach the URLs mentioned in Step 1 from the ReportWORQ Server and from a different computer, but cannot connect from ReportWORQ, 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.

    Sample URL Formats:

    • REST API Metadata URL Format: https://<IBM Cloud URL>/tm1/api/<Database>/api/v1/$metadata

    • Planning Analytics Database Information URL Format: https://<IBM Cloud URL>/tm1/api/<Database>/api/v1/Server

    In the URLs above, replace placeholders as follows:

    • <IBM Cloud URL>: Use the address of your IBM Planning Analytics Cloud environment.

    • <Database>: Use the name of the database instance, as defined in IBM Cloud

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

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

    • Database: Prod

    Your test URLs and responses would be as follows:

    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 shown 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
}

    Troubleshooting AWS Cloud Connections

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

    1. Verify your Data Center URL and TenantId by logging into your IBM PA SaaS on AWS environment from a web browser. Once logged in, you'll see a URL similar to the following: https://<Data_Center_URL>/?accountId=ABC123DEF456&tenantId=<Tenant_Id>&perspective=pa-home.

    2. Log in 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 contact IBM Support to troubleshoot API access to your IBM PA SaaS on AWS hosted database(s).

    Planning Analytics Database Names are Space and Case-Sensitive

    The database name in BOTH IBM Cloud and IBM PA SaaS on REST API endpoints is case and space-sensitive, so be sure to check this carefully when troubleshooting IBM Cloud or IBM PA SaaS on AWS connections to Planning Analytics from ReportWORQ.

    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.

    Note: Data Collection settings apply only if your ReportWORQ implementation includes Data Collection, which is a set of legacy features that remain available only to customers who are already licensed to use them.

    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 TurboIntegrator processes needed to support this ReportWORQ feature.

    • Enable ReportWORQ REST API Integration requires one-time Administrative permissions to create TurboIntegrator 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:

    DBR

    DIMX

    ELCOMPN

    ELWEIGHT

    TM1RPTELISCONSOLIDATED

    DBRW

    DIMNM

    ELISCOMP

    SUBSIZ

    TM1RPTELLEV

    DBS

    DIMSIZ

    ELISPAR

    TABDIM

    TM1RPTFILTER

    DBSW

    DNEXT

    ELLEV

    TM1ELLIST

    TM1RPTTITLE

    SUBNM

    DNLEV

    ELPAR

    TM1PRIMARYDATASOURCE

    TM1RPTVIEW

    DBRA

    DTYPE

    ELPARN

    TM1PRIMARYDBNAME

    TM1USER

    DFRST

    ELCOMP

    ELSLEN

    TM1RPTROW

    VIEW (Return cube name only)

    The following Planning Analytics Excel formulas are not currently supported:

    • DBSA

    • DBSS

    • ELEMENTFIRST

    • TM1GLOBALSANDBOX

    • TM1INFO

    • TM1RPTISEXPANDED

    Parameters Specific to Planning Analytics

    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.

    For more information about these Parameter types and other ReportWORQ Parameter options, see the Creating Jobs article.

    TurboIntegrator Automation Options

    IBM Planning Analytics has built-in capabilities, such as its TurboIntegrator ETL tool, that are well-suited to automating the execution of ReportWORQ Jobs. Two TurboIntegrator-related automations are supported by ReportWORQ:

    • REST API Integration: This is only supported for Self Hosted connections. It allows Planning Analytics developers to run ReportWORQ tasks, such as running Jobs, by calling the ReportWORQ REST API from a TurboIntegrator process.

    • Cube Monitoring: This is designed for IBM PA Cloud (primarily) or Self Hosted connections. It 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 described below, as are the TurboIntegrator processes and other objects that are created and used by ReportWORQ.

    REST API Integration

    This topic explains how to set up two TurboIntegrator Processes that integrate with the ReportWORQ REST API. This integration adds the following TI Processes to your Planning Analytics datasource:

    • 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.

    These TI Processes rely on accompanying scripts:

    • For Windows, each of these TI Processes references a provided PowerShell script.

    • For LINUX, you can edit the ReportWORQ - REST API Run Job Sample 2.1 Process to reference a provided .bash script.

    To obtain the TurboIntegrator Processes for ReportWORQ:

    1. In the Advanced Options area of the Datasource configuration settings for Self Hosted Instances, select the link to Install REST API TI Process. Two TurboIntegrator Processes are added to your Planning Analytics environment.

    2. Optionally, update the default parameter values in the following TurboIntegrator 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. For more information, see Determining the ReportWORQ URL Address in the ReportWORQ Configuration article.

    Windows only — to set up the PowerShell scripts:

    1. 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 computer than ReportWORQ, copy these files to the Planning Analytics server in the same folder path. If you do not use the default path, modify the Prolog of the TurboIntegrator process and adjust to the path used.

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

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

    2. 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 select Y to enable this script.



    3. For the ReportWORQ - REST API Run Job Sample 2.1 Process only: In the Prolog 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 within the PowerShellScript folder location (e.g., C:\ReportWORQ Scripts\RunApiJobRequests\).

    LINUX only - to set up the RunApiJob.bash script:

    1. Download the RunApiJob.bash script and then move it to a folder that can be accessed by TurboIntegrator. To download the script, select this link.

    2. In TurboIntegrator, edit the Prolog of the ReportWORQ - REST API Run Job Sample 2.1 Process to reference the RunApiJob.bash script instead of the RunApiJob.ps1 script, and to point to the folder that contains the script.
      For example:
      BashScriptFolder = '/reportworq/scripts/';

      BashScript = 'RunApiJob.bash' ;

      BashScriptFileLocation = BashScriptFolder | BashScript ;

    3. Edit the Epilog of the ReportWORQ - REST API Run Job Sample 2.1 Process to reference the RunApiJob.bash script directly in the EXECUTECOMMAND function, and to point to the folder that contains the script.
      For example:
      CommandToRun = ' cd '| BashScriptFolder |' ; sh ' | BashScript | ' -token ' | pAccessToken | ' -url ' | pApiUrl | ' -jobPath ' | JobRequestFolder | LOWER(JobFileName) | ' -tlsSecurity ' | pTlsSecurity | ' > ' | BashScriptFolder | 'RunApiJob.log';

    Additional Parameters for the ReportWORQ - REST API Run Job Sample 2.1 Process

    Additional parameters are as follows:

    • pJobName: The ReportWORQ job name to run.

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

    • pHoldForDelivery: Set to 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 in most environments: Tls11, Tls12.

    For more information about overriding ReportWORQ Job parameters, review 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 Job Run command with the appropriate JSON being constructed through TurboIntegrator.

    NOTE

    These TurboIntegrator processes provide working examples of ReportWORQ REST API integration. It is a good idea to make copies of them or rename them if you plan to customize and enhance them based on your automation requirements. For more tips and troubleshooting options please refer to the TI Script Troubleshooting section below.

    TI Script Troubleshooting

    If a user is having difficulty with their TI script, it is recommended that they check this short list to determine the error before contacting support:

    • Is the ReportWORQ API enabled?

    • Are you able to access ReportWORQ in a browser? (Is it running?)

    • Does the pApirUrl value match the ReportWORQ address and is it accessible from the TM1 server where you are attempting to run the script?

    • Does the active accessToken in the TI parameters match the API token configured in the ReportWORQ API integration?

    • Has the process run successfully since the RW5 script upgrade?

    • If you follow the Excel reference in the job definition, does it actually evaluate to a valid job name that exists in ReportWORQ?

    • Does the name of the TM1 server you are attempting to use match the value in the pServer parameter?
      If it does, are there multiple jobs with the same name or a job and folder that share the same name? If so, a full path to the job will need to be used instead of just the job name.

    • Is the path to the PowerShell file that is referenced in the TI script valid?

    Cube Monitoring

    To enable Cube Monitoring for Planning Analytics connections (Self Hosted, IBM Cloud):

    1. In the Self Hosted or IBM Cloud ReportWORQ Datasource configuration 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 the 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 TurboIntegrator process can be used to add requests to this queue cube.

      2. "ReportWORQ - Add Queued Job" and "ReportWORQ - Add Queued Job for Workspace" Processes: These TurboIntegrator processes provide 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. The Add Queued Jobs for Workspace process should be used when using the Workspaces feature in ReportWORQ so that the job being executed can be located.

    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.

      • pWorkspaceName: The name of the workspace where the job is located. The Add Queued Job for Workspace process should be used when more than one workspace has been configured.

      • Please note that the delimiter for text lists of parameters is two asterisks (**). When adding multiple parameters ensure that they are separated by **.

    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 required after this feature is enabled for a Planning Analytics connection.

    NOTE

    The ReportWORQ - Add Queued Job TurboIntegrator process provides a working example of Cube Monitoring integration. It is a good idea to make copies of this process or to rename it if you plan to further customize and enhance them based on your automation requirements.

    Was this article helpful?
    What's Next