Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Self-Service Analytics Using Dremio

DZone's Guide to

Self-Service Analytics Using Dremio

Learn about performing data transformation and data analysis using Dremio and performing data visualization using Tableau.

· Big Data Zone ·
Free Resource

The open source HPCC Systems platform is a proven, easy to use solution for managing data at scale. Visit our Easy Guide to learn more about this completely free platform, test drive some code in the online Playground, and get started today.

Dremio, a self-service data platform, helps data analysts and data scientists to determine, organize, accelerate, and share any data at any time irrespective of volume, velocity, location, or structure. Dremio allows business users to access data from a variety of sources and prevents them from relying on developers.

In this blog, let's discuss data transformation and data analysis using Dremio and data visualization using Tableau.

Prerequisites

Download and install Dremio from here.

Data Description

Online retail data with different product types, product prices, and quantities sold from December 2010 to December 2011 is used as a data source.

Sample data source:sample_data_source1

Synopsis:

  • Connect different data sources with Dremio
  • Perform data transformation
  • Create virtual datasets in Dremio
  • Connect virtual datasets with BI tools
  • Visualize results in Tableau

Connecting Different Data Sources With Dremio

Different types of data sources available for performing data transformation activities are shown in the below screenshot:connecting_different_data_sources_with_dremio

To connect Amazon S3 data sources with Dremio, perform the following:

In Data Source Types page, select the Amazon S3 data source.

Connect to the Amazon S3 location as shown in the below screenshot:

Image title

Connect to the MySQL connection and provide the required credentials as shown in the below screenshot:

Image title

Connect to Network Attached Storage (NAS) as shown in the below screenshot:

Image title

Performing Data Transformation

To transform data, perform the following:

Use UNION function to merge data from three different data sources such as S3, MySQL, and NAS and load data as virtual dataset as shown in the below screenshot:

performing_data_transformationAs price values are based on single quantity, the total price needs to be calculated based on quantity.

Add Total_Price as a new field. Calculate the total price based on quantity as shown in the below diagram:

performing_data_transformation1

Perform aggregation with stock quantity and stock price based on the products in the source data as shown in the below diagram:

performing_data_transformation2

Round off the total price values to two decimal digits as shown in the below diagram:

performing_data_transformation3

Creating Virtual Datasets in Dremio

Upon successfully transforming data, create virtual datasets (View) on Dremio spaces to store the data based on the source.

The virtual dataset for purchases done by each customer is as shown below:creating_virtual_datasets_in_dremio

The virtual dataset for most quantity sold based on the product is shown in the below diagram:creating_virtual_datasets_in_dremio1

Connecting Virtual Datasets With BI Tools

To connect the virtual datasets with BI tools, export the virtual dataset in .tds format to be used with BI tools such as Tableau, Qlik Sense, and Power BI as shown in the below diagrams:connecting_virtual_datasets_with_bi_tools connecting_virtual_datasets_with_bi_tools1

Visualizing Results in Tableau

On clicking the .tds  file in Tableau, you will be redirected to Tableau for visualizing the data.

Most purchases by customers:

most_purchases_by_customers

Maximum number of products sold:

maximum_number_of_products_sold

And that's it!

Managing data at scale doesn’t have to be hard. Find out how the completely free, open source HPCC Systems platform makes it easier to update, easier to program, easier to integrate data, and easier to manage clusters. Download and get started today.

Topics:
dremio ,data transformation ,data visualization ,tableau ,big data ,data analytics ,tutorial

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}