{"id":11,"date":"2007-04-10T21:25:46","date_gmt":"2007-04-11T02:25:46","guid":{"rendered":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/?p=11"},"modified":"2008-03-23T16:30:45","modified_gmt":"2008-03-23T21:30:45","slug":"ssis-package-that-process-all-partitionsmeasure-groupscubes-in-one-database","status":"publish","type":"post","link":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/11_ssis-package-that-process-all-partitionsmeasure-groupscubes-in-one-database","title":{"rendered":"SSIS package that process all partitions\/measure groups\/cubes in one database"},"content":{"rendered":"<p>Recently I posted a blog entry on how to process all dimensions in a single Analysis Services 2005 database using SQL Server Integration Services (SSIS) <a target=\"_blank\" href=\"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/?p=8\">package<\/a>. Here I&#8217;ll add code to this package that will allow you to process\u00a0whole database, cubes, measure groups or partitions.<\/p>\n<p><!--more-->Please follow steps from previous post on how to create new package and add script component. Then for the script component use code from bellow:<\/p>\n<blockquote><p>Imports System<br \/>\nImports System.Data<br \/>\nImports System.Math<br \/>\nImports Microsoft.SqlServer.Dts.Runtime<\/p>\n<p>Public Class ScriptMain<br \/>\n\u00a0\u00a0\u00a0 Public Sub Main()<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim ExecutionSuccessfull As Boolean = True &#8216; If true, package executed without errors<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim sProcessType As String = &#8220;ProcessFull&#8221;<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim oConnection As ConnectionManager<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 oConnection = Dts.Connections(&#8220;DM&#8221;)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim sServer As String = CStr(oConnection.Properties(&#8220;ServerName&#8221;).GetValue(oConnection))<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim sDatabaseID As String = CStr(oConnection.Properties(&#8220;InitialCatalog&#8221;).GetValue(oConnection))<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim oServer As New Microsoft.AnalysisServices.Server<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 oServer.Connect(sServer) &#8216; connect to the server and start scanning down the object hierarchy<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim oDB As Microsoft.AnalysisServices.Database = oServer.Databases.FindByName(sDatabaseID)<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If oDB Is Nothing Then<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ExecutionSuccessfull = False<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 GoTo Done<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Else<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim oDim As Microsoft.AnalysisServices.Dimension<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim oCube As Microsoft.AnalysisServices.Cube<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim oMeasureGroup As Microsoft.AnalysisServices.MeasureGroup<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim oPartition As Microsoft.AnalysisServices.Partition<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 oServer.CaptureXml() = True &#8216; Start capturing XML.<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216; oDB.Process(Microsoft.AnalysisServices.ProcessType.ProcessFull) &#8216; Use this to process whole DB at once<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216; Process all dimensions<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;For Each oDim In oDB.Dimensions<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0&#8216;\u00a0\u00a0\u00a0 &#8216; This will generate XMLA, but because CaptureXML is True, will not execute it!<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0&#8216;\u00a0\u00a0\u00a0 oDim.Process(Microsoft.AnalysisServices.ProcessType.ProcessFull) &#8216; Process all dimensions<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216; Next<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 For Each oCube In oDB.Cubes<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216; oCube.Process(Microsoft.AnalysisServices.ProcessType.ProcessFull) &#8216; Process all cubes<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 For Each oMeasureGroup In oCube.MeasureGroups<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216; oMeasureGroup.Process(Microsoft.AnalysisServices.ProcessType.ProcessFull) &#8216; Process all measure groups<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 For Each oPartition In oMeasureGroup.Partitions<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 oPartition.Process(Microsoft.AnalysisServices.ProcessType.ProcessFull)\u00a0 &#8216; Process all partitions<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Next<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Next<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Next<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 oServer.CaptureXml() = False &#8216; Stop capturing XML<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216; Execute captured XML. First parameter Transactional, second parameter Parallel, third optional parameter: processAffected<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216; These are very important parameters!<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim oResults As Microsoft.AnalysisServices.XmlaResultCollection<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim oResult As Microsoft.AnalysisServices.XmlaResult<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim dataBytes(0) As Byte<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 oResults = oServer.ExecuteCaptureLog(True, True)<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim oMessage As Microsoft.AnalysisServices.XmlaMessage<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;Log the errors and warnings<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 For Each oResult In oResults<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 For Each oMessage In oResult.Messages<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If oMessage.GetType.Name = &#8220;XmlaError&#8221; Then<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;The processing failed<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dts.Log(oMessage.Description, 0, dataBytes) &#8216;\u00a0You need to setup proper package loging to see this!<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ExecutionSuccessfull = False<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Else<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216;It&#8217;s just a warning.<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ExecutionSuccessfull = True &#8216; if you want to fail on warning, change this to False<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Next oMessage<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Next oResult<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If<br \/>\nDone:<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 oServer.Disconnect() &#8216; disconnect from the server &#8212; we are done<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If ExecutionSuccessfull Then<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dts.TaskResult = Dts.Results.Success<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Else<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dts.TaskResult = Dts.Results.Failure<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If<br \/>\n\u00a0\u00a0\u00a0 End Sub<\/p>\n<p>End Class<\/p><\/blockquote>\n<p>By default this package will build code to process each partition. This is just an example.\u00a0There is commented out code there that can process all measure groups or cubes or whole database at once. \u00a0Use this code as guidance to build your own SSIS package that does what you want.<\/p>\n<p>In my data warehouse design I decided to maintain the list of partitions in SQL Server table. I have a &#8220;partition base&#8221; table that contains base information about each measure group and partitioning field (I have just one, usually date field).\u00a0 Then I have a stored procedure, that during each DW load calculates if new partitions have to be created or old ones have to be deleted and loads this information into extended partition information table. It also marks which partitions are affected by latest load and has to be reprocessed.\u00a0Although this might seem like a complicated task,\u00a0it is not. And it is well worth the\u00a0effort. My SSIS package then compares this extended partition \u00a0table with actual SSAS database and then drops\/creates new partitions and process partitions that have to be processed.<\/p>\n<p>I would be interested to hear how other people are managing processing\u00a0of partitions?<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Recently I posted a blog entry on how to process all dimensions in a single Analysis Services 2005 database using SQL Server Integration Services (SSIS) package. Here I&#8217;ll add code to this package that will allow you to process\u00a0whole database, cubes, measure groups or partitions.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[4,5],"tags":[],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/11"}],"collection":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/comments?post=11"}],"version-history":[{"count":1,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/11\/revisions"}],"predecessor-version":[{"id":95,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/11\/revisions\/95"}],"wp:attachment":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/media?parent=11"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/categories?post=11"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/tags?post=11"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}