Azure Synapse Link: Announcing support for existing Azure Cosmos DB containers and seamless Power BI integration

This blog post was co-authored by Rodrigo Souza and Anitha Adusumilli from the Azure Cosmos DB Team.

Today we are announcing General Availability of Azure Synapse Link for existing Azure Cosmos DB containers. Using Azure Synapse Link, you can also now build Power BI dashboards with just a few clicks from Azure Cosmos DB portal.

Azure Synapse Link for Azure Cosmos DB is a cloud-native hybrid transactional and analytical processing (HTAP) capability that enables near real time analytics over operational data in Azure Cosmos DB.

Azure Synapse Link for existing Azure Cosmos containers

You can now enable analytical store and unlock Synapse Link for your existing Cosmos containers, which will trigger the initial sync of the data from transactional store to analytical store. There is no cost or performance impact on your transactional workloads, and you can do it using the Azure Portal, Azure Cosmos DB SDKs, Azure CLI, or PowerShell.

The sync process may last from seconds to days, depending on your data volume. You can check the progress in the Azure Portal, using the new left navigation item for Azure Synapse Link, located in the Integrations section. The image below shows you how to locate the Azure Synapse Link left navigation item and monitor the progress of your initial synchronization.

Please note that:

This new feature is only available for Azure Cosmos DB SQL API. The same capability for collections using our MongoDB API for Azure Cosmos DB is on our roadmap.
If you are using Azure Cosmos DB SDKs, Azure CLI, or PowerShell, analytical store is enabled by simply setting the analytical TTL property to -1, meaning that data is kept in the analytical store indefinitely, or to any positive integer, that is the number of seconds that the data will be kept in analytical store. As an example, if you set analytical TTL to 31536000, your data will be kept in analytical store for 1 year.
You can enable Synapse Link on multiple containers in an account at the same time.
You won’t be able to use Synapse Link on containers while initial sync is in progress between transactional and analytical store.

Once Synapse Link is enabled on your container, you can analyze the data in your container by using Synapse Spark or Synapse serverless SQL pools. You can directly access your Cosmos DB container’s analytical store data in multiple Synapse workspaces. There’s no cost or performance impact on your OLTP workloads when using Synapse Link.
You can also disable analytical store using PowerShell. You cannot re-enable analytical store once disabled.

Click here to learn more on how to configure and use Azure Synapse Link for Azure Cosmos DB, including how to enable analytical store on existing containers.

Integrated Azure Synapse Link Power BI experience

With the integrated Power BI experience in the Azure Cosmos DB portal, you can visualize your Azure Cosmos DB data in near real time in just a few clicks. Synapse Link enables you to build Power BI reports and dashboards with no performance or cost impact to your transactional workloads, and no ETL pipelines. You can either use DirectQuery or import modes. With DirectQuery, Power BI can query live data from your Azure Cosmos DB accounts, without importing or copying the data into Power BI.

To get started:

Step 1: Click on “Power BI” under “Integrations” in the Azure Cosmos DB portal. Select containers to start building your BI dashboard. If the containers are not enabled with Synapse Link, you will need to enable this before you can proceed further. It may take some time for the initial sync of transactional store to analytical store.
Step 2: Pick your Synapse workspace and serverless SQL pool database. Using Synapse Link, this step will automatically create T-SQL views for the Cosmos DB containers you’ve selected.
Step 3: You can directly now download .pbix file, which is initialized with your Synapse SQL connection credentials and with all the T-SQL views included as data sources.

Clicking on the .pbix file opens Power BI Desktop. That’s it – with just a few clicks, you can now start creating your BI reports.