{"id":4,"date":"2007-03-24T00:50:27","date_gmt":"2007-03-24T00:50:27","guid":{"rendered":"http:\/\/ssas-info.com\/VidasMatelisBlog\/?p=4"},"modified":"2008-12-16T21:05:30","modified_gmt":"2008-12-17T02:05:30","slug":"ssis-package-to-generate-ssas-2005-aggregates","status":"publish","type":"post","link":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/4_ssis-package-to-generate-ssas-2005-aggregates","title":{"rendered":"SSIS Package to generate SSAS 2005 aggregates"},"content":{"rendered":"<p>I prefer to use usage statistics to generate SSAS 2005 aggregates, but during the\u00a0initial phases of\u00a0a project they are not available. So I usually build a\u00a0set of random aggregates, and after enough usage statistics\u00a0are accumulated, I rebuild aggregates based on usage. Microsoft provides a\u00a0nice wizard to generate aggregates on measure groups and partitions. But because I have to do this task way to many times, I decided to build a\u00a0simple SSIS package that does it for me.<\/p>\n<p><!--more-->This package will cycle through cubes, then measure groups in specified Analysis Services database. It checks if the\u00a0measure group already has any aggregation design assigned to it. If yes, then the\u00a0package will make sure that each partition for this measure group will use this aggregation design. If no aggregation design is found for the\u00a0specific measure group, then the\u00a0package creates a\u00a0new aggregation design and assigns it to all partitions in this measure group. To simplify design, I will assume that just one aggregation design is important for any measure group. If a\u00a0measure group has more than one aggregation design already defined, I&#8217;ll\u00a0just pick one randomly\u00a0and try to use it for partitions in that measure group.<\/p>\n<p>Steps to create such package:<\/p>\n<ul>\n<li>Start SQL Server Business Intelligence Development studio and create new project based on &#8220;Integration Services Project&#8221; template.Create new package &#8220;CreateSSASAggregates.dtsx&#8221;<\/li>\n<li>In &#8220;Connection Managers&#8221; area right mouse click and then choose &#8220;New Analysis Services Connection&#8221;. Choose server name and Initial Catalog for this connection. Rename connection to &#8220;DM&#8221;.<\/li>\n<li>From the toolbar drop &#8220;Script Task&#8221; into Control Flow area.<\/li>\n<li>Double click on &#8220;Script task&#8221; control, change Name and Description properties to your requirements, then in the Script area click on the button &#8220;Design Script&#8221;<\/li>\n<li>As a first step you have to add reference to Analysis Services. On the left side of the script window you should see &#8220;Project Explorer&#8221; and tree view of components. Right mouse click on the &#8220;References&#8221; folder and select &#8220;Add reference&#8221;. Then find component &#8220;Analysis Management Objects&#8221; and click &#8220;Add&#8221; then &#8220;OK&#8221;.<\/li>\n<li>In the script area replace existing code with\u00a0the <span style=\"color: #ff0000;\">text\u00a0from the <\/span><a href=\"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/BlogFiles\/Post4\/CreateAgg.txt\" target=\"_blank\"><span style=\"color: #ff0000;\">following link<\/span><\/a>. Here is code use, but my blog software replaces single and double quotes with special characters:<\/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 optimizationWanted As Double = 30\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216; 30%<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim maxStorageBytes As Double = 102400000\u00a0\u00a0 &#8216; 100MB<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim oNewAD As Microsoft.AnalysisServices.AggregationDesign<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim optimization As Double<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim storage As Double<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim aggCount As Long<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim finished As Boolean<\/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 sDatabase As String = CStr(oConnection.Properties(&#8220;InitialCatalog&#8221;).GetValue(oConnection))<\/p>\n<p>\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(sDatabase)<br \/>\n\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; sDatabase, 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 End If<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim oCube As Microsoft.AnalysisServices.Cube<br \/>\n\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 Dim oMeasureGroup As Microsoft.AnalysisServices.MeasureGroup<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 For Each oMeasureGroup In oCube.MeasureGroups<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If oMeasureGroup.IsLinked Then &#8216; Added as per Bohdan Hotskyy suggestion (not tested)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Continue For<br \/>\n\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 If oMeasureGroup.AggregationDesigns.Count = 0 Then<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8216; Create addgregation designs<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 oNewAD = oMeasureGroup.AggregationDesigns.Add(oMeasureGroup.AggregationPrefix + &#8220;_&#8221; + oMeasureGroup.Name)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 oNewAD.InitializeDesign()<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 optimization = 0<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 storage = 0<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 aggCount = 0<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 finished = False<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 While Not finished And optimization &lt; optimizationWanted And storage &lt; maxStorageBytes<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 oNewAD.DesignAggregations(optimization, storage, aggCount, finished)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End While<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 oNewAD.FinalizeDesign()<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 oNewAD.Update()<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 oMeasureGroup.Update()<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If oMeasureGroup.AggregationDesigns.Count &gt; 0 Then<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim oAD As Microsoft.AnalysisServices.AggregationDesign<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 oAD = oMeasureGroup.AggregationDesigns(0)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim oPartition As Microsoft.AnalysisServices.Partition<br \/>\n\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 If oPartition.AggregationDesignID Is Nothing 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\u00a0\u00a0\u00a0\u00a0 oPartition.AggregationDesignID = oAD.ID<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\u00a0\u00a0\u00a0\u00a0 oPartition.Update()<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 End If<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 End If<\/p>\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Next<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Next<\/p>\n<p>Done:<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 Dts.TaskResult = Dts.Results.Success<br \/>\n\u00a0\u00a0\u00a0 End Sub<br \/>\nEnd Class<\/p><\/blockquote>\n<ul>\n<li>Close script window and save package.<\/li>\n<li>Package is ready to be executed.<\/li>\n<\/ul>\n<p>Enjoy,<\/p>\n<p>Vidas Matelis<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I prefer to use usage statistics to generate SSAS 2005 aggregates, but during the\u00a0initial phases of\u00a0a project they are not available. So I usually build a\u00a0set of random aggregates, and after enough usage statistics\u00a0are accumulated, I rebuild aggregates based on usage. Microsoft provides a\u00a0nice wizard to generate aggregates on measure groups and partitions. But because [&hellip;]<\/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":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/4"}],"collection":[{"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/comments?post=4"}],"version-history":[{"count":5,"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/4\/revisions"}],"predecessor-version":[{"id":136,"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/4\/revisions\/136"}],"wp:attachment":[{"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/media?parent=4"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/categories?post=4"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/tags?post=4"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}