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.
[sourcecode language='csharp']
// Structure used to keep the Measure name and their partition durations
struct
Measure
{
public string MeasureName;
// Partition duration in days
public int iPartitionDays;
public Measure(string measureName, int ipartitionDays){
MeasureName = measureName;
iPartitionDays = ipartitionDays;
}
}
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 < 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] >= " + 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;
}