In this exercise, we will retrieve customer billing records and blend with additional address details to generate a full billing address for each customer.

Primary Learning Objective

To highlight how the Tabular Transformation Advanced Query Command can be used to blend data from multiple data sources.

Secondary Learning Objectives

--

Prerequisites

Supporting Template

Blend Data


Step 1: Create a Chain

  • From the Sample Workspace for Transformations Workspace, select the DEV Environment.

  • Add a new Chain.

    • Name the Chain, Transformation QS - Blend Data.

  • Save the Chain.


Step 2: Retrieve Data from a Source System

Use the HTTP BizApp to illustrate how to retrieve data from a data source for which a OneCloud Integration Studio BizApp does not exist.

  • Add a GET Command from the HTTP BizApp to the Start node.

  • Configure the Command with the following:

Name

GET - Customer Billing

User Name

<leave blank>

Password

<leave blank>

CA Certificate

<leave blank>

Certificate

<leave blank>

Certificate Private Key

<leave blank>

Show Response

Checked

URL

customer Workspace Variable

Query string

<leave blank>

Content type

text/csv

Response

<leave blank>

  • Save the Command


Step 3: Preview the Customer Billing Data

Preview the data structure of the source system in order to understand the layout and allow us to define future transformations more easily.

  • Add a List File Content Command from the File Utilities BizApp to the Chain.

  • Connect the Start Node (HTTP - Get Customer Billing) to the List File Content Command.

  • Configure the Command with the following:

Name

List File Content - Customer Billing

File Name

Response Output from the Get - Customer Billing Command

Preview Lines

10

Offset

0

Save the Command.


Step 4: Retrieve zip code list

Use the HTTP BizApp to retrieve the list of all zip codes and the city and state to which they are associated.

  • Add a GET Command from the HTTP BizApp to the Start node.

  • Configure the Command with the following:

Name

GET - Zip Codes

User Name

<leave blank>

Password

<leave blank>

CA Certificate

<leave blank>

Certificate

<leave blank>

Certificate Private Key

<leave blank>

Show Response

Checked

URL

zipcodes Workspace Variable

Query string

<leave blank>

Content type

text/csv

Response

<leave blank>

  • Save the Command


Step 5: Blend data

Finally, the customer billing information is enriched to create full billing address including the street address, city, state, and zip code. This is enabled through the use of a JOIN query. The query provided illustrates how to join data from different data sets based on a common value (zip code) across the different data sets. The query also illustrates the use of several SQLite functions - Substring (SUBSTR), Concatenate (||), and length - that can be used to format the output of the query.

  • Add an Advanced Query Command from the Tabular Transformation BizApp to the Chain.

  • Connect the Get - Zip Codes Command to the Advanced Query Command.

  • Edit the Command.

  • Name the Command: Advanced Query - Blend Data.

  • In the Tables section of the Command parameters

    • Click the Add button. This will create a second set of table parameters to specify.

    • In the first set of table parameters, select the Response Output from the GET - Customer Billing Command for the File parameter. Enter customers in the Table Name parameter.

    • In the second set of table parameters, select the Response Output from the GET - Zip Codes Command for the File parameter. Enter zip_codes in the Table Name parameter.

  • Enter the below for the Query parameter:

    Select 
    customers.first_name
    ,customers.last_name
    ,customers.street_address
    ,zip_codes.default_city
    ,zip_codes.default_state
    ,customers.zip as unformatted_zip
    ,Substr('00000' || customers.zip,length(customers.zip)+1,5) as formatted_zip_code
    from customers
    join zip_codes
    on customers.zip=zip_codes.zip_code

  • The Customer Billing and Zip Code data is comma-delimited so we leave the Input Delimiter as Comma. The Output Delimiter can be left as Comma as well.

  • Check the Preview results option.

  • Save the Command.


Step 6: Test the Exercise

Now that the Chain is complete, test the result.

  • Publish the Chain.

  • Click Execute and then select Run Chain.

  • Once the Chain has completed, click the Advanced Query node.

    • Click the Logs tab and confirm the data per the image below


📚 Topics covered in this exercise:

File Utilities

HTTP

Tabular Transformation


Did this answer your question?