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