Data Warehousing, BI and Data Science

29 September 2010

Updating Partition Source SQL

Filed under: Analysis Services — Vincent Rainardi @ 7:44 am

Say you have a Measure Group with 100 partitions with source SQL = “select [some columns] from table1 where col1 = X” where X = 1 to 100. Table1 has been renamed to Table2 and you need to change all the partition source SQLs from “Table1” to “Table2”

If you have only 10 partitions then you could update them one by one in BIDS. Takes 5 minutes job done. But if you have 100 partitions? Hmm…. Do you want to spend an hour? Not really.

So how do we update all partitions in 1 go?

1. Replace All in XMLA

Export to XMLA, do a “replace all” in SSMS, execute the XMLA, recreate the BIDS project from the resulting cube. In the Find & Replace dialog box you can use Regex if necessary (in the “Use” drop down list, select “Regular Expression”, 2008 and R2).

2. Using XMLA alter command

In SSMS Object Explorer, navigate to the measure group containing the partitions then script as Alter. The result is below.

<Alter …>
   …
   <Partitions>
      <Partition>
        <ID>1</ID>
        …
        <QueryDefinition>SELECT … FROM Table1 = 1</QueryDefinition>
        …
      </Partition>
      <Partition>
         <ID>2</ID>
         …
         <QueryDefinition>SELECT … FROM Table1 = 2</QueryDefinition>
         …
      </Partition>
      … etc (all the partitions are scripted here)
   <Partitions>
</Alter>

Do a “Replace all” to replace “Table1” with “Table2” and execute the XMLA. Then recreate the BIDS project from the resulting cube.

3. Update the partition file

In Windows explorer, navigate to the folder where your BIDS project is located. Find the *.partitions file. Edit it using Mgt Studio (you can drag and drop from Win Explorer to SSMS, no need to do File, Open file).

First part of this file is aggregation. Keep scrolling down until you find <Partitions> tag. Like this: (yes it’s the same as the XMLA snippet on point 2 above)

<Partitions>
  <Partition … >
    <ID>1</ID>
    <Name>1</Name>
    …
    <Source … >
      <DataSourceID>DataSource1</DataSourceID>
      <QueryDefinition>SELECT … </QueryDefinition>
    </Source>
    …
  </Partition>
  <Partition … >
    <ID>1</ID>
    <Name>1</Name>
    …
    <Source … >
      <DataSourceID>DataSource1</DataSourceID>
      <QueryDefinition>SELECT … </QueryDefinition>
    </Source>
    …
  </Partition>
  …
 </Partitions>

Do a “replace all” to change “Table1” to “Table2”. The word “Table1” could exist somewhere else (not only in partition). So it is safer to block the text between  <Partitions> and  </Partitions> then do a replace all “within selection only” like this:

Save it. Open the solution using BIDS as normal. Look at the partition tab of the cube. Your partition SQLs are all changed.

4. Change the Partition SQL using AMO

Hmm everything is using AMO these days 🙂

Here we go:

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.AnalysisServices;
using System.IO;

