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.
Data Warehousing and Data Science 