Unblocking Power BI —Should Data Mart be Implemented in Azure Synapse or Power BI Premium? (Ep. 7)

Technologies include Azure, Power BI, and Excel. Together, they can be leveraged to deliver a scalable and data-driven cloud BI platform

Co-authors: Gananda Hayardisi, Wipada Chanthaweethip

Last year, it was obvious from the Microsoft BI official announcement that Power BI Premium is a superset of Azure Analysis Services. You may check out my old post below.

Unblocking Power BI — Power BI Premium as a Superset of Analysis Services (Ep. 5) | by Korkrid Akepanidtaworn (Kyle) | Dev Dream Team | Medium

The concept of data mart vs. data warehouse has been one of the biggest hurdles to my customers. The difference comes in three aspects:

  1. Data Size — a data mart is typically less than 100 GB; a data warehouse is typically larger than 100 GB and often a terabyte or more.
  2. Range — a data mart is limited to a single focus for one line of business; a data warehouse is typically enterprise-wide and ranges across multiple areas.
  3. Source System — a data mart includes data from just a few sources; a data warehouse stores data from multiple sources.

The generic modern data warehouse architecture on Azure is usually solidifying AI, BI, and Analytics capabilities within Azure Synapse Analytics, including Azure Data Lake Storage Gen 2, Azure Data Factory, and other related services. There’s no question that Azure Synapse Analytics is the recommended option for “Model & Serve” layer; however, should a data mart be implemented in Azure Synapse Analytics or Power BI? As you know, this architecture gets rid of the need for Analysis Service and replaces it with Azure Synapse Analytics + Power BI. Hence, I will lay out the pros and cons of the two Microsoft BI solution patterns:

Azure Synapse Analytics as a Cloud Lakehouse: A New Data Management Paradigm | by Korkrid Akepanidtaworn (Kyle) | Towards Data Science

BI Solution Pattern 1: Data Model in Azure Synapse Analytics

The architecture consists of the following components:

  • Blob Storage. Blob storage is used as a staging area for the source data before loading it into Azure Synapse.
  • Azure Synapse. Azure Synapse is a distributed system designed to perform analytics on large data. It supports massive parallel processing (MPP), which makes it suitable for running high-performance analytics.
  • Power BI. Power BI is a suite of business analytics tools to analyze data for business insights. In this architecture, it queries the semantic model stored in Analysis Services.

With data mart hosted in Azure Synapse, you may establish “DirectQuery” as a recommended connection mode. With DirectQuery, “queries are sent back to your Azure SQL Data Warehouse in real time as you explore the data. Real-time queries, combined with the scale of SQL Data Warehouse enables users to create dynamic reports in minutes against terabytes of data.” The idea here is to maintain the single source of truth and achieve the optimal query performance. (Azure SQL Data Warehouse with DirectQuery — Power BI | Microsoft Docs)

Advantages (+)

  • Enable analytics on large data with massive parallel processing (MPP)
  • Store in relational tables with columnar storage & columnar ordering
  • Use all or subset of the data in materialized views can get faster performance
  • Tune the performance with result set caching (so recomputation is not needed.)
  • Come with more complete security. (Row-level security (RLS), Column-level security, and Dynamic Data Masking etc.)
  • Get most up-to-date data from source à Single source of truth
  • It may no need Power BI Premium to handle large dataset
  • Possible for no data movement (serverless pool)

Disadvantages (-)

  • There is a limit of 128 max concurrent queries in SQL DW and 1,024 max open concurrent connections. When the concurrency limit is exceeded, the request goes into an internal queue where it waits to be processed. When the connection limit is exceeded, connections will be refused with an error.
  • Slower dashboard performance & DirectQuery performance.
  • Cannot add new columns or create calculated column within Power BI
  • Cannot leverage DAX functionality due to some limitations
  • Query load depends on Synapse only
  • Auto date/time is unavailable
  • Synapse can only connect to a single Power BI workspace now

BI Solution Pattern 2: Data Model in Power BI Premium

The architecture consists of the following components:

  • Blob Storage. Blob storage is used as a staging area for the source data before loading it into Azure Synapse.
  • Azure Synapse. Azure Synapse is a distributed system designed to perform analytics on large data. It supports massive parallel processing (MPP), which makes it suitable for running high-performance analytics.
  • Power BI. Power BI is a suite of business analytics tools to analyze data for business insights. In this architecture, it queries the semantic model stored in Analysis Services.

Advantages (+)

  • Allow for faster query performance
  • Leverage DAX functionality
  • More flexible to combine with other dataset/ data sources
  • Time intelligence capabilities
  • As source still resides in Synapse, functionalities like materialized views, result-set cache etc. can still be used

Disadvantages (-)

  • Lack some part of the security, particularly column-level security
  • No Perspectives feature yet. Perspectives, in tabular models, define viewable subsets of a model that provide focused, business-specific, or application-specific viewpoints of the model
  • Need schedule to refresh data to keep data current — it may bottleneck if we have so many schedule at the same time
  • There’s a dataset size limit (400GB)

I will end this article with the decision flow for ease of adoption and consideration:

Trusted advisor on the journey to digital transformation and cloud adoption, Solutions Architect (Data Platform & AI), Technical Writer on Medium.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store