namespace AMO
{
  class Program
  {
    static void Main(string[] args)
    {
      // Output to text file
      StreamWriter F = new StreamWriter("C:\\Data\\Apps\\DotNet Projects\\AMO Partition Source SQL\\File.txt");

      // Connect to server
      Server SSAS_Svr = new Server();
      SSAS_Svr.Connect("Data Source=localhost;Initial Catalog=Adventure Works DW 2008;Integrated Security=SSPI;");
      if ((SSAS_Svr != null) && (SSAS_Svr.Connected))
      {
         // Find the database
         Database UW_DB = SSAS_Svr.Databases.FindByName("Adventure Works DW 2008");
         if (UW_DB != null)
         {
           Cube UW_Cube = UW_DB.Cubes.FindByName("Adventure Works");
           if (UW_Cube != null)
           {
             // Find the Measure Group
             MeasureGroup MG = UW_Cube.MeasureGroups.FindByName("Internet Sales");
             F.WriteLine("Measure Group: " + MG.Name);

             // Get partition source SQL for each measure group,
             // and replace the source table.
             foreach (Partition P in MG.Partitions)
             {
               F.WriteLine("Partition: " + P.Name);

               // Get the Partition Source SQL
               F.WriteLine("Before:");
               F.WriteLine(((QueryBinding)P.Source).QueryDefinition.ToString());
               // Partition Source can be DsvTableBinding or QueryBinding.
               // Only QueryBinding Source has QueryDefinition. Hence the cast.

               // Replace the source table
               StringBuilder NewSQL = new StringBuilder(((QueryBinding)P.Source).QueryDefinition.ToString());
               NewSQL.Replace("[dbo].[FactInternetSales]", "[dbo].[FactInternetSales2]");
               ((QueryBinding)P.Source).QueryDefinition = NewSQL.ToString();

               // Write the new Source SQL
               F.WriteLine("After:");
               F.WriteLine(((QueryBinding)P.Source).QueryDefinition.ToString());
             }
           }
         }

         // Disconnect from AS Server
         SSAS_Svr.Disconnect();
       }

       //Close the file writer
       F.Close();
     }
  }
}

Hope it’s useful. I welcome comments and discussion at vrainardi@gmail.com.
To copy and paste from SSMS to WordPress with colour and tabs retained, you just need to wrap it in
“sourcecode” tag. See here for details

Vincent Rainardi, London, 29/9/2010.

Advertisements

8 Comments »

  1. Here is this and some other articles on SSAS Partitions: http://ssas-wiki.com/w/Articles#Partitions

    Comment by Sam Kane — 23 February 2011 @ 8:06 pm | Reply

  2. I`m new to the C# and was trying to work out how to get at the query definition. Your post was spot on and saved my sanity! Thanks

    Comment by Andrew — 22 June 2012 @ 9:08 am | Reply

  3. A time-saver for sure. I too was failing at getting a the query statement and would not have thought to use (QueryBinding)P.Source. Thank you.

    Comment by Kenneth Borrowdale — 19 September 2013 @ 6:53 pm | Reply

  4. [<EntryPoint>]
    let main argv =
    let adoConnStr = "Data Source={serverName};Catalog={DatabaseName}"
    let amo = new Microsoft.AnalysisServices.Server()
    amo.Connect(adoConnStr)
    let asdb = amo.Databases.FindByName("{DatabaseName}")
    let cube = asdb.Cubes.FindByName("{CubeName}")
    let mg = cube.MeasureGroups.FindByName("{MeasureGroupName}")
    let partitions = mg.Partitions

    //// this defines the partitions as a table in BIPOC, used by the view:
    //let parts = getPartitions psql

    let showpartitions = [ for p in partitions do yield ( ( p.Source :?> QueryBinding ).QueryDefinition ) ]

    for pno in 0..(partitions.Count – 1) do
    let p = partitions.[pno]
    let qb = p.Source :?> QueryBinding
    let newsql = sprintf "SELECT * FROM BIPOC.dbo.DRWFactPartition(%d) — Updated:%s" (pno+1) (DateTime.Now.ToString())
    qb.QueryDefinition <- newsql
    // partitions.[pno].Slice <- parts.[pno].slice
    p.Update()
    // if p.State = AnalysisState.Unprocessed then p.Process()

    amo.Disconnect()
    0 // return an integer exit code

    Comment by sqlxprt — 14 November 2014 @ 5:19 pm | Reply

    • Thank you

      Comment by Vincent Rainardi — 14 November 2014 @ 7:24 pm | Reply

      • thanks. Now that I’ve reblogged the code (from tallpeak.wordpress.com) to sqlxprt.wordpress.com , you can delete #5 and #4; they lost indentation, which is important in F#. The user will have to tab it back to the appropriate indentation to get the code to work. Arguably, Visual Studio makes that easy because it underlines the errors, but still…

        Comment by sqlxprt — 16 November 2014 @ 1:38 am

    • Done, sqlxprt. Should match with sqlxprt.wordpress.com now. What’s your name if you don’t mind?

      Comment by Vincent Rainardi — 16 November 2014 @ 2:01 pm | Reply


RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: