Overview

This exercise demonstrates how to use a relational data source as an alternative to using a file to load to IBM Planning Analytics.

📓 Feel free to recreate the Chain from the previous tutorial (Load Data from File to IBM PA) step-by-step. To save time, the chain is copied.

Primary Learning Objective

This exercise is designed to highlight the loading of data from a data file into IBM PA.

Secondary Learning Objectives

This exercise also highlights using the HTTP BizApp, passing variables from one chain to another and light data transformations.

Prerequisites

Configure a Workspace for IBM Planning Analytics

Create an IBM PA Cloud Connection

Create a Relational Connection

Create a File Utilities Connection

Create a Tabular Transformation Connection

Supporting Template

Load Relational Data to IBM PA


Step 1: Copy the Chain

  • Locate the Chain to be copied. Here it's "Load Data to IBM PA".

  • Click the ellipses to the far-right of the Chain.

  • Select Copy from the pop-up menu.

  • Rename the newly copied Chain:

    • Click the pencil icon to the right of the name.

    • Click "Chain Settings" near the upper-right corner.

    • Change the Name parameter to Load relational data to IBM PA

  • Save the change.

Step 2: Delete the Get Data Command

  • Click on the HTTP Command "Get Data".

  • Select the "trash" icon

  • Click the "DELETE" button to confirm.

Step 3: Add Query Command to Start of Chain

  • In the Available BizApp section, select Microsoft SQL Server ➡️ Execute Query.
    💡 Use the Find tool to search for "SQL" and then search for "E".

  • Choose the Command Execute Query:

    • Click-and-drag the icon onto the pallet.

    • Then move the icon in the Chain Start circle.

    • The Chain Start will show a plus (+) icon indicating that the command may be placed at the start of the Chain.

  • Edit the Command:

    • Double-click the icon

    • Ensure the desired name and type of Connection is correct.

      • For this example, it's "Microsoft SQL Server (IBM)" and CloudRunner.

    • Set the query to: SELECT * FROM SAMPLE_DB.dbo.SIMPLE_FACT.

    • Set the delimiter to Tab.

  • Save the changes.

Step 4: Edit the Remaining Commands

  • Double-click each Command to edit:

    • "Preview Data" Command:

      • Change the Input File to be the output from "Execute Query".

        • Click in the File name parameter to delete "Not Found".

        • On the left-side panel, select the 🔽 to the right of "Execute Query".

        • Choose "Result Set (CSV)".

      • Save the change.

    • "Strip PKG_TYPE" Command:

      • Change the Input File to be the output from "Execute Query".

        • Click in the File name parameter to delete "Not Found".

        • On the left-side panel, select the 🔽 to the right of "Execute Query".

        • Choose "Result Set (CSV)".

      • Set the delimiter to tab.

      • Save the change.

    • "Smart Filter Rows" Command:

      • Set the delimiter to tab.

      • Save the change.

    • "Map Headers for PA" Command:

      • Set the delimiter to tab.

      • Save the change.

    • "Write Cube Cells" Command:

      • Set the delimiter to TSV.

      • Save the change.

Step 4: Publish & Run the Chain

Publishing is an important feature in OneCloud. Chains must be published, before they can be run. A Chain and its respective Commands will not be executed otherwise. Whether making one alteration or many, the Chain must be published every time a Chain or Command is edited.

Publish ➡️ Execute ➡️ Run Chain.

  • To publish a Chain, click "Publish" near the upper-right corner.

  • Optionally, add a Publish Chain Comment. For this example, leave it blank.

  • Once published, the "Execute" button becomes active. Click it.

  • Once executed, the "Run Chain" button is activated. Click it.

  • The Chain will animated when it is running.

  • Click on the Anaplan Upload File Command.

  • When the Chain has been run, the outputs will be available to view by clicking on a given Command and selecting "LOGS" from the right-side panel.


📚 Related Topics from this Section:

Connections

Environments

Chain Variable

Notification Integration

Workspace Variable

The HTTP BizApp

The File Utilities BizApp


Did this answer your question?