{"id":8,"date":"2007-04-01T00:56:45","date_gmt":"2007-04-01T00:56:45","guid":{"rendered":"http:\/\/ssas-info.com\/VidasMatelisBlog\/?p=8"},"modified":"2008-12-16T20:54:39","modified_gmt":"2008-12-17T01:54:39","slug":"ssis-package-to-process-all-dimensions-in-ssas-2005-db-continuing","status":"publish","type":"post","link":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/8_ssis-package-to-process-all-dimensions-in-ssas-2005-db-continuing","title":{"rendered":"SSIS package to process all dimensions in SSAS 2005 DB (continuing)"},"content":{"rendered":"<p>After I published blog entry about a\u00a0 SSIS package that processes dimensions, I received a suggestion from Darren Gosbell (his blog is <a href=\"http:\/\/geekswithblogs.net\/darrengosbell\" target=\"_blank\">here<\/a>) that instead of building XMLA code by concatenating strings, I\u00a0 should use the\u00a0 CaptureXML option from an\u00a0 SSAS server object and then process dimensions using dim.Process method and execute XMLA using ExecuteCaptureLog routine.<\/p>\n<p><!--more-->I remembered that I tried to use dim.Process method when I originally wrote this package about a year ago, and I had performance related problems. So I re-tested this package by removing code that builds XMLA and instead just executing dim.Process method. This worked, but it took me 6 minutes to process 57 dimensions in my test database. A\u00a0 package that builds XMLA code and submits it to the\u00a0 SSAS server was able to do the\u00a0 same thing in 25 seconds. That is a\u00a0 big difference. I guessed that results are related to the fact that in my manually built XMLA code I added the\u00a0 &lt;Parallel&gt; tag to batch and that makes all dimensions to be processed, processed\u00a0 in parallel.<\/p>\n<p>At first I thought that the\u00a0 option using CaptureXML property would produce the\u00a0 exact same results as the dim.Process method. But thanks to Darren Gosbell&#8217;s additional explanations, I setup my tests to see if CaptureXML makes any difference. I was quite surprised by the results. As I did not use CaptureXML method before, I was not sure what it does. I found that basically after you set CaptureXML property to True, statements to SSAS are not executed, they are just logged internally. You can later execute that log using ExecuteCaptureLog call. Most importantly ExecuteCaptureLog accepts 3 parameters that are very important:<br \/>\nTransactional (True\/False)<br \/>\nParallel (True\/False)<br \/>\npocessAffected (True\/False)<br \/>\nIn my tests I did not use third parameter, as it was irrelevant.<\/p>\n<p>My results with different parameters are bellow. I added comments on what is different in XMLA code submitted (captured with profiler).<\/p>\n<ul>\n<li><strong>Transactional = True, Parallel = True<\/strong>, Execution Time : 22 sec, Note: Executes Batch with parameter: &lt;Batch xmlns=&#8230; &lt;Parallel&gt;<\/li>\n<li><strong>Transactional = True, Parallel = False<\/strong>, Execution Time: 28 sec, Note: Executes Batch with parameter: &lt;Batch xmlns=&#8230;<\/li>\n<li><strong>Transactional = False, Parallel = False<\/strong>, Execution Time: <strong>6 min<\/strong>, Note: Executes Batch with parameter: &lt;Batch Transaction=&#8221;false&#8221; xmlns=&#8230;<\/li>\n<li><strong>Transactional = False, Parallel = True<\/strong>, Error: Errors in the OLAP storage engine: Element Parallel is not supported in non-transactional batch.<\/li>\n<\/ul>\n<p>As you can see the biggest impact to performance was the fact that multiple dimensions are processed in a single batch. Submitting each transaction in separate batch increased processing time 20 times.<\/p>\n<p>So after learning all this I was able to simplify my package significantly. I will even look at\u00a0 rewriting my packages that does cube\/measure group and partition re-processing.<\/p>\n<p>Thanks to comments and example from Jesse Orosz (his <a href=\"http:\/\/jesseorosz.spaces.live.com\/\" target=\"_blank\">blog<\/a>), I\u00a0 added very simple error handling &#8211; I will\u00a0 fail package if ExecuteCaptureLog will produce error. For simplicity of this package, I\u00a0 did not introduce proper error logging.<\/p>\n<p>Below are new steps to create an\u00a0 SSIS package that processes all dimensions (process full option) in a single database:<\/p>\n<ul>\n<li>Create a\u00a0 new package.<\/li>\n<li>In connection manager add a new Analysis Services connection, name\u00a0 the connection\u00a0DM. If different name is used, adjust script below. Set this connection to point to your required server and SSAS database.<\/li>\n<li>To control flow area add new\u00a0&#8220;Script&#8221; tasks. Rename it to\u00a0&#8220;Process All Dimensions&#8221;<\/li>\n<li>Double click on this new task and go to Script screen and press on button &#8220;Design Script&#8221;.<br \/>\nIn Project explorer select &#8220;References&#8221; and after right mouse click select &#8220;Add reference&#8221;. Choose Analysis Management Objects.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" title=\"Image\" src=\"http:\/\/www.ssas-info.com\/images\/stories\/VidasBIBlog\/ssispckgdim1.jpg\" border=\"0\" alt=\"Image\" hspace=\"6\" width=\"559\" height=\"380\" \/><\/p>\n<ul>\n<li><strong>Replace script with <span style=\"color: #ff0000;\">code from this <\/span><\/strong><a href=\"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/BlogFiles\/Post8\/ProcessDim.txt\" target=\"_blank\"><strong><span style=\"color: #ff0000;\">link<\/span><\/strong><\/a>. Here is the same code, but my wordpress software replaces single and double quotes with special characters, so do not use text below:<\/li>\n<\/ul>\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<br \/>\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Dim oDim As Microsoft.AnalysisServices.Dimension<\/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 For Each oDim In oDB.Dimensions<br \/>\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \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 \u00a0 \u00a0 \u00a0 oDim.Process(Microsoft.AnalysisServices.ProcessType.ProcessFull)<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<\/p>\n<p>\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 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<ul>\n<li>NOTE: My blog software replaces single quotes and double quotes with special characters. Please<\/li>\n<li>Close script editor<\/li>\n<li>Save Package with your preferred name<\/li>\n<li>Package will look similar to this:<\/li>\n<\/ul>\n<p><img loading=\"lazy\" title=\"Image\" src=\"http:\/\/www.ssas-info.com\/images\/stories\/VidasBIBlog\/ssispckgdim2.jpg\" border=\"0\" alt=\"Image\" hspace=\"6\" width=\"319\" height=\"362\" \/><\/p>\n<ul>\n<li>You are ready to execute it<br \/>\n\u00a0<\/li>\n<\/ul>\n<p>As you can see this script is much simpler than my original version.<\/p>\n<p>For proper production version I will have to add proper error handling &#8211; save errors\/warnings to dts log.<\/p>\n<p>You can find forum where we talked about this package <a href=\"http:\/\/forums.microsoft.com\/MSDN\/ShowPost.aspx?PostID=1411009&amp;SiteID=1\" target=\"_blank\">here<\/a>.<\/p>\n<p><strong><span style=\"color: #ff0000;\">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.?\\<\/span><\/strong><\/p>\n<p>Vidas Matelis<\/p>\n<p><strong>June 5, 2007 Note:<\/strong> Added to script library <a href=\"http:\/\/www.ssas-info.com\/analysis-services-scripts\">http:\/\/www.ssas-info.com\/analysis-services-scripts<\/a>. Please note that <strong><span style=\"color: #ff0000;\">in the script library there is\u00a0a zip file with package code<\/span><\/strong>. Please use it if you have any problems.<\/p>\n<p><strong>November 8, 2008 Note:<\/strong> Created PowerShell script that does the same thing: <a href=\"http:\/\/www.ssas-info.com\/analysis-services-scripts\/1211-powershell-script-to-process-all-dimensions-in-one-analysis-services-database\">http:\/\/www.ssas-info.com\/analysis-services-scripts\/1211-powershell-script-to-process-all-dimensions-in-one-analysis-services-database<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>After I published blog entry about a\u00a0 SSIS package that processes dimensions, I received a suggestion from Darren Gosbell (his blog is here) that instead of building XMLA code by concatenating strings, I\u00a0 should use the\u00a0 CaptureXML option from an\u00a0 SSAS server object and then process dimensions using dim.Process method and execute XMLA using ExecuteCaptureLog [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","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\/8"}],"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=8"}],"version-history":[{"count":6,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/8\/revisions"}],"predecessor-version":[{"id":114,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/8\/revisions\/114"}],"wp:attachment":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/media?parent=8"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/categories?post=8"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/tags?post=8"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}