On my return from a short holiday last week (South Africa had two public holidays in one week – Monday and Friday so taking 3 days off gave me the whole week ; ) ) I found an email from a blog reader (Ashkan) asking me to add a blog about SSAS partition creation in SSIS. I had promised that I would in my previous blog so I thought I better get to work.
So here it is…
For this example, I am going to discuss how I usually set up SSAS partitioning on a new cube. In most cases, this is at the stage when the warehouse is fully populated with history data and I need to create a large number of SSAS partitions to represent all this data. So what we need to start is a cube that has been deployed to a SSAS server. (This example will add partitions to the an existing cube on a server not partitions (metadata) to a cube in an Analysis Services project.) This deployed cube will have at least one measure group in it and just the default partition. What I would then need to do, is change the default partition’s query definition to point to my first data set, usually my first month in my warehouse (assuming we are partitioning by month) and then use the following code to create all the partitions that I need.
For this example, I am going to use the sample SSAS database Adventure Works and more specifically the default partition, Internet_Sales which is part of the Measure Group, Internet Sales in the cube, Adventure Works DW.
This partition’s source is set to table binding and is mapped to a specific source table in the cube’s DSV. For this example, I want to partition the measure group against a single fact table aiming each partition at a specific month in that table. Therefore I need to change the partition to query binding and set the source value to:
SELECT fis.[ProductKey],fis.[OrderDateKey],fis.[DueDateKey],fis.[ShipDateKey],fis.[CustomerKey],fis.[PromotionKey],fis.[CurrencyKey],fis.[SalesTerritoryKey],fis.[SalesOrderNumber],fis.[SalesOrderLineNumber],fis.[RevisionNumber],fis.[OrderQuantity],fis.[UnitPrice],fis.[ExtendedAmount],fis.[UnitPriceDiscountPct],fis.[DiscountAmount],fis.[ProductStandardCost],fis.[TotalProductCost],fis.[SalesAmount],fis.[TaxAmt],fis.[Freight],fis.[CarrierTrackingNumber],fis.[CustomerPONumber],
CONVERT ( CHAR ( 10 ), SalesOrderNumber ) + 'Line ' + CONVERT ( CHAR ( 4 ), SalesOrderLineNumber ) AS [SalesOrderDesc]
FROM [dbo].[FactInternetSales] fis
INNER JOIN dbo.DimTime dt
ON fis.OrderDateKey = dt.TimeKey
WHERE Cast(Convert(varchar(6), FullDateAlternateKey, 112) as int) = 200107
(I realise that this is not the best SQL query in the world but it illustrates the point)
The partition Internet_Sales should then be renamed to something that makes more sense. I usually name partitions according to the measure group to which they belong and the specific month they are assigned to. e.g. InternetSales200107
Once this has been done we have a complete partition that we can use to clone more… and you thought cloning was illegal! ; )
In my previous blog (Analysis Services Partition Processing), I went into a lot of detail explaining the script task and how I prefer to capture the XMLA and executing it against the server using an Analysis Services DDL Task. This SSIS package is very similar and therefore I am not going to repeat all that detail here but rather provide the code that is different and follow up with an explanation.
The Script Task’s code would look like this:
Imports System, System.Data, System.Math, Microsoft.SqlServer.Dts.Runtime
Imports AMO = Microsoft.AnalysisServices
Imports Microsoft.AnalysisServices.QueryBinding
Public Class ScriptMain
Public Sub Main()
Dim amoServer As AMO.Server, amoMeasureGroup As AMO.MeasureGroup
Dim amoPartition As AMO.Partition, oVariables As Variables = Dts.Variables
Dim strXMLAScript As String, strTaskName As String
Dim amoQueryBinding As AMO.QueryBinding, amoNewPartition As AMO.Partition
Try
Dts.VariableDispenser.LockOneForRead("System::TaskName", oVariables)
strTaskName = oVariables.Item("System::TaskName").Value.ToString
amoServer = New AMO.Server()
amoServer.Connect("Data Source=LOCALHOST;Initial Catalog=Adventure Works DW;" & _
"Provider=MSOLAP.3;Integrated Security=SSPI;Impersonation Level=Impersonate;")
amoMeasureGroup = amoServer.Databases.FindByName(amoServer.ConnectionInfo.Catalog _
.ToString).Cubes.FindByName("Adventure Works").MeasureGroups. _
FindByName("Internet Sales")
amoServer.CaptureXml = True
' Get first partition
amoPartition = amoMeasureGroup.Partitions(0)
' Loop through number of partitions needed
For iMonth As Integer = 200108 To 200112
amoNewPartition = amoPartition.Clone()
amoNewPartition.ID = "InternetSales " & iMonth
amoNewPartition.Name = "InternetSales " & iMonth
amoNewPartition.Slice = "" ' Add as needed
' Create new query binding
amoQueryBinding = New AMO.QueryBinding
' Set new query binding
amoQueryBinding.QueryDefinition = GetQueryBindingText(iMonth.ToString)
amoQueryBinding.DataSourceID = amoPartition.DataSource.ID
amoNewPartition.Source = amoQueryBinding
' Add to measure group and update parition
amoMeasureGroup.Partitions.Add(amoNewPartition)
amoNewPartition.Update()
' Report progress
Dts.Events.FireInformation(0, strTaskName, "Added partition - " & _
amoNewPartition.Name & " to create partition list.", String.Empty, _
0, True)
Next
' Set in transaction and in parallel properties and capture
strXMLAScript = amoServer.ConcatenateCaptureLog(True, False)
' Write to package variable
Dts.VariableDispenser.LockOneForWrite("User::strXMLAScript", oVariables)
oVariables.Item("User::strXMLAScript").Value = strXMLAScript
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
Dts.Events.FireError(0, strTaskName, ex.Message, String.Empty, 0)
End Try
End Sub
Private Function GetQueryBindingText(ByVal strMonth As String) As String
Return "SELECT fis.[ProductKey],fis.[OrderDateKey],fis.[DueDateKey],fis.[ShipDateKey]," & _
"fis.[CustomerKey],fis.[PromotionKey],fis.[CurrencyKey],fis.[SalesTerritoryKey]," & _
"fis.[SalesOrderNumber], fis.[SalesOrderLineNumber], fis.[RevisionNumber]," & _
"fis.[OrderQuantity],fis.[UnitPrice],fis.[ExtendedAmount]," & _
"fis.[UnitPriceDiscountPct], " & _
"fis.[DiscountAmount], fis.[ProductStandardCost], fis.[TotalProductCost], " & _
"fis.[SalesAmount], fis.[TaxAmt], fis.[Freight], fis.[CarrierTrackingNumber], " & _
"fis.[CustomerPONumber], " & _
"CONVERT ( CHAR ( 10 ), SalesOrderNumber ) + 'Line ' + CONVERT ( CHAR ( 4 ), " & _
"SalesOrderLineNumber ) AS [SalesOrderDesc] " & _
"FROM [dbo].[FactInternetSales] fis " & _
"INNER JOIN dbo.DimTime dt " & _
"ON fis.OrderDateKey = dt.TimeKey " & _
"WHERE Cast(Convert(varchar(6), FullDateAlternateKey, 112) as int) = " & strMonth
End Function
End Class
This code will loop through a list of your choice (I have simply chosen to loop through 5 months to prove the concept) creating new partitions. This is done by cloning the existing one (the one we edited earlier) and then overwriting the properties that need to be changed. These properties include the partition ID, Name and the query binding. If the cube is using a slicer then you could overwrite this property too. I haven’t included it in this example.
Please notice that I needed to change the parallel argument in the ConcatenateCaptureLog method to false from the example in my previous blog.
strXMLAScript = amoServer.ConcatenateCaptureLog(True, False)
Once this is complete, you can execute the package and it will create all the partitions you would like (or at least according to your loop). Or create new partitions (by making a few alterations) as and when they are needed at the end of your ETL but before a dimension and measure group OLAP process is done. This obviously will need to be approved by your SSAS DBA. I have on the odd occasion found that creating SSAS objects like partitions dynamically through code in a scheduled job, without letting your DBA know, can get you into a little bit of hot water…
Hope you find this helpful!
Happy Deving!
(I have added a zip file to SkyDrive with the sample package. It can be found here).
