Tableau + Data360 Analyze: Better Together
Data360 Analyze allows Tableau users to:
- Blend more complex data sources
- Perform more advanced analytical routines
This article provides just one example where the complexity of the data rules can be easily assessed and managed using Data360 Analyze to complement the power of a Tableau dashboard.
Download the Example Files
The attachment at the bottom of this article contains the examples referenced including the data.
Download the file and extract to the location of your choosing. Once the files have been extracted, continue with the instructions below.
Importing the Example File
Import the example data flow (*.lna) by following the instructions in the Help section titled "Importing and exporting data flows and library nodes".
To run the examples:
- You will need to update the "InputDirectory" and "OutputDirectory" Run properties to point to the folders containing the input and output directories, which will differ depending on where you have saved the attachment.
- If your Data360 Analyze license provides you with access to the Market Basket Analysis node that is used in this example, you will need to first run the R Library Package Download node, found in the Logistics category to ensure that relevant libraries are downloaded (you will need to accept the agreement).
- The example Tableau dashboard was developed in Tableau 10.1. You will need a compatible version and valid license key.
Example: Transaction Analysis
In this example, we want to display our sales transactions in a Tableau dashboard:
The data required for this dashboard involves some of the following complexities:
- There are four (4) data sources of different file types that we will need to read in: CSV, tab-delimited, XML and JSON.
Data360 Analyze has native connectors for these file types. - Sales transactions are multi-currency: USD, GBP and EUR. The purchased amounts in EUR use a comma to denote decimals whereas amounts in GBP and USD use a period. In order to have consistency in the data, we will need to replace the comma with a period.
This is easily done in a Tranform node by applying the Python replace() function (you could alternatively use the Python expression in a Calculate Fields node). - Further, to ensure that total purchases sales amount can be summed appropriately, we will need to convert non-USD based transactions into USD using the relevant currency exchange rate on that date.
We can use the Lookup node to reference the Historical Currency Rates data and then apply a simple multiplication formula to apply the currency exchange rate to convert non-USD transactions to USD (see the Lookup node's processRecords script). - Transaction data need to be joined to the customer data on a customer identifier - we will need to output any transactions that don't have a customer identifier for further investigation.
Using the Merge node to join the Transactions data to the Customer data will automatically give us the transactions where no customer record was matched. These records can be easily output to Excel using the Output Excel node. There are many other output nodes in Data360 Analyze that can be used. - The customer data includes the postal code/ zip code, which will be needed for mapping the sales transactions in Tableau. For the customers in the UK the full postal code is provided, however, Tableau only recognizes the first segment of the five- to seven-character UK postcode so we will need to extract the first segment. (Tableau: Prepare Your Geographic Fields).
We can use the Python split() function in a Transform node to divide the UK postal codes by the space character and then use the Python list indexing notation [0] to extract the first element in the list. - Transaction data will need to be enriched with the Product data as well so that we have more descriptive product references instead of just an ID number.
This is easily done using a Lookup node. - Market Basket analysis is to be performed on the purchased products to see the products that are purchased together.
For those with the Advanced Analytics nodes enabled by their license, there is a Market Basket Analysis node that can be used to perform this analysis.
The blue text above describes how these complexities may be managed within Data360 Analyze - step through the data flow and follow the logic as well as the data at each step. There are comments within the logic to help provide some guidance.
Please comment below if you have any questions!
Comments
2 comments
We are running version 3.4.3 and when I import the lna file. It errors and the reason is the Data Converter (Superseded) node is not available. What should replace this node in the dataflow.
Forget the question. I did not have superseded nodes showing. It does work. Thank you
Please sign in to leave a comment.