Incremental Refresh Using Stored Procedure, Parameters Not Overridden in Power BI Service

[jjhendrickson]

Hello,
I am attempting to use configure incremental refresh on dat retrieved from a stored procedure. I have set up my RangeStart and RangeEnd parameters as date/time and set them one day apart. Then I wrote the following M code to load data from the SP.

let
    Source = Sql.Database("myConnectionString", "myDatabase",[CommandTimeout=#duration(0, 2, 0, 0)]),
    NativeQuery = Value.NativeQuery
        (
            Source,
            "
            EXEC mySchema.myStoredProcedure
                @startDate,
                @endDate,
                @return
            ",
            [startDate=RangeStart,endDate=RangeEnd,return="Screenings"],
            [EnableFolding=true]
        )
in
    NativeQuery

The @return variable here determines which dataset in returned from the SP execution.
I then set up inceremental refresh on the table, choosing three years for the archive and 10 days from the increment.

The refresh works just fine is desktop and I see one day of data as expected. However, after publishing to the Power BI Service and triggering an initial refresh. I do not see data from the full archive period as expected. Instead, I still only see only the one day of data that the RangeStart and RangeEnd parameters - as configured in the desktop file - allow. This persists after multiple refresh attempts. Essentially, the Power BI Service does not seem to be overridding the incremental refresh parameters based on the incremental refresh configuration.

Additional Notes:
This table is used as a source for other tables in the semantic model (referenced)

This table is hidden in my semantic model (load is enabled)
My SP variables startDate and endDate are of the data type datetime

[lbendlin · Accepted solution]

correct. They don't know anything about the query that runs on that table.  Don't use them, find another way, for example via the data model.

[jjhendrickson · Accepted solution]

I found this workaround. Re: incremental refresh on table by reference - Microsoft Fabric Community , which fixes the issue by passing the incremental refresh parameters to dummy variables in the reference table queries. With the dummy variables in the reference queries, Incremental Refresh configurations can be applied (there will be a warning that query folding can't be confirmed). After publishing and refreshing, all tables now have partitions and the correct amount fo data.

[lbendlin]

How many partitions have been created for that table?

[jjhendrickson]

Good prompt. I connected via SSMS to the XMLA endpoint and saw that the table loaded from the SP actually does have multiple partitions and the correct amount of rows. This table is hidden in my semantic model because it is a staging table for two other tables that use it as a referenced source. Both of these tables have no partitions and only have data for the one day. It seems that reference tables do not "inherit" the incremental refresh settings from the source table?

[lbendlin · Accepted solution]

correct. They don't know anything about the query that runs on that table.  Don't use them, find another way, for example via the data model.

[jjhendrickson]

Thanks, this knowledge helped me to discover a workaround. 

[lbendlin]

Don't do it. Get rid of the need for these tables.  The "may not fold"  warning has a reason - you may negate the performance benefits of the fully folded incremental refresh, and all you get is partition management.