Dazzling Dashboards and Business Intelligence with Azure Synapse Analytics Part 1: Overview

We data scientists and business intelligence (BI) specialists need the right tools to succeed. However, it’s a complex effort to pull data from multiple systems and then use a wide range of separate tools to understand and report business data.

Microsoft’s Azure Synapse Analytics gathers all the tools we need into one location. This all-in-one data hub helps us save time and focus on what we love to do: delve deep into our data to extract valuable insights.

Azure Synapse Analytics helps us easily pull and connect data from multiple sources — and types of sources. Azure Synapse Analytics streamlines this process into repeatable, scheduled pipelines. It allows us to explore the data using queries directly in the dashboard, experiment inside notebooks, then publish and share our visualizations and findings with others so they can get as excited about the data as we are.

In this series of articles, we’ll use the Contoso BI Demo Dataset for Retail Industry to demonstrate how Azure Synapse Analytics turns data into clear and helpful visualizations. We won’t go through every feature Azure Synapse Analytics offers, but we’ll reveal some of what these tools can do. We may inspire you to explore more business intelligence tools for your own data needs.

Here’s a brief overview of the steps we’ll take:

1. Import the dataset into Azure.
2. Create a workspace and ingest the data.
3. Run SQL queries and build a notebook to view and transform the data.
4. Create and publish visualizations.

Although this dataset is fictional, it represents realistic data. Our goal in this series is to answer some fundamental business questions using this data, such as:

• What are our total revenue and gross profit?
• Which stores are performing best?
• Which products are selling the most and least?
• How much revenue is each channel (store, online, catalog, or reseller) generating?
• Which promotions were the most effective?

Preparing for our Project

Before we get started, we must ensure we have all the tools we need for this project. All of these tools are either free or include a free trial. To follow along, make sure you have the following:

• Azure account: Sign up to receive 12 months of free access to popular services and $200 of Azure credit. Note that Power BI requires an account with a non-personal email address, so avoid signing up with Gmail, Hotmail, telecommunications provider email addresses, and the like. We’ll use Power BI later to create data visualizations.
• SQL Server (Express): We'll restore the dataset locally to SQL Server then migrate it to Azure.
• SQL Server Management Studio (SSMS): This tool will connect to the SQL Server and deploy the data to Azure.
• Contoso BI Demo Dataset for Retail Industry: We’ll use this dataset for this tutorial.
• Azure SQL: We’ll use this SQL server, which is fully hosted on the Azure cloud.
• Power BI (workspace): We’ll use this to create and share our dazzling dashboards and data visualizations.
• Power BI Desktop: We’ll use this desktop tool to transform the data into visualizations.
• Azure Synapse Analytics: This is Microsoft’s ultimate hub for data scientists and BI specialists.

Now that we have everything we need, we can begin our project.

Next Steps

To learn more about Azure Synapse Analytics, register to view the Hands-on Training Series for Azure Synapse Analytics and continue to the second article of this series to learn how to import and analyze business intelligence data.