{"id":5722,"date":"2023-03-14T08:50:14","date_gmt":"2023-03-14T15:50:14","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/cosmosdb\/?p=5722"},"modified":"2023-03-14T09:20:03","modified_gmt":"2023-03-14T16:20:03","slug":"enabling-real-time-dashboards-azure-cosmos-db-now-supports-power-bi-directquery-mode-and-dataflows-in-preview","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/cosmosdb\/enabling-real-time-dashboards-azure-cosmos-db-now-supports-power-bi-directquery-mode-and-dataflows-in-preview\/","title":{"rendered":"Enabling real-time dashboards: Power BI DirectQuery mode and Dataflows support in Azure Cosmos DB"},"content":{"rendered":"

The <\/span>Azure Cosmos DB V2 Connector for Power BI<\/span><\/a>, with support for querying the Azure Cosmos DB transactional store in both DirectQuery and Import modes, is now in preview. The new V2 connector also supports Azure Cosmos DB as a data source in <\/span>Power BI dataflows<\/span><\/a>. It\u2019s available now on Power BI components<\/a> including Desktop, Power BI service, Power BI Mobile and on the Power Query<\/a> platform.<\/span><\/span><\/p>\n

Real-time reporting without importing data<\/h3>\n

The DirectQuery mode enables queries pushdown, including aggregations to the Azure Cosmos DB container, when a filter on partition key is specified. This new capability unlocks real time reporting and enables connecting directly to Cosmos DB without needing to import the data into Power BI cache. The DirectQuery mode in the V2 connector enables you to query and visualize large Azure Cosmos DB datasets without the limitations imposed by the Import mode.<\/span>\u00a0<\/span><\/p>\n

Before the V2 connector, Power BI developers and users were limited to the imported dataset, refreshed or not with a fixed schedule. Now with the support for in-partition aggregations, you can obtain data in real time and optimize performance with the aggregations being executed in Azure Cosmos DB, avoiding the load of big volumes of data into Power BI cache.<\/span>\u00a0<\/span><\/p>\n

Imagine a hypothetical IoT scenario where thousands of devices send billions of data points per day. Users want to monitor totals per day and when necessary, dig into one specific device. With the Import mode in V1 connector, users had to import the entire dataset into Power BI cache to dynamically perform aggregations and apply filters based on the given device. Now with the DirectQuery Mode, users can create a dashboard that will dynamically pass the DeviceId<\/em> <\/strong>as a parameter to the query that is pushed down to Azure Cosmos DB.<\/span>\u00a0<\/span><\/p>\n

For partitioned Azure Cosmos DB containers, a SQL query with an aggregate function is pushed down to Azure Cosmos DB if the query also contains a filter (WHERE clause) on the Partition Key. For example, considering DeviceId<\/em><\/strong> from the scenario above as the partition key, then a SQL query with aggregations can be pushed down when there is a filter on this property:<\/span>\u00a0<\/span><\/p>\n

SELECT SUM(ColumnName<\/em><\/strong>) FROM TableName<\/em> <\/strong>WHERE DeviceId<\/em> <\/strong>= ‘1234’ <\/span>\u00a0<\/span><\/p>\n

This is what we call in-partition aggregations. If you need cross-partition aggregations, we recommend <\/span>Azure Synapse Link for Azure Cosmos DB<\/span><\/a>.<\/span><\/p>\n

Please note that although aggregations would not be pushed down to Azure Cosmos DB when the query does not contain a filter on the Partition Key, the projections and filters would still be pushed down. For eg: if DeviceId<\/em> <\/strong>was not the partition key on the Cosmos DB container in the above query, the filter will be pushed down to Cosmos DB and the Sum aggregation of ColumnName<\/strong><\/i> will be performed in the Power BI cache.\u00a0<\/span><\/p>\n

The most optimal way to specify Partition Key filter is to use <\/span>Power BI dynamic M Parameters<\/span><\/a>. For that, you need a <\/span>Dashboard Slicer<\/span><\/a> to list the possible Partition Keys and the selected value is used as a parameter for the dashboard with the aggregation.\u00a0<\/span>\u00a0<\/span><\/p>\n

How to use Azure Cosmos DB V2 connector for Power BI<\/span>\u00a0<\/span><\/h3>\n

To start creating your Power BI dashboard, follow these steps:<\/span>\u00a0<\/span><\/p>\n

Step 1 – Get Data<\/span><\/h4>\n

Click on <\/span>Get Data<\/span><\/b> in Power BI Desktop and search for Azure Cosmos DB and choose the <\/span>Azure Cosmos DB V2 (Beta)<\/span><\/b> option. If a <\/span>third-party<\/span><\/b> warning pops up, ignore it, and click on <\/span>continue<\/span><\/b>. In the next screen is where you will decide between <\/span>Import<\/span><\/b> or <\/span>DirectQuery<\/span><\/b> modes. They are both supported now.<\/span><\/p>\n

\"Configure<\/a>
Configure your connection to Azure Cosmos DB using the V2 connector, including the DirectQuery mode now<\/em><\/figcaption><\/figure><\/p>\n

<\/h4>\n

Step 2 – Specify the partition key dynamic filter<\/h4>\n

To specify the Partition Key filter using dynamic M parameters after the initial configuration, you would create a dataset with unique Partition Key values, create a parameter, add it as filter on main dataset, bind it to the unique Partition key dataset, and use it as a slicer for the main dataset. Please follow the detailed steps to <\/span>specify the Partition Key filter using dynamic M parameters<\/span><\/a>. You can then add visualizations and apply Partition Key filter from the slicer as shown below.<\/span><\/p>\n

\"Filter<\/a>
Filter on a partition key to push aggregations down to Azure Cosmos DB<\/em><\/figcaption><\/figure><\/p>\n

<\/h3>\n

Use Azure Cosmos DB as source in Power BI dataflows<\/h3>\n

The <\/span>Azure Cosmos DB V2 Connector for Power BI<\/span><\/a> supports Azure Cosmos DB as data source in <\/span>Power BI dataflows,<\/span><\/a> which can be used to create reusable transformation logic and can be shared across datasets, reports, and dashboards. To create a dataflow with Azure Cosmos DB as data source, use the \u201cDefine new tables\u201d option from dataflow interface, choose Azure Cosmos DB v2 as data source, as shown below and specify any transformation logic that that can be reused across Power BI artifacts.<\/span>\u00a0<\/span><\/p>\n

\"Image<\/a><\/p>\n

Azure Cosmos DB V2 is now a supported data source on Power Query platform.<\/em><\/p>\n

Once the Dataflow is created, it can be consumed from Power BI Desktop and Power BI service. To consume the Azure Cosmos DB based Dataflow from Power BI Desktop, select \u201cDataflows\u201d in the Get Data dropdown. Select the Azure Cosmos DB Dataflow to create a dataset that will allow you to create reports and dashboards. You can <\/span>connect to the Dataflow in both DirectQuery and Import modes.<\/span><\/a><\/p>\n

\"Dataflow<\/a>
Dataflow Get Data in Power BI Desktop<\/em><\/figcaption><\/figure><\/p>\n

<\/h3>\n

What to use When<\/span>\u00a0<\/span><\/h3>\n

With this announcement, customers now have three options to implement Business Intelligence solutions on top of their Azure Cosmos DB data: <\/span>Azure Cosmos DB V2 connector for Power BI<\/span><\/a>, <\/span>Azure Synapse Link<\/span><\/a>, and <\/span>Azure Cosmos DB ODBC driver<\/span><\/a>. But what is the right option for you?<\/span>\u00a0<\/span><\/p>\n\n\n\n\n\n\n\n
Option<\/span><\/b>\u00a0<\/span><\/td>\nUsed for\u2026<\/span><\/b>\u00a0<\/span><\/td>\n<\/tr>\n
Azure Cosmos DB V1 connector for Power BI<\/span>\u00a0<\/span><\/td>\n\n
    \n
  • Legacy version now, upgrade to V2 when possible.<\/span>\u00a0<\/span><\/li>\n<\/ul>\n<\/td>\n<\/tr>\n
Azure Cosmos DB V2 connector for Power BI<\/span>\u00a0<\/span><\/td>\n\n
Azure Synapse Link for Cosmos DB<\/span>\u00a0<\/span><\/td>\n\n
Azure Cosmos DB ODBC driver<\/span>\u00a0<\/span><\/td>\n\n