private static List <string> GetPartitionKeys(bool forProcessing, PartitioningConfiguration partitioningConfiguration, Granularity granularity)
{
//forProcessing: false to return complete target set of partitions, true to return partitons to be processed (may be less if incremental mode).
List <string> partitionKeys = new List <string>();
int numberOfPartitions = (forProcessing && !_modelConfiguration.InitialSetUp ? partitioningConfiguration.NumberOfPartitionsForIncrementalProcess : partitioningConfiguration.NumberOfPartitionsFull);
for (int i = numberOfPartitions - 1; i >= 0; i--)
{
DateTime periodToAdd;
switch (granularity)
{
case Granularity.Daily:
periodToAdd = partitioningConfiguration.MaxDate.AddDays(-i);
partitionKeys.Add(Convert.ToString((periodToAdd.Year * 100 + periodToAdd.Month) * 100 + periodToAdd.Day));
break;
case Granularity.Monthly:
periodToAdd = partitioningConfiguration.MaxDate.AddMonths(-i);
partitionKeys.Add(Convert.ToString(periodToAdd.Year * 100 + periodToAdd.Month));
break;
default: //Granularity.Yearly:
periodToAdd = partitioningConfiguration.MaxDate.AddYears(-i);
partitionKeys.Add(Convert.ToString(periodToAdd.Year));
break;
}
}
partitionKeys.Sort();
return(partitionKeys);
}
private static Partition CreateNewPartition(Table table, Partition templatePartition, PartitioningConfiguration partitioningConfiguration, string partitionKey, Granularity granularity)
{
string beginParam = GetDateKey(partitionKey, granularity, partitioningConfiguration.IntegerDateKey, false, templatePartition.Source is MPartitionSource);
string endParam = GetDateKey(partitionKey, granularity, partitioningConfiguration.IntegerDateKey, true, templatePartition.Source is MPartitionSource);
Partition newPartition;
newPartition = new Partition();
templatePartition.CopyTo(newPartition);
newPartition.Name = partitionKey;
//string query = String.Format(partitioningConfiguration.TemplateSourceQuery, beginParam, endParam);
string query = partitioningConfiguration.TemplateSourceQuery.Replace("{0}", beginParam).Replace("{1}", endParam);
switch (newPartition.Source)
{
case MPartitionSource mSource:
mSource.Expression = query;
break;
case QueryPartitionSource querySource:
querySource.Query = query;
break;
}
table.Partitions.Add(newPartition);
return(newPartition);
}
private static Partition CreateNewPartition(Table table, Partition templatePartition, PartitioningConfiguration partitioningConfiguration, string partitionKey)
{
string selectQueryTemplate;
switch (partitioningConfiguration.Granularity)
{
//Format that might work on more data sources, but requires flag to indicate whether partitioning column is date or integer YYYYMMDD or not:
// SELECT YEAR(CURRENT_TIMESTAMP) * 10000 + MONTH(CURRENT_TIMESTAMP) * 100 + DAY(CURRENT_TIMESTAMP)
//ANSI standard to get month from date is EXTRACT(MONTH FROM @DateTimeVarUnclean), which doesn't work with SQL Server
case Granularity.Daily:
selectQueryTemplate = "SELECT * FROM {0} WHERE CAST(CONVERT(varchar, {1}, 112) AS int) = {2} ORDER BY {1}";
break;
case Granularity.Monthly:
selectQueryTemplate = "SELECT * FROM {0} WHERE FLOOR(CAST(CONVERT(varchar, {1}, 112) AS int) / 100) = {2} ORDER BY {1}";
break;
default: //Granularity.Yearly:
selectQueryTemplate = "SELECT * FROM {0} WHERE FLOOR(CAST(CONVERT(varchar, {1}, 112) AS int) / 10000) = {2} ORDER BY {1}";
break;
}
Partition newPartition;
newPartition = new Partition();
templatePartition.CopyTo(newPartition);
newPartition.Name = partitionKey;
((QueryPartitionSource)newPartition.Source).Query = String.Format(selectQueryTemplate, partitioningConfiguration.SourceTableName, partitioningConfiguration.SourcePartitionColumn, partitionKey);
table.Partitions.Add(newPartition);
return(newPartition);
}
/// <summary>
/// Merge months into a year, or days into a month.
/// </summary>
/// <param name="modelConfiguration">Configuration info for the model</param>
/// <param name="messageLogger">Pointer to logging method</param>
/// <param name="analysisServicesTable">Name of the partitioned table in the tabular model.</param>
/// <param name="targetGranularity">Granularity of the newly created partition. Must be year or month.</param>
/// <param name="partitionKey">Target partition key. If year, follow yyyy; if month follow yyyymm.</param>
public static void MergePartitions(ModelConfiguration modelConfiguration, LogMessageDelegate messageLogger, string analysisServicesTable, Granularity targetGranularity, string partitionKey)
{
_modelConfiguration = modelConfiguration;
_messageLogger = messageLogger;
Server server = new Server();
try
{
LogMessage("", MessageType.Informational, false);
LogMessage($"Merge partitions into {partitionKey} for table {analysisServicesTable}", MessageType.Informational, false);
LogMessage(new String('-', partitionKey.Length + analysisServicesTable.Length + 33), MessageType.Informational, false);
LogMessage("", MessageType.Informational, false);
LogMessage("=>Actions & progress:", MessageType.Informational, false);
//Check target granularity
if (targetGranularity == Granularity.Daily)
{
throw new InvalidOperationException($"Target granularity for merging must be year or month.");
}
//Check new partition key is expected format
int partitionKeyParsed;
if (!(
(partitionKey.Length == 4 && targetGranularity == Granularity.Yearly) ||
(partitionKey.Length == 6 && targetGranularity == Granularity.Monthly)
) || !int.TryParse(partitionKey, out partitionKeyParsed)
)
{
throw new InvalidOperationException($"Partition key {partitionKey} is not of expected format.");
}
//Check configuration contains the partitioned table
bool foundMatch = false;
PartitioningConfiguration partitionConfig = null;
foreach (TableConfiguration tableConfig in modelConfiguration.TableConfigurations)
{
if (tableConfig.AnalysisServicesTable == analysisServicesTable && tableConfig.PartitioningConfigurations.Count > 0)
{
partitionConfig = tableConfig.PartitioningConfigurations[0];
foundMatch = true;
break;
}
}
if (!foundMatch)
{
throw new InvalidOperationException($"Table {analysisServicesTable} not found in configuration with at least one partitioning configuration defined.");
}
Database database;
Connect(server, out database);
Table table = database.Model.Tables.Find(analysisServicesTable);
if (table == null)
{
throw new Microsoft.AnalysisServices.ConnectionException($"Could not connect to table {analysisServicesTable}.");
}
//Find template partition
Partition templatePartition = table.Partitions.Find(analysisServicesTable);
if (templatePartition == null)
{
throw new InvalidOperationException($"Table {analysisServicesTable} does not contain a partition with the same name to act as the template partition.");
}
//See if there is already a partition with same key - do not want to delete an existing partition in case of inadvertent data loss
if (table.Partitions.Find(partitionKey) != null)
{
throw new InvalidOperationException($"Table {analysisServicesTable} already contains a partition with key {partitionKey}. Please delete this partition and retry.");
}
//Check there are partitions to be merged
Granularity childGranularity = targetGranularity == Granularity.Yearly ? Granularity.Monthly : Granularity.Daily;
List <Partition> partitionsToBeMerged = GetPartitionsCurrent(table, childGranularity, partitionKey);
if (partitionsToBeMerged.Count == 0)
{
LogMessage($"No partitinos found in {analysisServicesTable} to be merged into {partitionKey}.", MessageType.Informational, false);
}
else
{
//Done with validation, so go ahead ...
LogMessage("", MessageType.Informational, false);
LogMessage($"Create new merged partition {DateFormatPartitionKey(partitionKey, targetGranularity)} for table {analysisServicesTable}", MessageType.Informational, true);
Partition newPartition = CreateNewPartition(table, templatePartition, partitionConfig, partitionKey, targetGranularity);
foreach (Partition partition in partitionsToBeMerged)
{
LogMessage($"Partition {partition.Name} to be merged into {DateFormatPartitionKey(partitionKey, targetGranularity)}", MessageType.Informational, true);
}
newPartition.RequestMerge(partitionsToBeMerged);
LogMessage($"Save changes for table {analysisServicesTable} ...", MessageType.Informational, true);
database.Model.SaveChanges();
Console.ForegroundColor = ConsoleColor.White;
LogMessage("", MessageType.Informational, false);
LogMessage("Finish: " + DateTime.Now.ToString("hh:mm:ss tt"), MessageType.Informational, false);
}
}
catch (Exception exc)
{
Console.ForegroundColor = ConsoleColor.Red;
LogMessage("", MessageType.Informational, false);
LogMessage($"Exception occurred: {DateTime.Now.ToString("hh:mm:ss tt")}", MessageType.Error, false);
LogMessage($"Exception message: {exc.Message}", MessageType.Error, false);
if (exc.InnerException != null)
{
LogMessage($"Inner exception message: {exc.InnerException.Message}", MessageType.Error, false);
}
LogMessage("", MessageType.Informational, false);
Console.ForegroundColor = ConsoleColor.White;
}
finally
{
try
{
_modelConfiguration = null;
_messageLogger = null;
if (server != null)
{
server.Disconnect();
}
}
catch { }
}
}
private static Partition CreateNewPartition(Table table, Partition templatePartition, PartitioningConfiguration partitioningConfiguration, string partitionKey, Granularity granularity)
{
string beginParam = GetDateKey(partitionKey, granularity, partitioningConfiguration.IntegerDateKey, false);
string endParam = GetDateKey(partitionKey, granularity, partitioningConfiguration.IntegerDateKey, true);
Partition newPartition;
newPartition = new Partition();
templatePartition.CopyTo(newPartition);
newPartition.Name = partitionKey;
((QueryPartitionSource)newPartition.Source).Query = String.Format(partitioningConfiguration.TemplateSourceQuery, beginParam, endParam);
table.Partitions.Add(newPartition);
return(newPartition);
}
