Automatically generate SSAS tabular model partitions

[Richard_Powell]

Hi. I hope someone can give some useful suggestions.

My workplace have a suite of SSAS tabular models. They used to be created in compatibility level 1103. We then used a free piece of software with them called Partition Manager (which was a free download from Codeplex, to show it's age!) to dynamically generate partitions at the start of a new month. Our cubes were partitioned by each month of the year. Partition Manager could also be controlled to process only a few partitions, by maintaining tables in it's own SQL Server database.

The tabular models have now been rewritten in compatibility level 1200, to allow us to process in parallel and generally give us a better build time performance. However, as we don't know of an equivalent to Partition Manager, i.e. a method to automatically generate partitions, we have created them as rolling window models, to match the time span of our data warehouse.

If we were to change our tabular models again to have incremental time partitions (i.e. not a rolling window) that are generated automatically, what methods exist for us to do this for compatibility level 1200 onwards? Are there scripts or software products to do this? Or anything else?

[OnurOz · Accepted solution]

Hi Richard,

We've implemented our own partitioning and processing code, using the official Microsoft client library for Analysis Services, called AMO.I guess it is similar to what Partition Manager does under the hood.

Starting from compatibility level 1200 (aka SQL Server 2016) you can either use "Tabular Object Model (TOM)" or "Analysis Management Object (AMO)" to manage your partitions programmatically.

Check the MS Learn: https://learn.microsoft.com/en-us/analysis-services/analysis-services-developer-documentation?view=a...

Also these: 

• https://github.com/MicrosoftDocs/bi-shared-docs/blob/main/docs/analysis-services/amo/amo-concepts-an...
• https://www.scribd.com/document/434016561/Automated-Partition-Management-for-Analysis-Services-Tabul...
• https://datakuity.com/2021/05/31/dynamically-partitioning-tables-in-ssas-tabular/
• https://www.tackytech.blog/how-to-automate-the-management-of-custom-partitions-for-power-bi-datasets...

---

😊 If this post helped you, feel free to give it some Kudos! 👍

✅ And if it answered your question, please mark it as the accepted solution.

---

[v-hashadapu]

Hi @Richard_Powell , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

[Richard_Powell]

Thank you for all the replies and suggestions.  It definitely sounds like using the Tabular Object Model is the way to go.  Our problem with that is a complete lack of experience of creating C# code and PowerShell scripts.  Some form of training would be needed there, to get us going.  Also, if Tabular Editor can generate all that for us, then it's over to my manager making a case to the company of paying for it.

[v-hashadapu · Accepted solution]

Hi @Richard_Powell , Thank you for reaching out to the Microsoft Community Forum.

As @lbendlin and @OnurOz correctly note, from compatibility level 1200 onward, you can manage partitions using XMLA/TMSL scripts noted or programmatically through AMO/TOM. These are the supported ways to create, delete and process partitions in modern tabular models.

To make this work end to end, you can use TOM or TMSL inside a scheduled job or pipeline (for example, in Fabric, ADF or SQL Agent) that runs monthly, generates new time based partitions and processes only what’s changed. Tools like PowerShell, C# scripts or Tabular Editor can handle this easily, giving you the same dynamic partitioning Partition Manager offered but using Microsoft’s current, fully supported frameworks.

[OnurOz · Accepted solution]

Hi Richard,

We've implemented our own partitioning and processing code, using the official Microsoft client library for Analysis Services, called AMO.I guess it is similar to what Partition Manager does under the hood.

Starting from compatibility level 1200 (aka SQL Server 2016) you can either use "Tabular Object Model (TOM)" or "Analysis Management Object (AMO)" to manage your partitions programmatically.

Check the MS Learn: https://learn.microsoft.com/en-us/analysis-services/analysis-services-developer-documentation?view=a...

Also these: 

• https://github.com/MicrosoftDocs/bi-shared-docs/blob/main/docs/analysis-services/amo/amo-concepts-an...
• https://www.scribd.com/document/434016561/Automated-Partition-Management-for-Analysis-Services-Tabul...
• https://datakuity.com/2021/05/31/dynamically-partitioning-tables-in-ssas-tabular/
• https://www.tackytech.blog/how-to-automate-the-management-of-custom-partitions-for-power-bi-datasets...

---

😊 If this post helped you, feel free to give it some Kudos! 👍

✅ And if it answered your question, please mark it as the accepted solution.

---

[lbendlin]

Is this inside a semantic model or is that a legacy SSAS tabular model?  You can use XMLA scripts to manage partition creation, deletion, and boundary rules definition.