Update custom partition

[Peter_23]

Hi community.

I make a custom partition (Not datetime parameter) text parameter (Key) with SMSS (TSML) so when I create a new partition it's requiered a parameter. So it's fine.

When I have to update the partition I need to pass key (parameter) in TSML script.

If I define this parameter on SM settings (parameter section) then I update with custom script the semantic model,  it's works!

So is there a way to pass this variable (parameter) in  TMSL script?

Thanks in advance.

[Poojara_D12]

Hi @Peter_23 

To pass a custom parameter (key) in a TMSL script:

1. Define the parameter in the Tabular Model (SM) settings.
2. Inject the parameter value into the TMSL script manually, or dynamically using external scripts like PowerShell.
3. In TMSL, reference the parameter in the partition's query section (e.g., filtering by the key column).

TMSL doesn’t support direct variable substitution, so you need to inject the parameter value via external scripting during execution.

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[v-kpoloju-msft]

Hi @Peter_23,

Thank you for reaching out to the Microsoft Fabric community Forum.

Tabular model scripting language does not directly support parameterization like power bi desktop. However, you can configure and manage custom parameters for partitions in SSMS or Visual Studio and reference them in TMSL scripts. Please go through the below following steps may resolve your issue.

• Open sql server management studio connect to your analysis services instance. Navigate to the database and model in question. Verify that the custom partition parameter is correctly set up in the model’s settings. Ensure that the parameter is listed in the Parameters section and has the correct value and type text parameter in this case.
• In SSMS, navigate to the Partitions section under the relevant model. Verify that the partition settings are correct and that the custom parameter is used in the query definition.

If you need to manually update the partition, follow these steps:

• Right-click on the partition you want to update, Select Edit. Ensure that the key parameter is correctly specified in the query or filter section. Save and process the changes to see if the update is successful.
• Open Visual Studio and connect to your Analysis Services project. Navigate to the model and partitions. Verify the custom parameter setup and ensure it's correctly used in the partition definition. If needed, manually update the parameter and process the partition to reflect the changes.
• After making the necessary changes in SSMS or Visual Studio, test the model to ensure that the custom partition updates correctly. Check if the data is processed as expected with the updated parameter.

Please go through the below documentation links for better understanding.

Tabular Model Scripting Language (TMSL) Reference | Microsoft Learn

Create an Analysis Services Project | Microsoft Learn

I hope my suggestions give you good ideas, if you have any more questions, please feel free to reach out.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thank you.

[Peter_23]

Thanks @Poojara_D12  @v-kpoloju-msft 

I created a script with powershell, so 

