/// <summary>
/// Adds the new partition to specified table based on the template query
/// </summary>
/// <param name="newParitionInfo"></param>
/// <param name="tableToProcess"></param>
/// <param name="templatePartition"></param>
private static void AddPartitionToTable(NewPartitionInfo newParitionInfo, Table tableToProcess, Partition templatePartition)
{
if (newParitionInfo == null || tableToProcess == null || templatePartition == null)
{
return;
}
Partition newPartition = new Partition();
templatePartition.CopyTo(newPartition);
newPartition.Name = newParitionInfo.PartitionName;
switch (newPartition.Source)
{
case MPartitionSource mSource:
mSource.Expression = newParitionInfo.SourceQuery;
break;
case QueryPartitionSource querySource:
querySource.Query = newParitionInfo.SourceQuery;
break;
}
tableToProcess.Partitions.Add(newPartition);
}
public static HttpResponseMessage Run([HttpTrigger(AuthorizationLevel.Function, "post",
Route = "ProcessTabularModel/{databaseName}/tables/{tableName}/repartition/{count}/bydate/{date?}")] HttpRequestMessage req,
string databaseName,
string tableName,
string count,
string date,
TraceWriter log)
{
log.Info($"Received request to auto repartition {databaseName}/{tableName} by months.");
DateTime targetDate = DateTime.TryParse(date, out targetDate) ? targetDate : DateTime.Today;
int maxPartitionCount = Int32.TryParse(count, out maxPartitionCount) ? maxPartitionCount : 180;
log.Info($"Start Date: {targetDate} | Maximum Partitions: {maxPartitionCount}");
try
{
// Determine number of partitions to be created by month based on number of partitions and end date and their properties
List <NewPartitionInfo> newPartitionInfoList = new List <NewPartitionInfo>();
for (int partitionCount = 0;
partitionCount < maxPartitionCount;
partitionCount++)
{
DateTime currentTargetDate = targetDate.AddMonths(-partitionCount);
NewPartitionInfo newPartitionInfo = new NewPartitionInfo()
{
TableName = tableName,
PartitionName = QueryHelper.GeneratePartitionKey(currentTargetDate, PartitionGranularity.Monthly),
SourceQuery = QueryHelper.GetSourceQueryBasedOnDate(tableName, currentTargetDate, PartitionGranularity.Monthly)
};
newPartitionInfoList.Add(newPartitionInfo);
}
// Create the missing partitions in the database
SqlServerAnalysisServerTabular tabularModel = new SqlServerAnalysisServerTabular()
{
ConnectionString = ConfigurationManager.ConnectionStrings["SsasTabularConnection"].ConnectionString,
DatabaseName = databaseName ?? ConfigurationManager.AppSettings["DatabaseName"]
};
if (newPartitionInfoList.Count > 0)
{
tabularModel.CreatePartitions(tableName, newPartitionInfoList.ToArray());
}
}
catch (Exception e)
{
log.Info($"C# HTTP trigger function exception: {e.ToString()}");
return(req.CreateErrorResponse(HttpStatusCode.InternalServerError, e));
}
return(req.CreateResponse(HttpStatusCode.OK,
new { result = $"Repartitioned {databaseName}/{tableName} - {maxPartitionCount} partitions from {targetDate.ToShortDateString()}" }));
}
/// <summary>
/// Creates the partition based on the specified partition information
/// </summary>
/// <param name="newPartitionInfo">New parition information</param>
/// <param name="replaceExistingPartition">flag to indicate, if the existing partition with same names should be removed, prior to creating a new one</param>
public void CreateNewPartition(NewPartitionInfo newPartitionInfo, bool replaceExistingPartition = false)
{
if (newPartitionInfo != null)
{
Server analysisServer = new Server();
try
{
analysisServer.Connect(this.ConnectionString);
Database database = analysisServer.Databases.GetByName(this.DatabaseName);
Model model = database.Model;
Table tableToProcess = model.Tables.Find(newPartitionInfo.TableName);
if (tableToProcess != null)
{
Partition templatePartition = tableToProcess.Partitions.Find(newPartitionInfo.TableName);
Partition existingPartition = tableToProcess.Partitions.Find(newPartitionInfo.PartitionName);
/* Delete existing partition, if configured to replace partition */
if (replaceExistingPartition)
{
DeletePartition(model, tableToProcess, existingPartition);
existingPartition = null;
}
/* if partition does not exist or has been removed */
if (existingPartition == null)
{
AddPartitionToTable(newPartitionInfo, tableToProcess, templatePartition);
ModelSaveWithRetries(model);
}
}
}
finally { analysisServer.Disconnect(); }
}
}
/// <summary>
/// Merge the list of partitions to the specified partition list
/// </summary>
/// <param name="tableName">Name of the table</param>
/// <param name="targetPartitionInfo">Target partition information</param>
/// <param name="sourcePartitionNames">Names of source partitions that will be merged</param>
public void MergeParitions(string tableName, NewPartitionInfo targetPartitionInfo, string[] sourcePartitionNames)
{
if (String.IsNullOrEmpty(tableName) ||
targetPartitionInfo == null ||
sourcePartitionNames == null ||
sourcePartitionNames?.Length == 0)
{
return;
}
List <Partition> partitionsToMerge = new List <Partition>();
Server analysisServer = new Server();
try
{
analysisServer.Connect(this.ConnectionString);
Database database = analysisServer.Databases.GetByName(this.DatabaseName);
Model model = database.Model;
Table tableToProcess = model.Tables.Find(tableName);
if (tableToProcess != null)
{
// Get reference to the target partition based on partition name
Partition existingTargetPartition = tableToProcess.Partitions.Find(targetPartitionInfo.PartitionName);
// if target partition does not exist
if (existingTargetPartition == null)
{
// create the new partition and disconnect
CreateNewPartition(targetPartitionInfo);
analysisServer.Disconnect();
//Refresh the model with new connection
analysisServer.Connect(this.ConnectionString);
database = analysisServer.Databases.GetByName(this.DatabaseName);
model = database.Model;
tableToProcess = model.Tables.Find(tableName);
existingTargetPartition = tableToProcess.Partitions.Find(targetPartitionInfo.PartitionName);
}
// if target parition exists (or was created in the prior step)
if (existingTargetPartition != null)
{
// for the given partition names
foreach (string partitionName in sourcePartitionNames)
{
// check if the source partition exist
Partition sourcePartition = tableToProcess.Partitions.Find(partitionName);
if (sourcePartition != null)
{
// if source partition exists , add to partition to merge list
partitionsToMerge.Add(sourcePartition);
}
}
// if there is partition is the partition list
if (partitionsToMerge.Count > 0)
{
// request merge and update the model
existingTargetPartition.RequestMerge(partitionsToMerge);
ModelSaveWithRetries(model);
}
}
}
}
finally { analysisServer.Disconnect(); }
}
