Forum Discussion
Custom query - Incremental Refresh
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.
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.
13 Replies
What data source are you using? Are you using the same data source for your fact table and for the custom table used by the polling queries?
What SQL queries do you see being run on Snowflake when the refresh takes place? Are you writing your own SQL queries anywhere or are you just pointing to tables/views in Snowflake?
Hi Chris.
I'm just pointing to tables through navigation panel in Power Query, I'm not using custom SQL querys. Both tables are at the same environment and my user have admin permissions.
My Snowflake query is this one:As the problem is with RefreshBookmark config, can be possible that I made a mistake configurating Incremental Refresh?
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.
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?
