Lesson 2. Data Export

KNIME-Data-Wranglers-L2-Lesson2

KNIME Analytics Platform provides different deployment options: exporting data in different file formats, writing models in PMML format, integrating an external reporting tool, creating a REST API, and building an Analytics Application accessible via the KNIME WebPortal. 

In this lesson we show you two common ways of handling the results of your project: exporting them as a data file for later use, and sending them to an external reporting tool.

This lesson includes exercises, and the data files, solution workflows, and prebuilt, empty exercise workflows with instructions are available in the L2-DW KNIME Analytics Platform for Data Wranglers - Advanced folder in the E-Learning repository on the KNIME Hub.

Write to a File

Different file formats have their own writer nodes. Here we introduce one of them, the CSV Writer node.

 

Exercise: Write to a File

1) Read the adult.csv file available in the data folder on the KNIME Hub. The data are provided by the UCI Machine Learning Repository.

2) Calculate the total number of rows and average age for all women with income >50K per year

3) Write the resulting table as a CSV file into the data folder using the knime:// protocol

Empty exercise workflow 03_Write_Data_to_File in the KNIME Hub course repository.

 

Solution: Write to a File

Download the adult.csv file from the data folder on the KNIME Hub, and read the file with the File Reader node. Use, for example, the Rule-based Row Filter node with the following expression to filter the data:

$sex$ = "Female" AND $income$ = ">50K" => TRUE

Use the GroupBy node to calculate the average age and row count in the filtered data. 

Export the aggregated table with the CSV Writer node. Start the file path with “knime://”, and continue with the path from the currently active workflow to the data file, for example “knime.workflow/../../data/women_aggregated.csv”.

Solution workflow 03_Write_Data_to_File - Solution in the KNIME Hub course repository.

 

Export Data to a Report

KNIME Analytics Platform does not offer a native reporting solution, but rather it integrates with a number of commercial reporting platforms, like BIRT, Tableau, Spotfire, and Power BI.

Export Data into a BIRT Report

BIRT (Business Intelligence Reporting Tool) is a reporting solution with an open source component. The open source component is integrated within KNIME Analytics Platform via its KNIME Report Designer extension.

Reference workflows are available in the Examples/05_Reporting/01_BIRT repository on the KNIME Hub. 

Exercise: Export Data into a BIRT Report

1) Read the adult.csv file available in the data folder on the KNIME Hub. The data are provided by the UCI Machine Learning Repository.

2) Create a pivot table, by workclass, that counts the number of records for each income class

3) Sort the pivot table by workclass

4) Send the data to BIRT for use in a report

5) In the BIRT Report editor, use a grid to layout the following elements:

- A report title

- A formatted pivot table

BONUS) Create a bar chart of counts with workclass on the x-axis, and income classes in two different series on the y-axis

Empty exercise workflow 04_Export_to_BIRT_Report in the KNIME Hub course repository.

 

Solution: Export Data into a BIRT Report

1) Download the adult.csv file from the data folder on the KNIME Hub, and read the data with the File Reader node

2-3) Create the pivot table with the Pivoting node. Select “workclass” as the group column, and “income” as the pivot column. Apply the aggregation method “Count” to any column. Sort the pivot table with the Sorter node. Select “workclass” as the sorting column.

4-5) Use the Data to Report node to send the pivot table to a BIRT report. Open the report editor. Open the Master Page tab, and drag a Grid into the Header field. Drag a Label into the Grid, and write the title of your report in the field that activates. Open the Layout tab, and drag again a Grid into the report. Drag a Table into a Grid cell. In the dialog that opens, select your pivot table as the dataset.

BONUS) Use the Bar Chart node, select “workclass” as the category column and both income columns as the y-axis columns. Apply sum or average as the aggregation method. Check “Generate image” on top of the configuration dialog. Connect the node’s image output to an Image to Report node. In the report editor, drag an Image into a Grid cell. In the dialog that opens, select “Dynamic image” > “Select Image Data…”. In the next dialog that opens, select the bar chart image as the dataset, and check the “Image” item in the list below.


Solution workflow 04_Export_to_BIRT_Report - Solution in the KNIME Hub course repository.

Export Data into a Tableau Report

Tableau is a popular reporting solution. It’s neither free, nor open source. If you have a Tableau license, you can use the nodes from the KNIME Tableau integration to export data directly into a Tableau TDE file or into a Tableau Server.

 

Reference workflows are available in the Examples/05_Reporting/02_Tableau on the KNIME Hub.

LinkedInTwitterShare