cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
tekulka New Member
New Member

Need to implement incremental refresh where DataSource is kusto

Hi Team,

 

So I want to implement incremental refresh for power BI premium. This is because we have around 300M+ records in our telemetry and we need to fetch it all in order to generate reports. Now since the data source is kusto, in incremental steps given as per documentation, I get added a step as FilteredRows in M QUery which when I right click I do not see Native query being enabled. This says the query is not foldable. As a result I'm trying to add these parameters (aka RangeStart and RangeEnd) to the first step while querying from kusto itself.

 I need to know if there;s a way I can formulate this query. I did not find any article regarding the same. Here's a snippet from my query

 

let KustoQuery = 
let Source = Json.Document(Web.Contents("*****.show version",[Query=[#"csl"="traces 
| where appName == ""****"" 
and timestamp > ago("&RangeStart&") 
and timestamp < ago("&RangeEnd&")
| project timestamp, *****

 

**** are appropriate value as per logs. We have a column called timestamp of type DateTime in kusto. The requirement is to pass this parameter in the form of RangeStart and RangeEnd in the source query itself.

 

Any help would be appreciated.

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft artemus
Microsoft

Re: Need to implement incremental refresh where DataSource is kusto

Kusto does not emit the native query. It does not mean it is not foldable. Add the step to see if it is folding:

Value.Metadata(PreviousStep)[QueryFolding]

Enable diagnonsis and I think you should be able to find the native kusto query being run.

 

Not sure if this helps, but this is what I'm using

 

let 
    FormatArgument = (arg as any) =>
        if arg is date then
            "datetime(" & Date.ToText(arg, "yyyy-MM-dd") & ")"
        else if arg is datetime then
            "datetime(" & DateTime.ToText(arg, "yyyy-MM-dd HH:mm:ss") & ")"
        else if arg is datetimezone then
            "datetime(" & DateTime.ToText(DateTimeZone.RemoveZone(DateTimeZone.ToUtc(arg)), "yyyy-MM-dd HH:mm:ss") & ")"
        else if arg is duration then
            "time(" & Duration.ToText(arg) & ")"
        else if arg = null then
            error "null value passed to function"
        else
            Text.From(arg)
in
    FormatArgument
let 
    KustoFn = (fn as text, args as list, options as nullable record) =>
        Kusto.Contents("yourcluster", "yourdb",
        fn & "(" & Text.Combine(List.Transform(args, #"FormatKustoArgs"), "," ) & ")"
        , options)
in
    KustoFn

Let me know if you see any bugs with using the kusto connector, I do have contributer access to this.

View solution in original post

1 REPLY 1
Microsoft artemus
Microsoft

Re: Need to implement incremental refresh where DataSource is kusto

Kusto does not emit the native query. It does not mean it is not foldable. Add the step to see if it is folding:

Value.Metadata(PreviousStep)[QueryFolding]

Enable diagnonsis and I think you should be able to find the native kusto query being run.

 

Not sure if this helps, but this is what I'm using

 

let 
    FormatArgument = (arg as any) =>
        if arg is date then
            "datetime(" & Date.ToText(arg, "yyyy-MM-dd") & ")"
        else if arg is datetime then
            "datetime(" & DateTime.ToText(arg, "yyyy-MM-dd HH:mm:ss") & ")"
        else if arg is datetimezone then
            "datetime(" & DateTime.ToText(DateTimeZone.RemoveZone(DateTimeZone.ToUtc(arg)), "yyyy-MM-dd HH:mm:ss") & ")"
        else if arg is duration then
            "time(" & Duration.ToText(arg) & ")"
        else if arg = null then
            error "null value passed to function"
        else
            Text.From(arg)
in
    FormatArgument
let 
    KustoFn = (fn as text, args as list, options as nullable record) =>
        Kusto.Contents("yourcluster", "yourdb",
        fn & "(" & Text.Combine(List.Transform(args, #"FormatKustoArgs"), "," ) & ")"
        , options)
in
    KustoFn

Let me know if you see any bugs with using the kusto connector, I do have contributer access to this.

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors