{"id":7,"date":"2007-03-29T00:55:03","date_gmt":"2007-03-29T00:55:03","guid":{"rendered":"http:\/\/ssas-info.com\/VidasMatelisBlog\/?p=7"},"modified":"2008-11-16T19:25:36","modified_gmt":"2008-11-17T00:25:36","slug":"ssis-package-to-process-all-dimensions-in-ssas-2005-db","status":"publish","type":"post","link":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/7_ssis-package-to-process-all-dimensions-in-ssas-2005-db","title":{"rendered":"SSIS package to process all dimensions in SSAS 2005 DB"},"content":{"rendered":"<p><strong>Vidas: My next blog entry contains updated code !<\/strong><\/p>\n<p>It is quite often that I have to process all the dimensions in a database. I like to use scripts for that. Here I have step by step guide on how to create an SSIS package that processes all dimensions in one database.<\/p>\n<p><!--more--><\/p>\n<ul>\n<li>In connection manager add a new Analysis Services connection, name connection &#8220;DM&#8221;. If a different name is used, adjust the script below<\/li>\n<li>For a package add Boolean type variable oNeedsFullReprocessing. A true value will mean that the dimensions will need ProcessFull option, false will mean that ProcessUpdate is required.<\/li>\n<li>For a package create variable pDimProcessCmd with type String. This will contain an XML command<\/li>\n<li>Add to package Script task. For this task in script window set properties:<br \/>\n\u00a0 ReadOnlyVariables pNeedsFullReprocessing<br \/>\n\u00a0 ReadWriteVariables pDimProcessCmd<\/li>\n<li>Click on &#8220;Design Script&#8221;<\/li>\n<li>In Project Explorer, right mouse click on &#8220;References&#8221;, then &#8220;Add Reference&#8221; and choose &#8220;Analysis Management Objects&#8221;. This will add reference &#8220;Microsoft.AnalysisServices&#8221;<\/li>\n<li>Add code from below to this script\u00a0<\/li>\n<\/ul>\n<blockquote><p>Imports System<br \/>\nImports System.Data<br \/>\nImports System.Math<br \/>\nImports Microsoft.SqlServer.Dts.Runtime<br \/>\nPublic Class ScriptMain<br \/>\n\u00a0\u00a0\u00a0 Public Sub Main()<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim oNeedsFullReprocessing As Variable = Dts.Variables(&#8220;pNeedsFullReprocessing&#8221;)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim sNeedsFullReprocessing As String = CStr(oNeedsFullReprocessing.Value)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim sProcessType As String = &#8220;ProcessUpdate&#8221;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If sNeedsFullReprocessing = &#8220;True&#8221; Then<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sProcessType = &#8220;ProcessFull&#8221;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Else<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sProcessType = &#8220;ProcessUpdate&#8221;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If<br \/>\n\u00a0<br \/>\n\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)<br \/>\n\u00a0\u00a0\u00a0<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim sXMLA As String = &#8220;&#8221;<\/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 MsgBox(&#8220;Did not find expected database: &#8221; &amp; sDatabaseID, MsgBoxStyle.OkOnly, &#8220;Error looking for partition&#8221;)<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<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sXMLA = &#8220;&#8221;<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sXMLA = sXMLA + &#8220;&lt;Batch xmlns=^http:\/\/schemas.microsoft.com\/analysisservices\/2003\/engine^&gt;&#8221; &amp; vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sXMLA = sXMLA + &#8221;\u00a0 &lt;Parallel&gt;&#8221; &amp; vbCrLf<br \/>\n\u00a0<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim sDimID As String<br \/>\n\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 For Each oDim In oDB.Dimensions<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sDimID = oDim.ID<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sXMLA = sXMLA + &#8221;\u00a0\u00a0\u00a0 &lt;Process xmlns:xsd=^http:\/\/www.w3.org\/2001\/XMLSchema^ xmlns:xsi=^http:\/\/www.w3.org\/2001\/XMLSchema-instance^&gt;&#8221; &amp; vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sXMLA = sXMLA + &#8221;\u00a0\u00a0\u00a0\u00a0\u00a0 &lt;Object&gt;&#8221; &amp; vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sXMLA = sXMLA + &#8221;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &lt;DatabaseID&gt;&#8221; &amp; sDatabaseID &amp; &#8220;&lt;\/DatabaseID&gt;&#8221; &amp; vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sXMLA = sXMLA + &#8221;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &lt;DimensionID&gt;&#8221; &amp; sDimID &amp; &#8220;&lt;\/DimensionID&gt;&#8221; &amp; vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sXMLA = sXMLA + &#8221;\u00a0\u00a0\u00a0\u00a0\u00a0 &lt;\/Object&gt;&#8221; &amp; vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sXMLA = sXMLA + &#8221;\u00a0\u00a0\u00a0\u00a0\u00a0 &lt;Type&gt;&#8221; &amp; sProcessType &amp; &#8220;&lt;\/Type&gt;&#8221; &amp; vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sXMLA = sXMLA + &#8221;\u00a0\u00a0\u00a0\u00a0\u00a0 &lt;WriteBackTableCreation&gt;UseExisting&lt;\/WriteBackTableCreation&gt;&#8221; &amp; vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sXMLA = sXMLA + &#8221;\u00a0\u00a0\u00a0 &lt;\/Process&gt;&#8221; &amp; vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Next<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sXMLA = sXMLA + &#8221;\u00a0 &lt;\/Parallel&gt;&#8221; &amp; vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sXMLA = sXMLA + &#8220;&lt;\/Batch&gt;&#8221; &amp; vbCrLf<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sXMLA = Replace(sXMLA, &#8220;^&#8221;, &#8220;&#8221;&#8221;&#8221;)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dts.Variables(&#8220;pDimProcessCmd&#8221;).Value = sXMLA<br \/>\nDone:<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 oServer.Disconnect() &#8216; disconnect from the server &#8212; we are done<br \/>\n\u00a0\u00a0\u00a0<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dts.TaskResult = Dts.Results.Success<br \/>\n\u00a0\u00a0\u00a0 End Sub<br \/>\nEnd Class<\/p><\/blockquote>\n<ul>\n<li>Close script editor<\/li>\n<li>Add taks &#8220;Analysis Services Execute DDL&#8221; and connect it to previously created script task.<\/li>\n<li>In DDL screen change properties:<br \/>\nConnection: DM<br \/>\nSourceType: Variable<br \/>\nSource: User::pDimProcessCmd<\/li>\n<li>Save package.<\/li>\n<li>Done<\/li>\n<\/ul>\n<p>Vidas Matelis\u00a0\u00a0<\/p>\n<p>&#8212;&#8212;<\/p>\n<p>March 29, 2007. Darren Gosbell, MVP comments<\/p>\n<p>Since you are making an AMO connection to the server anyway, you might want to make use of the CaptureXML and CaptureLog properties in your package as this would save you from having to do manual string concatenations to build the XMLA command. In fact you would not actually even need to pull out the xmla, you could use the ExecuteCaptureLog method.<\/p>\n<p>So in pseudo code it would go something like the following:<\/p>\n<p>Code SnippetServer.CaptureXml = True<br \/>\nfor each dim in Database.Dimensions<br \/>\n\u00a0\u00a0 dim.Process()<br \/>\nnext<br \/>\nServer.ExecuteCaptureLog(True,False)<\/p>\n<p>\u00a0The actually dim.Process calls get captured and the whole batch would get sent through when the ExecuteCaptureLog is called.<\/p>\n<p>Just a suggestion<\/p>\n<p>&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211;<\/p>\n<p><strong>Vidas: My next blog entry contains updated code !<\/strong><\/p>\n<p><strong>Note: If in script editor you see blue underline for Microsoft.AnalysisServices definitions, then you have to copy Microsoft.AnalysisServices.dll to the .NET folder.<br \/>\nDefault location from: C:\\Program Files\\Microsoft SQL Server\\90\\sdk\\Assemblies\\Microsoft.AnalysisServices.DLL<br \/>\nDefault location to: C:\\WINDOWS\\Microsoft.NET\\Framework\\v2.0.?\\<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Vidas: My next blog entry contains updated code ! It is quite often that I have to process all the dimensions in a database. I like to use scripts for that. Here I have step by step guide on how to create an SSIS package that processes all dimensions in one database.<\/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\/7"}],"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=7"}],"version-history":[{"count":1,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/7\/revisions"}],"predecessor-version":[{"id":120,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/7\/revisions\/120"}],"wp:attachment":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/media?parent=7"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/categories?post=7"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/tags?post=7"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}