I have a cube that has about 200 million rows and I decided to partition by month. The data is spread out over about 10 years. That means that per measure group, I have around 120 partitions to create. In addition to that, I have 3 measure groups. That means a total of around 360 partitions.
I could have created all of these parititons by hand, but that would have taken forever.
I decided to use an idea that I got from the Project REAL SSAS Technical Drilldown document. They discussed using SSIS to create new partitions while a cube was in production.
I decided that I could use the same technique to create my partitions, except by using Excel.
Here’s what I did:
I created one partiton in the cube (for each measure group) and processed the cube. There wasn’t any data in the fact table so this was really fast. I then scripted out the CREATE script for the partition using SSMS. After the script was created, it was pretty obvious what needed to change in the script to prepare it for the next partition. I big things that I had to change were Name, ID, and the WHERE clause of the sql statement.
I used Excel to string together the xml by putting the pieced that needed to change in columns going down the sheet. Once you type in “Jan”, if you drag that cell down, Excel knows to put “Feb” in the next cell and so on.
So as you go down the page, the xmla string that is concatenated together is now customized to create a bunch of partitions in a flash.