/// <summary>
/// Check the ordinal position of Link Keys against their business key definitions.
/// </summary>
/// <param name="validationObject"></param>
/// <param name="inputDataTable"></param>
/// <param name="physicalModelDataTable"></param>
/// <param name="evaluationMode"></param>
/// <returns></returns>
internal static Dictionary <string, bool> ValidateLinkKeyOrder(Tuple <string, string, string, string> validationObject, DataTable inputDataTable, DataTable physicalModelDataTable, EnvironmentModes evaluationMode)
{
// First, the Hubs need to be identified using the Business Key information. This, for the Link, is the combination of Business keys separated by a comma.
// Every business key needs to be iterated over to query the individual Hub information
List <string> hubBusinessKeys = validationObject.Item3.Split(',').ToList();
// Now iterate over each Hub, as identified by the business key.
// Maintain the ordinal position of the business key
var hubKeyOrder = new Dictionary <int, string>();
int businessKeyOrder = 0;
foreach (string hubBusinessKey in hubBusinessKeys)
{
// Determine the order in the business key array
businessKeyOrder++;
// Query the Hub information
DataRow[] selectionRows = inputDataTable.Select(TableMappingMetadataColumns.SourceTable + " = '" + validationObject.Item1 + "' AND " + TableMappingMetadataColumns.BusinessKeyDefinition + " = '" + hubBusinessKey.Replace("'", "''").Trim() + "' AND " + TableMappingMetadataColumns.TargetTable + " NOT LIKE '" + FormBase.TeamConfiguration.SatTablePrefixValue + "_%'");
try
{
// Derive the Hub surrogate key name, as this can be compared against the Link
string hubTableName = selectionRows[0][TableMappingMetadataColumns.TargetTable.ToString()].ToString();
string hubTableConnectionId = selectionRows[0][TableMappingMetadataColumns.TargetConnection.ToString()].ToString();
var hubTableConnection = GetTeamConnectionByConnectionId(hubTableConnectionId);
string hubSurrogateKeyName = MetadataHandling.GetSurrogateKey(hubTableName, hubTableConnection, FormBase.TeamConfiguration);
// Add to the dictionary that contains the keys in order.
hubKeyOrder.Add(businessKeyOrder, hubSurrogateKeyName);
}
catch
{
//
}
}
// Derive the Hub surrogate key name, as this can be compared against the Link
var linkKeyOrder = new Dictionary <int, string>();
if (evaluationMode == EnvironmentModes.PhysicalMode)
{
var connTarget = new SqlConnection {
ConnectionString = validationObject.Item4
};
var connDatabase = connTarget.Database;
var sqlStatementForLink = new StringBuilder();
sqlStatementForLink.AppendLine("SELECT");
sqlStatementForLink.AppendLine(" OBJECT_NAME([object_id]) AS [TABLE_NAME]");
sqlStatementForLink.AppendLine(" ,[name] AS [COLUMN_NAME]");
sqlStatementForLink.AppendLine(" ,[column_id] AS [ORDINAL_POSITION]");
sqlStatementForLink.AppendLine(" ,ROW_NUMBER() OVER(PARTITION BY object_id ORDER BY column_id) AS [HUB_KEY_POSITION]");
sqlStatementForLink.AppendLine("FROM [" + connDatabase + "].sys.columns");
sqlStatementForLink.AppendLine(" WHERE OBJECT_NAME([object_id]) LIKE '" + FormBase.TeamConfiguration.LinkTablePrefixValue + "_%'");
sqlStatementForLink.AppendLine("AND column_id > 4");
sqlStatementForLink.AppendLine("AND OBJECT_NAME([object_id]) = '" + validationObject.Item2 + "'");
// The hubKeyOrder contains the order of the keys in the Hub, now we need to do the same for the (target) Link so we can compare.
connTarget.Open();
var linkList = Utility.GetDataTable(ref connTarget, sqlStatementForLink.ToString());
connTarget.Close();
foreach (DataRow row in linkList.Rows)
{
var linkHubSurrogateKeyName = row["COLUMN_NAME"].ToString();
int linkHubSurrogateKeyPosition = Convert.ToInt32(row["HUB_KEY_POSITION"]);
if (linkHubSurrogateKeyName.Contains(FormBase.TeamConfiguration.DwhKeyIdentifier)
) // Exclude degenerate attributes from the order
{
linkKeyOrder.Add(linkHubSurrogateKeyPosition, linkHubSurrogateKeyName);
}
}
}
else // virtual
{
int linkHubSurrogateKeyPosition = 1;
var workingTable = new DataTable();
try
{
// Select only the business keys in a link table.
// Excluding all non-business key attributes
workingTable = physicalModelDataTable
.Select($"{PhysicalModelMappingMetadataColumns.TableName} LIKE '%{FormBase.TeamConfiguration.LinkTablePrefixValue}%' " +
$"AND {PhysicalModelMappingMetadataColumns.TableName} = '{validationObject.Item2}' " +
$"AND {PhysicalModelMappingMetadataColumns.OrdinalPosition} > 4", $"{PhysicalModelMappingMetadataColumns.OrdinalPosition} ASC").CopyToDataTable();
}
catch (Exception ex)
{
GlobalParameters.TeamEventLog.Add(Event.CreateNewEvent(EventTypes.Error, $"An error occurred during validation of the metadata. The errors is {ex}."));
}
if (workingTable.Rows.Count > 0)
{
foreach (DataRow row in workingTable.Rows)
{
var linkHubSurrogateKeyName = row[PhysicalModelMappingMetadataColumns.ColumnName.ToString()].ToString();
if (linkHubSurrogateKeyName.Contains(FormBase.TeamConfiguration.DwhKeyIdentifier)
) // Exclude degenerate attributes from the order
{
linkKeyOrder.Add(linkHubSurrogateKeyPosition, linkHubSurrogateKeyName);
linkHubSurrogateKeyPosition++;
}
}
}
}
// Check for duplicates, which indicate a Same-As Link or Hierarchical Link
var duplicateValues = hubKeyOrder.Where(i => hubKeyOrder.Any(t => t.Key != i.Key && t.Value == i.Value)).ToDictionary(i => i.Key, i => i.Value);
// Run the comparison, test for equality.
// Only if there are no duplicates, as this indicates the SAL / HLINK which is not currently supported
bool equal = false;
if (duplicateValues.Count == 0)
{
if (hubKeyOrder.Count == linkKeyOrder.Count) // Require equal count.
{
equal = true;
foreach (var pair in hubKeyOrder)
{
string value;
if (linkKeyOrder.TryGetValue(pair.Key, out value))
{
// Require value be equal.
if (value != pair.Value)
{
equal = false;
break;
}
}
else
{
// Require key be present.
equal = false;
break;
}
}
}
}
else
{
equal = true;
}
// return the result of the test;
Dictionary <string, bool> result = new Dictionary <string, bool>();
result.Add(validationObject.Item2, equal);
return(result);
}
/// <summary>
/// Load the information from the TEAM configuration file into memory.
/// </summary>
public void LoadTeamConfigurationFile(string fileName)
{
// Load the rest of the (TEAM) configurations, from wherever they may be according to the VDW settings (the TEAM configuration file).
ConfigurationSettingsEventLog.Add(Event.CreateNewEvent(EventTypes.Information, $"Retrieving TEAM configuration details from {fileName}."));
if (File.Exists(fileName))
{
var configList = FileHandling.LoadConfigurationFile(fileName);
if (configList.Count == 0)
{
ConfigurationSettingsEventLog.Add(Event.CreateNewEvent(EventTypes.Warning, $"No lines detected in file {fileName}. Is it empty?"));
}
string[] configurationArray = new[]
{
"StagingAreaPrefix",
"PersistentStagingAreaPrefix",
"PresentationLayerLabels",
"TransformationLabels",
"HubTablePrefix",
"SatTablePrefix",
"LinkTablePrefix",
"LinkSatTablePrefix",
"TableNamingLocation",
"KeyNamingLocation",
"KeyIdentifier",
"PSAKeyLocation",
"SchemaName",
"RowID",
"EventDateTimeStamp",
"LoadDateTimeStamp",
"ExpiryDateTimeStamp",
"ChangeDataIndicator",
"RecordSourceAttribute",
"ETLProcessID",
"ETLUpdateProcessID",
"LogicalDeleteAttribute",
"RecordChecksum",
"CurrentRecordAttribute",
"AlternativeRecordSource",
"AlternativeHubLDTS",
"AlternativeRecordSourceFunction",
"AlternativeHubLDTSFunction",
"AlternativeSatelliteLDTSFunction",
"AlternativeSatelliteLDTS",
"EnvironmentMode"
};
foreach (string configuration in configurationArray)
{
if (configList.ContainsKey(configuration))
{
switch (configuration)
{
case "EnvironmentMode":
Enum.TryParse(configList[configuration], out EnvironmentModes localEnvironmentMode);
EnvironmentMode = localEnvironmentMode;
break;
case "StagingAreaPrefix":
StgTablePrefixValue = configList[configuration];
break;
case "PersistentStagingAreaPrefix":
PsaTablePrefixValue = configList[configuration];
break;
case "PresentationLayerLabels":
PresentationLayerLabels = configList[configuration];
break;
case "TransformationLabels":
TransformationLabels = configList[configuration];
break;
case "HubTablePrefix":
HubTablePrefixValue = configList[configuration];
break;
case "SatTablePrefix":
SatTablePrefixValue = configList[configuration];
break;
case "LinkTablePrefix":
LinkTablePrefixValue = configList[configuration];
break;
case "TableNamingLocation":
TableNamingLocation = configList[configuration];
break;
case "LinkSatTablePrefix":
LsatTablePrefixValue = configList[configuration];
break;
case "KeyNamingLocation":
KeyNamingLocation = configList[configuration];
break;
case "KeyIdentifier":
DwhKeyIdentifier = configList[configuration];
break;
case "PSAKeyLocation":
PsaKeyLocation = configList[configuration];
break;
case "SchemaName":
SchemaName = configList[configuration];
break;
case "RowID":
RowIdAttribute = configList[configuration];
break;
case "EventDateTimeStamp":
EventDateTimeAttribute = configList[configuration];
break;
case "LoadDateTimeStamp":
LoadDateTimeAttribute = configList[configuration];
break;
case "ExpiryDateTimeStamp":
ExpiryDateTimeAttribute = configList[configuration];
break;
case "ChangeDataIndicator":
ChangeDataCaptureAttribute = configList[configuration];
break;
case "RecordSourceAttribute":
RecordSourceAttribute = configList[configuration];
break;
case "ETLProcessID":
EtlProcessAttribute = configList[configuration];
break;
case "ETLUpdateProcessID":
EtlProcessUpdateAttribute = configList[configuration];
break;
case "LogicalDeleteAttribute":
LogicalDeleteAttribute = configList[configuration];
break;
case "RecordChecksum":
RecordChecksumAttribute = configList[configuration];
break;
case "CurrentRecordAttribute":
CurrentRowAttribute = configList[configuration];
break;
case "AlternativeRecordSource":
AlternativeRecordSourceAttribute = configList[configuration];
break;
case "AlternativeHubLDTS":
AlternativeLoadDateTimeAttribute = configList[configuration];
break;
case "AlternativeRecordSourceFunction":
EnableAlternativeRecordSourceAttribute = configList[configuration];
break;
case "AlternativeHubLDTSFunction":
EnableAlternativeLoadDateTimeAttribute = configList[configuration];
break;
case "AlternativeSatelliteLDTSFunction":
EnableAlternativeSatelliteLoadDateTimeAttribute = configList[configuration];
break;
case "AlternativeSatelliteLDTS":
AlternativeSatelliteLoadDateTimeAttribute = configList[configuration];
break;
default:
ConfigurationSettingsEventLog.Add(Event.CreateNewEvent(EventTypes.Error, $"Incorrect configuration '{configuration}' encountered."));
break;
}
ConfigurationSettingsEventLog.Add(Event.CreateNewEvent(EventTypes.Information, $"The entry '{configuration}' was loaded from the configuration file with value '{configList[configuration]}'."));
}
else
{
ConfigurationSettingsEventLog.Add(Event.CreateNewEvent(EventTypes.Error, $"* The entry '{configuration}' was not found in the configuration file. Please make sure an entry exists ({configuration}|<value>)."));
break;
}
}
ConfigurationSettingsEventLog.Add(Event.CreateNewEvent(EventTypes.Information, $"TEAM configuration updated in memory" + $"."));
var lookUpValue = "MetadataConnectionId";
if (configList.TryGetValue(lookUpValue, out var value))
{
if (value != null)
{
if (ConnectionDictionary.Count == 0)
{
ConfigurationSettingsEventLog.Add(Event.CreateNewEvent(EventTypes.Warning, $"The connection dictionary is empty, so the value for the metadata connection cannot be set. Is the path to the connections file correct?"));
MetadataConnection = null;
}
else
{
MetadataConnection = ConnectionDictionary[configList["MetadataConnectionId"]];
}
}
else
{
MetadataConnection = null;
}
}
else
{
ConfigurationSettingsEventLog.Add(Event.CreateNewEvent(EventTypes.Warning, $"The key/value pair {lookUpValue} was not found in the configuration file."));
}
}
else // No file found, report error.
{
ConfigurationSettingsEventLog.Add(Event.CreateNewEvent(EventTypes.Error, $"No valid TEAM configuration file was found. Please select a valid TEAM configuration file (settings tab => TEAM configuration file)"));
}
}
