Forum Discussion

jhowe1's avatar
jhowe1
Icon for Helper III rankHelper III
1 year ago
Solved

Power BI - Override incremental refresh policy with XMLA

Hi we have a report running in our Prod workspace that's hitting memory limits. It needs remodeling basically, but to give us time it has incremental policies already enabled on 6 queries (all same 6 months), we want to reduce the incremental refresh window from 6 months to 3 months to see if it will refresh as normal. We don't want to do this in PBI desktop, because as mentioned when we publish it does a full refresh it will be extremely slow and possibly hit memory limits again. Through SSMS/XMLA how do I CHANGE incremental refresh policies? i.e. changing required tables from 6 months to 3 months? Looking for XMLA examples thank you. Can't find anything online...

Need Help
  • jhowe1's avatar
    jhowe1
    1 year ago

    As there doesn't seem to be a good way of scripting an incremental policy using SSMS and I can't find any full examples online, tabular editor seems to be able to modify incremental policies. I will try this for now, if I get stuck I will contact MS support, thank you. 

5 Replies

  • collinq's avatar
    collinq
    Icon for Super User rankSuper User
    1 year ago

    Hi jhowe1 ,

    I am not the expert on XML but I found one with these instructions:


    find the semantic model

    Right click and select New Query 

    Select MDX or XMLA

     

    Be sure not to delete the existing M query in teh "expression" field.

     

    here is the sample script:
    {
    "createOrReplace": {
    "object": {
    "database": "YourDatasetName",
    "table": "YourTableName"
    },
    "table": {
    "name": "YourTableName",
    "partitions": [
    {
    "name": "Incremental",
    "source": {
    "type": "m",
    "expression": "let\n Source = ... // your M query here\nin\n Source"
    },
    "refreshPolicy": {
    "mode": "incremental",
    "incrementalGranularity": "month",
    "historicalRange": "3",
    "refreshRange": "1",
    "policyType": "basic"
    }
    }
    ]
    }
    }
    }

     

     

    • jhowe1's avatar
      jhowe1
      Icon for Helper III rankHelper III
      1 year ago

      Sorry don't know what you mean by the existing M query?

      • v-menakakota's avatar
        v-menakakota
        Icon for Community Support rankCommunity Support
        1 year ago

        Hi jhowe1   ,
        Thanks for reaching out to the Microsoft fabric community forum. 

        The existing M query is the Power Query script that controls how your table’s data is loaded and transformed. If you’re not familiar with viewing or copying that from the deployed dataset, it can be a bit tricky especially when working directly against a production model via XMLA.

        Because changing this incorrectly could trigger a full refresh and cause the same memory issues you’re trying to avoid, I’d recommend also raising a Microsoft Support ticket.

        Please refer below link on how to raise a contact support or support ticket. 
        How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn 

        Thank you.