Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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. ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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. 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
cpwebb
Microsoft Employee
3 years ago

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's avatar
    Anonymous
    Not applicable
    3 years ago

    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. 

  • cpwebb's avatar
    cpwebb
    Microsoft Employee
    3 years ago

    Glad to hear you got it working!