topic Re: Incremental refresh for custom connector with call to REST API: RangeStart and RangeEnd in Developer

Incremental refresh for custom connector with call to REST API: RangeStart and RangeEnd

emikelsoft — Tue, 05 Mar 2024 10:56:51 GMT

Hi,

I have developed a custom connector for Power BI with NavigationTables as shown in the sample of Power Query SDK.
How can I pass RangeStart and RangeEnd Parameters to my Rest API?
Where and when does the incremental refresh Logic set these parameters in the Power BI Service?

My Function:

[DataSource.Kind = "Foo", Publish = "Foo.Publish"]

shared Foo.Contents = Value.ReplaceType(FooImplementation, type function (Url as Uri.Type) as any);

I already tried:
shared Foo.Contents = Value.ReplaceType(FooImplementation, type function (Url as Uri.Type, optional RangeStart as nullable datetime, optional RangeEnd as nullable datetime) as any);

But RangeStart and RangeEnd are always empty. If I specifiy the values when I connect to the datasource, I always get the specified values and not the overwritten ones from the incremental refresh logic.

Any Ideas?
BR, Michael

Re: Incremental refresh for custom connector with call to REST API: RangeStart and RangeEnd

lbendlin — Tue, 05 Mar 2024 21:57:09 GMT

RangeStart and RangeEnd must be included in the construction of the URL. Note that if your query doesn't fold (which is rather likely to happen here) then you will not get any performance gains from Incremental Refresh.

Re: Incremental refresh for custom connector with call to REST API: RangeStart and RangeEnd

emikelsoft — Wed, 06 Mar 2024 05:52:18 GMT

I include the parameters in the url. The problem is that the parameters are always empty. Is it the right way to defne them as I did in the original post?

Re: Incremental refresh for custom connector with call to REST API: RangeStart and RangeEnd

lbendlin — Wed, 06 Mar 2024 11:52:56 GMT

Sounds like you didn't mark the table as incremental refresh yet and didn't publish the pbix?

Re: Incremental refresh for custom connector with call to REST API: RangeStart and RangeEnd

emikelsoft — Wed, 06 Mar 2024 13:09:51 GMT

I aslo marked the table as incremental refresh and published the pbix.
I really comes down to the question if the definition of the params in the custom connector is at the right place and has the right naming.

Is this the right way of doing it:

shared Foo.Contents = Value.ReplaceType(FooImplementation, type function (Url as Uri.Type, optional RangeStart as nullable datetime, optional RangeEnd as nullable datetime) as any);

Re: Incremental refresh for custom connector with call to REST API: RangeStart and RangeEnd

lbendlin — Wed, 06 Mar 2024 13:19:17 GMT

RangeStart and RangeEnd must be included in the construction of the URL. I don't see that - you seem to be supplying them separately.

Re: Incremental refresh for custom connector with call to REST API: RangeStart and RangeEnd

emikelsoft — Wed, 06 Mar 2024 13:23:35 GMT

Here is the full code:

[Version = "1.0.0"]
section Foo;

[DataSource.Kind = "Foo", Publish = "Foo.Publish"]
shared Foo.Contents = Value.ReplaceType(FooNavTable, type function (Url as Uri.Type, optional RangeStart as nullable datetime, optional RangeEnd as nullable datetime) as any);

DefaultRequestHeaders = [
#"Content-Type" = "application/json",
#"api-key" = Extension.CurrentCredential()[Key]
];

FooNavTable = (url as text, optional rangeStart as nullable datetime, optional rangeEnd as nullable datetime) as table =>
let
source = Json.Document(Web.Contents(url, [Headers = DefaultRequestHeaders, RelativePath = "navTable"])),
tablesList = List.Transform(source, each
[
Name = _[Name],
KeyColumn = _[KeyColumn], // Der Schlüssel sollte eindeutig sein
Data = GetTable(url, Name, KeyColumn, _[Schema], rangeStart, rangeEnd),
ItemKind = "Table",
ItemName = "Table",
IsLeaf = true
]),

tablesTable = Table.FromRecords(tablesList),

