Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Generating large numbers of partitions using Excel

with 4 comments

Quite often when I’m doing proof-of-concept type work I find myself in the situation where I need to build a cube with near-production data volumes, and in order to make sure that performance is good I have to partition that cube. However setting up tens or even possibly hundreds of partitions in BIDS manually is no-one’s idea of fun, so how can you automate this process easily? If you’re using a SQL Server datasource then you should try using the functionality built into the Analysis Services Stored Procedure Project:
http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=Partition&referringTitle=Home

But what if you’re using an Oracle datasource or hit a bug with the ASSP code? Most consultants have a preferred method (such as their own custom code, or SSIS packages) but I thought I’d blog about the most commonly used approach which is to use Excel. Here are the steps:

  1. Your starting point should be a cube with just one partition in each measure group.I would recommend not putting all your data in this partition, but create it as a slice of the data, the first of the partitions you want to create. So you’ll probably want to make it query-bound rather than table bound, and set the Slice property appropriately (see here for why this is important).
  2. Open up SQL Management Studio and expand the tree in the Object Explorer so you can see the partition for your first measure group then right click and Script Partition As -> CREATE To. This will open a new XMLA window and give you your template XMLA command to create a partition.
  3. Let’s say we are going to partition by month, we have three months and they have surrogate keys 1 to 3. Our template partition is correctly configured for Month 1 and we want to be able to alter this template for months 2 and 3, so we’re going to ‘parameterise’ the following properties:
    1. ID – so, if your existing ID property is set to "Month_1", we need to replace the 1 with a string we can easily find with a search and replace like "@@@", making the new ID "Month_@@@"
    2. Name – which is usually the same as the ID
    3. Query Definition – you will have a Where clause in the SQL query behind the partition which is something like "Where Month_ID=1" and this should be changed to "Where Month_ID=@@@"
    4. Slice – the tuple will be something like "[Period].[Month].&[1]" which again should be changed to "[Period].[Month].&[@@@]"
  4. Copy this XMLA command text into a cell in a new Excel workbook, say cell A1. Make sure you paste the text into the formula bar and not directly onto the worksheet – you want it all in one cell.
  5. Underneath this cell we’re going to use Excel formulas to take this template and generate the XMLA needed for all the partitions we want. In cell A2 enter the value 1, in A3 enter 2 and so on for as many months as you need. Remember in Excel if you enter values like this that increment by 1, if you select that area then drag it downwards Excel will automatically fill the new cells with incrementing values
  6. In cell B2 we’re going to use an Excel formula to replace the string @@@ with the value in A2. So something like the following will work:
    =SUBSTITUTE($A$1,"@@@",A2)
  7. You can then copy and drag this formula downwards, and you’ll see all your new XMLA commands to create partitions appear in B3 and the cells underneath
  8. Copy and paste the new XMLA into a new XMLA query window in SQL Management Studio
  9. You may find that some unwanted double-quotes have appeared now. You need to replace the double sets of double quotes ("") with (") and the delete the single sets of double quotes. So first do a find and replace on "" and change it to something like @@@, then do a find and replace to delete all instances of ", then do another find and replace to change @@@ to ".
  10. You now need to wrap these XMLA commands in a batch statement so they can be run together. So paste the following text before the first Create:
    <Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    and this at the very end:
    </Batch>
  11. Now delete your original partition and execute the new XMLA batch command you’ve just created and hopefully you’ll have your new partitions created. You can then process them.

Written by Chris Webb

August 27, 2008 at 11:41 am

Posted in Analysis Services

4 Responses

Subscribe to comments with RSS.

  1. Chris-
     
    You mention that setting the slice property is important, based on the separate article you reference.  That article points to a Microsoft Connect issue that you submitted that states the issue exists even when you set the slice property.  Can you clarify whether the issue exists, regardless of setting the slice property of the partition?  And, have you tested this in 2008 to see if it still exists this way?

    Kory

    September 8, 2008 at 4:51 pm

  2. Hi Kory,
     
    The slice property is very important. With SP2 RTM I came across several scenarios with different customers where AS was retrieving data from partitions that wasn\’t needed for the query, but this was a side-effect of cache pre-fetching (see http://sqlcat.com/whitepapers/archive/2007/12/16/identifying-and-resolving-mdx-query-performance-bottlenecks-in-sql-server-2005-analysis-services.aspx) not a failing of the partition slice: that\’s to say, AS knew what data was in what partition, and was retrieving extra data on purpose. I know that post SP2 the situation has got a lot better, but I don\’t think it has completely been solved. At the moment in AS2008 I\’m dealing with a similar problem…

    Chris

    September 8, 2008 at 10:07 pm

  3. Hi Chris. I know this post is a bit old so I hope you get this message. Have your latest book which made me aware of the Slice property. I\’m on 2008, all partitions (MOLAP) are set to one day of data based on one FACT table (that is loaded daily). The partitions are qeury based where the WHERE clause is simply set to "WHERE CalendarID = YYYYMMDD". Do I really need to set the SLICE property in this instance? Our partitions range from 5 to 20 million rows but are all under 1 GB (or just about equal to). I\’ve been directed to so many links and most of them give you reasons why to set SLICE but few of them show you solid examples of how to do it. Mosha\’s link is the closes thing I\’ve found that clearly tells you how to do it. I understand his article up to the point of his "We just need to make sure that the resulting string is XML’ized, i.e. ‘&’ is replaced with ‘&amp;’ if it is to be pasted directly into XMLA script for ALTER partition"…that totally loses me. You have to then run a XMLA script as well? Why would you need to do that? You mentioned the Slice property "…has to be a simple tuple I think" on an MSDN forumn. The column I use in my Query based statement (CalendarID) is not a AS measure…it\’s a SQL based query. I couldn\’t reference it has a tuple when setting the SLICE property so I\’m just really confused as to how to set it.The whole SLICE property is a black hole to me. Enjoy the latest book you have co-authored…definately one the better reads. I like the fact it includes opinions of what works and what doesn\’t…very helpful.Thanks and best wishes,Ed Egan

    Ed

    August 20, 2009 at 4:23 pm

  4. Hi Ed,Yes, you really should set the Slice property to be 100% safe; for details why, see:http://blogs.msdn.com/sqlcat/archive/2007/03/05/ssas-partition-slicing.aspxIf you\’re setting the Slice property in BIDS then there\’s no need to worry about any XMLA – Mosha only says that because he\’s modifying existing partitions by executing an XMLA command rather than using BIDS. The Slice property can indeed only be a simple MDX tuple representing the slice of the cube that\’s contained in the partition; don\’t get this confused with the SQL query you\’ve bound the partition to. For example, if you\’ve got a partition bound to a SQL query that looks something like this:SELECT *FROM MyFactTableWHERE YearID=2009You might have a Slice property for this partition that is something like:([Date].[Calendar Year].&[2009])The SQL query limits the data in the partition to the year 2009; the MDX tuple declares the partition should only contain data associated with the 2009 member on the Calendar Year hierarchy.

    Chris

    August 20, 2009 at 9:50 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,302 other followers

%d bloggers like this: