6/10/2017 - Tables and Pivot Tables in Flow

Overview


In this post, we'll build a six-step workflow that produces Pivot Table and Table results. It shows how to load data, use expressions to derive time-period values from a date field, build a hypercube using those time-period values as dimensions and, finally, how to create and view pivot table and table results using the hypercube.

Getting Started

Add the Sample Data


After logging into Flow, you'll first need to add the "Product Sales" sample dataset to your Flow account. Click on the down arrow down arrow button in the top menu bar then click on the sample data sample data icon to open the Add Sample Dataset dialog. Click the ADD link next to the "Product Sales" entry. The sample data will be added to your account, see below.

add sample data set
sample data set
sample data set

Open Cloud Connect


Flow provides an integrated development environment for building analytics-oriented workflows called Cloud Connect. To launch Cloud Connect, open the Workflows menu in the left sidebar of the Flow portal then click the Launch Cloud Connect button as shown to the right.

Depending upon your browser, you may need to add an extension to enable ClickOnce and launch Cloud Connect. Click on the View Cloud Connect Requirements link beneath the Launch Cloud Connect button to learn the requirements for your particular browser. If this is your first time using Cloud Connect, it may require a minute or so to install. Once the installation has completed, the application will open automatically.

workflow menu
workflow menu

Cloud Connect

  • 1. Top Menu
  • 2. Workflow Library
  • 3. Workflow Action Editor Tab
  • 4. Workflow Actions Menu
  • 5. Working Data View

Add a New Workflow


To get started, we'll need to add a new workflow. From the Cloud Connect top menu, click the add workflow button. The Add New Workflow dialog will appear as shown on the right.

Enter the workflow name then click OK. The new workflow is added to the Workflows list on the left-side of your screen. To open the workflow for editing, simply double click its name. Alternatively, you can right-click the name and then select Open from the context menu.

add pivot table flow
open pivot table flow

Add a Load Data Workflow Step


Add a Load Dataset step by clicking on the actions menu menu and selecting Load Dataset from the drop-down menu, the Load Dataset Dialog will display. Select the "Product Sales" dataset from the drop-down list then click OK to add the Load Dataset workflow step.

Next, click the run button button on the Workflow Menu then pressEnter or click Yes in response to the Run All dialog. The Load Dataset action will run and import the Product Sales data. The data will be displayed in the Workind Data at the botton of your screen.

load dataset workflow action

Creating Pivot Table and Table Results

Add Date Expressions for Year, Quarter, and Year-Quarter


We are going to build a tables that group our product sales data by Country, Year, and Segment as shown to the right. Our product sales data has a Date data point but no data points for year, quarter, or year-quarter. Therefore, we need to extract these values from data points in the Date column.

To extract the year, quarter, and year-quarter from each Date column data point, we will add Expression actions to evaluate each Date data point, compute the required result, and save the result to new data point values.

tabular report menu item
expression builder menu item

Open the Expression Builder by clicking on the actions menu menu and selecting Expression Builder from the drop-down menu, the Expression Builder dialog will display.

Add Expression Steps:

  • Select the Product Sales dataset from the Collection drop-down list in Expression Builder's Select Working Data box.
  • In the Select Expression and Operation Type box, Select the Date expression type from theExpression Type drop-down list.
  • Next, select the Year operation from the Operation drop-down list.
  • Within the Build Expression box, under Input 1, use the Datapoint drop-down list to choose the Date datapoint.
  • In the Result box, enter a name for the expression result, in this case: Year
  • Click the Add Expression button to add the expression to the list of expressions that will be evaluated when this workflow step runs.
  • Click OK to add the expression action to the Create Grouped Report workflow.
table report date expressions

Add a Build Hypercube Step


Flow builds tables and pivot tables from hypercubes. A hypercube is a specialized data container used to group and summarize multidimensional data.

For more information on working with multi-dimensional data in flow see: A Basic Introduction to Multidimensional Analysis Using Flow.

Build Hypercube Steps

  • From the Working Data drop-down list, select the Product Sales dataset, a check box list of possible dimensions will be displayed.
  • Add dimensions for Segment, Country, and Year by checking the box next to each data point name.
  • Enter Grouped Report Hypercube in the Hypercube Name text box.
  • Finally, click OK to add the Build Hypercube workflow step.

Now run the workflow to view the results. Click the run button button in the workflow menu bar and answer Yes to the prompt. When the workflow completes, the working data tab will display a flattened view of hypercube, see below.

hypercube display

Add Hypercube Expressions to Summarize Data


