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.
The concept of data mart vs. data warehouse has been one of the biggest hurdles to my customers. The difference comes in three aspects:
- 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.
- 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.
- 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:
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:
References
- Data Warehouse vs Data Mart | James Serra’s Blog
- Azure Synapse Analytics & Power BI | James Serra’s Blog
- BI solution architecture in the Center of Excellence — Power BI | Microsoft Docs
- The Difference Between a Data Warehouse and a Data Mart | Snowflake
- Enterprise business intelligence — Azure Reference Architectures | Microsoft Docs
- mspnp/azure-sqldw-enterprise-bi (github.com)
- What is dedicated SQL pool (formerly SQL DW)? — Azure Synapse Analytics | Microsoft Docs
- Large datasets in Power BI Premium — Power BI | Microsoft Docs
- Azure SQL Data Warehouse with DirectQuery — Power BI | Microsoft Docs
