Visualize Azure Cosmos DB data using Power BI
APPLIES TO: NoSQL
This article describes the steps required to connect Azure Cosmos DB data to Power BI Desktop.
You can connect to Azure Cosmos DB from Power BI desktop by using one of these options:
Use Azure Synapse Link to build Power BI reports with no performance or cost impact to your transactional workloads, and no ETL pipelines.
You can either use DirectQuery or import mode. With DirectQuery, you can build dashboards/reports using live data from your Azure Cosmos DB accounts, without importing or copying the data into Power BI.
Connect Power BI Desktop to Azure Cosmos DB account with the Azure Cosmos DB connector for Power BI. This option is only available in import mode and will consume RUs allocated for your transactional workloads.
Reports created in Power BI Desktop can be published to PowerBI.com. Direct extraction of Azure Cosmos DB data cannot be performed from PowerBI.com.
Before following the instructions in this Power BI tutorial, ensure that you have access to the following resources:
Create an Azure Cosmos DB database account and add data to your Azure Cosmos DB containers.
To share your reports in PowerBI.com, you must have an account in PowerBI.com. To learn more about Power BI and Power BI Pro, see https://powerbi.microsoft.com/pricing.
Let's get started
Building BI reports using Azure Synapse Link
You can enable Azure Synapse Link on your existing Azure Cosmos DB containers and build BI reports on this data, in just a few clicks using Azure Cosmos DB portal. Power BI will connect to Azure Cosmos DB using Direct Query mode, allowing you to query your live Azure Cosmos DB data, without impacting your transactional workloads.
To build a Power BI report/dashboard:
Sign into the Azure portal and navigate to your Azure Cosmos DB account.
From the Integrations section, open the Power BI pane and select Get started.
Currently, this option is only available for API for NoSQL accounts. You can create T-SQL views directly in Synapse serverless SQL pools and build BI dashboards for Azure Cosmos DB for MongoDB. See "Use Power BI and serverless Synapse SQL pool to analyze Azure Cosmos DB data with Synapse" for more information.
From the Enable Azure Synapse Link tab, you can enable Synapse Link on your account from Enable Azure Synapse link for this account section. If Synapse Link is already enabled for your account, you will not see this tab. This step is a pre-requisite to start enabling Synapse Link on your containers.
Enabling Azure Synapse Link has cost implications. See Azure Synapse Link pricing section for more details.
Next from the Enable Azure Synapse Link for your containers section, choose the required containers to enable Synapse Link.
If you already enabled Synapse Link on some containers, you will see the checkbox next to the container name is selected. You may optionally deselect them, based on the data you'd like to visualize in Power BI.
If Synapse Link isn't enabled, you can enable this on your existing containers.
If enabling Synapse Link is in progress on any of the containers, the data from those containers will not be included. You should come back to this tab later and import data when the containers are enabled.
Depending on the amount of data in your containers, it may take a while to enable Synapse Link. To learn more, see enable Synapse Link on existing containers article.
You can check the progress in the portal as shown in the following screen. Containers are enabled with Synapse Link when the progress reaches 100%.
From the Select workspace tab, choose the Azure Synapse Analytics workspace and select Next. This step will automatically create T-SQL views in Synapse Analytics, for the containers selected earlier. For more information on T-SQL views required to connect your Azure Cosmos DB to Power BI, see Prepare views article.
Your Azure Cosmos DB container proprieties will be represented as columns in T-SQL views, including deep nested JSON data. This is a quick start for your BI dashboards. These views will be available in your Synapse workspace/database; you can also use these exact same views in Synapse Workspace for data exploration, data science, data engineering, etc. Please note that advanced scenarios may demand more complex views or fine tuning of these views, for better performance. For more information. see best practices for Synapse Link when using Synapse serverless SQL pools article.
You can either choose an existing workspace or create a new one. To select an existing workspace, provide the Subscription, Workspace, and the Database details. Azure portal will use your Azure AD credentials to automatically connect to your Synapse workspace and create T-SQL views. Make sure you have "Synapse administrator" permissions to this workspace.
Next, select Download .pbids to download the Power BI data source file. Open the downloaded file. It contains the required connection information and opens Power BI desktop.
You can now connect to Azure Cosmos DB data from Power BI desktop. A list of T-SQL views corresponding to the data in each container are displayed.
For example, the following screen shows vehicle fleet data. You can load this data for further analysis or transform it before loading.
You can now start building the report using Azure Cosmos DB's analytical data. Any changes to your data will be reflected in the report, as soon as the data is replicated to analytical store, which typically happens in a couple of minutes.
Building BI reports using Power BI connector
Connecting to Azure Cosmos DB with the Power BI connector is currently supported for Azure Cosmos DB for NoSQL and API for Gremlin accounts only.
Run Power BI Desktop.
You can Get Data, see Recent Sources, or Open Other Reports directly from the welcome screen. Select the "X" at the top right corner to close the screen. The Report view of Power BI Desktop is displayed.
Select the Home ribbon, then click on Get Data. The Get Data window should appear.
Click on Azure, select Azure Cosmos DB (Beta), and then click Connect.
On the Preview Connector page, click Continue. The Azure Cosmos DB window appears.
Specify the Azure Cosmos DB account endpoint URL you would like to retrieve the data from as shown below, and then click OK. To use your own account, you can retrieve the URL from the URI box in the Keys blade of the Azure portal. Optionally you can provide the database name, collection name or use the navigator to select the database and collection to identify where the data comes from.
If you are connecting to this endpoint for the first time, you are prompted for the account key. For your own account, retrieve the key from the Primary Key box in the Read-only Keys blade of the Azure portal. Enter the appropriate key and then click Connect.
We recommend that you use the read-only key when building reports. This prevents unnecessary exposure of the primary key to potential security risks. The read-only key is available from the Keys blade of the Azure portal.
When the account is successfully connected, the Navigator pane appears. The Navigator shows a list of databases under the account.
Click and expand on the database where the data for the report comes from. Now, select a collection that contains the data to retrieve.
The Preview pane shows a list of Record items. A Document is represented as a Record type in Power BI. Similarly, a nested JSON block inside a document is also a Record. To view the the properties documents as columns, click on the grey button with 2 arrows in opposite directions that symbolize the expansion of the record. It's located on the right of the container's name, in the same preview pane.
Power BI Desktop Report view is where you can start creating reports to visualize data. You can create reports by dragging and dropping fields into the Report canvas.
There are two ways to refresh data: ad hoc and scheduled. Simply click Refresh Now to refresh the data. Check Power BI documentation for more information about the scheduled refresh option.