Get started with datamarts
This article describes how to get started using datamarts, including various sample data that can jump-start your experience. You'll learn about sample datasets you can use with datamarts, how to create datamarts from scratch, how to rename or delete a datamart, and other helpful information to get you acquainted and proficient with datamarts.
You can use the following various types of sample data to explore datamarts. All of the following resources contain free sample data:
Eight Departmental Samples in Excel workbook format, which are Excel versions of the Power BI built-in samples containing the datasets from numerous use cases:
- Customer profitability
- IT spend analysis
- Human resources
- Opportunity analysis
- Procurement analysis
- Retail analysis
- Sales and marketing supplier quality analysis
A financial data sample workbook, which is a simple flat table in an Excel file available for download. It contains anonymous data with fictitious products including sales divided by segments and region.
An Excel workbook version of the AdventureWorks dimensional model, in a tutorial that walks you through creating a Power BI report with the data.
Northwind Traders OData feed, data from a fictitious organization that manages orders, products, customers, suppliers, and many other aspects of a small business.
You can also start using datamarts from any dataflow you currently have as well. Starting from an existing dataflow will copy data into your datamart, at which point you can apply other transformations or just use it as a data source to explore datamarts.
Create a datamart
To create a datamart, navigate to your existing Power BI Premium Gen 2, Premium per capacity (PPC), or Premium Per User (PPU) workspace. Datamarts require a Power BI Premium subscription. In your Premium Gen 2 workspace, select + New and then select **Datamart (Preview) to create a datamart.
It usually takes approximately 10 seconds to provision a new datamart. Once initialized, you can load data into your datamart. For more information about getting data into a datamart, see the get and transform data section in this article.
Get and transform data
There are many ways to connect to data and transform it in a datamart. For general information about data in Power BI, see connect to data in Power BI.
To load data into your datamart, open your datamart (or create a new datamart) and select Get Data.
If you choose to get data from another source, a data source selection window appears where you can select from a multitude of data sources.
You can also drag and drop files from your computer to load data into your datamart, such as Excel files. Some data sources may require parameters or connection strings to properly connect.
Once connected, select the tables you want to load into your datamart. You can apply transformations to your selected data and load the data into the datamart. Once the data is loaded, the tables are imported into your datamart. You can monitor the progress in the status bar.
For each table you select, a corresponding view is created in the datamart that appears in the Object explorer in Data View.
To model your data, navigate to Model view by selecting on the Model View icon at the bottom of the window, as shown in the following image.
Create a measure
To create a measure (a measure is a collection of standardized metrics) select the table in the Table Explorer and select the New Measure button in the ribbon, as shown in the following image.
Enter the measure into the formula bar and specify the table and the column to which it applies. The formula bar lets you enter your measure. Similar to Power BI Desktop, the DAX editing experience in datamarts presents a rich editor complete with auto-complete for formulas (intellisense). The DAX editor enables you to easily develop measures right in datamart, making it a more effective single source for business logic, semantics, and business critical calculations.
You can expand the table to find the measure in the table.
Create a relationship
To create a relationship in a datamart, select the Model view and select your datamart, then drag the column from one table to the column on the other table to initiate the relationship. In the window that appears, configure the relationship properties.
Select the Confirm button when your relationship is complete to save the relationship information.
Hide elements from downstream reporting
You can hide elements of your datamart from downstream reporting by selecting Data view and right-clicking on the column or table you want to hide. Then select Hide in report view from the menu that appears to hide the item from downstream reporting.
You can also hide the entire table and individual columns by using the Model view canvas options, as shown in the following image.
Access auto-generated datasets
To access auto-generated datasets, go to the Premium Gen 2 workspace and find the dataset that matches the name of the datamart.
To load the dataset, click the name of the dataset.
Manage datamart refresh
You can refresh a datamart in two ways:
From the datamart context menu, select Refresh now or select Scheduled refresh.
From the datamart settings page, select Scheduled refresh
To set up incremental refresh for a datamart, select the table for which you want to set up incremental refresh for in the datamart editor. In the Table tools ribbon, select the Incremental refresh icon, and a right pane appears enabling you to configure incremental refresh for the selected table.
Datamarts and deployment pipelines
Datamarts are supported in deployment pipelines. Using deployment pipelines, you can deploy updates to your datamart across a designated pipeline. You can also use rules to connect to relevant data in each stage of the pipeline. To learn how to use deployment pipelines, see Get started with deployment pipelines.
Access or load an existing datamart
To access an existing datamart, navigate to your Power BI Premium Gen 2 workspace and find your datamart from the overall list of data items in your workspace, as shown in the following image.
You can also select the Datamarts (Preview) tab in your Premium Gen 2 workspace, and see a list of available datamarts.
Select the datamart name to load the datamart.
Rename a datamart
There are two ways to rename a datamart:
First, from within the Datamart editor, select the datamart name from the top of the editor and edit the datamart name in the window that appears, as shown in the following image. Select on the ribbon outside of the rename window to save the new name.
Alternatively, you can change the datamart name from the workspace list view. Select the more menu (...) next to the datamart name in the workspace view.
From the menu that appears, select Rename.
When you rename a datamart, the auto-generated dataset based on that datamart is also automatically renamed.
Delete a datamart
To delete a datamart, navigate to the workspace and find the datamart you want to delete. Select the more menu (...) and select Delete from the menu that appears.
Datamart deletion is not immediate, and requires a few days to complete.
Datamart context menus
Datamarts offer a familiar experience to create reports and access supported actions using its context menus.
The following table describes the datamart context menu options:
|Menu Option||Option Description|
|Analyze in Excel||Uses the existing Analyze in Excel capability on auto-generated dataset. Learn more about Analyze in Excel|
|Create report||Build a Power BI report in DirectQuery mode. Learn more about get started creating in the Power BI service|
|Delete||Delete dataset from workspace. A confirmation dialog notifies you of the impact of delete action. If Delete action is confirmed, then the datamart and related downstream items will be deleted
|Manage permissions||Enables users to add other recipients with specified permissions, similar to allowing the sharing of an underlying dataset or allowing to build content with the data associated with the underlying dataset.
|Refresh history||Provides the history of refresh activity with the duration of activity and status.
|Rename||Updates the datamart and auto-generated dataset with the new name.
|Settings||Learn more about datamart settings|
|Share||Lets users share the datamart to build content based on the underlying auto-generated dataset and query the corresponding SQL endpoint. Shares the datamart access (SQL- read only, and autogenerated dataset) with other users in your organization. Users receive an email with links to access the detail page where they can find the SQL Server URL and can access the auto-generated dataset to create reports based on it.
|View lineage||This shows the end-to-end lineage of datamarts from the data sources to the datamart, the auto-generated dataset, and other datasets (if any) that were built on top of the datamarts, all the way to deports, dashboards and apps.
Datamart settings are accessible from the context menu for datamarts. This section describes and explains the datamart settings options and their description. The following image shows the datamart settings menu.
The following is a list of settings available for each datamart.
|Datamart description||Lets users add metadata details to provide descriptive information about a datamart.
|Server settings||The SQL endpoint connection string for a datamart. You can use the connection string to create a connection to the datamart using various tools, such as SSMS.
|Data source credentials||Lets you get data source information and edit credentials.
|Schedule refresh||Data refresh information for the datamart, based on the schedule defined by the user.
|Sensitivity label||Sensitivity label applied on datamart, which also gets propagated on the downstream auto-generated dataset, reports, and so on.
The sensitivity labels propagation to downstream dataset, reports won't happen in the following scenarios:
The following table shows settings for auto-generated datasets. When these settings are applied on an auto-generated dataset, they're also applied to datamart as well.
|Endorsement and discovery|
Datamarts considerations and limitations
When using datamarts with named connections, the following limitations apply:
- You can only create one cloud connection of a particular path and type, for example, you could only create one SQL plus server/database cloud connection. You can create multiple gateway connections.
- You cannot name or rename cloud data sources; you can name or rename gateway connections.
This article provided sample data and instructions on how to create and interact with datamarts.
The following articles provide more information about datamarts and Power BI:
- Introduction to datamarts
- Understand datamarts
- Analyzing datamarts
- Create reports with datamarts
- Access control in datamarts
- Datamart administration
For more information about dataflows and transforming data, see the following articles: