What is the difference between Power BI datamarts and a data warehouse data mart?
by Jonas Ivesdal
06. Sep 2022, 5 minutes reading time
Same - but different? This is our thoughts on the newly released Power BI datamarts solution.
Datawarehouse (DWH) data mart
A data mart is not a new term, it has been around for years and is a database containing subject-oriented data.
"A data mart is a structure / access pattern specific to data warehouse environments, used to retrieve client-facing data. The data mart is a subset of the data warehouse and is usually oriented to a specific business line or team. Whereas data warehouses have an enterprise-wide depth, the information in data marts pertains to a single department" (Wikipedia.com)
Traditionally, a data mart is part of a data warehouse solution and is operated, maintained, managed and governed by the data warehouse operations team. It is basically a subset of the enterprise solution including only the data relevant to the business line, to make it more user-friendly for self-service. This ensures trusted, qualified, up-to-date data which is based on the same ‘one truth’ as other reports based on the data warehouse.
What is new with Power BI datamarts
The Power BI datamarts concept is similar to the traditional data mart concept , where the fundamental difference between the two lies in the operation and maintenance phase of the solution. Where the traditional data mart is operated by ‘IT’ / the data warehouse operations team, the Power BI datamarts solution is managed by the department/person who made it.
From the documentation provided by Microsoft:"[Power BI] Datamarts are self-service analytics solutions, enabling users to store and explore data that is loaded in a fully managed database. Datamarts provide a simple and optionally no-code experience to ingest data from different data sources, extract transform and load (ETL) the data using Power Query, then load it into an Azure SQL database that's fully managed and requires no tuning or optimization. Self-service users can easily perform relational database analytics, without the need for a database administrator".
In the Power Platform world, Power BI datamart is a combination of Dataflow, Azure SQL Database and a Dataset. It is part of the Premium license for Power BI. All data related to a domain is then stored in an Azure SQL Database and can be accessed as any other database using T-SQL queries (or no/low code) as long as the user has a Premium license (Premium Per Capacity or Premium Per User). These three features are combined and accessible in a Premium Workspace in Power BI Services.
Compared to local Excel spreadsheets, MS Access databases or other user-oriented solutions, Power BI datamarts is a better and more structured approach, for storing data and creating useful insights. It is also more scalable and less person dependent than a local Excel spreadsheet data model.
Placing Power BI datamarts in the Automation Maturity Ladder
As explained in our online book on data warehousing, we have identified some typical maturity levels of data warehouse automation. Looking at this model, the Power BI datamarts solution can be placed between levels 1 and 2 as shown below
It is one step up from the ad hoc data delivery approach at Level 1 with manual reports and no central data storage, but not quite as mature as Level 2 due to the siloed approach. Although it is less person dependent than the Ad Hoc Data Delivery Level, it is still person dependent as it is not (meant to be) managed centrally by IT.
When to use Power BI datamarts - Thoughts and recommendations
When report developers and data users become more experienced and the need for data is higher than what can be delivered by IT/DWH, Power BI datamarts can be a mitigating solution for smaller datasets. However, over time – when data is shared and used there will be a need to ‘move up the ladder’ in the illustration above. We see the Power BI datamarts as a quick solution for a small-medium-sized organizations without a data warehouse, or as a great prototyping/use case testing/piloting tool in larger organizations. It can potentially offload the DWH team by creating a better solution specification, almost production ready.
We would recommend that if the Power BI datamarts are shared and heavily used, they should be migrated over to a more robust DWH operations environment. This is due to the risks involving data quality, data management and governance, person dependencies and operations. There is also a risk of ‘shadow IT’ if too much data handling is done outside of IT.
A final thought - from years of experience delivering data to users – business users will need IT to generate the necessary Power BI licenses and Power BI architecture, allow users access to source systems, and set up gateways, API key tokes etc.
In addition to new Power BI features, we recommend using a Data Warehouse Automation tool to deliver more data faster to the end users. This enables standardization, control, and agility in data delivery. To read more about data warehouse automation, read our blog on agile data warehouse development and operation.
Jonas Ivesdal
Jonas Ivesdal is an analytics consultant at BI Builders. With a passion for the art and science of collecting, processing, analysing and visualizing data, he believes in presenting data honestly so that the client can make the correct decisions.
.jpeg?width=60&name=unnamed%20(3).jpeg){kind=avatar}
