Skip to main content
Adaptive Insights
Knowledge and Support - Adaptive Insights

Exporting Planning Data with the Scripted Planning Exporter

You can use the Scripted Planning Exporter (SPE), a customized Pentaho plugin, to export Planning data to external systems. (For additional information on Adaptive’s use of Pentaho, see Setting Up a Scripted Data Source.)

 

Understanding the SPE

The SPE works by making a call to the Adaptive Planning export REST API and then stores the results of that API call in a file you specify. The SPE supports output to CSV and XML file formats. (Not all export data can be transformed into CSV format.)

The SPE is a plugin bundled with the Adaptive Data Agent starting with version v51.17.0734. You can verify which version of the Adaptive Data Agent Service Manager you have by opening the program. The version number appears on the information in the About tab. (For information on installing the Adaptive Data Agent, see Installing the Adap­tive Data Agent Service Manager.) When the SPE is installed, it appears in the Input folder on the Design tab of Spoon, as shown below.

The terminology around the Scripted Planning Exporter Adapter may be slightly confusing. Because the data flows from Planning to the script and then on to some exter­nal destination, it is a data export from Planning and a data import into the external desti­nation. However, looked at from Spoon, the SPE is an input step, because it provides input into the script. This is why it is labeled "Adaptive Planning Input" in Spoon.

You can start the SPE directly from within the Spoon environment or remotely by including it in a scripter loader. The general process for using the SPE is to write a script that cre­ates an XML request in the format accepted by the Planning server, passes that to the SPE, and then processes the results. Below is a sample script that shows the minimum requirements for an SPE script:

      Scripted Planning Exporter
-------------------------------------------
Input:
      requestXMLString: request in XML format
      targetResponseFormat: CSV or XML
      targetFolder: target folder to store result file generated by
         this plugin

Output: The type of output fields is String, except
    requestSuccess, which is boolean
      requestSuccess: Boolean, ‘Y’ or ‘N’
      planningMsgs: concatenate Planning messages in a string,
    delimited by ‘###’
      responseFileName: complete path of the response file name
      fieldNames: column name in CSV header, or NULL if
    targetResponseFormat is XML
      numberRecReturned: String, number of records returned by
    Planning, or NULL if targetResponseFormat is XML

 Best practices for this script include:

  • All the input field names are case-sensitive. Be sure to define them in the Kettle script accordingly.
  • requestXMLString: Each export API has its own request XML format. See the Adaptive Planning REST API for additional information.
  • targetResponseFormat: As of API v11, only the payload of the 'exportData' API can be transformed into CSV format. All other exports must be XML.
  • TargetFolder: In general, you want the Restrict Runner Access to Files checkbox to be unchecked in the Agent Service Manager , since the target folder can be anywhere in the file system.
  • responseFileName: The name of the response file is in the following format: <tar­getFolder>_ApiResponse_<exportMethod>_<UUID>.<targetFormat>; for example, C:\pdiTemp\ApResponse_exportData_83980a17b3dc40c48282483cb9ea5e96.csv
  • requestSuccess: If the export request goes through on the Planning server side, the requestSuccess has a value of true ('Y'). If the export request fails on the results of a call to planning, the SPE throws an exception with the error messages returned by the Planning server.

The general workflow for extracting the information from Planning and sending it to an out­put file appears below.

Using the Planning Export APIs from the Kettle Script

The SPE uses Adaptive Planning APIs v11 or greater. You can use the following APIs with the SPE:

  • customReportValues : returns a set of data for the requested report criteria in the requested instance
  • exportData : retrieve a set of values from a specified version

For more information on Adaptive APIs, visit the Adaptive API.

Configuring the SPE Kettle Plugin

To configure the SPE Kettle plugin, follow these steps:

The Adaptive Planning Input Step dialog box appears (shown below).

  1. Click Adaptive Planning Input in the Spoon Design tab, as shown earlier.

  2. Enter the SPE’s information:

  • Active Agent: Populated automatically by the SPE.
  • Instance Host: populated automatically by the SPE.
  • Instance Code: Populated automatically by the SPE.
  • Login: Value of the login context parameter.
  • Login Password: Value of the password context parameter.
  • Locale: Defaults to en_US.
  1. Click OK.

You need to create a script that defines the inputs and outputs for the SPE operation. (See the sample script earlier.)

Configuring the SPE within Adaptive Integration’s Cloud UI

The general process for creating a scripted loader with Integration’s cloud UI is:

  • create a scripted loader
  • select an entry point
  • configure required parameters

Start by creating a new scripted loader. For information on how to do this, see Creating Scripted Loaders. You also need to select an entry point in the Entry Point drop-down list. The entry point is set to the name of the script to be executed when the scripted loader is executed.

Finally, you must map and expose the local and shared context parameters defined in the scripted loader as environment variables in the Kettle/Spoon environment. For example, if a context parameter called "login" is defined in the loader setup screen in the Parameters section, it is exposed as the variable "${login}" in the Kettle environment. The image below shows the definition of two context parameters in a scripted loader.

The next image shows references in the SPE to both of the corresponding Kettle variables defined above.

You can update the Kettle variables at design time with the current default context param­eter values by starting the Adaptive Script Editor and triggering a refresh by clicking Refresh Configuration.

  • Was this article helpful?