Building Lakehouse Dashboards Using Azure Synapse Analytics Serverless Pool

By:   |   Updated: 2022-07-08   |   Comments (2)   |   Related: > Azure Synapse Analytics


Please do not scroll away - stay informed.
Dear Database Professional,

Did you know that MSSQLTips.com publishes new SQL Server content on a daily basis as well as offers free webinars and tutorials?

We know your day is hectic and you don't necessarily have time to research new topics and solutions every day, but we can keep you informed.

Take 30 seconds to register for our newsletter and look for free educational content to help you grow your career. >> REGISTER HERE <<

Thank you,
Greg Robidoux and Jeremy Kadlec (MSSQLTips.com Co-Founders)
Problem

This tip is part of the series of posts dedicated to the building of end-to-end Lakehouse solutions leveraging Azure Synapse Analytics. In the previous posts, we've explored ways to build ETL pipelines to ingest, transform and load data into Lakehouse. In this tip, we'll be exploring the ways to build reporting based on the Lakehouse data.

Solution

The Lakehouse concept we've been discussing so far is based on Delta Lake technology. You can query Delta Lake tables directly using their storage locations. Here's an example query that reads from the SalesOrderHeader Delta table:

select * from delta.`/delta/silver/SalesOrderHeader/`    

Building semantic layer on the Lakehouse

Alternatively, you can create a semantic layer over Delta tables that includes creating databases, external tables, views, etc. You can leverage Spark pools or Synapse serverless to create a semantic layer.

In this tip, I'm going to use Synapse serverless to build Lakehouse objects that can further be queried by various reporting tools.

The semantic layer objects can be created using the T-SQL language. Synapse GUI can also be used to generate certain commands. I will demonstrate usage of the Synapse GUI methods here, as that's the easiest method for beginners, in my view. I'll create the following Delta objects:

  • Database
  • Schema
  • External table

Let's open Synapse Studio, select the Data tab, then the Workspace tab, click Add button then select the SQL database command, as follows:

Synapse Studio

Figure 1

Select the Serverless option, enter the database name and click Create button, as follows:

create sql database

Figure 2

Once created, you'll see the new EDW database object under the workspace tab:

new database

Figure 3

Next select the Schemas command, followed by the New schema command, as follows:

create schema

Figure 4

This will add a template script to create a schema. Let's tweak it to create two schemas, as follows:

CREATE SCHEMA [dim];
GO
CREATE SCHEMA [fact];
GO

The table creation requires a few steps like creating file format, data source, etc. You can follow the steps similar to creating schemas using Synapse GUI, in which case you'll need to add the required code manually. However, I'll share an easier path that would help us generate these objects automatically.

Let's, switch to the Linked tab, open the default Azure Data Lake Storage account, navigate to the location of the Gold Delta root folder, select the DimCustomer folder, right-click and select New SQL script, then Create external table commands, as follows:

create script

Figure 5

Select the database, and enter the schema/table name, as follows:

new external table

Figure 6

This will generate a SQL script with all necessary commands. Let's change the context to the EDW database, and tweak the schema name in that script, as follows:

script

Figure 7

Note that the script also contains a select statement to browse the data in the table. Let's run the script and examine the results, as follows:

script

Figure 8

Now that we've created a dimension table, let's follow a similar process to create [fact].[FactSalesOrderHeader] table.

Once you've got the required tables, you can also create views based on those tables, just like you'd do in a relational database.

Creating Power BI dashboards

Let's open the Power BI Desktop and use the Get data command to select the Azure Synapse Analytics connection:

power bi get data

Figure 9

Enter your Synapse workspace connection string in the following format: WORKSPACE-ondemand.sql.azuresynapse.net

Optionally, enter the Delta database name.

Here's my screenshot:

attach sql server database

Figure 10

Select and load the tables we've created, as follows:

select and load tables

Figure 11

Once the data is uploaded, the rest should be straightforward. Here's a simple dashboard I've created, that displays total sales by customers:

simple dashboard report

Figure 12

Next Steps

Read these related articles:






get scripts

next tip button



About the author
MSSQLTips author Fikrat Azizov Fikrat Azizov has been working with SQL Server since 2002 and has earned two MCSE certifications. Hes currently working as a Solutions Architect at Slalom Canada.

View all my tips


Article Last Updated: 2022-07-08

Comments For This Article




Thursday, July 14, 2022 - 6:45:50 AM - Nosferatus2K Back To Top (90260)
As mentioned here:
https://docs.microsoft.com/en-us/azure/synapse-analytics/database-designer/concepts-lake-database#data-storage
Lake databases can have tables only in CSV or Parquet Format

Thursday, July 14, 2022 - 4:17:22 AM - Nosferatus2K Back To Top (90259)
Hi,
Here "Alternatively, you can create a semantic layer over Delta tables that includes creating databases, external tables, views, etc. You can leverage Spark pools or Synapse serverless to create a semantic layer." you mention delta lake file format.
But then in figure 7 you line 3, you are specifying PARQUET file format.
Which is the format?
Thanks



download














get free sql tips
agree to terms