Solved: Re: Custom query - Incremental Refresh

Anonymous · ‎05-08-2023

Hi everyone.

I'm having issues trying to implement this solution described by Chris Webb in his blog. This process should create a custom way to choose which partition refresh in a incremental.

Following Chris steps I have my fact table with incremental configuration (monthly).

This table has every partition needed.

Then I have a custom table with one row for every partition date interval.

I loaded this table using Power Query, filtered as Chris said, with the same parameters as fact table.

Then I used Tabular Editor 2 to configure the PollingExpression.

But this is where my problem begins. When I ran the full refresh using TMSL script I noted that my table script doesn't show refreshBookmark option.

Even without that option configured, I tried to run a partial refresh with the output column set to 2 but Server Management gave me back this error.

So, after all this thread. Can someone please indicate me what I'm doing wrong. I tried to follow every step but I don't know if my problem came from tables configuration in Power Query, or applying PollingExpression, or running TSML code in Management Studio.

Thanks to anyone who can help me.

Anonymous · ‎05-11-2023

Hi Chris.

I finally made it work. Thanks to @nexus150 (Ricardo Rincón) and you Chris for all your help.

My issue was that I configured wrong the incremental refresh. I selected an archive data starting longer than rows in my custom table. Let me explain me better:

- I have 9 rows (months intervals) in my custom table but selected 60 months to archive data. So, PollingExpression was looking for months that didn't exist. Changing archive data to 8 rows solved the issue.

Also, in my case, the refreshBookmark property was created just after the first reload with applyRefreshPolicy: true, never with false as were the case at your blog.

Finally, I test some different scenarios just to be sure how it work this method and these are my takeaways:

Test #1: using date field with data in just one partition and loading everything from SSMS.
Result: successful, works perfectly.

Test #2: Using DetectDataChangesQuery as a scalar, not table.
Result: successful, works perfectly.

Test #3: Test with DetectDataChangesQuery as a table loaded in the model.
Result: successful, works perfectly.

Test #4: Test without initial reload to applyRefreshPolicy: false.
Result: successful, works perfectly.

Thanks again Chris for your kindness and your patience trying to help me.

View solution in original post

cpwebb · ‎05-09-2023

Yes, but what SQL queries do you see running on Snowflake when you try to refresh? That will tell you what might be going wrong.

Anonymous · ‎05-09-2023

Chris, I pasted the Snowflake query in the previous message.

It's this one:

This one is the query that Snowflake execute when I run the full refresh TSML script. Or do you mean other query? I'm a little bit lost here.

cpwebb · ‎05-09-2023

Sorry for not being clear: do you see any queries being run against your custom table in Snowflake?

A few other ideas:

When you did a full refresh, did you set the applyRefreshPolicy property as shown in my blog?
I see you have selected "Detect Data Changes" in the incremental refresh dialog. Can you deselect that and only use the properties in Tabular Editor to configure this?

Anonymous · ‎05-09-2023

Hi Chris, sorry the delay. I was reconstructing the report from zero.
1. When I refresh the custom table I have a query against Snowflake.

2. When I made the full refresh I don't have any query using custom table. It's like PollingExpression isn't recognized by the incremental.

3. When I uploaded the report, I used Tabular Editor 2 to Apply RefreshPolicy in order to create fact table partitions.

4. Next, I configured the PollingExpression to set DetectDataChangesQuery table and saved the model.

5. With partitions created and PollingExpressions configured, then I execute your TMSL script to full refresh. I made this several times reuploading the file, with "Detect Data Changes" selected but also unselected.

6. I checked the table script searching refreshBookmark option but nothing about it. I suppose my fact table isn't recognizing the PollingExpression for some reason.

I even capitalize columns RangeStart and RangeEnd in my DetectDataChangesQuery table in case that capital letters at name was a problem, but doesn't work.

Sorry for all this post, it's just this solution it's crucial to my production model. Thanks for all your time and knowledge.

cpwebb · ‎05-10-2023

The only difference I can see between what you're doing and my example is that you are loading the DetectDataChangesQuery query to the dataset and I'm not. Can you disable load for DetectDataChangesQuery and try again?

Anonymous · ‎05-10-2023

Hi again Chris.

How do you use DetectDataChangesQuery table without load it into the model? I thought that you did it.

I disable DetectDataChangesQuery load and re try it but keep without generate refreshBookmark property.

Ricardo Rincón it's also trying to help me with this, he pointed that one of my mistakes was configure Archive data starting in Incremental refresh longer than my dates in custom tables. I have 9 rows at custom table and was loading 60 months in incremental.

So, after that I was able to relaunch the full reload with refresh policy true and just in that case I have this.

So I have 2 problems still:
- After first full refresh doesn't have refreshBookmarks.
- Incremental load just refresh partition between incremental parameters dates, ignores custom table.

cpwebb · ‎05-11-2023

No, the engine doesn't need to have the DetectDataChangesQuery query loaded into the dataset in order to use it.

Can you share the M code for your version of DetectDataChangesQuery? I just noticed that from the screenshots you shared, you haven't applied a filter on it using the RangeStart and RangeEnd parameters. Remember that the query should return a table with just one column and one row.

Anonymous · ‎05-11-2023