Forum Discussion

gulrog's avatar
gulrog
New Member
10 years ago
Solved

Azure DocumentDB and slow refresh

I'm testing PowerBi and the azure documentdb datasource. It works fine, but when I try to Refresh the data it takes forever. I have 500 000 records in this database, and it uses around 15 minutes to ...
  • ankitpatira's avatar
    ankitpatira
    10 years ago

    gulrog Below is the screenshot of data refresh rate in power bi based on subscription. Even if you have Pro it is going to take a lot to refresh 25 million records. Data refresh in power bi is not delta so each time you refresh it gets all the data.

     

    I would suggest you look at limiting your data you import. Also power bi has direct query feature that doesn't import but connects live to data source. DirectQuery is available with azure sql db and azure sql dw but i don't think it is yet available with document db since it is still in beta. You can submit that as an idea to get it prioritised.

     

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Filtering DocumentDB data based on relative times at the source is possible with some tweaking:

 

1. Create a DocumentDB data source as normal. Do not enter any custom SQL.

2. Right click the query and select "Advanced Editor"

3. Immediately after the let line add in your custom query such as the example below. This uses the Power Query functions to construct a string on the fly that will be sent to the database. In this case it works out the date for 30 days ago and embeds that date and the current date into the string to get data for the last 30 days only. If done correctly you should see a new "querystring" step added under Query Settings.

 

let

   querystring = "SELECT * FROM yourdoc WHERE (yourdoc.datetimefield BETWEEN '" & DateTimeZone.ToText(DateTimeZone.UtcNow() - #duration(30,0,0,0),"yyyy-MM-ddTHH:mm:ss.000Z" ) & "' AND '" & DateTimeZone.ToText(DateTimeZone.UtcNow(),"yyyy-MM-ddTHH:mm:ss.000Z") & "')",

 

*** rest of original script ***

 

4. Under query settings go to the Source step. Should look something like:

 

= DocumentDB.Contents("https://yourserver.documents.azure.com", "yourdb", "yourcollection")

 

5. Add a 4th parameter to the end exactly as shown here-> [Query = querystring]

 

= DocumentDB.Contents("https://yourserver.documents.azure.com", "yourdb", "yourcollection", [Query = querystring])

 

PowerBI will now use a filtered query against DocumentDB

Kpham's avatar
Kpham
Resolver I
7 years ago

Dear all, 

 

How should my query look like when I specifically need to get data where  "Document.LaneId"=1

 

 

 
let
    Source = DocumentDB.Contents("https://xxx-iot-service-data.documents.azure.com/"),
    iotreportdata = Source{[id="iotreportdata"]}[Collections],
    iotreportdata_xxxTestingAggregatedBy5Minutes =      
    iotreportdata{[db_id="iotreportdata",id="XXXTestingAggregatedBy5Minutes"]}[Documents],
    #"Expanded Document" = Table.ExpandRecordColumn(iotreportdata_XXXTestingAggregatedBy5Minutes, "Document", 
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Document.ProductsInRange", Int64.Type}, {"Time", type time}})
in
    #"Changed Type2"