With a OneCloud Workspace setup and Connections to source data, create a Chain.

The data typically will be provided in an open standard format such as a flat-file. In this example, we have provided a link to download the sample data that corresponds to the sample IBM PA TM1 application.

Step 1: Create a Chain

  • If needed, navigate to the "DEV" Environment of the "Sample Workspace for IBM PA":

    • Click "Workspaces", scroll to the appropriate Workspace, then click on the desired Environment.

    • Alternatively, click "Build" and the Chains canvas will appear. If necessary, switch to the desired Environment.

  • Click the green (+) icon near the center of the canvas to "Add a New Chain".

  • Name the Chain "Load Data to IBM PA".

  • Add a Chain Variable by selecting the blue (+) button

    • Name the variable "base-url".

      • Set the variable's value to: https://s3.amazonaws.com/onecloud-demo
        💡 Copy the above URL and paste into the variable field.

  • Near the top of the window, click the message icon ✉️ to add a Notification.

    • If necessary, click the button to add a new Notification.

    • Add an Email Notification by clicking the appropriate button.

      • Select the condition as "Success".

      • Add recipients by selecting from the drop-down list.

  • Save the Chain.

📓 To concatenate strings in a OneCloud field, be sure to have the field selected and simply add the characters or variables as required.

Step 2: Add a HTTP Command to Retrieve Data

Add the first Command.

  • In the Available BizApp section, select HTTP ➡️ GET.
    💡 Use the Find tool to search for "HTTP" and then search for "GET".

  • Choose the Command GET:

    🏆 Double-click the icon to auto-load the Command into the Chain Start circle.

    • 📓 Alternatively, 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.

  • Rename the Command "Get Data", by double-clicking the icon or hovering over it and selecting the pencil-icon.

    • Under "Basic Info", type "Get Data".

  • Scroll down to input the URL to be used in the GET Command.

  • Set the URL parameter by:

    • Choosing the Chain Variable on the left-side panel, then "base-url"

    • Type "/"

    • Select the Workspace Variable on the left-side panel, then choose "data-type"

    • Type "/"

    • Add the filename SIMPLE_FACT.csv

  • Save the command.

📓 To concatenate strings in a OneCloud field, be sure to have the field selected and simply add the characters or variables as required.

Step 3: Add a Command to Preview the Data

Add a second command to the Chain. Add the Command List File Content to preview the data.

  • Select the File Utilities ➡️ List File Content Command.
    💡 Use the Find tool to search for "File Utilities" and "List File Content".

  • Connect the "Get Data" Command to the "List File Content" Command.

    • Click and hold the 🔽 at the bottom of the "Get" Command, then drag to the 🔽 at the top of the "List File Content" Command.

    • Edit the "List File Content" Command.

    • Rename the Command: "Preview Data".

    • Scroll down to set the File Name to Response.

      • From the left-side panel, choose "Get Data".

      • Click "Response".

    • Set the Preview Lines parameter to "10".

  • Save the Command.

⚠️ If the warning below is displayed when editing the List File Content Command, back out to Connections (left-side panel) and enable the Environment for the File Utilities Connection. If one doesn't exist, create it using the previous Connection tutorials as a guide.

Step 4: Strip the PKG_TYPE Column

In this example, the target cube in IBM Planning Analytics has the following dimensions: Year, Measures, Market, Product, and Scenario. However, the source data contains an extra column "PKG_TYPE". To allow the source data to be directly streamed to the target cube, this sixth column needs to be stripped prior to the load. OneCloud has various lightweight transformation functions to perform this function.

Add a third Command to the Chain to strip the extra column. Add the Column Filter Command.

  • Select Tabular Transformation ➡️ Transform ➡️ Column Filter.
    💡 Use the Find tool to search for "Tabular" and "Column". Transform is a subcategory of the BizApp.

  • Connect the "Preview Data" Command to the "Column Filter" Command.

  • Edit the "Column Filter" Command.

    • Rename the Command: "Strip PKG_TYPE".

    • Set the Input File to be "Response" of the Get Command

      • From the left-side panel, click the 🔽 to the right of "Get Data"

    • Select "Preview Results".

    • If needed, set the Delimiter to "Comma".

    • If needed, set the Pattern Type to "Exact".

    • Establish the Pattern as "PKG_TYPE".

  • Save the Command.

⚠️ If the warning below is displayed when editing the List File Content Command, back out to Connections (left-side panel) and enable the Environment for the File Utilities Connection. If one doesn't exist, create it using the previous Connection tutorials as a guide.

Step 5: Map Headers

Add a fourth Command to the Chain to map headers so that the retrieved data can be directly streamed to a target IBM Planning Analytics cube.

  • Select Tabular Transformation ➡️ Transform ➡️ Map Headers.
    💡 Use the Find tool to search for "Tabular" and "Map". Transform is a subcategory of the BizApp.

  • Connect the "Column Filter" Command to the "Map Headers" Command.

  • Edit the "Column Filter" Command.

    • Rename the Command: "Map Headers for PA".

    • Set the Input File to be "Column Filter Output" of the "Strip PKG_TYPE" Command

      • From the left-side panel, click the 🔽 to the right of "Strip PKG_TYPE"

    • Select "Preview Results".

    • If needed, set the Delimiter to "Comma".

    • If needed, set the Pattern Type to "Exact".

    • For the "Input Headers" Section, type the following (hitting enter/return after each):

      • YEAR

      • MEASURES

      • PRODUCT

      • MARKET

      • SCENARIO

      • DATA

    • For the "Output Headers" Section, type the following (hitting enter/return after each):

      • [Year][Year]

      • [Measures][Measures]

      • [Product][Product]

      • [Market][Market]

      • [Scenario][Scenario]

      • Value

  • Save the Command.

