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
