How to partition Cube – using C#


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.


</P><FONT color=#008000 size=2><FONT color=#008000 size=2>
<P>// Structure used to keep the Measure name and their partition durations</P></FONT></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>
<P>struct</FONT></FONT><FONT size=2> </FONT><FONT color=#2b91af size=2><FONT color=#2b91af size=2>Measure<BR></FONT></FONT><FONT size=2>{<BR></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>&nbsp;&nbsp;&nbsp;&nbsp; public</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>string</FONT></FONT><FONT size=2> MeasureName;<BR>&nbsp;&nbsp;&nbsp;&nbsp; </FONT><FONT color=#008000 size=2><FONT color=#008000 size=2>// Partition duration in days<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</FONT></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>public</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>int</FONT></FONT><FONT size=2> iPartitionDays;<BR></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>&nbsp;&nbsp;&nbsp;&nbsp; public</FONT></FONT><FONT size=2> Measure(</FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>string</FONT></FONT><FONT size=2> measureName, </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>int</FONT></FONT><FONT size=2> ipartitionDays){<BR>&nbsp;&nbsp;&nbsp;&nbsp; MeasureName = measureName;<BR>&nbsp;&nbsp;&nbsp;&nbsp; iPartitionDays = ipartitionDays;<BR>&nbsp;&nbsp; }<BR>}<BR>


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 = new Collection();
Measures.Add(
new Measure("DownLoad Summary", 7));
Measures.Add(
new Measure("Distinct Downloaders Daily", 7));
Measures.Add(
new Measure("Distinct Downloaders Weekly", 30));
Measures.Add(
new Measure("Top Referer", 30));


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 void ProcessCube( Collection Measures, string cubeName)
{
 Server oServer =
new Server();
 
try
{
     // Get Connection object and then get the Server and Database name from DTS connection object
     ConnectionManager oConnection = null;
     for (int i = 0; i &lt; Dts.Connections.Count; i++)
     {
          oConnection = Dts.Connections[i];
          if (oConnection.Name == "CubeConnectionName") //Connection name as defined in ETL package
             {
              
break;
              }
else
               oConnection = null;
            }
         
string sServer = (string)(oConnection.Properties["ServerName"].GetValue(oConnection));
         
string sDatabase = (string)(oConnection.Properties["InitialCatalog"].GetValue(oConnection));
          oServer.Connect(sServer);
// connect to the server and start scanning down the object hierarchy
         
Database oDB = oServer.Databases.FindByName(sDatabase);
          Cube oCube = oDB.Cubes.FindByName(cubeName);
          
foreach (Measure m in Measures)
          {
             CreatePartition(oCube, m.MeasureName, m.iPartitionDays);
//Create old paritions
             
DeletePartitions(oCube, m.MeasureName, m.iPartitionDays); // Delete old partitions
         
}
     }
    
finally
   
{
        
if (oServer.Connected)
            oServer.Disconnect();
    }
}


/// Create a new partition based on the skipdays
///
///
Cube Name to be processed
/// MeasureName which need to be partitioned
/// Partition duration e.g. for daily=1, weekly=7, Monthly =30
/// true on success
///


/// This code Creates the new partition names, using the default Measure name as the partition name and the
/// DateSKey. for e.g. if the measure name is Browser and the DateSKey=39241
/// then the partition name will be 'Browser_39241'


 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] &gt;= " + Convert.ToString(iCurrentDateSKey) + " AND [DateSKey] = " + Convert.ToString(iCurrentDateSKey) + " AND [DateSKey] &lt; " + 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;
        }
 


 


 

Skip to main content