Query folding with cosmos db on date field

[PBILover]

Hi ,

Thanks all the users for always helping!!

         I have a cosmos db as a source (JSON format), does power BI supports query folding? 

I am trying to do Query folding on a date time (for the incremental dataset refresh policy ), i am not sure if it is working. I checked in diagnostic steps option but it does not show that it has applied filters on the query for cosmos. 

       I tried 2 queries one with incremental dataset policy and another without incremental dataset refresh. First query is taking more than 40 -50 minutes to refresh while second query with whole dataset refresh takes only 20-25 minutes.

Is there any step by step document or any reference site which helps me in a query folding for cosmos db or implement a incremental data set refresh for cosmos db .

Thanks you very much.

[v-yingjl · Accepted solution]

Hi @PBILover ,

Please consider this point first when using query folding:

If you cannot use query folding of this data source in json format, try to use the data source connector.

About details in query folding, you can refer these videos and documents:

1. Query folding guidance in Power BI Desktop 
2. Power Query query folding 
3. Is Query Folding happening in Power BI? 
4. Native Query: Be careful when using in Power BI 

To configure incremental refresh, you can refer these documents and video:

1. Incremental refresh in Power BI 
2. All You Need to Know About the Incremental Refresh in Power BI: Load Changes Only 
3. Power BI Incremental Refresh - Understanding Detect Data Changes 

[v-yingjl · Accepted solution]

Hi @PBILover ,

Please consider this point first when using query folding:

If you cannot use query folding of this data source in json format, try to use the data source connector.

About details in query folding, you can refer these videos and documents:

1. Query folding guidance in Power BI Desktop 
2. Power Query query folding 
3. Is Query Folding happening in Power BI? 
4. Native Query: Be careful when using in Power BI 

To configure incremental refresh, you can refer these documents and video:

1. Incremental refresh in Power BI 
2. All You Need to Know About the Incremental Refresh in Power BI: Load Changes Only 
3. Power BI Incremental Refresh - Understanding Detect Data Changes 

[PBILover]

@v-yingjl  thank you very much for your reply.

For your suggestion 'If you cannot use query folding of this data source in json format, try to use the data source connector' , can you please let me know which data source connector shall i use?

[Sajjad]

Hi @PBILover ,

Were you able to achieve this? and improve dataset refresh time after implementing incremntal refresh.

Thanks,

Sajjad

[PBILover]

Yes.. It works

[Sajjad]

Hi @PBILover ,
Could you please shed more light into this ?

- How did you enable query folding for cosmos DB - as from MS documentation folding is supported for only relational DB's and Odata, and not for No SQL DB (cosmos DB).

- I have used Microsoft certified cosmos DB connector but unable to achieve query folding and reduce refresh time after implementing Incremental refresh. Which connector did you use?

- Any other important steps that are required to follow.

Thanks for your time.

Sajjad

[Anonymous]

I'm also trying to understand if query folding and therefore incremental refresh is possible usng Cosmos db as the data source.

I wonder if @PBILover was referring to this connector, which expicitly states that it does deliver query folding...

Nick

[v-yingjl · Accepted solution]

Hi @PBILover ,

Please try to use Azure Cosmos DB connector in power bi desktop, refer this document:

Azure Cosmos DB documentation 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.