Cosmos DB to Dataverse Reporting Projection
On a schedule, the flow queries Cosmos DB for aggregate metrics (counts, sums, distributions by partition), projects them into a Dataverse reporting table, and refreshes a Power BI dashboard. Surfaces operational Cosmos data as governed business reporting.
Provided as-is, without warranty of any kind. Review and test each pattern in a non-production environment before deploying it to live automations. See our Terms.
Overview
This flow surfaces Azure Cosmos DB operational data as governed business reporting. On an hourly schedule it runs an aggregate (GROUP BY) query against Cosmos, projects each metric group into a dedicated Dataverse reporting table as a timestamped snapshot, and refreshes a Power BI dataset so dashboards stay current. Why it matters: Cosmos is excellent for applications but not for BI. Aggregating server-side (rather than reading raw documents) keeps RU cost low, and projecting the aggregates into Dataverse gives a governed, blendable, time-series reporting surface without hammering the operational store. Status: Built and shipped Off as a FlowLibs reference implementation (CF-796). Going live requires only authorizing the connections and setting the environment-variable values, with no flow-logic changes.
Use Case
A team runs an application backed by Azure Cosmos DB and wants business dashboards built from that data, including counts, sums, and distributions by partition/type, refreshed automatically and stored as governed snapshots for trend analysis.
Flow Architecture
Recurrence - Hourly Reporting Cadence
Recurrence (Hour / 1)Sets the refresh cadence. Adjust frequency/interval to match dashboard freshness needs.
Initialize Snapshot Batch Id
Initialize Variable (String)Mints a guid() batch/correlation id so all rows from one run are traceable together.
Initialize Snapshot Timestamp
Initialize Variable (String)Captures one utcNow() for the whole run so every row shares the same snapshot date.
Initialize Aggregate Query
Initialize Variable (String)Holds the configurable Cosmos SQL aggregate query from flowlibs_CosmosAggregateQuery.
Initialize Report Table Name
Initialize Variable (String)Documents the target Dataverse reporting table (entity set) from flowlibs_CosmosReportTableName.
Query Cosmos Aggregates
Azure Cosmos DB QueryDocuments_V5Runs the GROUP BY query against Cosmos. Results return under body/value.
Project Each Metric To Dataverse
Apply to each then Dataverse CreateRecordIterates the aggregate groups and writes one governed reporting row per group.
Refresh Power BI Dataset
Power BI RefreshDatasetRefreshes the dataset that sits over the Dataverse table so dashboards reflect the new snapshot.
Compose Run Summary
ComposeBuilds a run summary (batch id, snapshot date, target table, groups projected, PBI target) for run history/audit.
Environment Variables
| Schema name | Type | Default | Description |
|---|---|---|---|
| flowlibs_CosmosAccountName | String | flowlibs-cosmos | Cosmos DB account name (reused across FlowLibs solutions). |
| flowlibs_CosmosDatabaseId | String | ReferenceDb | Cosmos database id (reused). |
| flowlibs_CosmosContainerId | String | ReferenceData | Cosmos container id (reused). |
| flowlibs_CosmosAggregateQuery | String | SELECT c.type AS partitionKey, COUNT(1) AS n FROM c GROUP BY c.type | Configurable Cosmos SQL GROUP BY query that produces the metric groups (new). |
| flowlibs_CosmosReportTableName | String | flowlibs_cosmosreportsnapshots | Target Dataverse reporting table entity set (new). |
| flowlibs_PowerBIWorkspaceId | String | <configure> | Power BI workspace (group) id passed to RefreshDataset groupid (reused). |
| flowlibs_PowerBIDatasetId | String | <configure> | Power BI dataset id passed to RefreshDataset datasetid (reused). |
Connectors & Connections
| Connector | API name | Actions used |
|---|---|---|
| Azure Cosmos DB | shared_documentdb | QueryDocuments_V5 |
| Microsoft Dataverse | shared_commondataserviceforapps | CreateRecord |
| Power BI | shared_powerbi | RefreshDataset |
Note — All connections are referenced as solution connection references; the flow is portable between environments as long as a connection is mapped at import time.
Customization Guide
Almost every realistic variant of this flow can be implemented by changing environment variable values. A few cases require small edits inside the flow definition — those are called out explicitly below.
- Change the metric
- Edit flowlibs_CosmosAggregateQuery. The projection uses coalesce() so it tolerates different column names: it reads the group key from partitionKey or type, and the value from n, count, or total.
- Synapse Link
- Point the query at the analytical store instead of the transactional container for heavy aggregations.
- Partition view
- Add a partition dimension to the GROUP BY to get metrics per partition key; each becomes its own snapshot row.
- Time-series
- Every run stamps flowlibs_snapshotdate and flowlibs_snapshotbatchid, so the table is already a trend history; build Power BI visuals over the snapshot date.
- Cadence
- Change the Recurrence frequency/interval for more or less frequent refreshes.
Key Expressions
The flow is intentionally light on Power Fx / WDL gymnastics — the heaviest expressions are the branch-name concatenation and the approval outcome check. They are listed below in the order they appear in the flow.
EXPR.01Aggregate query (default)
The configurable Cosmos SQL GROUP BY query held in flowlibs_CosmosAggregateQuery.
EXPR.02Batch id
Mints a correlation id so all rows from one run are traceable together.
EXPR.03Snapshot timestamp
Captures one timestamp for the whole run so every row shares the same snapshot date.
EXPR.04Resilient value map
Reads the metric value from n, count, or total.
EXPR.05Resilient key map
Reads the group key from partitionKey or type.
EXPR.06Groups projected
Counts the aggregate groups returned by the Cosmos query.
Comments
Sign in to join the conversation.
Sign inNo comments yet. Be the first to share your experience with this flow.