Step 6: Stream Data to IBM Planning Analytics

Add a command to directly stream the transformed data to IBM Planning Analytics.

⚠️ Sample Basic Cube - For this next step, you will require an IBM Planning Analytics cube that is dimensionalized with the "Sample Basic" structure. In a prior tutorial, a Connection was created for the source text file. On this page, there are links to the dimensional metadata so you can build your own cube.

Year Dimension: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec

Measures Dimension: Sales, COGS, Marketing, Payroll, Misc

Market Dimension: New York, Massachusetts, Florida, Connecticut, New Hampshire, California, Oregon, Washington, Utah

Product Dimension: Cola, Diet Cola, Caffeine Free Cola, Old Fashioned, Diet Root Beer, Sasparilla, Birch Beer, Dark Cream, Vanilla Cream

Scenario Dimension: Actual, Budget

  • Select IBM Planning Analytics ➡️ Data Operations ➡️ Write Cube Cells.
    💡 Use the Find tool to search for "IBM" and "Write". Data Operations is a subcategory of the BizApp.

  • Connect the "Map Headers" Command to the "Write Cube Cells" Command.

  • Edit the "Column Filter" Command.

    • Depending on your IBM PA Environment, set the "Cube Name" parameter to the cube that hosts the Sample Basic dimensional structure.

      • For this example, it's "PlanSamp".

    • If needed, set the File format parameter to "CSV (Comma delimited)".

    • Set the CSV to be "Map Headers Output" of the "Map Headers for IBM PA" Command.

      • From the left-side panel, click the 🔽 to the right of "Map Headers for IBM PA".

  • Save the changes.

Step 7: 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.

⚠️ This Chain fails. What now? Click the Command that failed ("Write Cube Cells") and select "LOGS" to get an idea of what may have caused the failure. Then proceed to Step 8.

Step 8: Data Preparation for a Successful Load

There is a chance that the source data needs to be prepped and transformed before it is loaded to IBM Planning Analytics. When directly loading data to a target Planning Analytics cube, there is a requirement that the data is dimensionally correct with corresponding nodes in the target cube. If there are one or more issues, then the write to the IBM Planning Analytics cube will fail with a warning like in the following example:

To address this issue, OneCloud has capabilities to transform the data. In this step we will use the Smart Filter in the CSV Transformation BizApp to filter out records that have the Measures Profit % and Margin %.

  • Add the command Tabular Transformation ➡️ Filter ➡️ Smart Filter by clicking on the (+) below the command group.

  • Confirm the appropriate Runner is set. For this example it's "CloudRunner".

  • Select the Input File to be the variable Strip Pkg Type ➡️ Column Filter Output.

  • Set the primary filter operator to be Or.

  • Set the Text Filter operator to be Or.

  • Add a filter and set the Column name to be MEASURES, the Condition to be Contains and Compare Text to be %.

  • To skip over the Number and Date filters, set them each to be Or.

  • To remove all records that match the filter criteria, disable the Inverse option.

  • Save the Command.

📓 The Column Name was entered as "Measures", despite being listed as MEASURES. Parameters can be case sensitive or insensitive. If unedited, the Chain will fail ... again. Be mindful of case when setting filters.

Step 9: Reorder the Commands

The prior step added a new Command in a Command Group to filter out records that are not needed in the load. The command order, however, needs to be adjusted so that the Smart Filter command occurs before the Map Headers Command.

Additionally, after the commands are rearranged, the input of Map Headers need to be realigned to be the output of the Smart Filters. It is easy to "re-wire" the chain by following these steps.

  • Two (2) links need to be deleted.

    • The links to delete are between:

      • "Strip PKG_TYPE" and "Map Headers for PA" Commands

      • "Write Cube Cells" and "Smart Filter Rows" Commands

    • Double-click the link line.

    • Click "Delete".

  • Make space to rearrange the Commands by click-hold-and-dragging the Commands.

    • "Smart Filter Rows" needs to be move up in the Chain.

    • "Write Cube Cells" and "Map Headers for PA" need to be moved down.

  • Drag "Smart Filter Rows" before "Map Headers for PA".

  • Connect "Smart Filter Rows" to "Strip PKG_TYPE" and "Map Headers for PA"

  • Edit the "Map Headers for PA" Command:

    • Delete the current Input File:

      • Click in the field and press delete/backspace.

    • Change the Input File to be the output of "Smart Filters Row":

      • On the left-side panel select the 🔽 to the right of "Smart Filters Row".

      • Select "".

  • Save the changes.

  • Publish and Run the Chain as before.

Step 10: Publish & Run the Chain (Again)

Publish ➡️ Execute ➡️ Run Chain.

  • 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

The Tabular Transformation BizApp

IBM Planning Analytics TM1 BizApp


Did this answer your question?