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?
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.
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.