Power BI Data Marts
“This new feature brings the power of SQL databases into the hands of Power BI developers and analysts, helping you uncover more insights and drive digital transformation.”
Data Marts is bringing democratization of self-service BI to the Data Warehouse—meaning Data Warehouses will no longer be bottlenecks, expensive, or technical heavy to implement.
Power BI Data Mart provides a unified experience for self-service Data Warehouse, especially valuable for small to medium markets. It is SQL-based and comes with a SQL endpoint that can be used for all your other systems, like SSMS, Azure Data Studio, or even Excel!
Data Mart in bullets:
⚡ An intuitive no code experience
⚡ Integrated Power BI development
⚡ Unified relational database with BI semantic model
⚡ Integrated governance
⚡ Easy to use
Shortly put, Data Marts is the no/low code Data Warehouse that's going to do for Data Warehousing for Power BI did for data modeling and reporting—completely disrupt and change how we do things, for the better.
Thats the vision, at least. I finally got a chance to give it a spin myself. I think you can imagine my excitement when I finally got this notification:
So far so good, but nothing new under the hood. We can:
Get Data
Add source permissions
Pick tables and columns
Power Query transformations
Everything, we already could do with Data Flows.
But then something new happened…
The service started building the SQL database in the background and load all our data into it - automatically!
Data modelling is also possible
When you create your Data Mart it also creates an automatic DirectQuery Dataset. By defining the relationships, here, at the Data Mart level it is automatically used in the dataset too.
Furthermore the relationship meta-data is added to the SQL database for consumption by other systems too.
Each individual table can be configured for Incremental refresh, reducing the load on both your source systems and possibly also your Premium capacity.
So far, so good, all in all a major upgrade to Data Flows with a cleaner UI and with a SQL endpoint
(So that it can be utilized outside of Power BI)
But, let’s look at the juicy stuff, the stuff that is going to transcend it beyond just a world-class ingestion tool and into a full-fledged Data Mart; Queries and transformations!
The new UI Query Design tool is really neatly designed, intuitive and easy to use. I’m sure alot of non-technical users will love it. For more technical savvy developers we also have door-number-two:
SQL queries, directly on the Data Mart tables!
Incredibly promising so far, and to make it even better, it also works with SSMS, Azure Data Studio, Azure Synapse and possibly even more tools.
So time for the million dollar question. What do we use it for?
With all these query capabilities, how do we save our result and re-use them later?
I am sorry to dissapoint here, but:
What an anti-climactic way to end this post… and for that I am sorry, but I am just as dissapointed.
The upside?
They are adding it - and it seems they know it is urgently needed.
I still completely buy in on the vision and aspiration to offer no-code low-code Data Mart capabilities to Power BI users, but without the ability to save queries, we have nothing more than a polished, and very powerful, upgrade to Data Flows.
Let me end with my own personal wish-list:
Saving queries
Open and edit queries across the UI and the SQL editors
Option to materialize saved query or not (view vs. table)
Choose INSERT, REPLACE or UPSERT for each table refresh, with timestamps for point-in-time analysis
Version control for all invovled objects, as we’ll as DevOps integration
API capabilities (Priority one: being able to run SQL queries on the Data Mart via API. Important for reverse ETL scenarios)
Additional ressources: