Forum Discussion
Incremental Refresh Using Stored Procedure, Parameters Not Overridden in Power BI Service
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
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.
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.
6 Replies
- lbendlinSuper User
How many partitions have been created for that table?
- jjhendricksonFrequent Visitor
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?
- lbendlinSuper User
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.
- jjhendricksonFrequent Visitor
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.