Self-service, self-service, self-service. Everywhere you turn these days we hear that term. From the grocery store to the pet groomer, the mantra of self-service is upon us. Now self-service has entered the world of business analytics. If you’re an end user, this word brings excitement and joy. If you’re in IT, it fills you with trepidation and nightmares. There’s a big difference between making a mistake when you’re bathing Fluffy and accidentally publishing HIPPA data to your organization!
Power BI puts self-service capability at the forefront of its platform, allowing users to import files of just about any type. It finally makes it possible to report on the Excel files that have been around forever. The trick becomes, how do we take those same Excel files and make them safe for enterprise-wide consumption? How do we vet that data to make sure it’s managed and curated to protect things? How do we handle backups and proper structure?
The answer is Power BI datamarts. Datamarts in Power BI have been around for a while, about nine months, but they are now finally making their way from preview to production. In this blog, we discuss
- What are Power BI datamarts
- When to use Power BI datmarts
- How datamarts differ from datasets and dataflows
- Basic administration of Power BI datamarts
What are Power BI datamarts
In the Power BI world, datamarts provide a way to bridge the gap between users and IT. On one side of the gap we have IT, who can find itself overwhelmed with requests from business users to get data from many different data sources into one managed, centralized repository. On the other side, we have users who take it upon themselves to work directly from Excel files or create their own splintered databases in tools like Access. Datamarts marry user access and IT governance in a way that makes both sides happy.
Datamarts let users upload files, point to existing databases or get data from a huge variety of sources. Just look at this list of source choices below—it’s not even showing all the available options!
After connected and loaded, this data is moved into a managed Azure database. This new database can be modeled and accessed in a variety of ways including a no-code experience. The datamart will automatically generate a dataset that can be used to build reports and dashboards. This same datamart can be queried directly through the use of SQL. IT gets the peace of mind that data is properly managed and governed while business users can move forward on the analytics they need to achieve organizational goals.
When to use Power BI datamarts
The decision about when to use a Power BI datamart comes primarily down to timing. How soon do your users need access to this data versus how fast can the IT group get the data modeled, transformed and loaded? If users need access pronto, then the datamart is your answer.
Datamarts offer other advantages beyond just Power BI reporting. For example, if you need to share data between other Microsoft applications, datamarts may be a good choice. They can also be used as sources for other datamarts or items by using the SQL endpoint. Which brings us to when to use a datamart, a dataflow or a dataset.
Datamarts versus datasets and dataflows
In short, a datamart is a database. It is a connection to a data source that has been pushed into a database. That datamart can then produce a dataset.
Datasets are the metrics and semantic layers needed for reporting. Datamarts automatically generate datasets when built. Dataflows and datamarts integrate together.
Dataflows are the data transformation component in Power BI. They are a Power Query process independent of Power BI that stores data into your Dataverse (your Azure datalake storage). This data can be edited and transformed using Power Query online. Like datamarts, dataflows aren’t limited to just Power BI. They can be used with any of the Power Platform/Power Apps tools. A dataflow gives you the benefit of a transformation engine in Power Query and a re-usable ETL component.
Datamarts and dataflows can be used together to meet specific needs. If you have existing dataflows, a datamart lets you apply additional transformations or use SQL to perform ad hoc analysis.
If you have existing datamarts, a dataflow provides ETL for large-scale data volumes. Dataflows can be used with deployment pipelines from your datalake into a datamart, which in turn produces a dataset allowing for the coveted self-service reporting and ad hoc querying.
After a datamart has been created, the next steps are loading the data, transformations if needed, then modeling. At the end of that flow, users can start writing reports.
You can set up datamarts to incrementally refresh data or schedule a refresh. They are also fully supported in deployment pipelines, allowing updates to move from testing to production with relative ease. You can apply rules to connect to relevant data in each stage of the pipeline.
If you want to analyze a datamart, you can use the Datamart Editor. Here you can do a visual query or write SQL directly.
Visual Editor
SQL Editor
Outside the editors, tools like SQL Management Studio (SSMS) or Azure Data Studio (ADS) can be used to access and query the data. Which tool you use depends on what needs to be completed:
- If complex administration is needed, security management or statistics, then SSMS is the better option.
- If a user is on a Mac or Linux system, is mostly editing or executing queries, or visualizing set results, ADS is the way to go.
Basic administration of Power BI datamarts
When it comes to administration, you can do it like any other aspect of Power BI. Datamarts can be enabled or disabled from the Power BI admin portal:
From the admin portal you can also see a list of all datamarts in a workspace. Existing Power BI APIs can be used to retrieve datamart information such as GetGroupsAsAdmin and the workspace scanner API. For more in-depth audit logs and activity reports, administrators can use the Microsoft 365 Admin Center.
Self-service and full service with Power BI datamarts
With datamarts, IT and business users are no longer at odds. Datamarts are an excellent way to provide a full service and self-service environment for your organization. Users get quick access to the data they need and power users can perform ad hoc queries in a visual or SQL driven interface. IT can sleep well at night knowing they have a managed governed set of data available.
We encourage you to check out datamarts while the feature is still in preview so you will be ready when this exciting feature gets moved to production!
Thank you to Pat Powers for this blog. A senior Senturus BI consultant, Pat is also one of our Power BI trainers – and a perpetual student favorite. To learn more about Power BI datamarts and watch how-to demos, watch our on demand webinar Power BI Datamarts: Self-Service Speed, IT Governance.
