/// <summary>
/// Read configuration information from the database.
/// </summary>
/// <param name="connectionInfo">Information required to connect to the configuration and logging database.</param>
/// <returns>Collection of partitioned models with configuration information.</returns>
public static List <ModelConfiguration> ReadConfig(ConfigDatabaseConnectionInfo connectionInfo)
{
using (SqlConnection connection = new SqlConnection(GetConnectionString(connectionInfo)))
{
connection.Open();
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandType = CommandType.Text;
command.CommandText = @"
SELECT [ModelConfigurationID]
,[AnalysisServicesServer]
,[AnalysisServicesDatabase]
,[InitialSetUp]
,[IncrementalOnline]
,[IncrementalParallelTables]
,[IntegratedAuth]
,[TableConfigurationID]
,[AnalysisServicesTable]
,[Partitioned]
,[PartitioningConfigurationID]
,[Granularity]
,[NumberOfPartitionsFull]
,[NumberOfPartitionsForIncrementalProcess]
,[MaxDate]
,[SourceTableName]
,[SourcePartitionColumn]
FROM [dbo].[vPartitioningConfiguration]
ORDER BY
[ModelConfigurationID],
[TableConfigurationID],
[PartitioningConfigurationID];";
List <ModelConfiguration> modelConfigs = new List <ModelConfiguration>();
ModelConfiguration modelConfig = null;
int currentModelConfigurationID = -1;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
TableConfiguration tableConfig = null;
int currentTableConfigurationID = -1;
if (modelConfig == null || currentModelConfigurationID != Convert.ToInt32(reader["ModelConfigurationID"]))
{
modelConfig = new ModelConfiguration();
modelConfig.TableConfigurations = new List <TableConfiguration>();
modelConfigs.Add(modelConfig);
modelConfig.ModelConfigurationID = Convert.ToInt32(reader["ModelConfigurationID"]);
modelConfig.AnalysisServicesServer = Convert.ToString(reader["AnalysisServicesServer"]);
modelConfig.AnalysisServicesDatabase = Convert.ToString(reader["AnalysisServicesDatabase"]);
modelConfig.InitialSetUp = Convert.ToBoolean(reader["InitialSetUp"]);
modelConfig.IncrementalOnline = Convert.ToBoolean(reader["IncrementalOnline"]);
modelConfig.IncrementalParallelTables = Convert.ToBoolean(reader["IncrementalParallelTables"]);
modelConfig.IntegratedAuth = Convert.ToBoolean(reader["IntegratedAuth"]);
modelConfig.ConfigDatabaseConnectionInfo = connectionInfo;
currentModelConfigurationID = modelConfig.ModelConfigurationID;
}
if (tableConfig == null || currentTableConfigurationID != Convert.ToInt32(reader["TableConfigurationID"]))
{
tableConfig = new TableConfiguration();
tableConfig.PartitioningConfigurations = new List <PartitioningConfiguration>();
modelConfig.TableConfigurations.Add(tableConfig);
tableConfig.TableConfigurationID = Convert.ToInt32(reader["TableConfigurationID"]);
tableConfig.AnalysisServicesTable = Convert.ToString(reader["AnalysisServicesTable"]);
currentTableConfigurationID = tableConfig.TableConfigurationID;
}
if (Convert.ToBoolean(reader["Partitioned"]))
{
tableConfig.PartitioningConfigurations.Add(
new PartitioningConfiguration(
Convert.ToInt32(reader["PartitioningConfigurationID"]),
(Granularity)Convert.ToInt32(reader["Granularity"]),
Convert.ToInt32(reader["NumberOfPartitionsFull"]),
Convert.ToInt32(reader["NumberOfPartitionsForIncrementalProcess"]),
Convert.ToDateTime(reader["MaxDate"]),
Convert.ToString(reader["SourceTableName"]),
Convert.ToString(reader["SourcePartitionColumn"])
)
);
}
}
return(modelConfigs);
}
}
}
/// <summary>
/// Partitions tables in a tabular model based on configuration
/// </summary>
/// <param name="modelConfiguration">Configuration info for the model</param>
/// <param name="messageLogger">Pointer to logging method</param>
public static void PerformProcessing(ModelConfiguration modelConfiguration, LogMessageDelegate messageLogger)
{
_modelConfiguration = modelConfiguration;
_messageLogger = messageLogger;
Server server = new Server();
try
{
Database database;
Connect(server, out database);
Console.ForegroundColor = ConsoleColor.White;
LogMessage($"Start: {DateTime.Now.ToString("hh:mm:ss tt")}", false);
LogMessage($"Server: {_modelConfiguration.AnalysisServicesServer}", false);
LogMessage($"Database: {_modelConfiguration.AnalysisServicesDatabase}", false);
Console.ForegroundColor = ConsoleColor.Yellow;
foreach (TableConfiguration tableConfiguration in _modelConfiguration.TableConfigurations)
{
Table table = database.Model.Tables.Find(tableConfiguration.AnalysisServicesTable);
if (table == null)
{
throw new Microsoft.AnalysisServices.ConnectionException($"Could not connect to table {tableConfiguration.AnalysisServicesTable}.");
}
if (tableConfiguration.PartitioningConfigurations.Count == 0)
{
//Non-partitioned table. Process at table level.
LogMessage("", false);
LogMessage($"Non-partitioned processing for table {tableConfiguration.AnalysisServicesTable}", false);
LogMessage(new String('-', tableConfiguration.AnalysisServicesTable.Length + 37), false);
if (_modelConfiguration.IncrementalOnline)
{
LogMessage($"Process table {tableConfiguration.AnalysisServicesTable} /Full", true);
table.RequestRefresh(RefreshType.Full);
}
else
{
LogMessage($"Process table {tableConfiguration.AnalysisServicesTable} /DataOnly", true);
table.RequestRefresh(RefreshType.DataOnly);
}
}
else
{
//Non-partitioned table. Process based on partitioning configuration(s).
Partition templatePartition = table.Partitions.Find(tableConfiguration.AnalysisServicesTable);
if (templatePartition == null)
{
throw new Microsoft.AnalysisServices.ConnectionException($"Table {tableConfiguration.AnalysisServicesTable} does not contain a partition with the same name to act as the template partition.");
}
foreach (PartitioningConfiguration partitioningConfiguration in tableConfiguration.PartitioningConfigurations)
{
LogMessage("", false);
LogMessage($"Rolling-window partitioning for table {tableConfiguration.AnalysisServicesTable}", false);
LogMessage(new String('-', tableConfiguration.AnalysisServicesTable.Length + 38), false);
//Figure out what processing needs to be done
List <string> partitionKeysCurrent = GetPartitionKeysTable(table, partitioningConfiguration.Granularity);
List <string> partitionKeysNew = GetPartitionKeys(false, partitioningConfiguration, partitioningConfiguration.Granularity);
List <string> partitionKeysForProcessing = GetPartitionKeys(true, partitioningConfiguration, partitioningConfiguration.Granularity);
DisplayPartitionRange(partitionKeysCurrent, true, partitioningConfiguration.Granularity);
DisplayPartitionRange(partitionKeysNew, false, partitioningConfiguration.Granularity);
LogMessage("", false);
LogMessage("=>Actions & progress:", false);
//Check for old partitions that need to be removed
foreach (string partitionKey in partitionKeysCurrent)
{
if (Convert.ToInt32(partitionKey) < Convert.ToInt32(partitionKeysNew[0]))
{
LogMessage($"Remove old partition {DateFormatPartitionKey(partitionKey, partitioningConfiguration.Granularity)}", true);
table.Partitions.Remove(partitionKey);
}
}
//Process partitions
string selectQueryTemplate;
switch (partitioningConfiguration.Granularity)
{
case Granularity.Daily:
selectQueryTemplate = "SELECT * FROM {0} WHERE {1} = {2} ORDER BY {1}";
break;
case Granularity.Monthly:
selectQueryTemplate = "SELECT * FROM {0} WHERE FLOOR({1} / 100) = {2} ORDER BY {1}";
break;
default: //Granularity.Yearly:
selectQueryTemplate = "SELECT * FROM {0} WHERE FLOOR({1} / 10000) = {2} ORDER BY {1}";
break;
}
foreach (string partitionKey in partitionKeysForProcessing)
{
Partition partitionToProcess = table.Partitions.Find(partitionKey);
if (partitionToProcess == null)
{
//Doesn't exist so create it
partitionToProcess = new Partition();
templatePartition.CopyTo(partitionToProcess);
partitionToProcess.Name = partitionKey;
((QueryPartitionSource)partitionToProcess.Source).Query = String.Format(selectQueryTemplate, partitioningConfiguration.SourceTableName, partitioningConfiguration.SourcePartitionColumn, partitionKey);
table.Partitions.Add(partitionToProcess);
LogMessage($"Create new partition {DateFormatPartitionKey(partitionKey, partitioningConfiguration.Granularity)}", true);
if (!_modelConfiguration.InitialSetUp)
{
IncrementalProcessPartition(partitionKey, partitionToProcess, partitioningConfiguration.Granularity);
}
}
else if (!_modelConfiguration.InitialSetUp)
{
//Existing partition for processing
IncrementalProcessPartition(partitionKey, partitionToProcess, partitioningConfiguration.Granularity);
}
if (_modelConfiguration.InitialSetUp)
{
if (partitionToProcess.State != ObjectState.Ready)
{
//Process new partitions sequentially during initial setup so don't run out of memory
LogMessage($"Sequentially process {DateFormatPartitionKey(partitionKey, partitioningConfiguration.Granularity)} /DataOnly", true);
partitionToProcess.RequestRefresh(RefreshType.DataOnly);
database.Model.SaveChanges();
}
else
{
//Partition already exists during initial setup (and is fully processed), so ignore it
LogMessage($"Partition {DateFormatPartitionKey(partitionKey, partitioningConfiguration.Granularity)} already exists and is processed", true);
}
}
}
}
//Ensure template partition doesn't contain any data
if (_modelConfiguration.InitialSetUp)
{
((QueryPartitionSource)templatePartition.Source).Query = String.Format("SELECT * FROM {0} WHERE 0=1", tableConfiguration.PartitioningConfigurations[0].SourceTableName); //assuming the same for all partitioning configurations
templatePartition.RequestRefresh(RefreshType.DataOnly);
}
}
//If processing tables sequentially (but all partitions being done in parallel), then save changes now
if (!_modelConfiguration.IncrementalParallelTables)
{
LogMessage($"Save changes for table {tableConfiguration.AnalysisServicesTable} ...", true);
database.Model.SaveChanges();
}
}
//Commit the data changes, and bring model back online if necessary
LogMessage("", false);
LogMessage("Final operations", false);
LogMessage(new String('-', 16), false);
if (_modelConfiguration.IncrementalParallelTables)
{
LogMessage("Save changes ...", true);
database.Model.SaveChanges();
}
if (_modelConfiguration.InitialSetUp || (!_modelConfiguration.InitialSetUp && !_modelConfiguration.IncrementalOnline))
{
LogMessage("Recalc model to bring back online ...", true);
database.Model.RequestRefresh(RefreshType.Calculate);
database.Model.SaveChanges();
}
Console.ForegroundColor = ConsoleColor.White;
LogMessage("", false);
LogMessage("Finish: " + DateTime.Now.ToString("hh:mm:ss tt"), false);
}
catch (Exception exc)
{
Console.ForegroundColor = ConsoleColor.Red;
LogMessage("", false);
LogMessage($"Exception occurred: {DateTime.Now.ToString("hh:mm:ss tt")}", false);
LogMessage($"Exception message: {exc.Message}", false);
LogMessage($"Inner exception message: {exc.InnerException.Message}", false);
}
finally
{
try
{
_modelConfiguration = null;
_messageLogger = null;
if (server != null)
{
server.Disconnect();
}
}
catch { }
}
}
/// <summary>
/// Read configuration information from the database.
/// </summary>
/// <param name="connectionInfo">Information required to connect to the configuration and logging database.</param>
/// <param name="modelConfigurationIDs">Comma-delimited list of ModelConfigurationIDs to filter on when getting worklist from the configuration and logging database.</param>
/// <returns>Collection of partitioned models with configuration information.</returns>
public static List <ModelConfiguration> ReadConfig(ConfigDatabaseConnectionInfo connectionInfo, string modelConfigurationIDs)
{
string commandText = String.Format(@"
SELECT [ModelConfigurationID]
,[AnalysisServicesServer]
,[AnalysisServicesDatabase]
,[InitialSetUp]
,[IncrementalOnline]
,[IntegratedAuth]
,[MaxParallelism]
,[CommitTimeout]
,[RetryAttempts]
,[RetryWaitTimeSeconds]
,[TableConfigurationID]
,[AnalysisServicesTable]
,[Partitioned]
,[PartitioningConfigurationID]
,[Granularity]
,[NumberOfPartitionsFull]
,[NumberOfPartitionsForIncrementalProcess]
,[MaxDateIsNow]
,[MaxDate]
,[IntegerDateKey]
,[TemplateSourceQuery]
FROM [dbo].[vPartitioningConfiguration]
WHERE [DoNotProcess] = 0 {0}
ORDER BY
[ModelConfigurationID],
[TableConfigurationID],
[PartitioningConfigurationID];",
(String.IsNullOrEmpty(modelConfigurationIDs) ? "" : $" AND [ModelConfigurationID] IN ({modelConfigurationIDs}) "));
using (SqlConnection connection = new SqlConnection(GetConnectionString(connectionInfo)))
{
connection.Open();
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandType = CommandType.Text;
command.CommandText = commandText;
List <ModelConfiguration> modelConfigs = new List <ModelConfiguration>();
ModelConfiguration modelConfig = null;
int currentModelConfigurationID = -1;
TableConfiguration tableConfig = null;
int currentTableConfigurationID = -1;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
if (modelConfig == null || currentModelConfigurationID != Convert.ToInt32(reader["ModelConfigurationID"]))
{
modelConfig = new ModelConfiguration();
modelConfig.TableConfigurations = new List <TableConfiguration>();
modelConfigs.Add(modelConfig);
modelConfig.ModelConfigurationID = Convert.ToInt32(reader["ModelConfigurationID"]);
modelConfig.AnalysisServicesServer = Convert.ToString(reader["AnalysisServicesServer"]);
modelConfig.AnalysisServicesDatabase = Convert.ToString(reader["AnalysisServicesDatabase"]);
modelConfig.InitialSetUp = Convert.ToBoolean(reader["InitialSetUp"]);
modelConfig.IncrementalOnline = Convert.ToBoolean(reader["IncrementalOnline"]);
modelConfig.IntegratedAuth = Convert.ToBoolean(reader["IntegratedAuth"]);
modelConfig.MaxParallelism = Convert.ToInt32(reader["MaxParallelism"]);
modelConfig.CommitTimeout = Convert.ToInt32(reader["CommitTimeout"]);
modelConfig.RetryAttempts = Convert.ToInt32(reader["RetryAttempts"]);
modelConfig.RetryWaitTimeSeconds = Convert.ToInt32(reader["RetryWaitTimeSeconds"]);
modelConfig.ConfigDatabaseConnectionInfo = connectionInfo;
currentModelConfigurationID = modelConfig.ModelConfigurationID;
}
if (tableConfig == null || currentTableConfigurationID != Convert.ToInt32(reader["TableConfigurationID"]))
{
tableConfig = new TableConfiguration();
tableConfig.PartitioningConfigurations = new List <PartitioningConfiguration>();
modelConfig.TableConfigurations.Add(tableConfig);
tableConfig.TableConfigurationID = Convert.ToInt32(reader["TableConfigurationID"]);
tableConfig.AnalysisServicesTable = Convert.ToString(reader["AnalysisServicesTable"]);
currentTableConfigurationID = tableConfig.TableConfigurationID;
}
if (Convert.ToBoolean(reader["Partitioned"]))
{
tableConfig.PartitioningConfigurations.Add(
new PartitioningConfiguration(
Convert.ToInt32(reader["PartitioningConfigurationID"]),
(Granularity)Convert.ToInt32(reader["Granularity"]),
Convert.ToInt32(reader["NumberOfPartitionsFull"]),
Convert.ToInt32(reader["NumberOfPartitionsForIncrementalProcess"]),
Convert.ToBoolean(reader["MaxDateIsNow"]),
(reader["MaxDate"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(reader["MaxDate"])),
Convert.ToBoolean(reader["IntegerDateKey"]),
Convert.ToString(reader["TemplateSourceQuery"])
)
);
}
}
return(modelConfigs);
}
}
}
private static void PerformProcessing()
{
Server server = new Server();
try
{
Database database;
Connect(server, out database);
Console.ForegroundColor = ConsoleColor.White;
LogMessage($"Start: {DateTime.Now.ToString("hh:mm:ss tt")}", MessageType.Informational, false);
LogMessage($"Server: {_modelConfiguration.AnalysisServicesServer}", MessageType.Informational, false);
LogMessage($"Database: {_modelConfiguration.AnalysisServicesDatabase}", MessageType.Informational, false);
Console.ForegroundColor = ConsoleColor.Yellow;
foreach (TableConfiguration tableConfiguration in _modelConfiguration.TableConfigurations)
{
Table table = database.Model.Tables.Find(tableConfiguration.AnalysisServicesTable);
if (table == null)
{
throw new Microsoft.AnalysisServices.ConnectionException($"Could not connect to table {tableConfiguration.AnalysisServicesTable}.");
}
if (tableConfiguration.PartitioningConfigurations.Count == 0)
{
//Non-partitioned table. Process at table level.
LogMessage("", MessageType.Informational, false);
LogMessage($"Non-partitioned processing for table {tableConfiguration.AnalysisServicesTable}", MessageType.Informational, false);
LogMessage(new String('-', tableConfiguration.AnalysisServicesTable.Length + 37), MessageType.Informational, false);
if (_modelConfiguration.IncrementalOnline)
{
LogMessage($"Process table {tableConfiguration.AnalysisServicesTable} /Full", MessageType.Informational, true);
table.RequestRefresh(RefreshType.Full);
}
else
{
LogMessage($"Process table {tableConfiguration.AnalysisServicesTable} /DataOnly", MessageType.Informational, true);
table.RequestRefresh(RefreshType.DataOnly);
}
}
else
{
//Validate multiple granularity ranges.
tableConfiguration.ValidatePartitioningConfigurations();
//Find template partition.
Partition templatePartition = table.Partitions.Find(tableConfiguration.AnalysisServicesTable);
if (templatePartition == null)
{
throw new InvalidOperationException($"Table {tableConfiguration.AnalysisServicesTable} does not contain a partition with the same name to act as the template partition.");
}
//Process based on partitioning configuration(s).
foreach (PartitioningConfiguration partitioningConfiguration in tableConfiguration.PartitioningConfigurations)
{
LogMessage("", MessageType.Informational, false);
LogMessage($"Rolling-window partitioning for table {tableConfiguration.AnalysisServicesTable}", MessageType.Informational, false);
LogMessage(new String('-', tableConfiguration.AnalysisServicesTable.Length + 38), MessageType.Informational, false);
//Figure out what processing needs to be done
List <string> partitionKeysCurrent = GetPartitionKeysCurrent(table, partitioningConfiguration.Granularity);
List <string> partitionKeysNew = GetPartitionKeysTarget(false, partitioningConfiguration, partitioningConfiguration.Granularity);
List <string> partitionKeysForProcessing = GetPartitionKeysTarget(true, partitioningConfiguration, partitioningConfiguration.Granularity);
DisplayPartitionRange(partitionKeysCurrent, true, partitioningConfiguration.Granularity);
DisplayPartitionRange(partitionKeysNew, false, partitioningConfiguration.Granularity);
LogMessage("", MessageType.Informational, false);
LogMessage("=>Actions & progress:", MessageType.Informational, false);
//Check for old partitions that need to be removed
foreach (string partitionKey in partitionKeysCurrent)
{
if (Convert.ToInt32(partitionKey) < Convert.ToInt32(partitionKeysNew[0]))
{
LogMessage($"Remove old partition {DateFormatPartitionKey(partitionKey, partitioningConfiguration.Granularity)}", MessageType.Informational, true);
table.Partitions.Remove(partitionKey);
}
}
//Process partitions
foreach (string partitionKey in partitionKeysForProcessing)
{
Partition partitionToProcess = table.Partitions.Find(partitionKey);
if (partitionToProcess == null)
{
partitionToProcess = CreateNewPartition(table, templatePartition, partitioningConfiguration, partitionKey, partitioningConfiguration.Granularity);
LogMessage($"Create new partition {DateFormatPartitionKey(partitionKey, partitioningConfiguration.Granularity)}", MessageType.Informational, true);
if (!_modelConfiguration.InitialSetUp)
{
IncrementalProcessPartition(partitionKey, partitionToProcess, partitioningConfiguration.Granularity);
}
}
else if (!_modelConfiguration.InitialSetUp)
{
//Existing partition for processing
IncrementalProcessPartition(partitionKey, partitionToProcess, partitioningConfiguration.Granularity);
}
if (_modelConfiguration.InitialSetUp)
{
if (partitionToProcess.State != ObjectState.Ready)
{
//Process new partitions sequentially during initial setup so don't run out of memory
LogMessage($"Sequentially process {DateFormatPartitionKey(partitionKey, partitioningConfiguration.Granularity)} /DataOnly", MessageType.Informational, true);
partitionToProcess.RequestRefresh(RefreshType.DataOnly);
database.Model.SaveChanges();
}
else
{
//Partition already exists during initial setup (and is fully processed), so ignore it
LogMessage($"Partition {DateFormatPartitionKey(partitionKey, partitioningConfiguration.Granularity)} already exists and is processed", MessageType.Informational, true);
}
}
}
}
//Ensure template partition doesn't contain any data
if (_modelConfiguration.InitialSetUp)
{
string beginParam = GetDateKey("19010102", Granularity.Daily, tableConfiguration.PartitioningConfigurations[0].IntegerDateKey, false, templatePartition.Source is MPartitionSource);
string endParam = GetDateKey("19010101", Granularity.Daily, tableConfiguration.PartitioningConfigurations[0].IntegerDateKey, false, templatePartition.Source is MPartitionSource);
//Query generated will always return nothing
string query = tableConfiguration.PartitioningConfigurations[0].TemplateSourceQuery.Replace("{0}", beginParam).Replace("{1}", endParam);
switch (templatePartition.Source)
{
case MPartitionSource mSource:
mSource.Expression = query;
break;
case QueryPartitionSource querySource:
querySource.Query = query;
break;
}
templatePartition.RequestRefresh(RefreshType.DataOnly);
}
}
}
//Commit the data changes, and bring model back online if necessary
LogMessage("", MessageType.Informational, false);
LogMessage("Final operations", MessageType.Informational, false);
LogMessage(new String('-', 16), MessageType.Informational, false);
//Save changes setting MaxParallelism if necessary
if (_modelConfiguration.MaxParallelism == -1)
{
LogMessage("Save changes ...", MessageType.Informational, true);
database.Model.SaveChanges();
}
else
{
LogMessage($"Save changes with MaxParallelism={Convert.ToString(_modelConfiguration.MaxParallelism)}...", MessageType.Informational, true);
database.Model.SaveChanges(new SaveOptions()
{
MaxParallelism = _modelConfiguration.MaxParallelism
});
}
//Perform recalc if necessary
if (_modelConfiguration.InitialSetUp || (!_modelConfiguration.InitialSetUp && !_modelConfiguration.IncrementalOnline))
{
LogMessage("Recalc model to bring back online ...", MessageType.Informational, true);
database.Model.RequestRefresh(RefreshType.Calculate);
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;
//Auto retry
if (_retryAttempts > 0)
{
LogMessage($"Retry attempts remaining: {Convert.ToString(_retryAttempts)}. Will wait {Convert.ToString(_modelConfiguration.RetryWaitTimeSeconds)} seconds and then attempt retry.", MessageType.Informational, false);
_retryAttempts -= 1;
Thread.Sleep(_modelConfiguration.RetryWaitTimeSeconds * 1000);
PerformProcessing();
}
}
finally
{
try
{
_modelConfiguration = null;
_messageLogger = null;
if (server != null)
{
server.Disconnect();
}
}
catch { }
}
}
/// <summary>
/// Defragment all partitions tables in a tabular model based on configuration
/// </summary>
/// <param name="modelConfiguration">Configuration info for the model</param>
/// <param name="messageLogger">Pointer to logging method</param>
public static void DefragPartitionedTables(ModelConfiguration modelConfiguration, LogMessageDelegate messageLogger)
{
_modelConfiguration = modelConfiguration;
_messageLogger = messageLogger;
Server server = new Server();
try
{
Database database;
Connect(server, out database);
Console.ForegroundColor = ConsoleColor.White;
LogMessage($"Start: {DateTime.Now.ToString("hh:mm:ss tt")}", MessageType.Informational, false);
LogMessage($"Server: {_modelConfiguration.AnalysisServicesServer}", MessageType.Informational, false);
LogMessage($"Database: {_modelConfiguration.AnalysisServicesDatabase}", MessageType.Informational, false);
Console.ForegroundColor = ConsoleColor.Yellow;
LogMessage("", MessageType.Informational, false);
LogMessage($"Defrag partitioned tables in database {_modelConfiguration.AnalysisServicesDatabase}", MessageType.Informational, false);
LogMessage(new String('-', _modelConfiguration.AnalysisServicesDatabase.Length + 38), MessageType.Informational, false);
LogMessage("", MessageType.Informational, false);
LogMessage("=>Actions & progress:", MessageType.Informational, false);
foreach (TableConfiguration tableConfiguration in _modelConfiguration.TableConfigurations)
{
//Only interested in partitoned tables
if (tableConfiguration.PartitioningConfigurations.Count > 0)
{
Table table = database.Model.Tables.Find(tableConfiguration.AnalysisServicesTable);
if (table == null)
{
throw new Microsoft.AnalysisServices.ConnectionException($"Could not connect to table {tableConfiguration.AnalysisServicesTable}.");
}
LogMessage($"Defrag table {tableConfiguration.AnalysisServicesTable} ...", MessageType.Informational, true);
table.RequestRefresh(RefreshType.Defragment);
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 { }
}
}
/// <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 { }
}
}