/// <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)")); } }