navigationTable = Table.ToNavigationTable(tablesTable, {"Name"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")
in
navigationTable;

GetTable = (url as text, tableName as text, keyColumn as text, schema as record, optional rangeStart as nullable datetime, optional rangeEnd as nullable datetime) as table =>
let
startDate = if rangeStart = null then "" else DateTime.ToText(rangeStart, "yyyy-MM-dd"),
endDate = if rangeEnd = null then "" else DateTime.ToText(rangeEnd, "yyyy-MM-dd"),
source = Json.Document(Web.Contents(url, [Headers = DefaultRequestHeaders, Query=[rangeStart=startDate, rangeEnd=endDate], RelativePath = tableName])),
toTable = Table.FromRecords(source),
typedTable = SetDataTypes(toTable, schema),
typedTableWithKey = Table.AddKey(typedTable, { keyColumn }, true)
in
typedTableWithKey;

SetDataTypes = (table as table, schema as record) as table =>
let
schemaTable = Record.ToTable(schema),
transformationList = List.Transform(Table.ToRows(schemaTable),
each {_{0}, GetTypeFromString(_{1})
}),
transformedTable = Table.TransformColumnTypes(table, transformationList)
in
transformedTable;

GetTypeFromString = (typeString as text) as type =>
let
result = if typeString = "number" then Int64.Type
else if typeString = "string" then Text.Type
else if typeString = "boolean" then Logical.Type
else if typeString = "Date" then Date.Type
else if typeString = "DateTime" then DateTime.Type
else if typeString = "Time" then Time.Type
else if typeString = "Decimal" then Decimal.Type
else Any.Type
in result;

Re: Incremental refresh for custom connector with call to REST API: RangeStart and RangeEnd

lbendlin — Wed, 06 Mar 2024 15:55:29 GMT

Everything in Power Query is case sensitive. "rangeStart" and "rangeEnd" will not work.

You must hand over the URL already precompiled. The Power BI service must be able to inject the RangeStart and RangeEnd values directly.

Re: Incremental refresh for custom connector with call to REST API: RangeStart and RangeEnd

emikelsoft — Wed, 06 Mar 2024 16:55:21 GMT

RangeStart and RangeEnd is upercase at the shared function. Do you mean it should be upercase on the other functions as well?

Or can you give an example to see where exactly those params get injected?

By the way, the url can not be precompiled because we need our customer to connect to his individual endpoint.

Many thanks for your help so far.

Re: Incremental refresh for custom connector with call to REST API: RangeStart and RangeEnd

lbendlin — Wed, 06 Mar 2024 17:51:33 GMT

Incremental refresh only works on partition level. I cannot see how you would be able to add additional filters like your customer name. Are you expecting this to address different partitions?

Let's take a step back. What is your primary reason for trying to use Incremental Refresh?

Re: Incremental refresh for custom connector with call to REST API: RangeStart and RangeEnd

emikelsoft — Wed, 06 Mar 2024 20:37:00 GMT

We want to reduce the amount of data to be transfered. We configured to load factData for the last 5 years into the archive partition, so only once. The last 3 month should load on every refresh to get the latest data. So we applied incremental refresh on a timestamp-columd.

Re: Incremental refresh for custom connector with call to REST API: RangeStart and RangeEnd

lbendlin — Wed, 06 Mar 2024 21:05:23 GMT

Will you have separate semantic models for each of the customers?

Re: Incremental refresh for custom connector with call to REST API: RangeStart and RangeEnd

emikelsoft — Wed, 06 Mar 2024 21:38:52 GMT

yes! But we are talking about a custom connector. From other posts I learned, that custom connectors have the ability for incremental refresh. Unfortunately I can not find any samples online.

Re: Incremental refresh for custom connector with call to REST API: RangeStart and RangeEnd

lbendlin — Wed, 06 Mar 2024 21:50:28 GMT

The way incremental refresh works is as follows:

You (the developer) create a Power Query script that access your data source (including via a custom connector). The Power Query code contains a filter for RangeStart (inclusive) and RangeEnd (exclusive). That filter can be in the standard M code or it can be a part of the constructed but visible URL of the custom connector.

The Power BI service is managing the partitions and will inject the appropriate values for RangeStart and RangeEnd into the query for each partition.

If the Power BI service cannot set these parameters in a way that allows your custom connector to consume them then this won't work.

Re: Incremental refresh for custom connector with call to REST API: RangeStart and RangeEnd

emikelsoft — Wed, 06 Mar 2024 21:55:50 GMT

Ok, can you give an example for that filter within an custom connector?

Re: Incremental refresh for custom connector with call to REST API: RangeStart and RangeEnd

lbendlin — Wed, 06 Mar 2024 22:15:39 GMT

I don't have an example for that. Best I can give you is this

let Source = PowerBIRESTAPI.Navigation(), #"Filtered Rows" = Table.SelectRows(Source{[Key="AppWorkspace"]}[Data]{[Key="RefreshHistory"]}[Data], each DateTime.From([Start Time]) >= RangeStart and DateTime.From([Start Time]) < RangeEnd) in #"Filtered Rows"

That likely doesn't fold, so the performance benefits are minimal (the space benefits still apply).

Re: Incremental refresh for custom connector with call to REST API: RangeStart and RangeEnd

emikelsoft — Wed, 06 Mar 2024 22:31:40 GMT

There are many examples like this on the internet. But unfortunately not any showing how to so that in a custom connector. Where should RangeStart and RangeEnd be defined. You cannot not just use this patameters without defining them before.

Anyway, thank you for your help

Re: Incremental refresh for custom connector with call to REST API: RangeStart and RangeEnd

emikelsoft — Sat, 09 Mar 2024 18:42:39 GMT

I found that query folding might be the issue. Currently I could make some steps into the right direction: Implementing paging and Table.View.

The missing link is still to get the RangeStart and RangeEnd params into the connector. The implementation of my connector is more or less the same as the Tripin Sample with Folding.

Has anybody a sample for doing it in a custom connector?

br, Michael

Re: Incremental refresh for custom connector with call to REST API: RangeStart and RangeEnd

emikelsoft — Tue, 12 Mar 2024 09:41:23 GMT

Finally we found the solution:
It is all up to query folding. The article helped a lot:
https://bengribaudo.com/blog/2022/01/20/6500/power-query-m-primer-part-23-query-folding-i

One does not need to define RangeStart nor RangeEnd parameters anywhere in the connector.
Instead one should implement Table.View as shown here:
https://learn.microsoft.com/en-us/power-query/samples/trippin/10-tableview1/readme

The only missing information is that one need to implement OnSelectRows.
Further information:
https://community.fabric.microsoft.com/t5/Power-Query/Query-folding-Table-View-handler-function-to-filter-rows-add/td-p/3154149

br, Michael

Re: Incremental refresh for custom connector with call to REST API: RangeStart and RangeEnd

lbendlin — Tue, 12 Mar 2024 12:34:42 GMT

One does not need to define RangeStart nor RangeEnd parameters anywhere in the connector.

They need to be used in the Power Query that calls the connector.

How are you planning to do the partition refresh? All manually?