Introduction
In May 2022, we announced the preview of datamarts in Power BI. This self-service capability within Power BI Premium empowers users of any level to uncover actionable insights from their own data in a relational database without any help from IT teams. Since the announcement, we have been monitoring your feedback from our blog posts to Microsoft Fabric ideas to better understand what you enjoy when working with datamarts and what you hope to see improved.
From our various feedback forums, we have noticed the following recurring themes in datamarts:
Highlights
- Simple, low-code centric experiences to start performing relational database analytics.
- Pre-provisioned managed database without requiring any tuning for a frictionless experience.
- Seamless integration with existing Power BI artifacts such as Dataflows for easy ETL.
Requested features
- Add DDL/DML support for expanded T-SQL language capabilities
- Git integration to enable a collaborative and efficient review process with developers.
- Backup of datamarts data to maintain resilience and continuity of data infrastructure.
- Advanced security measures to control the level of access at a granular level.
We appreciate your consistent comments to help improve our product and based on your feedback; we are announcing the unification of datamarts to become data warehouses in Microsoft Fabric!
Reason for unification
Although customers appreciate datamarts providing a simpler way to get SQL experiences for analytics, there’s worry about scale, performance, and going ‘all in’ because there’s no grow-up story. With the advent of Microsoft Fabric, you don’t have to choose between a Power BI Datamart or a full-blown data warehouse. Fabric data warehouse is the natural evolution of Power BI datamarts, built on next generation analytics technology and for the era of AI from Microsoft evolved from Azure Synapse Analytics and SQL Server.
As Fabric Data Warehouse is generally available (GA), it provides enterprise-ready benefits beyond those of datamarts including:
- DDL/DML support: Create and manage SQL objects with DML/DDL support and multi-table transaction guarantees.
- Increased scalability: While datamarts are limited to 100GB, Fabric Data Warehouses can handle any data size with fast query execution and optimization.
- Open data format: All files stored as Delta/Parquet for cross engine interoperability enabling collaboration with business analysts and data scientists.
- AI integration: Built with AI from the ground up, the SQL engine is AI powered to offer the best query performance with no tuning required along with Copilot to simplify SQL development.
- Rich SQL security: Granular level security at the object, row, column levels are provided to help you ensure the right users have granular access.
- Cross-database queries: Query data across multiple databases within the same Fabric workspaces including Fabric data warehouses and SQL analytics endpoints.
- Elevated developer experience: You can ingest data to warehouse using multiple ways like data pipelines, dataflows and native high throughput Copy into statement. Along with this, rich intellisense, and TSQL notebook experience elevates experience to develop and consume data from warehouse.
- Source control capabilities: Deployment pipelines, Git integration, and SQL projects are all supported to improve the efficiency of your development collaboration.
- Integration with Fabric: Leverage other Fabric services for data science, analytics, or AI scenarios such as Fabric ML.
- Flexible licensing: Fabric Data warehouse is supported as part of your Power BI Premium Capacity subscription (P SKU), or as Fabric capacity (F SKU with pay-as-you-go or reservation model) starting at $0.36 per hour. Free 60-day trials are available to help you get started too.
- Direct lake model: Supports Power BI Direct Lake mode on tables which decreases the amount of time needed for a semantic model refresh and removes need for duplicate sets of data.
- Data recovery: Capabilities to clone tables and restore a warehouse to a prior point-in-time in event of accidental corruption or data loss.
Next steps
New datamarts
Starting 1st June 2025, creating a new Power BI datamart will not be available. As you go to the Fabric portal to create a new Datamart, you’ll be notified on datamart being retired.
Existing datamarts
Starting 1st Oct 2025, Power BI datamarts will not be supported and datamarts will be cleaned up from workspaces. To avoid losing your data and breaking reports built on top of datamarts, migrate your datamart to warehouse. Before starting the migration process, you can use the following tools to get a list of your datamarts:
- Admin Monitoring workspace – Feature Usage and Adoption report
- Purview Hub
- Power BI admin portal
- Power BI REST APIs
The following options describe how to manually migrate your existing datamarts to a Fabric Data Warehouse.
Option 1: Use the accelerator scripts to migrate your datamart to warehouse.
Option 2: Use manual steps to migrate your datamart to warehouse as below.
- Navigate to your Datamart, click on Transform data in the ribbon, then Export template to export your Power BI datamart schema and data as a template in Power Query Online.
- Create a Fabric warehouse from the Fabric portal.
- From the Home tab of the warehouse, select the Get data option and then New Dataflow Gen2. Important: Creating a new dataflow gen2 through the warehouse will set each query’s destination to the current warehouse as the default destination, this can ultimately save you time by avoiding having to configure the destination for each query. You can also update or adjust these options including writing to different locations or changing the update method.
- Within the new dataflow gen2 editor, select Import from a Power Query Template located in the center of the empty editor.
- Select the save & run option, which will both publish your dataflow and start a refresh of your data loaded into the Fabric Data Warehouse.
- Connect your Power BI reports and dashboards to your Fabric Data Warehouse via the Default Semantic Model or as a data source.
Additional considerations
In this section, you’ll learn on how to optimize your datamart for Fabric Data Warehouse. Although these are optional, they may be helpful depending on what type of datamart features your organization is leveraging.
- Enable the Custom Semantic Model in your Fabric Data Warehouse
Datamarts automatically add all tables to the default Power BI semantic model. In Fabric Data Warehouse, based on customer feedback, we have modified the default behavior to not automatically add tables and views to the default Power BI semantic model. This change will ensure the background sync will not get triggered. This will also disable some actions like ‘New Measure’, ‘Create Report’, ‘Analyze in Excel’.
If you want to change this default behavior, you can:
- Manually enable the Sync the default Power BI semantic model setting for each data warehouse or SQL analytics endpoint in the workspace. This will restart the background sync that will incur some consumption costs.
- Manually pick tables and views to be added to semantic model through Manage default Power BI semantic model in the ribbon or info bar.
From here, you can then recreate any measures, relationships, or other business semantics as needed.
To learn more on how to edit data models in the Power BI service, refer to, Edit Data Models documentation.
Alternatively, you can script out the definitions and reapply to a new dataset programmatically. To first script out the default semantic model, you can use SQL Server Management Studio (SSMS).
View the Tabular Model Scripting Language (TMSL) schema of the semantic model by scripting it out via the Object Explorer in SSMS. To connect, use the semantic model’s connection string, which looks like powerbi://api.powerbi.com/v1.0/myorg/username. You can find the connection string for your semantic model in the Settings, under Server settings. From there, you can generate an XMLA script of the semantic model via SSMS’s Script context menu action. For more information, see Dataset connectivity with the XMLA endpoint.
- Script out any row-level security (RLS) and reapply as needed
Fabric Data Warehouse supports more security and governance controls than Power BI datamarts at a granular level. Every object in datamarts is accessible via a view in the Model Schema. You can easily create a new Schema called Model, add Views from the tables, and then provide SQL security there, or provide any custom T-SQL security needs as required, going far beyond the GUI of the datamart.
Learn More
Security for data warehousing in Microsoft Fabric
Row-level security in Fabric data warehousing
- Incremental refresh with dataflows Gen 2
Incremental refresh is a feature that allows you to refresh only the data that has changed since the last refresh, instead of refreshing the entire data. This can improve the performance and efficiency of your dataflows and reduce the load on your sources and destinations. For guidance on how to set up incremental refresh with Dataflow Gen2, refer to the Pattern to incrementally amass data with Dataflow Gen2 documentation.
Closing
The unification of Power BI datamarts with Fabric Data Warehouses offers a significant evolution in Microsoft’s analytics capabilities. By addressing user feedback and enhancing features such as scalability, security, and AI integration, Fabric Data Warehouses offer a robust solution for modern data needs.
We encourage users to migrate their existing datamarts to Fabric Data Warehouses to take full advantage of these improvements and ensure continuity in their data operations.
For detailed guidance on the migration process and to explore the benefits of Fabric Data Warehouses, please refer to the Upgrade a Power BI Datamart to a Warehouse documentation.