Setting data slices on MOLAP partitions

Last month I had a case open with MS and the subject of setting data slices came up. (http://jesseorosz.spaces.live.com/blog/cns!E322FD91218E57CF!304.entry). I decided to do a little investigating myself and found some of our queries were doing a scan of all the partitions when they shouldn’t have been. Not good. This was severely slowing down user queries.

After running queries and setting the data slices manually it became evident this was the problem. I’ll set the slices via my AMO code. Well, it wasn’t as easy as I initially thought it would be. All of our measure groups set slices on dates, however the date dimensions can have different names across cubes. Revenue Date, Sales Date, Customer Date ect.

 
We have a cube metadata table used by our AMO code which allows certain settings on a measure group by measure group basis. What type of partitoning is done, what fact table is used, whether to turn the processing off, if we want to only maintaining a rolling number of days, ect. A bunch of different settings, something like 25. I added to that table what slice should be used in that measuregroup.
 
 
Subset of our cube metadata table

Photo Sharing and Video Hosting at Photobucket
 
 
Though the AMO code I then lookup that slice and set it.
 
————————————————————————————————————————————

‘Day/Week/Month Slice Set
If sProcessingType = "Day" Or sProcessingType = "Week" Or sProcessingType = "Month" Then
  
GetSlice(sProcessingType, sCalendarDWID, ""
)
   oNewPartition.Slice = strDateSlice
End If

‘Hour Slice Set
If sProcessingType = "Hour" Then
   GetSlice(sProcessingType, sCalendarDWID, sTimeOfDayDWID)
   oNewPartition.Slice =
"(" & strDateSlice & "," & strHourSlice &
")"
End If

objMeasureGroup.Partitions.Add(oNewPartition)
oNewPartition.Source =

New QueryBinding(objDatabase.DataSources(0).ID, sQueryText)
oNewPartition.Update()
oNewPartition.Process(ProcessType.ProcessFull)
objServer.CommitTransaction()

Public Sub GetSlice(ByVal sProcessingType, ByVal sCalendarDWID, ByVal sTimeOfDayDWID)

Dim cmd As New SqlCommand

If objConnection.State = ConnectionState.Closed Then
   objConnection.ConnectionString = strConn
   objConnection.Open()
End If

cmd.Connection = objConnection
cmd.CommandType = CommandType.Text
cmd.CommandTimeout = 600

‘Day Slice
If sProcessingType = "Day" Then
Try
  
cmd.CommandText = "select DaySlice from vDim_Calendar where calendardwid = " & sCalendarDWID
  
strDateSlice = (cmd.ExecuteScalar())
  
objConnection.Close()
  
strDateSlice = strDaySlice & strDateSlice
Catch
  
objConnection.Close()
  
strKeepProcessing =
False
End Try

End If

‘Hour Slice
If sProcessingType = "Hour" Then
Try
   cmd.CommandText =
"select DaySlice from vDim_Calendar where calendardwid = " & sCalendarDWID 
   strDateSlice = (cmd.ExecuteScalar()) 
   cmd.CommandText =
"select HourSlice from vDim_TimeOfDay where timeofdaydwid = " & sTimeOfDayDWID 
   strHourSlice = (cmd.ExecuteScalar()) 
   objConnection.Close() 
   strDateSlice = strDaySlice & strDateSlice
Catch 
   objConnection.Close() 
   strKeepProcessing =
False
End Try
End If

‘Week Slice
If sProcessingType = "Week" Then
Try
   cmd.CommandText =
"select WeekSlice from vDim_Calendar where calendardwid = " & sCalendarDWID 
  strDateSlice = (cmd.ExecuteScalar()) 
  objConnection.Close() 
  strDateSlice = strWeekSlice & strDateSlice
Catch
  objConnection.Close()
   strKeepProcessing =
False
End Try
End If

‘Month Slice
If sProcessingType = "Month" Then
Try 
   cmd.CommandText =
"select MonthSlice from vDim_Calendar where calendardwid = " & sCalendarDWID 
   strDateSlice = (cmd.ExecuteScalar()) 
   objConnection.Close() 
   strDateSlice = strMonthSlice & strDateSlice
Catch 
   objConnection.Close() 
   strKeepProcessing =
False
End Try
End If

End Sub

————————————————————————————————————————————
 
 
To find what value the slice should be set to we pull the value from our Date dimension table. I decided to use the member name rather than the member key since our member keys differ throughout our databases.
 
Set slice on membername: [Date].[Day].[10/03/07]
Set slice on member key (add the ampersand): [Date].[Day].&[2433]
 

Subset of Date table

Photo Sharing and Video Hosting at Photobucket
 
 
 
It’s fairly easy, however it took a while to think of how to do this all in one central place with the slice dimensions having different names. It’s controlled in that cube metadata table and requires no changes to our AMO code whenever we add a new measuregroup.  
 
If you have a database of a larger size definitely set the slice yourself. Don’t depend on Analysis Services to do it for you. Everything I’ve read told me the opposite (automatic slice detection) but from what I experienced automatic slice detection is just not reliable.
 
 
Various links about data slices:
 
Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

One Response to Setting data slices on MOLAP partitions

  1. Eric D Jacobsen says:

    Hi Jesse.  Please note I made some improvements in the logic of partition exclusion, released after SSAS 2005 SP2.  It might be for some of your scenarios that setting the slice is no longer necessary.  Queries involving aggregations were sometimes not being excluded; now the fact table part of the partition is checked for attribute ID ranges.  Multiple slices (e.g. {OR,WA}) were not being used for partition exclusion and now they are.  One should have any build after 9.0.3166 to see these improvements.
    Here are links for customers to obtain the most recent cumulative updates, as of Nov 2007.Cumulative update 3, build 9.0.3186 : http://support.microsoft.com/kb/939537Cumulative update 4, build 9.0.3200 : http://support.microsoft.com/kb/941450Cumulative update 5, … not available today, later this KB article will be updated … http://support.microsoft.com/kb/943656
    Thanks,
    — EricJ
    SQL Customer Advisory Team

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s