Blogs / Tableau / Exploring Azure Blob and Tableau

Exploring Azure Blob and Tableau

Mar 15, 2021

SHARE

In this digital age, data is the most precious asset of every organization across the globe. Enormous volumes of data generated from various sources necessitate the usage of cloud storage solutions.

For Microsoft Azure customers, Azure Blob storage can be used to store large amounts of different types of data. To analyze and visualize this data, a powerful data visualization tool like Tableau can be used.

Currently, there is no direct Tableau connector for Azure Blob storage, but there are many indirect methods to connect with Tableau. This blog will explore the possibilities, advantages, and limitations of different strategies for bringing data from Azure Blob storage into Tableau and visualizing it.

Version information: Tableau 2020.2, Azure Data Factory version 2 (V2), Azure SQL Server 12.0.2000.8

Methods for visualizing Azure Blob data in Tableau

To visualize Azure Blob storage data in Tableau, various third-party connection pipes, ETL tools, or data warehouses with direct Tableau connectors can be used. However, Azure users are more likely to be using Azure services, for cost-effectiveness and connection advantages. We will utilize services from the Microsoft Azure stack to explore connectivity with Tableau.

We will look at the following methods:

Method 1: Azure Blob —- (via ADF) –> Azure SQL database –> Tableau

Exploring Azure Blob and Tableau

Method 2: Azure Blob –> Azure SQL warehouse (Synapse Analytics) –> Tableau

Exploring Azure Blob and Tableau

To understand the process, consider a CSV file RetailSales, stored in Azure Blob container. The goal is to bring the data in this file into Tableau for further analysis.

Exploring Azure Blob and Tableau
RetailSales CSV file stored in Azure Blob container

Step 1Connecting Azure Blob via ADF to Azure SQL database

  • Using Azure Data Factory (ADF) – a cloud-based ETL and data integration service, the file is copied from Azure Blob to Azure SQL database by creating a linked service and a pipeline involving copy activity. Detailed steps can be found in this Microsoft documentation.
Exploring Azure Blob and Tableau
Creating a pipeline with Blob source and copy activity
Exploring Azure Blob and Tableau
Adding a Pre-copy script to the pipeline to truncate destination table in Azure SQL DB

On running the copy process, a table is created in the Azure SQL database, which contains data from the CSV file RetailSales:

Exploring Azure Blob and Tableau
Table created in Azure AQL DB as a result of copy activity

Step 2Connecting from Azure SQL database to Tableau

  • This is done using the Microsoft SQL Server connector present in Tableau, provided that necessary credentials and connection details are available:
Exploring Azure Blob and Tableau
Providing connection details and credentials to Microsoft SQL Server connector in Tableau
  • Once signed in via the connector, the table containing the RetailSales data can be further analyzed:
Exploring Azure Blob and Tableau
Selecting the RetailSales table and viewing its data inside Tableau
  • With the data in Tableau, we can create different types of visualizations
Exploring Azure Blob and Tableau
Visualizing data in Tableau

Advantages

  • Customers of Azure Blob who are already using Azure SQL database and ADF would find this approach very beneficial. They can save significantly on costs since there are no third-party connectors involved.
  • Connecting from Azure Blob to the Azure SQL database using ADF is an easy-to-implement and straightforward process. Users can expect seamless connection.

Limitation

  • This process involves connecting to Azure SQL DB first and from there to Tableau. It’s not a direct connection to Tableau from Azure Blob which some third-party connectors can provide.

Method 2: Azure Blob –> Azure SQL warehouse (Synapse Analytics) –> Tableau

Let us consider the same example of the RetailSales CSV file in the Azure Blob container. We will push the data in the file to the Azure SQL warehouse, and from there, we will connect to Tableau. Connecting to Azure SQL warehouse can be done in two ways:

  1. Azure Data Factory (Copy method).
  2. Using Polybase to connect to external data (Blob storage) from Azure warehouse via external tables.

The first option is similar to the previous case, where we connected from Azure Blob to the Azure SQL database via ADF. The exact process can be followed for the Azure SQL warehouse.

The second option involves using Polybase. As given in the Microsoft Docs, Polybase enables Azure SQL warehouse to process Transact-SQL queries that read data from external data sources such as Azure Blob storage. Detailed steps are available in this Microsoft documentation.

Step 1Connecting to Azure Blob using Polybase from Azure SQL warehouse

  • The steps and the corresponding code as mentioned in the documentation are applied to the RetailSales example and given here for reference:
Exploring Azure Blob and Tableau
T-SQL queries defining external data source, external file format, and external table
Exploring Azure Blob and Tableau
Data from external table
  • Thus, the CSV file RetailSales stored in Azure Blob is now available in the Azure SQL warehouse.

Step 2Connecting to Tableau from Azure SQL warehouse

  • Again, this is done with the Microsoft SQL Server connector using the necessary credentials and connection details.
Exploring Azure Blob and Tableau
Providing connection details and credentials to Microsoft SQL Server connector in Tableau

In the Data Source screen in Tableau, the external table can be selected and its data can be viewed.

Exploring Azure Blob and Tableau
Selecting the external table and viewing its data inside Tableau
  • With the data in Tableau, we can again create different types of visualizations.
Exploring Azure Blob and Tableau
Visualizing data in Tableau

Advantages

  • The Azure SQL warehouse customers will find this method advantageous as it removes even the Azure Data Factory dependence. Since neither ADF nor any third-party connectors are used, this method can be very cost-effective.
  • Polybase provides a robust and efficient way to connect with Azure Blob, making it easier to bring data inside the Azure SQL warehouse.

Limitation

  • Again, the connection from Azure Blob to Tableau is not direct as data is pushed to Azure SQL warehouse and then to Tableau.

So far, we have explored the possibilities of bringing data via services available in the Microsoft Azure stack. In the next blog, we will look at handling various file types and real-time data in Azure Blob storage and bringing them inside Tableau.

Thank you for reading and keep checking this space for more information on the Azure-Tableau realm. For more blogs on Tableau, you can visit here.

To learn more about Visual BI’s Tableau and Microsoft Azure consulting services, you can contact us here.


Corporate HQ:
5920 Windhaven Pkwy, Plano, TX 75093

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192

solutions@visualbi.com


Copyright © Visual BI Solutions Inc.

Subscribe To Our Newsletter

Subscribe To Our Newsletter

Join our mailing list to receive the latest news and updates from our team.

You have Successfully Subscribed!

Share This Blog!

Share this blog with your friends and colleagues!