CODEWITHSUNDEEP
powerbiazure-cosmosdb
Jordán L. Ibáñez
Jordán L. Ibáñez

Reputation: 11

Optimize data load from Azure Cosmos DB to Power BI

Currently we have a problem with loading data when updating the report data with respect to the DB, since it has too many records and it takes forever to load all the data. The issue is how can I load only the data from the last year to avoid taking so long to load everything. As I see, trying to connect to the COSMO DB in the box allows me to place an SQL query, but I don't know how to do it in this type of non-relational database.

Example

Upvotes: 1

Views: 1835

Answers (3)

GregGalloway
GregGalloway

Reputation: 11625

Power BI has an incremental refresh feature. You should be able to refresh the current year only.

If that still doesn’t meet expectations I would look at a preview feature called Azure Synapse Link which automatically pulls all Cosmos DB updates out into analytical storage you can query much faster in Azure Synapse Analytics in order to refresh Power BI faster.

Upvotes: 3

Vilson C. Gärtner
Vilson C. Gärtner

Reputation: 1

In the Source of your Query, you can make a select based on the CosmosDB _ts system property, like:

Query ="SELECT * FROM XYZ AS t WHERE t._ts > 1609455599"

In this case, 1609455599 is the timestamp which corresponds to 31.12.2020, 23:59:59. So, only data from 2021 will be selected.

Upvotes: 0

Jon
Jon

Reputation: 4967

Depending on the volume of the data you will hit a number of issues. First is you may exceed your RU limit, slowing down the extraction of the data from CosmosDB. The second issue will be the transforming of the data from JSON format to a structured format.

I would try to write a query to specify the fields and items that you need. That will reduce the time of processing and getting the data.

For SQL queries it will be some thing like

SELECT * FROM c WHERE c.partitionEntity = 'guid'

For more information on the CosmosDB SQL API syntax please see here to get you started. You can use the query window in Azure to run the SQL commands, or Azure Storage Explorer to test the query, then move it to Power BI.

enter image description here

What is highly recommended is to extract the data into a place where is can be transformed into a strcutured format like a table or csv file.

For example use Azure Databricks to extract, then turn the JSON format into a table formatted object. You do have the option of using running Databricks notebook queries in CosmosDB, or Azure DataBricks in its own instance. One other option would to use change feed to send the data and an Azure Function to send and shred the data to Blob Storage and query it from there, using Power BI, DataBricks, Azure SQL Database etc.

Upvotes: 0

Related Questions