Sunday, November 15, 2009

Automating UBO

by ErinW via Erin Welker on 11/15/2009 4:14:15 PM

I was preparing for my presentation at PASS and wanted to show some code for automating usage-based optimization.  This is the process that allows for creating aggregation designs based on past query activity.  I was surprised to find that there were no code examples posted on the web on how to do this, at least none that I could find.  BIDSHelper and AggManager have similar functionality but, upon examining the code, they both use a more manual approach of examining each Dataset column in the query log and creating an aggregation when it encounters a "1" in the bit string.  I believe they did so due to the inferiority of the UBO algorithm in SQL Server 2005.  That being said, using the out-of-the-box UBO algorithm leveraged here is only recommended for use with SQL Server 2008.

I felt sure that this functionality had to be available in AMO (Analysis Management Objects) because it existed in its predecessor, DSO.  It turns out that there is supporting documentation in Books Online, but it took some help from some of the folks at Microsoft to find it.  Note to self, when looking for detailed documentation of functionality in AMO, look under XMLA.  This kind of makes sense - I just would have expected it to be replicated in the AMO documentation.  Maybe it just takes a developer to know how to find it.  XMLA can also be used as a means of automating UBO but I find the AMO method to be a little more elegant.

The methods used for leveraging query logs when designing aggregations are the same as those used in generic aggregation design.  The only difference is that a collection of queries is passed in addition to the other parameters.  There are two things to note, however: 

First, the Dataset column that is read from the query log must be preceded by a weighting factor, usually the number of times the query appeared in the log. 

Second, the queries string collection must only be passed the first time the method is executed.  If you pass this parameter in on subsequent calls you will get an error.  The DesignAggregations method is executed in a loop that is terminated when the size and/or optimization goals are met.  I'm not sure why it couldn't just ignore the queries parameter in subsequent calls, but it doesn't, so you need to code accordingly.

Both of these nuances are documented in Books Online.

Here's an AMO code snippet for adding an aggregation design based on query history.  Note that development is not among my primary skill sets, so you'll probably want to clean it up - but it works.  For the sake of the session demo, I hard-coded a measure group and created a brand new aggregation design.  Best practices indicate that you should add these new aggregations to the existing design so that you won't throw away aggregations that were leveraged by previously well-performing queries. In a real-world scenario, you'd also want to filter the query log so that you only design aggregations for queries that had a long Duration:

Public Sub Main()

   ' Declarations

   ' Get Server and Database name from DTS connection object 
   Dim oSSASConnection As ConnectionManager = Dts.Connections("Analysis Services")
   Dim oSQLConnection As ConnectionManager = Dts.Connections("SQL Server")

   Dim sSSASServer As String = CStr(oSSASConnection.Properties("ServerName").GetValue(oSSASConnection))
   Dim oSSASServer As New Microsoft.AnalysisServices.Server

   ' SQL Connection
   Dim strSQLQuery As String
   Dim strConnection As String
   Dim sqlCn As SqlConnection
   Dim oServerProperty As ServerProperty
   Dim sqlDataAdapter1 As SqlDataAdapter
   Dim dsQueryLog As DataSet
   Dim dvQueryLog As DataView
   Dim dRow As DataRow

   ' Aggregation variables
   Dim Queries As New StringCollection
   Dim strAggPrefix As String
   Dim aggName As String
   Dim aggDesign As AggregationDesign
   Dim optimization As Double = 0
   Dim storage As Double = 0
   Dim aggCount As Long = 0
   Dim finished As Boolean = False
   Dim firsttime As Boolean = True

   ' Measure group variables
   Dim DatabaseName As String
   Dim CubeName As String
   Dim oMeasureGroup As MeasureGroup
   Dim MeasureGroupID As String
   Dim oPartition As Partition

   ' Initialize
   DatabaseName = "Adventure Works DW 2008"
   CubeName = "Adventure Works"
   MeasureGroupID = "Fact Sales Summary"
   strAggPrefix = "PASS2009_"

   Try

      oSSASServer.Connect(sSSASServer)

      ' Initialize connections
      oServerProperty = oSSASServer.ServerProperties("Log\QueryLog\QueryLogConnectionString")
      strConnection = oServerProperty.Value.Substring(oServerProperty.Value.IndexOf(";") + 1)

      sqlCn = New SqlConnection(strConnection)
      sqlCn.Open()

      oMeasureGroup = oSSASServer.Databases(DatabaseName).Cubes(CubeName).MeasureGroups(MeasureGroupID)

      ' This would be a good place to update the EstimatedRows in the measure group and partitions
      ' Set oMeasureGroup.EstimatedRows = to the count of rows in the source fact table

      ' Get select queries from the query log
      strSQLQuery = "SELECT dataset, COUNT(*) FROM OLAPQueryLog WHERE MSOLAP_Database = '" & DatabaseName & "' " & _
          " AND MSOLAP_ObjectPath = 'ERIN-PC\SQL2008." & DatabaseName & "." & CubeName & "." & oMeasureGroup.ID & _
          "' GROUP BY dataset"
      sqlDataAdapter1 = New SqlDataAdapter(strSQLQuery, sqlCn)
      dsQueryLog = New DataSet
      sqlDataAdapter1.Fill(dsQueryLog, strSQLQuery)
      dvQueryLog = dsQueryLog.Tables(strSQLQuery).DefaultView

      ' Populate the Queries string collection with the distinct queries from the query log
      For Each dRow In dvQueryLog.Table.Rows
         Queries.Add(dRow(1).ToString & "," & dRow(0).ToString)
      Next

      ' Add a new design to the Fact Sales Summary measure group and design aggregations based on the passed list of queries
      aggName = strAggPrefix & "_" & oMeasureGroup.Name

      aggDesign = oMeasureGroup.AggregationDesigns.Add
      aggDesign.Name = aggName

      aggDesign.InitializeDesign()
      Do While ((Not finished) And (optimization < 100))
         If firsttime Then
            aggDesign.DesignAggregations(optimization, storage, aggCount, finished, Queries)
            firsttime = False
         Else
            aggDesign.DesignAggregations(optimization, storage, aggCount, finished)
         End If
      Loop

      aggDesign.FinalizeDesign()
      aggDesign.Update()

      ' Assign the new aggregation design to all partitions in the measure group
      For Each oPartition In oMeasureGroup.Partitions
         oPartition.AggregationDesignID = aggDesign.ID
         oPartition.Update()
      Next

      ' Process the indexes to build the new aggregations
      ' oMeasureGroup.Process(Microsoft.AnalysisServices.ProcessType.ProcessIndexes)

      Dts.TaskResult = ScriptResults.Success
      sqlCn.Close()

   Catch ex As Exception
      Dts.Events.FireError(0, "Design aggregations failed - ", ex.Message, "", 0)
      Dts.TaskResult = ScriptResults.Failure
   End Try

   If oSSASServer.Connected Then
      oSSASServer.Disconnect()
   End If

End Sub