At this point, we've loaded our Product Sales dataset, added an expressions to extract the year, quarter, and year-quarter summary dimensions, and created a hypercube using these dimensions from which we can generate our tables.

We now want to compute summary values for the Cost, Discount, Sales, and Profit columns for display in our tables. Flow provides an extensive library of functions and operations for computing summary values across hypercube dimensions. We are going to add another Expression Evaluation step that will sum these values.

From the Actions drop-down menu, select Expression Builder, the Expression Builder dialog appear as shown.

  • Select the Grouped Report Hypercube from the Collection drop-down list in the Expression Builder's Select Working Data box.
  • Check the Hypercube check box next to the Collection drop-down list.
  • In the Select Expression and Operation Type box, Select the Stat expression type from the Expression Type drop-down list.
  • Next, select the Sum operation from the Operation drop-down list.
  • Within the Build Expression box, under Input 1, use the Datapoint drop-down list to choose the Cost datapoint.
  • In the Result box, enter a name for the expression result, in this case: Total Cost
  • Click the Add Expression button to add the expression to the list of expressions that will be evaluated when this workflow step runs.
table summary values expression

When you are finished, click OK. The Expression Evaluation workflow step will be added to the workflow steps list.

Run the workflow again and view the results. Click the run button button in the workflow menu bar and answer Yes to the prompt. When the workflow completes, the working data tab will display a flattened view of hypercube which now includes our expression evaluation results.

Add Pivot Table Result Step


Tables summarize hypercube measures by two or more hypercube dimensions. A pivot table summarizes a measure defined by the combination of two dimensions. Each each cell in pivot table represents a unique pair of dimension labels. A table summarizes one or more measures by nesting dimensions in a specific order.

We'll first add a Pivot Table result. From the Results menu, select the Pivot Table menu item as shown to the right, the Pivot Table Result dialog will appear, see below.

pivot table menu item

Add Pivot Table Steps

  • From the Pivot Table Result dialog Hypercube drop-down list, select the Table Hypercube.
  • In the Table Data box select Year Quarter from the Column Data drop-down list.
  • Next, select Country from the Row Data drop-down list.
  • Enter the Result Name, Title, and Description
  • Finally, click OK to add the Pivot Table Result workflow step.
pivot table dialog

Run the Workflow and View the Table


Click the run button button on Workflow Menu. Press Enter or click Yes in response to the Run All dialog. The workflow will load the data, create the report, and save the report result to your Flow account. To view the report, go to the Flow portal and and open the Reports & Charts section under the Results menu. Click on the "Total Sales by Country and Year-Quarter" table name to display the the pivot table.

reports and charts menu
pivot table display

Pivot Table Drill Down

Click any cell value to drill down on the underlying values.

pivot table drill down

Pivot Table Chart

Click the chart button display chart icon to chart the pivot table.

pivot table chart display

Add Table Result Step


Next, we'll first add a Table result. From the Results menu, select the Table menu item as shown to the right, the Table Result dialog will appear, see below.

table result menu item

Add Table Steps

  • From the Hypercube drop-down list, select Table Hypercube.
  • From the Row Groups drop-down list, select Country, Year, and Segment in that order.
  • From the Column Data check box list, check the Total Cost, Total Discountss, Total Sales, and Total Profit boxes.
  • Enter the Result Name, Title, and (optionally) Description as shown.
  • Finally, click OK to add the Table Result workflow step.
run all dialog

Run the Workflow and View the Table


Click the run button button on Workflow Menu. Press Enter or click Yes in response to the Run All dialog. The workflow will load the data, create the report, and save the report result to your Flow account. To view the report, go to the Flow portal and and open the Reports & Charts section under the Results menu. Click on the "US Retail Sales Report" report name to display the report.

reports and charts table
table report result

Summary


In this post, we built a six-step workflow to create Pivot Table and Table results. To create these results, we first added a Load Dataset workflow action. This action imports the required data into the workflow's working data memory and builds the data point definitions needed to produce the tables. After running the Load Dataset action, we added a Build Expression step to extract the year, quarter, and year-quarter from the Date column values. We then built a hypercube with dimensions Segment, Country, and Year, Quarter, and Year-Quarter. Next, we added hypercube expressions to summarize the Cost, Discounts, Sales, and Profit values across each of our selected dimensions. We then added a workflow step to generate a Pivot Table that displays Total Sales by Country and Year-Quarter, and viewed the pivot table result in the Flow portal. Finally, we employed a Table result step to produce a table that displays summary values grouped by Country, Year, and Segment.