Power BI Datamarts - what are they and WHY?
Updated: Nov 27, 2022
Article update: 27 November 2022. Indications are that Power BI Datamarts will play a more substantial role in the data ecosystem envisioned by Microsoft than previously thought. Updates to follow when it becomes clearer.
Datamarts are not new, far from it!
Data marts, a concept first coined by Bill Inmon a loooong time ago. then championed by Ralph Kimball using a different approach than Inmon, is essentially a subject-oriented relational database that stores transactional data in rows and columns, which makes it easy to access, organize, and understand. The operative words here are subject-orientated as it focusses on a particular business process, for example accounts payable, or staff on-boarding, or asset works management, etc. which all forms part of the larger organisation and processes (and data warehouse).
These data marts are important as business users working within the particular business process which forms the topic of the subject-orientated data mart, should be able to easily analyse the data within the data mart using familiar tools, without having to understand the technical complexities of data. Someone working in accounts payable should be able to easily recognise the accounts payable data mart as it should have been modelled according to the process they are familiar with, so they should be able to, without too much upskill, create their invoicing ageing-, or AP trial balance report, or to analyse the history of payments.
Data marts are part of the larger data warehouse ecosystem, and no matter whether you favour an Inmon or a Kimball approach, their purpose remains the same.
So if data marts have been around for a long time, why the ability to create them in Power BI?
Data marts being part of the larger data warehouse ecosystem and are created by the data warehouse architects, business analysts and developers working collaboratively to understand the business processes and requirements and then create the data marts using their underlying data skills, for example by using the data lake, Synapse analytics and databricks, right? Well yes, but what about when business users wants to self-service and create their own data marts using their own subject matter expertise, but who do not have the typical technical skills required to create a data mart?
That is where Power BI Datamarts comes in. Guy in a cube gives us an awesome pragmatic description - I like pragmatic! "How do I create a data warehouse type thing, without having to go to central IT, I just need to stand this up for my business unit...anyone can do this, inside Power BI" - (1) Exploring the preview of datamart in Power BI! - YouTube
What?! A data mart created as part of self-service?
Yup, I know this is going to be controversial as there is still a tendency by centralised IT to view any data related self-service deep within the business as suspicious and of lesser quality.
I am not going to discuss the validity of these concerns in this article, except by saying that self-service has occurred within the business for a equally long time as what the data warehouse has been around for, just look at the pervasiveness of data work within tools such as Excel and MS Access. I also want to point out that self-service can lead to a dangerous mess unless users are empowered and enabled in the right way. And that this empowerment and enablement is achieved through pragmatic governance and guardrails, provision of authoritative data entities (from which these data marts can be created), cataloguing of all data and data assets, and supported technologies.
I do not think, well I hope not anyway, that the intent of Power BI Datamarts is to replace the authoritative data mart which is part of a larger data warehouse, which is developed in a well thought out and methodical manner, but that it is rather to fill that gap where a business user (the “citizen data worker”), without huge technical skills, needs to create a subject-orientated data mart for his business unit, hopefully from the aforementioned authoritative data entities
So Power BI Datamarts is only for data marts created by the citizen data worker? yes, well mostly…
There is however a possibility that Power BI Datamarts will not only enable the citizen data worker but also skilled developers to use this component as it leverages an Azure SQL Database behind the scene which means that T-SQL can also be used as part of the creation process. This is good segue to talk about how it works under the covers…
How does Power BI Datamarts work?
Under the covers, Power BI Datamart contains three layers that leverage existing Azure and Power BI technologies.
When creating a Power BI Datamart…
Data is read from sources, transformed, and resulting entities are built using Dataflows.
Data per entity is then stored in Azure SQL Database, as opposed to Azure Data Lake.
Data is then finally imported into the Power BI Dataset where the relationships, calculated measures, etc. are hosted.
What do you need to create a Power BI Datamart
If you’ve done Dataflows, then its easy, but you need Power BI Premium, or Premium Per User.
And note that as at the date of this article, Power BI Datamarts are still in public preview.
Governance, do not forget governance!!
Currently, you cannot turn Power BI Datamarts off. So it is really important to get onto the front foot and to implement some checks and balances. This could therefore form part of a larger Microsoft Purview regime, so that the enablers, i.e. those central BI or IT teams, can at least understand what is being created, by who, and take appropriate action to avoid sprawl, etc.
Allowing citizen data workers to create something as fundamental to an organisation’s data ecosystem as a data mart is an interesting move by Microsoft. This will remain a confusing area for a while especially given that the citizen data worker already has the ability to create subject-orientated models in the form of Power BI Datasets. But I do see some method in what some could consider madness, as long as the organisation readies itself for the realities that Power BI Datamarts will bring by urgently considering the appropriate guardrails, frameworks and governance.