Filtered query to Cosmos DB for large data set

[apalomo]

Hello,

I am storing a large IoT data set in a Cosmos DB collection. At the moment I am using Power BI to exploit and display the information stored in this collection.

The more the collection size grows, the more Power BI struggles updating the data sets and visualizations. For this reason, I added a filter to my tables in order to load only the information for the last 7 days.

However, I get the impression that Power BI does the following:

- Retrieves all the information from the Cosmos DB collection

- Then, filters out the information for the last 7 days

In this way, every time I update the Power BI data set it takes a long time. I was wondering if it is possible to filter the information directly in the query to Cosmos DB, so only the records for the last 7 days are return.

I know how to do this with Azure Storage Explorer, for example, but I am not aware how to do it in Power BI, or if it is still possible.

Regards,

Álvaro

[v-yulgu-msft]

Hi @apalomo,

You can directly add filters in data query code. Please provide a custom SQL statement where you can add data filter.

Best regards,

Yuliana Gu

[apalomo]

Hello Yuliana,

I found an mid-way solution using parameters. I defined "initDate" and "finishDate" as text-type parameters.

Then I modified the query so it looks like:

= DocumentDB.Contents("https://{comos-db-name}.documents.azure.com:443/", "{db-name}", "{collection-name}", [Query = "SELECT * FROM Document WHERE Document.date BETWEEN '"&initDate&"' AND '"&finishDate&"'"])

However, I am still wondering if it can be done playing with dates built-in functions.

[mtzvictor]

I found the solution in this post

https://community.powerbi.com/t5/Service/Azure-DocumentDB-and-slow-refresh/m-p/115509#M18257

Basically you have to:

Go to advanced Editor and do something as below. The function #duration(7,0,0,0) is where you subtract days from UTCNOW()

In this case the first value indicate 7 days, the next one hours, and so forth.

let 

querystring= "SELECT * FROM c WHERE ( c.YOURTimeVAR >= '" & DateTimeZone.ToText(DateTimeZone.UtcNow() - #duration(7,0,0,0),"yyyy-MM-ddTHH:mm:ss.000Z" ) & "')",
Source = DocumentDB.Contents("URLTOYOURCOSMOSDB", "DBNAME", "COLLECTION", [Query = querystring])

in

Source

Hope this helps

[Anonymous]

Greetings,

This was never worked in my case and  i have used the below query to fetch the data from power bi

SELECT * FROM c WHERE (c.StartDate BETWEEN '" & DateTimeZone.ToText(DateTimeZone.UtcNow() - #duration(7,0,0,0),"yyyy-MM-ddTHH:mm:ss.000Z" ) & "' AND '" & DateTimeZone.ToText(DateTimeZone.UtcNow(),"yyyy-MM-ddTHH:mm:ss.000Z") & "')

In order to test this query on cosmosdb query explorer. I did run this query and below is the error:

There is no response from microsoft yet after opening at ticket.

[mtzvictor]

I want to know that, but also if there is a way that instead of using Text parameters as you mention, use GETDATE(),

[apalomo]

Hello Yuliana,

thanks for the reply. In my document records a I have field "date", so one of the queries I can use for gathering the information for the last 7 days is the following:

SELECT * FROM Documents WHERE Documents.date BETWEEN '2018-06-01' AND '2018-06-06'

However, I would like to make this dynamic, so everytime I update my data set it takes the last 7 days. I have tried the next:

SELECT * FROM Documents WHERE Documents.date BETWEEN getdate()-7 AND getdate()

which normally would work in SQL, but not in this case. The getdate() function is not built-in in the SQL engine of Cosmos DB.

I have checked the library of built-in functions in Cosmos DB and I have to admit I am a bit disappointed with the lack of functions for dealing with dates.

Do you know any way to carry out this dynamic filtering in Power BI when I retreive the data? Maybe with parameters?

Regards,