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. 

cpwebb's avatar
cpwebb
Microsoft Employee
3 years ago

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

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

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

        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.