"createOrReplace":{
 "object":{
 "database":"base"
 "table":"mytable"
 "partition": "AA"
},
"partition":{
"name":"AA",
"source":{
"type": "m",
"expression":[
"let",
"source..."
"#\Filtered Rows\"= Table.SelectRows (source, each [#\"Query-filter"] = KEYPARAMETER )",
in...

So with it, it's created succesfull the partition, but I updated, the partition with the next script:

"refresh":{
"type": "dataOnly",
"objects": [
{
"database": "base",
"table":"mytable",
"partition":"AA"
}

I don't find a command to pass KEYPARAMETER in this update proccess, it is possible?e.g. Overrride KEYPARAMETER default value.

thanks in advance

[v-kpoloju-msft]

Hi @Peter_23,

Thank you for reaching out to the Microsoft Fabric community Forum.

As per the details provided, I have gone through the code and made some small changed some based on the requirement, so I request you to please try the below provided code if the issue persist, please feel free to reach out to us.  

Code1:

$updatedPartitionDefinition = @{
    "createOrReplace" = @{
        "object" = @{
            "database" = "base"
            "table" = "mytable"
            "partition" = "AA"
        }
        "partition" = @{
            "name" = "AA"
            "source" = @{
                "type" = "m"
                "expression" = @(
                    "let",
                    "source...",
                    "#\Filtered Rows\"= Table.SelectRows(source, each [#\"Query-filter\"] = \"NEW_KEYPARAMETER_VALUE\")",
                    "in..."
                )
            }
        }
    }
}

Code2:

$refreshRequest = @{
    "refresh" = @{
        "type" = "dataOnly"
        "objects" = @(
            @{
                "database" = "base"
                "table" = "mytable"
                "partition" = "AA"
            }
        )
    }
}

I hope my suggestions give you good ideas, if you have any more questions, please feel free to reach out.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly

Thank you.

[Peter_23]

I created a new partition with your scripts:

$updatedPartitionDefinition = @{
    "createOrReplace" = @{
        "object" = @{
            "database" = "base"
            "table" = "mytable"
            "partition" = "BB"
        }
        "partition" = @{
            "name" = "BB"
            "source" = @{
                "type" = "m"
                "expression" = @(
                    "let",
                    "source...",
                    "#\Filtered Rows\"= Table.SelectRows(source, each [#\"Query-filter\"] = \"BB\")",
                    "in..."
                )
            }
        }
    }
}

 

 I update the partitions with:

$refreshRequest = @{
    "refresh" = @{
        "type" = "dataOnly"
        "objects" = @(
            @{
                "database" = "base"
                "table" = "mytable"
                "partition" = "BB"
            }
        )
    }
}

I run the script, it update with  0 rows 😞 but the dataset it contains values with "BB" (value requiered for partition)

I don't know whats happening ..

I find xmla parameter  is  possible update the parameter with it?

thanks in advance.

[v-kpoloju-msft · Accepted solution]

Hi @Peter_23,

Thank you for your follow up query. I apologize for any inconvenience you may have experienced.

As per the details provided previously, I have gone through the code again and made some small changed based on the requirement, so I request you to please try the below provided code if the issue persist, please feel free to reach out to us.

Code1:

$updatedPartitionDefinition = @{

    "createOrReplace" = @{

        "object" = @{

            "database" = "base"

            "table" = "mytable"

            "partition" = "BB"

        }

        "partition" = @{

            "name" = "BB"

            "source" = @{

                "type" = "m"

                "expression" = @(

                    "let",

                    "source = ...",

                    "#\Filtered Rows\"= Table.SelectRows(source, each [#\"Query-filter\"] = \"BB\")",

                    "in",

                    "FilteredRows"

                )

            }

        }

    }

}

Code2:

$refreshRequest = @{

    "refresh" = @{

        "type" = "dataOnly"

        "objects" = @(

            @{

                "database" = "base"

                "table" = "mytable"

                "partition" = "BB"

            }

        )

    }

}

If you have any further queries please feel free to reach out to us we will guide you accordingly. If this post helps, then please give us 'Kudos' and consider Accept it as a solution to help the other members find it more quickly.

Thank you.

[Peter_23]

@v-kpoloju-msft  @rajendraongole1  @rajendraongole1  thank you.

I finally It works.

😀

[rajendraongole1]

Hi @Peter_23  - Yes, you can pass parameters in a Tabular Model Scripting Language (TMSL) script by defining them in the script itself or using external tools to dynamically inject the parameters when executing the script.

please check the reference links:

Refreshing Power BI Semantic models using the XMLA endpoint, TMSL scripting and PowerShell | by Carl...

Create command (TMSL) | Microsoft Learn

Delete SSAS Partitions with TMSL and PowerShell | Chris Koester

Solved: Premium Per User - incremental refresh with effect... - Microsoft Fabric Community

[Peter_23]

Thanks @rajendraongole1 

I mean to define this option in TSML script?

[rajendraongole1]

Hi @Peter_23  -

Unfortunately, TMSL does not directly support creating or modifying parameters ( like those defined in the power bi interface).  these parameters are typically managed in power bi desktop or through the XMLA endpoint of the model.

suggest you, you can pass the parameter indirectly by embedding its value into the query expression within the TMSL script. 

Note: TMSL does not natively support managing paramters like power bi desktop.

Hope this helps.