Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
With Cosmos DB in Microsoft Fabric, you can build interactive Power BI reports using your NoSQL data. This guide covers two approaches for connecting Power BI to your Cosmos DB in Fabric database:
- SQL analytics endpoint (via OneLake): Leverage mirrored data through the SQL analytics endpoint with DirectLake mode for optimal performance and no RU consumption
- Azure Cosmos DB v2 connector: Connect directly to your database using the Power BI connector with DirectQuery or Import mode
Each approach offers distinct advantages. The SQL analytics endpoint is recommended for most production scenarios, while the Azure Cosmos DB v2 connector provides flexibility for real-time reporting and direct database access.
Prerequisites
An existing Fabric capacity
- If you don't have Fabric capacity, start a Fabric trial.
An existing Cosmos DB database in Fabric
- If you don't have one already, create a new Cosmos DB database in Fabric.
An existing container with data
- If you don't have one already, we suggest that you load the sample data container.
Important
For this guide, the existing Cosmos DB database has the sample data set already loaded. The remaining examples assume you're using the same data set.
Choose your reporting approach
Select the approach that best fits your requirements:
| Use case | Recommended approach |
|---|---|
| Complex data types (arrays, objects, nested structures) | SQL analytics endpoint |
| No database resource consumption (RUs) | SQL analytics endpoint |
| Dynamic schema evolution | SQL analytics endpoint |
| Real-time data with direct database queries | Azure Cosmos DB v2 connector (DirectQuery) |
| Existing Power BI connector workflows | Azure Cosmos DB v2 connector |
Approach 1: Build reports using the SQL analytics endpoint
The SQL analytics endpoint provides access to mirrored data in OneLake, enabling you to build Power BI reports with DirectLake mode. This approach offers optimal performance without consuming database RUs and supports complex data types including arrays, objects, and hierarchical structures.
For more information about semantic model modes in Power BI, see Semantic model modes in the Power BI service.
Verify mirroring replication
Before building reports, ensure that mirroring has completed successfully at least once:
In the Fabric portal (https://app.fabric.microsoft.com), navigate to your Cosmos DB database.
In the database view, locate the Replication tab and select Monitor replication.
Verify that the replication status shows as Running or Completed and that data has been successfully replicated to OneLake.
Important
If mirroring hasn't completed at least once, the SQL analytics endpoint won't have data available for reporting. Check the replication monitoring panel for any errors or pending operations.
Configure your semantic model
Once mirroring has completed successfully, configure your semantic model:
In the menu bar, select the Cosmos DB list and then select SQL Endpoint to switch to the SQL analytics endpoint.
Select the Reporting tab.
In the ribbon, select New semantic model.
Select the tables you want to include in your report.
Select Save.
Note
By default, semantic models are empty. If you skip this step, any attempt to create a Power BI report results in an error due to an empty semantic model.
For more information about semantic models in Power BI, see Semantic models in the Power BI service.
Create and design your report
Once your semantic model is configured, create your Power BI report:
In the Reporting tab, select New Report.
Select Continue to open Power BI with your configured semantic model.
In the Power BI editor, drag fields from the Data pane to the report canvas.
Select visualizations from the Visualizations pane to create charts, tables, and other report elements.
Optionally, use Copilot to generate report suggestions:
- Select Copilot in the menu
- Select Suggest content for a new report page
- Review suggestions and select Create to add them
Tip
You can also create reports by selecting Pick a published semantic model from the Create tab in the Fabric portal, or by selecting Power BI semantic models in Power BI Desktop.
For more information about creating reports in Power BI, see Get started creating in the Power BI service.
Approach 2: Build reports using the Azure Cosmos DB v2 connector
The Azure Cosmos DB v2 Power BI connector enables direct connection to your Cosmos DB in Fabric database from the Power BI service. This approach supports DirectQuery for real-time reporting and Import mode for scheduled data loads.
Important
The Azure Cosmos DB v2 connector consumes request units (RUs) from your database. DirectQuery mode generates queries with each report interaction, while Import mode consumes RUs during data refresh. For more information, see Azure Cosmos DB v2 connector limitations.
Connect to your database
In the Fabric portal (https://app.fabric.microsoft.com), navigate to your workspace.
Select the Create tab on the left toolbar, and then select Get data.
Search for and select Azure Cosmos DB v2.
Enter your Cosmos DB in Fabric database endpoint URL (available from your database settings).
When prompted to authenticate, select Organizational account, sign in, and select Next.
Note
Account key authentication isn't supported for Cosmos DB in Fabric.
In the Navigator pane, select the database and container that contain the necessary data for your report.
The Preview pane shows a list of Record items. Each document is represented as a Record type in Power BI. Nested JSON blocks within documents also appear as Record types.
Expand the record columns to view document properties, then select Create a report. Optionally you can select Create a semantic model only or Transform Data.
Create visualizations
After loading your data:
In the new Report artifact, drag fields from the Data pane to the report canvas.
Select visualizations from the Visualizations pane to create charts, tables, and other report elements.
Configure filters, slicers, and other interactive elements to enhance your report.
Optionally, use Copilot for AI-assisted report creation.
Understand connection modes and data refresh
The Azure Cosmos DB v2 connector supports two connection modes with different refresh behaviors:
Import mode:
- Loads data into Power BI memory for fast query performance
- Requires manual or scheduled refresh to update data
- Ideal for large datasets where query speed is prioritized
DirectQuery mode:
- Queries the database in real-time with each report interaction
- Always displays current data without requiring refresh
- Performance depends on database optimization and partition key usage to minimize RU consumption
For more information about data refresh in Power BI, see Data refresh in Power BI.
Tip
Use Import mode for faster visualizations with scheduled data updates. Use DirectQuery mode for real-time data requirements with optimized queries and partition key filters to minimize RU consumption.
For more information about limitations when using the Azure Cosmos DB v2 connector, see Azure Cosmos DB v2 connector limitations.
Comparison: SQL analytics endpoint vs. Azure Cosmos DB v2 connector
| Feature | SQL analytics endpoint | Azure Cosmos DB v2 connector |
|---|---|---|
| Data access | Mirrored data via OneLake | Direct database connection |
| Connection mode | DirectLake (can fall back to DirectQuery) | DirectQuery or Import |
| RU consumption | None | Yes (especially DirectQuery) |
| Data freshness | Near real-time (mirroring latency) | Real-time (DirectQuery) or scheduled (Import) |
| Complex data types | Supported (arrays, objects, nested) | Not supported |
| Schema evolution | Handles dynamic schemas | Limited (first 1,000 docs) |
| Available in | Power BI service, Power BI Desktop | Power BI service, Power BI Desktop |
| Best for | Production BI, complex data, no RU impact | Real-time queries, direct access, simple schemas |
Tip
For most production BI scenarios with Cosmos DB in Fabric, the SQL analytics endpoint approach is recommended. It provides better support for complex data types, doesn't consume database RUs, and handles schema evolution more effectively. Use the Azure Cosmos DB v2 connector with DirectQuery mode when you need real-time data access and have optimized queries, or use Import mode for scenarios requiring direct database connectivity outside the Fabric mirroring workflow.