Create New SSAS Partition dynamically: XML Task
There a good number of posts exist on how to create and manage new SSAS partitions dynamically, but almost all of them based on AMO coding and may look intimidating to some folks especially with little .NET skills in the past but expert DBA skills presently J. Knowing my programming background, I was "kindly" asked to provide a framework for dynamic partition creation with as little code implementation as possible, so it can be maintained by a person with a different skills set. Let’s think a bit: SSAS partition object can be scripted as XMLA script, some XMLA tags can be replaced dynamically to ensure partition uniqueness, SSIS XML Task has Operation Type – Merge… Ok, enough thinking J
Assume, that we want to create a new partition for each new year of data. We will need a simple cube with at least one QueryBinding partition already created and having WHERE clause, just to generate our version of partition XMLA (original partition can be deleted after we are done), something like this: