Cosmos DB to Dataverse Reporting Projection

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

Environment Variables

Connectors & Connections

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.

SELECT c.type AS partitionKey, COUNT(1) AS n FROM c GROUP BY c.type

@{guid()}

@{utcNow()}

@coalesce(items('Project_Each_Metric_To_Dataverse')?['n'], items('Project_Each_Metric_To_Dataverse')?['count'], items('Project_Each_Metric_To_Dataverse')?['total'], 0)

@{coalesce(string(items('Project_Each_Metric_To_Dataverse')?['partitionKey']), string(items('Project_Each_Metric_To_Dataverse')?['type']), 'all')}

@length(coalesce(outputs('Query_Cosmos_Aggregates')?['body/value'], json('[]')))