Power BI Datamarts is one of the most exciting features that Microsoft has released for the Power Platform in recent years. Power BI Datamarts provides a low/no code experience directly within Power BI Service that allows developers to ingest data from disparate sources, perform ETL tasks with Power Query, and load data into a fully managed Azure SQL database. If you need high-level information on what a Power BI Datamart is and some example use cases, check out our other blog, What Are Power BI Datamarts?Â
In this blog, we’ll explore how to get started with building a Power BI Datamart using Snowflake Data Cloud as its data source.
Note: At the time of writing this blog, Power BI Datamarts is in preview.
Why are Power BI Datamarts Important?
Power BI Datamarts provide a low/no-code experience that allows users to ingest data from different data sources, perform ETL tasks with Power Query, and load the data into a fully managed Azure SQL database. Not only does the Azure SQL DB not require any tuning or optimization, but organizations will not be charged anything extra beyond their Power BI Premium subscription.Â
Once the data is loaded into the data mart, users can define relationships, as well as policies for business intelligence and analysis, and is automatically available as a semantic layer in the form of a Power BI Dataset within the same workspace that the data mart was created in.Â
This allows for quick report building across the enterprise while also being able to instill data governance. In addition to all of this, users can query the data mart directly by using a visual query builder within the data mart UI or a T-SQL endpoint.
This low/no-code solution combines the self-service analytics features we have come to know from Power BI with the ability for users to segment and explore their data without needing extensive knowledge in coding or database architecture or an IT development team to build any additional reporting structure.Â
In other words, it enables citizen data analysts to self-sufficiently build their own reporting tools directly from the source.
How to Build a Power BI Datamart Using Snowflake Data
Datamarts are a premium feature, which means you will need a Power BI Premium license (Per User or Capacity) to get started. If you have ever created a dataflow, the initial steps when creating a Power BI Datamart are similar:
1. From the Premium enabled workspace, select +New and then Datamart – this will create the datamart and may take a few minutes.
2. Select the data source that you will be using; you can import data from an SQL server, use Excel, connect a Dataflow, manually enter data, or select from any of the dozens of native connectors by clicking on Get Data. Power BI has a native Snowflake connector that we will use to build our datamart.
3. Fill out the connection details in the following screen. The Snowflake Server and Warehouse are required – if a Snowflake Role is also required due to your use case, provide those details in the Advanced option.
4. The next screen will display the structure of your Snowflake warehouse. Expand the databases and schemas to show the associated tables. Select the tables you want to include and then click the yellow Transform data button.
5. Then in the Transform Data screen, you will see the tables and views selected in the previous step imported as tables into Power Query.
   a. Here is where you will make any necessary data transformations the same way you would in Power Query in Power BI Desktop. Ensure your data types have been categorized correctly, make any necessary filtering, and add any additional column-level changes that cannot be made at the source data. Remember, try to make any data transformations as close to the source data as possible.
   b. After all of the transformations are made, click Save to start loading the data into the data mart.
6. Once that data is loaded, you will need to make sure your model is configured correctly with the proper relationships between tables (this process is similar to the one in Power BI Desktop) – consider that Power BI performs best with a star schema. By using a star schema, you will be able to reduce the strain of queries and refreshes in comparison to a single large table with no dimensions. Blog: Data Modeling Fundamentals in Power BI.
   a. To create a relationship between tables in a Power BI Datamart, first, you will need to navigate to the Model tab at the bottom of the Datamart Home page.
   b. You can create the relationship between tables by dragging the key fields from one table to another.
   c. The Edit Relationship screen will appear, ensure that the fields, tables, and cardinalities are needed (i.e., The Primary Key from Table A is labeled as a one-to-many relationship with the Foreign Key from Table B)
   d. Click Confirm to Validate and Apply this relationship, and repeat for all applicable relationships in your model.
Now that your datamart with Snowflake data has been created, it is ready for use. Perform analyses in the following ways:
Write custom SQL from inside of the datamart by clicking the New SQL query button
Create drag and drop visual query
Create a Power BI report directly from the datamart by clicking New Report.
Connect other services/tools using a T-SQL endpoint
Connect a report to the datamart (or the dataset that was automatically created) from the Power BI desktop by getting data from the OneLake data hub (see next section for steps)
Connecting to the Datamart From Power BI Desktop
Performing analysis on the Snowflake data in the Power BI Datamart is simple! One method of connecting to the data is to create a new report from Power BI Desktop.Â
1. With the desktop application open, select the OneLake data hub dropdown from the top and then Datamarts.
2. In the next window, search for or directly select the datamart and then click Connect.
3. You will now see and can start using to build your report all of the Snowflake data from the datamart in the Data panel on the right.
Conclusion
Power BI Datamarts are an excellent way to quickly and effectively create a data source for a specific audience from within Power BI. Like other solutions in the Power BI platform, there is native a Snowflake connector that makes this process exceptionally smooth if your organization is using Snowflake as its data source.Â
Once the datamart has been built, citizen data analysts can dive into it with a variety of tools to perform analysis, truly enabling Self-Service analytics and removing the gap between analysis and action.
Looking to accelerate your data journey with Snowflake and Power BI? phData would love to help guide you to success. Reach out today for any questions, advice, and guidance.
