Power DataMart
What is a Power BI Datamart?
The self-service data analytics abilities in Power BI Datamarts empower everyone to build secured and governed databases from various data sources with no code experience. It’s not just a combination of few components: dataflow + data warehouses + dataset; but also comes with a unified web UI that you can use to get data from sources, do data transformations, query databases, and build relationships, measures, and calculations.
Who can use a Power BI Datamart?
Anyone with Power BI Premium license! Whether you are already a BI developer, or someone with no developer background and never writes SQL code, Power BI Datamarts can be beneficial for you. There’s no need to install SSMS or Azure SQL databases, which means no other licenses are needed for a fully unified development platform. Plus, with the unified UI design, you no longer need to wait months for the IT team to build the databases you want.
How Can I Use Power BI Datamart?
A datamart can be built from source systems, or it can be built from an enterprise data warehouse created by IT for the case when you want a subset of the data warehouse where you can run your own queries on a much smaller data model.
It can be a better option than storing data in a data lake (which dataflow does now by storing data in CDM format in a data lake), as loading a dataflow into an Azure SQL Database allows you to assign roles and row level security, as well as allows for simple connections from a variety of tools, and has faster performance.
Because it has a brand-new web experience for data modelling and measures authoring, no Power BI desktop is required (this means Mac users don’t have to run Power BI Desktop in a Windows emulator anymore).
To create a datamart:
Log on to app.powerbi.com and go into a premium workspace (premium capacity or per user). On the home page of a workspace, click New -> Datamart. Then choose “Get Data” and connect to a data source. Then choose the data you want to import into the datamart, and you will be taken to Power Query. Then a datamart and a Direct Query dataset will be created (both with the same name). You will then be taken to the datamart workspace (visual designer) where there are four tabs:
- Data: The data in the source can be viewed in the table format, where you can create a new measure (via DAX) or setup incremental refresh, do filtering and sorting.
- Design: Create a query to view the data via a Power Query diagram View. This is for users who don’t know SQL – they can write custom queries using this visual query editor.
- SQL: A visual editor that allows you to create T-SQL queries to view the data. The request in the Microsoft Store for PBI Desktop is “Allow us to write SQL”. In the future you will be able to save queries.
- Model: Entire ETL processes can be performed in a Power BI Datamart and all changes to the model within a Power BI Datamart will translate to Power BI desktop as well. Users can easily create relationships between the tables with just a few clicks. Within the datamart’s unified interface, we can also create measures, and advance modelling. This greatly shortens the time of modeling and database creation that’s often caused by a gap between business and IT.
Row-level security and improved governance:
Power BI Datamart’s apply Row-Level Security to both data and datasets. Users can safely share data and datasets, build reports with restricted data access to given users. Also, sensitivity labels will protect sensitive data. Users have total control over the governance and manageability.
Benefits of Using Power BI Datamart:
If you have spent significant time working with dataflows, connecting to data sources, preparing the data, and then creating the data model can be time-consuming and tedious. The main benefit of Power BI Datamart is that it automates the process of data ingestion, transformation, and loading.
