Power BI Azure Cosmos DB integration for document data
Power BI Azure Cosmos DB integration turns application documents into tables that a report can use. Microsoft documents an Azure Cosmos DB v2 connector that supports both Import and DirectQuery. However, a document store does not behave like a relational database, so the partition key and the shape of each document matter. Good design starts with those two facts.
- Azure Cosmos DB v2 connector
- Import and DirectQuery
- Partition key aware design
Last updated
How does Power BI Azure Cosmos DB integration connect?
The author chooses the Azure Cosmos DB v2 connector in Power BI Desktop and enters the account endpoint. Then they pick a data connectivity mode. After sign-in, the navigator lists the databases and containers in that account.
Sign-in has two forms. Microsoft's article lists an account key and an organizational account. So we discuss both with your security team, because a key grants broad access while an organizational account ties access to a named identity.
The mode is the next choice. Import copies the documents into the semantic model on a schedule. DirectQuery leaves them in the container and asks for data when a visual needs it.
Design points for a document database source
Documents are flexible, so a report needs a few firm decisions.
Partition key
Queries that filter on the partition key stay cheap. So we design report filters around it where we can.
Nested data
Documents hold arrays and objects inside them. Then someone must decide how those become rows and columns.
Changing schema
New fields appear as the app evolves. Therefore the model needs a rule for fields that older documents lack.
Request units
Every read consumes throughput on the account. However, a well-filtered query keeps that cost modest.
Several containers
Related data may sit in different containers. So we plan the keys that join them in the model.
History
Apps often keep only the current state. When trends matter, we agree how to capture snapshots.
Should a Cosmos DB report use Import or DirectQuery?
Import suits most dashboards. The model holds a copy, so visuals respond quickly and place no load on the application between refreshes. However, the data is only as fresh as the last run.
DirectQuery suits cases where recent data matters more. Microsoft's article explains that the connector passes aggregate functions down to the container when a query filters on the partition key. So a report designed around that key can stay responsive.
Cross-partition analysis is harder. For aggregates across partitions, the same article points to Azure Synapse Link as the better route. We therefore look at your questions first and then recommend a mode, instead of choosing by habit. Speed problems after launch fall under Power BI performance tuning.
Pitfalls in a Power BI Azure Cosmos DB integration
These faults are common in document-based reporting.
- Reports that ignore the partition key
- Arrays expanded into millions of rows
- Missing fields treated as zero
- An account key shared in many files
- Refresh that scans every document each time
- No plan for fields the app adds later
Our steps for Power BI Azure Cosmos DB integration
Four stages take the work from sample documents to a model your team owns.
Read real documents
First we study samples with your developers and learn the partition key and the nesting.
Decide the mode
Then we choose Import or DirectQuery for each model and note the reason.
Flatten with care
Next we shape documents into tidy tables, so the model stays small and clear.
Test cost and speed
Finally we measure refresh and query load with your team, because throughput is a shared resource.
Azure Cosmos DB data in Power BI: common questions
Does Power BI have a native Azure Cosmos DB connector?
Yes. Microsoft's Power Query connector list includes an Azure Cosmos DB v2 connector, and its article marks it as generally available. The article lists Import and DirectQuery as supported for Power BI semantic models. For sign-in, it lists an account key and an organizational account.
Which Cosmos DB API does the Power BI connector cover?
Microsoft's own guide to this topic sits in the Azure Cosmos DB for NoSQL documentation, and that is the case we plan around. If your account uses another API, we check the current Microsoft articles first. Then we test against your data before we commit to a route.
Can Power BI use DirectQuery on Azure Cosmos DB?
Yes, the v2 connector article lists DirectQuery. It also explains that aggregates pass down to the container when the query filters on the partition key. So DirectQuery works best for reports designed around that key. For wide, cross-partition questions, Import or Azure Synapse Link is usually the wiser choice.
Will Power BI reports raise our Cosmos DB costs?
Reads consume throughput, so some effect is normal. We keep it small with filtered queries, sensible refresh times and models that load only the fields a report needs. We also review the load with your developers during testing. That way nobody meets a surprise after go-live.
How do we start a document data reporting project?
Call (480) 485-7373 or send a short message. One of our consultants replies within 2 hours and books a free 30-minute assessment. Power BI Agency is a division of Sataware Technologies and is independent of Microsoft. We work inside your own tenant and deliver online.
Let's build your first dashboard together.
Tell us what you report on today. A consultant replies within 2 hours with next steps.
Trusted partners
Microsoft Solutions Partner · AWS Partner
Cloud & AI Platforms
Azure SQL, Microsoft Fabric and the data pipelines behind your reports.
AI Business Solutions
Power BI reporting on your Dynamics 365, Business Central and Power Platform data.
Security
Row-level security and Entra ID sign-in on every report and workspace.
Amazon Web Services
Power BI on your AWS data, with connectors for Redshift, Athena and S3.