⬇ Download Reportworq

This is the archived documentation for Reportworq 5. It is kept for reference and is no longer updated.

Go to the Reportworq 6 documentation
Home › Administration Guide › Datasources

Datasources:#

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

The RWMDX function can be added to an Excel report to query the datasource and return data to the Excel spreadsheet, using an MDX query.

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:

Prerequisites

The following information is required when configuring connections to self hosted IBM Planning Analytics databases:

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:
  1. Configure the Distribution Credentials:
  1. Configure the Data Collection Credentials, if applicable:
  1. 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.
  2. In the Datasource Settings area, select the Enable datasource connection checkbox.
  3. Select the Save icon to save and apply the settings.

To review and configure Advanced Options:

  1. Select the Advanced Options button. the Advanced Options pane appears:
  2. Configure the advanced options as required:
  1. Close the Advanced Options pane.
  2. 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.
  3. 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:

To configure Reportworq to use Integrated Login:

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

and replacing placeholders in the command as follows:

Tap or click the image to view it full screen

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.

Tap or click the image to view it full screen

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:

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.
Tap or click the image to view it full screen

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:
  1. Configure the following Distribution Credentials as required:
  1. 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.
  2. 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.
  3. In the Datasource Settings area, select the Enable datasource connection checkbox.
  4. Select the Save icon to save and apply the settings.

To review and configure Advanced Optiopns:

  1. Select the Advanced Options button. The Advanced Options pane appears:
  2. Configure the advanced options as required:
  1. Close the Advanced Options pane.
  2. 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.
  3. Select the Save icon to save and apply the settings.

Connecting to Planning Analytics as a Service with MCSP#

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

Before you begin:

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

To add and configure a Datasource for IBM Planning Analytics with MCSP:

  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:
  1. 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.
  2. 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.
  3. 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.
  4. In the Datasource Settings area, select the Enable datasource connection checkbox.
  5. Select the Save icon to save and apply the settings.

To review and configure Advanced Options:

  1. Select the Advanced Options button. The Advanced Options pane appears:
  2. Configure the advanced options as required:
  1. Close the Advanced Options pane.
  2. 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.
  3. 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. If the URLs menitoned in Step 1 cannot be reached from the Reportworq Server, but can be reached from a different computer, verify:
  1. 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:

In the URLs above, replace placeholders as follows:

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

Your test URLs and responses would be as follows:

REST API Metadata Sample

<?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

