One of the newest additions to the Power BI components is the Datamart. Power BI Datamart is more than just another feature, it is a major milestone where the development of Power BI solutions will be revolutionized based on that. This is a feature that helps both citizen data analysts and developers. In this article and video, I’ll explain what is a Power BI datamart, how it helps you in your Power BI implementation, and why you should use it?
Video
Power BI for Citizen Data Analyst
Power BI came to the market in 2015 with the promise of being a tool for citizen data analysts. A citizen data analyst is someone who does not have a developer background but understands the business and the data related to that business. Power BI (and many other self-service tools) are targetting this type of audience.
You don’t need to be a developer to use the Power BI Desktop. Power BI Desktop is designed so easy and straightforward that even by just opening the tool and clicking here and there you would easily pick up how to use it. If you read a few guides you can easily build your first report and dashboard using Power BI. That is exactly the promise that Microsoft offered about Power BI.
Governance and Re-usability
However, as time goes by in your Power BI development cycle, and you build more Power BI files, you realize that you need something else. Building everything in a Power BI file is simple, but it makes the maintenance of that a bit of trouble. What if you want to re-use a table in another Power BI file? what if you want to re-use a measure or expression in another report?
That is why Power BI has been offering separate components to build the full architecture of Power BI Development, components, features, and technologies such as thin reports (reports that don’t have a dataset and connect live to another dataset), shared datasets (datasets that can be used to feed data into multiple reports), dataflows (the data transformation engine in the cloud), Composite model (combining a shared dataset with additional data sources) and etc. All of these technologies came to create a better development lifecycle for Power BI developers.
Although all the components above are fantastic features in the Power BI ecosystem. There is still a need for a database or a data warehouse as a central repository of the data. The need for this repository comes from many different aspects; keeping the integrated data in a structured way in a relational database, having a central database with all the data from other source systems in it, creating views to cover particular needs for reports and etc.
What is Datamart in Power BI?
Datamart is closing the database gap in the Power BI ecosystem, but it is much more than that. If you want just a database, you can design it in Azure SQL Database or other platforms. The problem is that you need to build the database in a tool such as SSMS (SQL Server Management Studio), then have an ETL process (such as Dataflows, ADF, or SSIS) to feed data into that database, and then the Power BI dataset using Power BI Desktop. Datamart gives you one single unified platform to build all of these without needing another tool, license, or service. Datamart makes the Power BI enough for you to do all your BI requirements.
Power BI Datamart is a combined set of Dataflow, Azure SQL Database, Power BI Dataset, and a Web UI to manage and build all of that in one place.
*The data warehouse term I use here sometimes causes confusion. Some will use the term data warehouse for scenarios of huge databases that need to scale with technologies such as Azure Synapse. However, The term Data Warehouse here means the database or repository where we store the star-schema-designed tables of dimension and fact tables for the BI model. These tables can be small or big. The repository for these is what we call a data warehouse.
Who the Datamart is for?
If you think, what is the use case of datamart, or who would use it? here are a few scenarios;
Power BI Datamart for Citizen Data Analyst
Daniel is a data analyst in a small to mid-size company. His background is not development. He knows the business though, he understands how the business operates and he understands the data related to the business. He wants to build dashboards and reports in Power BI. If he does all of that in Power BI Desktop, soon he realizes that there isn’t good governance around such a structure. His company doesn’t have a data warehouse as such, or no BI team to build him such thing. He can use Power BI datamart to have a fully governed architecture with Dataflow (transformation and ETL layer), Azure SQL Database (data warehouse or dimensional model), Power BI Dataset (the analytical data model), and then the report. All of these can be developed using the UI of the Power BI service. Daniel does not need to open any other tool or services, he does not need to learn SQL Server database technology or any other technologies except the Power BI itself. This is an example of Datamart empowering Daniel to build a Power BI solution that is scalable, governed, and self-service at the same time.
Power BI Datamart for Enterprises
Arwen is a data analyst in a large enterprise and his company has a data warehouse and BI team. However, every time Arwen asks for a change in the centralized data model from the BI team, it takes months if not years to get the results back (because of the bottleneck of requests from all other teams to the BI team). Now using Datamart, Arwen can build her data warehouse with the data transformation layer and everything in a way that can be consumable for future projects or by colleagues easily using Power BI. The solution will be governed by the Power BI service, the BI team can implement a process for certifying datamarts and as a result, Arwen not only implements faster but also helps the BI team to ger their backlog smaller. Power BI Datamart empowers both Arwen and the BI team in her organization to implement faster Power BI solutions in a fully-governed structure.
Power BI Datamart for Developers
Peter is a BI developer. He knows how to work with databases and write T-SQL queries. He can use the Web UI of the datamart to write T-SQL queries to the Azure SQL Database. Or he can use the database connection and connect to the database using a tool such as SSMS. He can also connect to the dataset built by Datamart using the XMLA endpoint using SSMS, Tabular Editor, or any other tools to enhance the data model and take it to the next level. Power BI Datamart empowers Peter in his development work throughout his Power BI implementation.
What are the features of Datamart that empower Power BI development?
Throughout this article so far, you read some of the features of Datamarts that empower the Power BI developers. Here I explain it separately.
Datamart Completes the BI Ecosystem
Datamart uses the Dataflows for the data transformation, Azure SQL Database for the data warehouse (or dimensional model), and Power BI Dataset for the analytical data model. And finally, the Power BI report can connect to the dataset. This builds a complete four-layer implementation in Power BI.
One Place to Manage and Build, No Other Tools Needed
The user interface to build the datamart is all web-based. You don’t even need to install Power BI Desktop. You can use any operating system (Mac, Windows, or even a tablet). You build the entire Power BI solution from getting data from data sources all the way to building the reports using the same UI in Power BI Service. You won’t need SSMS, Visual Studio, Power BI Desktop and etc.
Although at the early stages of building Datamarts, there are some functionalities that are not yet 100% possible using the Web-UI, this will be improved a lot in near future.
One License is enough
Datamarts builds an Azure SQL Database for you, but you don’t need to purchase a separate license from Azure Portal for that. You don’t even need to have an Azure subscription. Power BI Datamart gives you all of that using the Power BI Premium capacity, or Premium Per User license. The database, the Dataflow, and the dataset, all will be part of your Power BI license.
The Vast Horizon in The Future of Datamart
Datamart is just the beginning of many wonderful features to come. Think of what things you might have had if you had persistent storage for the data (like a data warehouse or database) which is not provided to you as an Azure SQL Database by the Datamart. You can start thinking about features such as Slowly Changing Dimension (SCD), and Inferred Dimension Member handling implementation, You can think about monitoring the dataflow processes in a way that the incremental refreshes data that is processed every night is stored in log tables and you can troubleshoot any potential problems easily.
Think about what features can be enabled now that there is a single web UI enabled for the developers, version control, and the ability for team members to work on the same Power BI project simultaneously can be on the horizon.
The futures I mentioned in the previous two paragraphs do not exist yet in Datamart. However, there is a vast horizon for all of these. Datamart can be the base on which all these amazing features can be built.
Governance
Like many other objects in the Power BI workspace, Datamart can have governance aspects such as endorsements and sensitivity labels. If the datamart is marked with specific organizational sensitivity labels, then even if the link is somehow sent by mistake to someone who isn’t part of the organization and should not see this data, that would be all covered by the sensitivity labels and configurations of Microsoft Azure behind the scene.
Datamart is for Citizen analysts as well as Developers
Datamart has a unified Web UI to build everything in one place, which helps citizen data analysts a lot since they don’t need to learn other tools or technologies to build data analysis solutions. Datamart also helps developers with the connections/endpoints in provides to Azure SQL Database or to the Power BI database XMLA endpoint for further development using other tools.
Summary: Datamart is the Future of Power BI
This article wasn’t about the technical aspects of Power BI Datamarts. It just explained what the Datamart is, what features it includes, and who should use it. I strongly believe that Datamart revolutionizes the way we develop Power BI solutions. Datamart is the future of building Power BI solutions in a better way. The next article explains some technical aspects of the Datamart.
Here are my Power BI Datamart article series for you to learn about it;
- Power BI Datamart – What is it and Why You Should Use it?
- Getting Started with Power BI Datamart
- Power BI Datamart Components
- Power BI Datamart Integration in the Power BI Ecosystem
I provide training and consulting on Power BI to help you to become an expert. RADACAD team is helping many customers worldwide with their Power BI implementations through advisory, consulting, architecture design, DAX support and help, Power BI report review and help, and training of Power BI developers. If you need any help in these areas, please reach out to me.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
Hi Reza, thank you for this great write-up. Question I have is what does a datamart offer beyond a dataset? I have dataflows > dataset > report. Why would I want to add a datamart in the mix? Seems I can do everything in a dataset that I can in a datamart.
Hi Tom.
Datamart also offers database storage. So it will be like dataflow > database > dataset > report
using dataflow just by itself, your storage will be CSV files inside Azure Data Lake storage. Which is fine, but it is not as good as a structured relational database. Having that database will give you a lot of options in the future. another thing is that you build everything in one editor rather than doing dataflow online, then dataset in Power BI Desktop and publishing, and then report separately.
Cheers
Reza
Hi Reza, thanks for sharing your vision on this. Here we were almost using Azure Data Lake Gen2 Storage Account in order to be able to access directly the CSV’s of partitioned data from dataflows in order to solve some problems related to perfomance. Do you know if Datamarts preview should already be available for everyone that has Premium Capacity? I’ve tried to test this feature on a premium workspace and also on PPU, but it seems that it’s not available yet.
Hi Lucas
If you are using PPU workspace, or Premium capacity yes. However, it is not yet available for all Azure regions. It would take a bit of time to be available everywhere.
Hi Reza. It’s great to see Datamart in preview, several more features that will help others jump in, have an experience more similar to Power BI Desktop, and yet be able to collaborate with data from others. Thanks much for your videos, very helpful.
Question for you — It looks like there is no way to create a new DAX field/column to a table is there? Like we can in Power BI Desktop’s table view, there is the “New column” button. I use that a lot. Is that correct? DAX measures are there but just not DAX fields/columns, yet.. I imagine that would be coming soon but maybe I’m missing it and it is there already?
Thanks again!
Hi Todd
You are right. The creation of DAX calculated columns and tables are not yet available in the web editor. I’m sure they will be soon.
But the dataset can be edited separately (I believe, not tested yet), and you can add those separately
or alternatively create those calculated tables and columns using Power Query instead.
Cheers
Reza
Have you explored whether Power BI datamarts can be a source for Azure Data Factory? I know they can be queried from SSMS. We made a big investment in dataflows but ran into a limitation when other teams that wanted to land our currated tables in their SQL Server, not in Power BI. Curious the degree to which we can use Power BI datamarts to serve this need as well.
At the moment the access to the underlying Azure SQL Database is limited. tables are not accessible directly. you can query them through Views. So based on the current settings, no you cannot import data into that database using other methods. However, I think in the future things like these will be possible and available.
Hello Reza,
A nice summary thank you. Is the intention that the Power BI report is connected to the dataset that is created by the datamart? Is it also possible to connect Power BI to the underlying SQL tables?
Many thanks
Hi Darran
Yes, the implementation will be like this:
Data source > dataflow (part of datamart) > Azure SQL DB (part of datamart) > Dataset (part of datamart) > Report
You can create a report with directQuery connection to the Azure SQL DB (I think, haven’t tried it yet). but ideally you want a dataset in between like the above flow I mentioned.
Many thanks!
Cheers. I am glad it helps!
Hi Reza, good article as usual. Do you know if it will be possible to have Surrogate Keys and SCD Type 2? Thanks again
Not yet. But now that we have the database, I guess those things will be coming soon.
As usual…awesome technology review!!!
Thanks. I am glad it helps!
Hi Reza – How would this work with direct query? I run into DQ limitations with DAX and ultimately just end up creating subject matter “import” datasets rather than trying to mess with composite models which just gets messy. The downside of course is the need to keep multiple datasets up to date if they contain some of the same queries. How do datamarts play into this situation?
Hi
I don’t think I understand your question correctly. If you are asking is it possible that we use DirectQuery as a source of datamart; The datamart is creating a database, if you already have a database to use as a DirectQuery, then you do not really need a datamart. you can just create a dataset with DirectQuery connection.
Hi Reza – I’ll try and clarify. With the datamart option since it is essentially in DQ mode already, we will face the DQ limitations as described by Microsoft, such as:
“Calculated tables and calculated columns that reference a DirectQuery table from a data source with Single Sign-on (SSO) authentication are not supported in the Power BI Service.
Auto date/time is unavailable in DirectQuery. For example, special treatment of date columns (drill down by using year, quarter, month, or day) isn’t supported in DirectQuery mode.”
And there are also some DAX limitations when using DQ.
I’ve tried creating composite models that shared, for example, the “sales orders” table from our “sales dataset”. So in my sales dataset, that table gets imported, but in our quality dataset (where we also need to reference the sales table) I brought the “sales order” table into my quality dataset by “chaining” the datasets together and selecting the “sales orders” table from my sales dataset (which of course comes in in DQ mode, while the other tables are in import mode (i.e. a composite model).
So I guess my question is, won’t there still be situations where using “import mode” for your dataset is still the best option due to some of the limitations with DQ?
Hi.
Datamart is not DirectQuery already. The data from the source will be imported into Azure SQL Database. and from Azure SQL Database will be IMPORTED into Power BI Dataset. All import. And all functionalities of Power BI will work without limit.
Hi Reza, Great article !! I have a question around composite model and data marts as I assume it might go hand in hand. Once data is imported from a source system into a Power BI data mart are we able to create a Power BI dataset as a composite model with direct query, incremental and aggregates on top of the data mart layer as I think it might also serve this use case well since the data resides in PBI Premium and does not need a gateway for the source ?
Hi Anthony
For me to understand your question correctly, please if my understanding is right or not: You want to create a datamart (which comes with a database and a dataset itself), and then create another Power BI dataset with DirectQuery to the dataset of the datamart? If that is the question, yes, you can. No, you don’t need a gateway for any of these.
Great article,
Can I import the Datamart to my local machine??
Hi Mike
Not sure what you mean by IMPORTING DATAMART. If you want to get data from the dataset of the datamart, you can do that in Power BI Desktop. There is also an option added in June edition which allows you to connect to the Datamart (which is the dataset of the datamart of course)
Reza,
With the introduction of datamart, is it necessary to invest in time to learn advanced M language? I’m more comfortable with SQL.
You would definitely get many benefits from learning advanced M. Even though the data is going to be stored in SQL database, still for your data transformation and feeding data into the datamart you are using Power Query.