locked
Easy way to change multiple partition definitions RRS feed

  • Question

  • I have 240+ partitions (4 per month since 2014). I've just removed one dimension and the associated key from the fact table. That means I have to now change the partition definition SELECT statement to remove that column.

    I started doing that one partition at a time in Visual Studio and then wondered if there was a quicker way to do it? I tried code view for the cube but it doesn't appear to include the partition code.


    Nick Ryan MIS Programmer Analyst, ANZ Bank

    Sunday, March 31, 2019 8:45 PM

Answers

  • You can manually (or scripting) modify visual studio project file storing partition information (notepad, then run string search/replace something like from ",your_missing_field," to ",")

    Another (usual way devops/admins do it) option (on server) is via AMO (PowerShell, C#, etc.) by iterating (foreach) through all partitions within MG (e.g. replace... $partition.Source.QueryDefinition=...  ... update..)

    Quite possibly your SSAS DB is maintained by some daily scripts - check there if partitions are created (and processed) automatically anyway.
    Sunday, March 31, 2019 11:10 PM

All replies

  • You can manually (or scripting) modify visual studio project file storing partition information (notepad, then run string search/replace something like from ",your_missing_field," to ",")

    Another (usual way devops/admins do it) option (on server) is via AMO (PowerShell, C#, etc.) by iterating (foreach) through all partitions within MG (e.g. replace... $partition.Source.QueryDefinition=...  ... update..)

    Quite possibly your SSAS DB is maintained by some daily scripts - check there if partitions are created (and processed) automatically anyway.
    Sunday, March 31, 2019 11:10 PM
  • Thanks for that. I can't change it on the server because I can't deploy it till the partition code is fixed. Modifying the project file sounds like the solution for me.

    It's a monthly load and yes, I do have a job step that creates the new partitions. I'll have to modify the template I use for the xmla to remove that redundant column.


    Nick Ryan MIS Programmer Analyst, ANZ Bank

    Monday, April 1, 2019 12:42 AM
  • I misunderstood the comment to "modify visual studio project file". This doesn't actually mean the Project file (dwproj). It means a file in the project called <project name>.partitions.

    Nick Ryan MIS Programmer Analyst, ANZ Bank

    Monday, April 1, 2019 2:58 AM