Creating New Partitions with AMO

One of the things that suprised me at the BI Convention last month was how many people fully reprocess partitions every time new data is added. Several people said they spent upwards of three hours a night adding new data. Most just used the built in SSIS tools. Even though our main fact table has 5 billion + rows and grows by about 50 million a day, the nightly processing (dimension excluded) takes less than 15 minutes. You must, must, must use AMO if you want any kind of efficieny in building partitions.
 
All of our processing actions are logged in a table we call "CubeProcessingLog" – we then compare this against the ETL log so we know what data doesn’t exist in the cube. No manual processing needs to ever be done. If a partition does need to be reprocessed, the corresponding record in the CubeProcessingLog table just needs to be set to recordactive = 0. 
 
I thought I would share a few lines of the code we use to handle the processing of our partitions. This is just a small chunk out of some 1500 lines of code we have to handle cube processing so it may not make sense on it’s own.
 
 
 ‘Create the partition
Try
 
 objServer.BeginTransaction()
  oClonePartition = objMeasureGroup.Partitions.FindByName(sTemplateType)
  BeginTime = Now
 
 InsertCubeProcessingLog("Process Partition Executing…", 0, 0, BeginTime, "12/31/9999", objDatabase.Name, objCube.Name, objMeasureGroup.Name,    . . .
 
 oNewPartition = oClonePartition.Clone
 
 oNewPartition.ID = sNewPartitionName
  oNewPartition.Name = sNewPartitionName
  objMeasureGroup.Partitions.Add(oNewPartition)
  oNewPartition.Source = New QueryBinding(objDatabase.DataSources(0).ID, sQueryText)
  oNewPartition.Update()
  oNewPartition.Process(ProcessType.ProcessFull)
  objServer.CommitTransaction()

  ‘If there’s auditing do it here
   If strAuditable = "Yes" Then
     InsertMeasureGroupAudit(sNewPartitionName, sProcessingType, sCalendarDWID, sTimeOfDayDWID)
   End If 

  sSQLQuery = "set EventDescription = ‘Process Partition’, issuccess = 1, recordactive = 1, eventendtime = ‘"   . . .

  UpdateCubeProcessingLog(sSQLQuery)

Catch
  objServer.RollbackTransaction()
  Dim ErrorReplace As String = Err.Description
  ErrorReplace = ErrorReplace.Replace("’", "")
  sSQLQuery = "set EventDescription = ‘Process Partition Failed: " & Err.Number & " – " & ErrorReplace & "’, issuccess = 0, recordactive = 0, eventendtime = ‘" & Now & "’   . . .
  UpdateCubeProcessingLog(sSQLQuery)
End Try

 

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

One Response to Creating New Partitions with AMO

  1. Dan Meyers says:

    I love to get more details about how your processing system works.  Sounds very interesting!

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