There are many different ways you can partition a cube. Here I am talking about – how you can use C# to partition a cube from a DTS package?
Using Microsoft.AnalysisServices Object API, you can travel the whole Hierarchy of objects and completely administer an Analysis Service Instance. From the connection Object you can get to the Server and database instances. And in database you can transverse the Cube and their different measures. Once you get hold of a measure then you can transverse the different partitions of measures and dig deep in to each partition query definitions etc.To keep things simple, there is one prerequisite to name your measure partitions in the in following format: MeasureName_DateSKey. DateSKey is a long number showing the date from 1st Jan 1990. To get the DateSKey for a date you can use the following formula:
DateTime d;d.Date.Subtract(new System.DateTime(1900, 1, 1, 0, 0, 0)).Days
So, if your measure name is ‘Distinct Downloaders Daily’ then it may have following partitions for every 7 days as follows: Distinct Downloaders Daily_39812, Distinct Downloaders Daily_398129… etc
Open a new DTS package, drag the script component and double click to start editing a C# script.
Include the following library:
using Microsoft.AnalysisServices;using System.Collections.ObjectModel;
Define a new structure as measure, to hold the definition of your measures and there partition boundaries.
[sourcecode language='csharp']
// Structure used to keep the Measure name and their partition durations
struct
Now define a collection of measures which you want to partition. Sure, this information you can get from a Config file too … It is hardcoded in code for the illustration purpose.
Collection Measures =
Now you can use this collection to call a method called ProcessCube. Here is the code, removed all the error handling for the sake of brevity.
private
///
bool CreatePartition(Cube oCube, string measureName, int iDaysToSkip) { Byte[] dataBytes = new Byte[5000]; QueryBinding oQueryBinding; bool bflag = false; int iCurrentDateSKey = (int)Dts.Variables["CubeCurrentDateSKey"].Value; //Get the currentDateSkey MeasureGroup oMeasureGroup = oCube.MeasureGroups.FindByName(measureName); // Find all measure groups if (oMeasureGroup == null) return false; Partition oPartition = oMeasureGroup.Partitions[oMeasureGroup.Partitions.Count - 1]; //Get the Last partition int LastCubeDateSkey = GetPartitionDateSKey(oPartition);//Get the lastkey DateSkey string DefaultPartitionName = oMeasureGroup.Name; //Get the Partition name first part string sNewPartitionName = string.Empty; if (LastCubeDateSkey == 1) //if it is first default partition { sNewPartitionName = DefaultPartitionName + "_" + iCurrentDateSKey; //Making the new partition Name } else { if (LastCubeDateSkey + iDaysToSkip <= iCurrentDateSKey) sNewPartitionName = DefaultPartitionName + "_" + iCurrentDateSKey; else return true; //No need to create the partition yet. Every thing is fine, go back } //Just another check, if the partition already existing then skip oPartition = oMeasureGroup.Partitions.FindByName(sNewPartitionName); Partition oPartitionNew; if (oPartition == null) { //Did not get the partition, lets create one,Get the last partition oPartition = oMeasureGroup.Partitions[oMeasureGroup.Partitions.Count - 1]; //Clone the properties from the last partition to the new partition. oPartitionNew = oPartition.Clone(); oPartitionNew.ID = sNewPartitionName; oPartitionNew.Name = sNewPartitionName; oQueryBinding = oPartitionNew.Source as QueryBinding; if (oQueryBinding == null) { return true; // No Query, No Partition } oQueryBinding.QueryDefinition = GetNewQuery(oQueryBinding.QueryDefinition, iCurrentDateSKey, iDaysToSkip); if (oQueryBinding.QueryDefinition == null || oQueryBinding.QueryDefinition == string.Empty) { Dts.Log("Partition : " + oPartitionNew.Name + " Empty Query returned", 0, dataBytes); Dts.Events.FireInformation(0, "CreatePartition", "Partition : " + oPartitionNew.Name + " Empty Query returned", null, 0, ref bflag); return false; } if (oMeasureGroup.Partitions.Contains(oPartitionNew)) { Dts.Log("Partition : " + oPartitionNew.Name + " Already exists.", 0, dataBytes); return true; //Oh! what a surprise, this partition already existing. } oMeasureGroup.Partitions.Add(oPartitionNew); try { oPartitionNew.Update(); Dts.Log("Partition updated: " + oPartitionNew.Name, 0, dataBytes); Dts.Events.FireInformation(0, "CreatePartition", "Partition updated: " + oPartitionNew.Name, null, 0, ref bflag); oPartitionNew.Process(ProcessType.ProcessFull); Dts.Log("Partition Processed: " + oPartitionNew.Name, 0, dataBytes); Dts.Events.FireInformation(0, "CreatePartition", "Partition Processed: " + oPartitionNew.Name, null, 0, ref bflag); } catch (Exception e) { if (oPartitionNew != null) Dts.Log("Error in Create partition: " + oPartitionNew.Name + "Exception: " + e.Message, 0, dataBytes); else Dts.Log("Error in Create partition: Unknown. Exception: " + e.Message, 0, dataBytes); return false; } } return true; } //~Create partitio ends here /// /// This function Gets the partition DateSKey from the partition name /// /// On Success the right DateSKey, on error zero /// private int GetPartitionDateSKey(Partition oPartition) { if (oPartition == null) return 0; try { return Convert.ToInt32(oPartition.Name.Substring(oPartition.Name.LastIndexOf("_") + 1)); } catch (Exception ex) { return 0; } } /// /// Get the new Query for the partition. /// string GetNewQuery(string sourceQuery, int iCurrentDateSKey, int iDaysToSkip) { string mainQuery = string.Empty; string newQuery = string.Empty; string newCondition; //From the Query get the Where clause int LastIndex = sourceQuery.ToUpper().LastIndexOf("WHERE"); if (LastIndex < 0) { mainQuery = sourceQuery.Substring(0, LastIndex); newCondition = " [DateSKey] >= " + Convert.ToString(iCurrentDateSKey) + " AND [DateSKey] = " + Convert.ToString(iCurrentDateSKey) + " AND [DateSKey] < " + Convert.ToString(iCurrentDateSKey + iDaysToSkip) + " "; newQuery = sourceQuery + " WHERE " + newCondition; } return newQuery; } //Deletes old partitions based on the data retention period. bool DeletePartitions(Cube oCube, string measureName, int iDaysToSkip) { Collection PartitionTobeDeleted = new Collection(); int iDateSkeyTobeDeleted; Byte[] dataBytes = new Byte[5000]; bool bflag = false; // 13 * 30 = 390 days - 13 months int iDaysTobeDeleted = 390; //TODO: Get from a variable ["DataRetentionPeriodInDays"] //Getting the number for which partition needs to deleted. old partitions iDateSkeyTobeDeleted = GetDateSKey(DateTime.Now) - iDaysTobeDeleted; MeasureGroup oMeasureGroup = oCube.MeasureGroups.FindByName(measureName); if (oMeasureGroup == null) { Dts.Log("Measure : " + oMeasureGroup.Name + " not found.", 0, dataBytes); return false; } Dts.Log("[DeletePartitions] Working on Measure: " + oMeasureGroup.Name, 0, dataBytes); string sPartitionName = string.Empty; //used for logging the correct PartitionName int MaxPartitions = oMeasureGroup.Partitions.Count; foreach (Partition oPartition2 in oMeasureGroup.Partitions) { sPartitionName = oPartition2.Name; if (!oPartition2.Name.Contains("_")) continue; //_ is not there it means it is not the partition which we need to delete try { // Get the boundary partition date from partition name int spartitionBoundaryDateSkey = GetPartitionDateSKey(oPartition2); if (spartitionBoundaryDateSkey == 0) continue; //if partition DateSKey is smaller then the partition to be deleted then delete //MaxPartition protects us from deleting all the partitions from the measures if (spartitionBoundaryDateSkey > 1) { MaxPartitions--; //can not drop object here because of foreach. PartitionTobeDeleted.Add(oPartition2); #region Log Dts.Log("Found partition that needs to be dropped: " + oPartition2.Name, 0, dataBytes); Dts.Events.FireInformation(0, "DeletePartition", "Found partition that needs to be dropped: " + oPartition2.Name, null, 0, ref bflag); #endregion } } catch (Exception e) { Dts.Log("Error in Delete partition: " + sPartitionName + "Exception: " + e.Message, 0, dataBytes); Dts.Events.FireInformation(0, "DeletePartition", "Error in Delete partition: " + sPartitionName + "Exception: " + e.Message, null, 0, ref bflag); } } //Deleting partition from the measure group foreach (Partition oPartition in PartitionTobeDeleted) { XmlaWarningCollection warningColln = new XmlaWarningCollection(); oPartition.Drop(DropOptions.Default, warningColln); } return true; }