Easy way to change multiple partition definitions

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

Answer 1

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.

Answer 2

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

Answer 3

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