{
	"@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. 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).
  2. 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:

In the URLs above, replace placeholders as follows:

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

Your test URLs and responses would be as follows:

REST API Metadata Sample

<?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

{
	"@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:///?accountId=ABC123DEF456&tenantId=&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).
Tap or click the image to view it full screen

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:

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

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:

DBRDIMSIZELPARNTM1PRIMARYDBNAME
DBRWDNEXTELSLENTM1RPTROW
DBSDNLEVELWEIGHTTM1RPTELISCONSOLIDATED
DBSWDTYPEMAKEAXISTM1RPTELLEV
SUBNMELCOMPMAKECALCTM1RPTFILTER
DBRAELCOMPNMAKEQUERYTM1RPTTITLE
DEFINECALC
Except SDatasource argument.		ELISCOMPSUBSIZTM1RPTVIEW
DFRSTELISPARTABDIMTM1SET
Uri_identifier argument is ignored.
ServerName argument is required.
Note: Selected Element argument and Active Display argument are case-sensitive and space-sensitive.
DIMXELLEVTM1ELLISTTM1USER
DIMNMELPARTM1PRIMARYDATASOURCEVIEW
Return cube name only.

The following Planning Analytics Excel formulas are not currently supported:

Parameters Specific to Planning Analytics#

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

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

Turbo Integrator Automation Options#

IBM Planning Analytics has built-in capabilities, such as its Turbo Integrator ETL tool, that are well-suited to automating the execution of Reportworq Jobs. Reportworq supports the following Turbo Integrator-related automations:

Configure only one of these automation options.

REST API Integration for Self Hosted Connections#

This topic explains how to set up a Turbo Integrator process that integrates with the Reportworq REST API. This integration adds the Reportworq - REST API Run Job Sample 2.4 TI process to your Planning Analytics datasource.

The main steps for setting up this integration are as follows:

To install the REST API TI process:

  1. In the Reportworq Administration interface, in the Datasources list, select the IBM Planning Analytics datasource. Datasource settings appear.
  2. Select Advanced Options.
  3. Select the link to Install REST API TI process. The Reportworq - REST API Run Job Sample 2.4 TI process is added to your Planning Analytics environment. Leave the Advanced Options pane open.

To upgrade the REST API TI process:

  1. First delete the existing Reportworq - REST API Run Job Sample 2.4 TI Process
  2. Follow the steps for the Install REST API TI process

To set up the RunApiJob.ps1 PowerShell script (for Windows only):

  1. In the Advanced Options pane for your IBM Planning Analytics datasource, select the link to Download REST API TI PowerShell Script. The Run Api Script.zip file appears in the Downloads folder.
  2. Extract the RunApiJob.ps1 script file and save it in the C:\Reportworq Scripts\ folder. Notes:
  1. Test the RunApiJob.ps1 script file for security policy changes:
  1. In Turbo Integrator, edit the Prolog of the Reportworq - REST API Run Job Sample 2.4 TI process to update the following variables, so they folders in your environment:

To set up the RunApiJob.bash script (for Linux only):

  1. Download the RunApiJob.bash script and save it in a folder that can be accessed by Turbo Integrator. To download the script, select this link.
  2. In Turbo Integrator, edit the Prolog of the Reportworq - REST API Run Job Sample 2.4 TI 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.4 TI 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';

To review and configure parameter values:

  1. In Turbo Integrator, navigate to the Reportworq - REST API Run Job Sample 2.4 TI process, and open the Parameters settings.
  2. Configure default values for the following parameters:
  1. Review and configure defaults for the remaining parameters as required:

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.4 TI process. The Parameter Override logic uses the Reportworq API Job Run command with the appropriate JSON being constructed through Turbo Integrator.

Tap or click the image to view it full screen

REST API Integration for IBM Cloud Hosted Connections#

This topic explains how to set up a Turbo Integrator process that integrates with the Reportworq REST API. This integration adds the Reportworq - Rest API Run Job Sample - Cloud - 1.0 TI process to your Planning Analytics datasource.

The main steps for setting up this integration are as follows:

To install the REST API TI process:

  1. In the Reportworq Administration interface, in the Datasources list, select the IBM Planning Analytics datasource. Datasource settings appear.
  2. Select Advanced Options.
  3. Select the link to Install REST API TI process. The Reportworq - Rest API Run Job Sample - Cloud - 1.1 TI process is added to your Planning Analytics environment.

To review and configure parameter values:

  1. In Turbo Integrator, navigate to the Reportworq - Rest API Run Job Sample - Cloud - 1.1 TI process, and open the Parameters settings.
  2. Configure default values for the following parameters:
  1. Review and configure defaults for the remaining parameters as required:

For more information about overriding Reportworq Job parameters, review instructions and samples on the Epilog tab of the Reportworq - Rest API Run Job Sample - Cloud - 1.1 TI process. The Parameter Override logic uses the Reportworq API Job Run command with the appropriate JSON being constructed through Turbo Integrator.

Tap or click the image to view it full screen

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:

Cube Monitoring#

The cube monitoring feature enables Planning Analytics developers on IBM Cloud Hosted connections to insert Reportworq Job execution requests into a cube that Reportworq monitors and processes at regular intervals.

This mechanism was originally designed to support IBM Cloud customers where a Planning Analytics Cloud-hosted TI process would not otherwise be able to create a file or call a REST endpoint on the locally hosted Reportworq Server. This limitation has since been removed. See the IMPORTANT notice below.

IMPORTANT: Cube Monitoring is a legacy solution that is now deprecated. Information about this option is provided to support existing applications. For new installations, use the REST API Integration for IBM Cloud Hosted Connections option instead.

If you have any issues with cube polling, we recommend that you upgrade to the new TI process.

To enable Cube Monitoring for Planning Analytics Cloud Connections:

  1. In the 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. Information about additional parameters for the Reportworq - Add Queued Job process:

No additional configuration in Reportworq is required after this feature is enabled for a Planning Analytics connection.

Tap or click the image to view it full screen

Feedback on this page

Comments, questions, requests, or something missing or unclear? Email us - the page you are on is filled in for you.

Email feedback on this page

Or write to support@reportworq.com directly.