Thursday, September 27, 2007

Automating Partition Creation

By Bryan C. Smith


 

The use of multiple partitions per measure group is promoted to improve both query and processing performance as well as the overall manageability of cubes. Partitioning strategies are often time-based, so that a single partition may represent a specific year, quarter, month, or some other unit of time.

One of the central challenges of partitioning a measure group on time (or any other non-static dimension) is that new partitions may be required over the life of the cube. If it can be predicted when these partitions might be needed, the required partitions can be manually created in advance by the admin. Where values cannot be predicted, an "overflow" partition can be created to catch any values not attributed to other, more precisely defined partitions.

Still, having the ETL layer query the database to determine the partitions required and then create those partitions on your behalf is a much more attractive approach. Below is a simplistic script demonstrating the use of AMO to do just that.

The script is written for execution within an SSIS Script task and requires a reference to the AMO library. Its goal is to add any year-based partitions to the Reseller Sales measure group within the Adventure Works cube of the Adventure Works DW multidimensional database. It is tested by dropping any partition from the Reseller Sales measure group.

Here is a quick rundown of the logic.

The script starts by establishing references to the Adventure Works cube and the Reseller Sales measure group within the Adventure Works DW multidimensional database. A query is issued against the DimTime table in the AdventureWorksDW relational database to determine which years exist within the table and the key ranges of the records associated with those years. (There is a critical assumption here that records in the DimTime table are sequenced by date. This may not be true within your data warehouse.)

The routine then loops through each partition in the Reseller Sales measure group to determine is a partition for that year exists. If not, a new partition is created with the appropriate query bindings. The first aggregation design used within the measure group is assigned to the new partition. (This is not a recommended approach but simply serves to demonstrate how aggregations can be assigned to new partitions.) The partition is then associated with the measure group and the change is submitted to SSAS. (It is important to note, the new partition is unprocessed at this time.)

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.AnalysisServices


 

Public
Class ScriptMain


 


Public
Sub Main()


 


'Locate Measure Group of Interest (and a Known Partition)


'---------------------------------------------


Dim MyServer As
New Server

MyServer.Connect("localhost")


 


Dim MyDatabase As Database = _

MyServer.Databases.GetByName("Adventure Works DW")


Dim MyCube As Cube = MyDatabase.Cubes.GetByName("Adventure Works")


Dim MyMeasureGroup As MeasureGroup = _

MyCube.MeasureGroups.GetByName("Reseller Sales")


 


'Retrieve Years in Dimension


'---------------------------------------------


Dim MyConnectionString As
String

MyConnectionString = "Server=localhost;" + _


"Database=AdventureWorksDW;Integrated Security=SSPI;"


Dim MySqlConnection As
New SqlClient.SqlConnection(MyConnectionString)


Dim MySqlCommand As
New SqlClient.SqlCommand( _

MySqlQueryString, MySqlConnection)


 

MySqlConnection.Open()


Dim MySqlReader As SqlClient.SqlDataReader = MySqlCommand.ExecuteReader


 


'Add Partitions That Don't Exist


'---------------------------------------------


While MySqlReader.Read


 


Dim MyYear As
String = MySqlReader.GetString(0)


Dim MyStartKey As Int32 = MySqlReader.GetInt32(1)


Dim MyEndKey As Int32 = MySqlReader.GetInt32(2)


 


Dim MyPartitionName As
String = "Reseller_Sales_" + MyYear


Try


Dim MyPartition As Partition = _

MyMeasureGroup.Partitions.GetByName(MyPartitionName)


Catch


Dim MyPartition As
New Partition


With MyPartition

.Name = MyPartitionName


If MyMeasureGroup.AggregationDesigns.Count > 0 Then

.AggregationDesignID = _

MyMeasureGroup.AggregationDesigns.Item(0).ID


End
If

.Source = New QueryBinding(MyCube.DataSource.ID, _

MyParitionQueryString(MyStartKey, MyEndKey))


End
With

MyMeasureGroup.Partitions.Add(MyPartition)

MyPartition.Update()


End
Try


End
While

MySqlReader.Close()


 

MySqlConnection.Close()

MyServer.Disconnect()


 

Dts.TaskResult = Dts.Results.Success


End
Sub


 


Private
Function MySqlQueryString() As
String


Dim Ret As
String

Ret = "select CalendarYear," + _


"min(TimeKey),max(TimeKey) " + _


"from dbo.DimTime " + _


"Group by CalendarYear"


Return Ret


End
Function


 


Private
Function MyParitionQueryString(ByVal StartKey As Int32, _


ByVal EndKey As Int32) As
String


Dim Ret As
String

Ret = "SELECT " + _


"[dbo].[FactResellerSales].[ProductKey]," + _


"[dbo].[FactResellerSales].[OrderDateKey]," + _


"[dbo].[FactResellerSales].[DueDateKey]," + _


"[dbo].[FactResellerSales].[ShipDateKey]," + _


"[dbo].[FactResellerSales].[ResellerKey], " + _


"[dbo].[FactResellerSales].[EmployeeKey]," + _


"[dbo].[FactResellerSales].[PromotionKey]," + _


"[dbo].[FactResellerSales].[CurrencyKey]," + _


"[dbo].[FactResellerSales].[SalesTerritoryKey]," + _


"[dbo].[FactResellerSales].[SalesOrderNumber]," + _


"[dbo].[FactResellerSales].[SalesOrderLineNumber]," + _


"[dbo].[FactResellerSales].[RevisionNumber]," + _


"[dbo].[FactResellerSales].[OrderQuantity]," + _


"[dbo].[FactResellerSales].[UnitPrice]," + _


"[dbo].[FactResellerSales].[ExtendedAmount], " + _


"[dbo].[FactResellerSales].[UnitPriceDiscountPct], " + _


"[dbo].[FactResellerSales].[DiscountAmount], " + _


"[dbo].[FactResellerSales].[ProductStandardCost], " + _


"[dbo].[FactResellerSales].[TotalProductCost], " + _


"[dbo].[FactResellerSales].[SalesAmount], " + _


"[dbo].[FactResellerSales].[TaxAmt], " + _


"[dbo].[FactResellerSales].[Freight], " + _


"[dbo].[FactResellerSales].[CarrierTrackingNumber], " + _


"[dbo].[FactResellerSales].[CustomerPONumber]" + _


"FROM [dbo].[FactResellerSales] " + _


"WHERE OrderDateKey BETWEEN " + StartKey.ToString + " AND " + _

EndKey.ToString


 


Return Ret


End
Function

End
Class

6 comments:

Unknown said...

The code looks great but please tell me one thing - isn't it a bad practice to code that much in your catch block ????
as far as i know, catch blocks are not supposed to be the entire logic of the code.
if you could check if exists and then add new partition when necessary instead it would be better

Telefone VoIP said...

Hello. This post is likeable, and your blog is very interesting, congratulations :-). I will add in my blogroll =). If possible gives a last there on my blog, it is about the Telefone VoIP, I hope you enjoy. The address is http://telefone-voip.blogspot.com. A hug.

Ajit Singh said...

Hello Brian,

Could you please change your blogspot layout to stretch one so that wider monitor users can utilize their screen width. it is just too narrow to read it comfortably.

Beac said...

When I paste your code into the script task, i get "Type 'server' is not defined". I do not know VB.net so not really sure what this error means. My guess is that this should be solved by the "Imports Microsoft.AnalysisServices" statement

Any ideas?

Sam Kane said...

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

Online Front said...

Feeling good to read such a informative blog, mostly i eagerly search for this kind of blog. I really found your blog informative and unique, waiting for your new blog to read.
Digital marketing Service in Delhi
SMM Services
PPC Services in Delhi
Website Design & Development Packages
SEO Services Packages
Local SEO services
E-mail marketing services
YouTube plans