internal static void SaveJsonInterfaceHubLinkXref()
{
const string fileName = "interfaceHubLinkXref";
// Get the information from the view
var sqlStatement = new StringBuilder();
sqlStatement.AppendLine(@"
SELECT
[SOURCE_SCHEMA_NAME]
,[SOURCE_NAME]
,[LINK_SCHEMA_NAME]
,[LINK_NAME]
,[HUB_SCHEMA_NAME]
,[HUB_NAME]
,[HUB_SURROGATE_KEY]
,[HUB_TARGET_KEY_NAME_IN_LINK]
,[HUB_SOURCE_BUSINESS_KEY_DEFINITION]
,[HUB_TARGET_BUSINESS_KEY_DEFINITION]
,[HUB_ORDER]
FROM [interface].[INTERFACE_HUB_LINK_XREF]
");
var conn = new SqlConnection {
ConnectionString = FormBase.ConfigurationSettings.ConnectionStringOmd
};
var inputDataTable = FormBase.GetDataTable(ref conn, sqlStatement.ToString());
// Make sure the output is sorted to persist in JSON
inputDataTable.DefaultView.Sort = "[LINK_NAME] ASC, [SOURCE_NAME] ASC, [HUB_NAME] ASC";
inputDataTable.TableName = fileName;
JArray outputFileArray = new JArray();
foreach (DataRow singleRow in inputDataTable.DefaultView.ToTable().Rows)
{
JObject individualRow = JObject.FromObject(new
{
sourceSchemaName = singleRow[0].ToString(),
sourceName = singleRow[1].ToString(),
linkSchemaName = singleRow[2].ToString(),
linkName = singleRow[3].ToString(),
hubSchemaName = singleRow[4].ToString(),
hubName = singleRow[5].ToString(),
hubSurrogateKey = singleRow[6].ToString(),
hubSurrogateKeyInLink = singleRow[7].ToString(),
hubSourcebusinessKeyDefinition = singleRow[8].ToString(),
hubTargetbusinessKeyDefinition = singleRow[9].ToString(),
hubOrder = singleRow[10].ToString()
});
outputFileArray.Add(individualRow);
}
string json = JsonConvert.SerializeObject(outputFileArray, Formatting.Indented);
File.WriteAllText(FormBase.GlobalParameters.OutputPath + fileName + FormBase.GlobalParameters.JsonExtension, json);
}
internal static void SaveJsonInterfaceSourcePsaXref()
{
const string fileName = "interfaceSourcePsaXref";
// Get the information from the view
var sqlStatement = new StringBuilder();
sqlStatement.AppendLine(@"
SELECT
[SOURCE_SCHEMA_NAME]
,[SOURCE_NAME]
,[TARGET_SCHEMA_NAME]
,[TARGET_NAME]
,[SOURCE_BUSINESS_KEY_DEFINITION]
,[TARGET_BUSINESS_KEY_DEFINITION]
,[TARGET_TYPE]
,[SURROGATE_KEY]
,[FILTER_CRITERIA]
,[LOAD_VECTOR]
FROM [interface].[INTERFACE_SOURCE_PERSISTENT_STAGING_XREF]
");
var conn = new SqlConnection {
ConnectionString = FormBase.ConfigurationSettings.ConnectionStringOmd
};
var inputDataTable = FormBase.GetDataTable(ref conn, sqlStatement.ToString());
// Make sure the output is sorted to persist in JSON
inputDataTable.DefaultView.Sort = "[SOURCE_NAME] ASC, [TARGET_NAME] ASC, [TARGET_BUSINESS_KEY_DEFINITION] ASC";
inputDataTable.TableName = fileName;
JArray outputFileArray = new JArray();
foreach (DataRow singleRow in inputDataTable.DefaultView.ToTable().Rows)
{
JObject individualRow = JObject.FromObject(new
{
sourceSchemaName = singleRow[0].ToString(),
sourceName = singleRow[1].ToString(),
targetSchemaName = singleRow[2].ToString(),
targetName = singleRow[3].ToString(),
sourceBusinessKeyDefinition = singleRow[4].ToString(),
targetBusinessKeyDefinition = singleRow[5].ToString(),
targetType = singleRow[6].ToString(),
surrogateKey = singleRow[7].ToString(),
filterCriteria = singleRow[8].ToString(),
loadVector = singleRow[9].ToString()
});
outputFileArray.Add(individualRow);
}
string json = JsonConvert.SerializeObject(outputFileArray, Formatting.Indented);
File.WriteAllText(FormBase.GlobalParameters.OutputPath + fileName + FormBase.GlobalParameters.JsonExtension, json);
}
internal static void SaveJsonInterfacePhysicalModel()
{
const string fileName = "interfacePhysicalModel";
// Get the information from the view
var sqlStatement = new StringBuilder();
sqlStatement.AppendLine(@"
SELECT
[DATABASE_NAME]
,[SCHEMA_NAME]
,[TABLE_NAME]
,[COLUMN_NAME]
,[DATA_TYPE]
,[CHARACTER_MAXIMUM_LENGTH]
,[NUMERIC_PRECISION]
,[ORDINAL_POSITION]
,[PRIMARY_KEY_INDICATOR]
FROM [interface].[INTERFACE_PHYSICAL_MODEL]
");
var conn = new SqlConnection {
ConnectionString = FormBase.ConfigurationSettings.ConnectionStringOmd
};
var inputDataTable = FormBase.GetDataTable(ref conn, sqlStatement.ToString());
// Make sure the output is sorted to persist in JSON
inputDataTable.DefaultView.Sort = "[DATABASE_NAME] ASC, [SCHEMA_NAME] ASC, [TABLE_NAME] ASC, [ORDINAL_POSITION] ASC";
inputDataTable.TableName = fileName;
JArray outputFileArray = new JArray();
foreach (DataRow singleRow in inputDataTable.DefaultView.ToTable().Rows)
{
JObject individualRow = JObject.FromObject(new
{
databaseName = singleRow[0].ToString(),
schemaName = singleRow[1].ToString(),
tableName = singleRow[2].ToString(),
columnName = singleRow[3].ToString(),
characterMaximumLength = singleRow[4].ToString(),
numericPrecision = singleRow[5].ToString(),
ordinalPosition = singleRow[6].ToString(),
primaryKeyIndicator = singleRow[7].ToString()
});
outputFileArray.Add(individualRow);
}
string json = JsonConvert.SerializeObject(outputFileArray, Formatting.Indented);
File.WriteAllText(FormBase.GlobalParameters.OutputPath + fileName + FormBase.GlobalParameters.JsonExtension, json);
}
/// <summary>
/// Saves the Business Key Component data set to disk as a JSON file in the default configuration directory
/// </summary>
internal static void SaveJsonInterfaceBusinessKeyComponent()
{
const string fileName = "interfaceBusinessKeyComponent";
// Get the information from the view
var sqlStatement = new StringBuilder();
sqlStatement.AppendLine(@"
SELECT
[SOURCE_SCHEMA_NAME]
,[SOURCE_NAME]
,[TARGET_SCHEMA_NAME]
,[TARGET_NAME]
,[BUSINESS_KEY_DEFINITION]
,[BUSINESS_KEY_COMPONENT_ID]
,[BUSINESS_KEY_COMPONENT_ORDER]
,[BUSINESS_KEY_COMPONENT_VALUE]
FROM [interface].[INTERFACE_BUSINESS_KEY_COMPONENT]
");
var conn = new SqlConnection {
ConnectionString = FormBase.ConfigurationSettings.ConnectionStringOmd
};
var inputDataTable = FormBase.GetDataTable(ref conn, sqlStatement.ToString());
// Make sure the output is sorted to persist in JSON
inputDataTable.DefaultView.Sort = "[SOURCE_NAME] ASC, [TARGET_NAME] ASC, [BUSINESS_KEY_COMPONENT_ID] ASC, [BUSINESS_KEY_COMPONENT_ORDER] ASC";
inputDataTable.TableName = fileName;
JArray outputFileArray = new JArray();
foreach (DataRow singleRow in inputDataTable.DefaultView.ToTable().Rows)
{
JObject individualRow = JObject.FromObject(new
{
sourceSchemaName = singleRow[0].ToString(),
sourceName = singleRow[1].ToString(),
targetSchemaName = singleRow[2].ToString(),
targetName = singleRow[3].ToString(),
businessKeyDefinition = singleRow[4].ToString(),
businessKeyComponentId = singleRow[5].ToString(),
businessKeyComponentOrder = singleRow[6].ToString(),
businessKeyComponentValue = singleRow[7].ToString()
});
outputFileArray.Add(individualRow);
}
string json = JsonConvert.SerializeObject(outputFileArray, Formatting.Indented);
File.WriteAllText(FormBase.GlobalParameters.OutputPath + fileName + FormBase.GlobalParameters.JsonExtension, json);
}
/// <summary>
/// Returns a list of Business Key attributes as they are defined in the target Link table.
/// </summary>
/// <param name="schemaName"></param>
/// <param name="tableName"></param>
/// <param name="versionId"></param>
/// <param name="queryMode"></param>
/// <returns></returns>
public static List <string> GetLinkTargetBusinessKeyList(string schemaName, string tableName, int versionId)
{
// Obtain the business key as it is known in the target Hub table. Can be multiple due to composite keys.
var conn = new SqlConnection();
conn = new SqlConnection {
ConnectionString = FormBase.ConfigurationSettings.ConnectionStringOmd
};
try
{
conn.Open();
}
catch (Exception)
{
}
// Make sure brackets are removed
tableName = tableName.Replace("[", "").Replace("]", "");
var sqlStatementForBusinessKeys = new StringBuilder();
sqlStatementForBusinessKeys.AppendLine("SELECT");
sqlStatementForBusinessKeys.AppendLine(" xref.[HUB_NAME]");
sqlStatementForBusinessKeys.AppendLine(" ,xref.[LINK_NAME]");
sqlStatementForBusinessKeys.AppendLine(" ,hub.[BUSINESS_KEY]");
sqlStatementForBusinessKeys.AppendLine("FROM[dbo].[MD_HUB_LINK_XREF] xref");
sqlStatementForBusinessKeys.AppendLine("JOIN[dbo].[MD_HUB] hub ON xref.HUB_NAME = hub.HUB_NAME");
sqlStatementForBusinessKeys.AppendLine("WHERE [LINK_NAME] = '" + tableName + "'");
sqlStatementForBusinessKeys.AppendLine("ORDER BY [HUB_ORDER]");
var keyList = FormBase.GetDataTable(ref conn, sqlStatementForBusinessKeys.ToString());
if (keyList == null)
{
//SetTextDebug("An error has occurred defining the Hub Business Key in the model for " + hubTableName + ". The Business Key was not found when querying the underlying metadata. This can be either that the attribute is missing in the metadata or in the table (depending if versioning is used). If the 'ignore versioning' option is checked, then the metadata will be retrieved directly from the data dictionary. Otherwise the metadata needs to be available in the repository (manage model metadata).");
}
var businessKeyList = new List <string>();
foreach (DataRow row in keyList.Rows)
{
//if (!businessKeyList.Contains((string)row["BUSINESS_KEY"]))
// {
businessKeyList.Add((string)row["BUSINESS_KEY"]);
// }
}
return(businessKeyList);
}
internal static void SaveJsonInterfaceSourceSatelliteAttributeXref()
{
const string fileName = "interfaceSourceSatelliteAttributeXref";
// Get the information from the view
var sqlStatement = new StringBuilder();
sqlStatement.AppendLine(@"
SELECT
[SOURCE_SCHEMA_NAME]
,[SOURCE_NAME]
,[TARGET_SCHEMA_NAME]
,[TARGET_NAME]
,[SOURCE_ATTRIBUTE_NAME]
,[TARGET_ATTRIBUTE_NAME]
,[MULTI_ACTIVE_KEY_INDICATOR]
FROM [interface].[INTERFACE_SOURCE_SATELLITE_ATTRIBUTE_XREF]
");
var conn = new SqlConnection {
ConnectionString = FormBase.ConfigurationSettings.ConnectionStringOmd
};
var inputDataTable = FormBase.GetDataTable(ref conn, sqlStatement.ToString());
// Make sure the output is sorted to persist in JSON
inputDataTable.DefaultView.Sort = "[SOURCE_NAME] ASC, [TARGET_NAME] ASC, [SOURCE_ATTRIBUTE_NAME] ASC, [TARGET_ATTRIBUTE_NAME] ASC";
inputDataTable.TableName = fileName;
JArray outputFileArray = new JArray();
foreach (DataRow singleRow in inputDataTable.DefaultView.ToTable().Rows)
{
JObject individualRow = JObject.FromObject(new
{
sourceSchemaName = singleRow[0].ToString(),
sourceName = singleRow[1].ToString(),
targetSchemaName = singleRow[2].ToString(),
targetName = singleRow[3].ToString(),
sourceAttributeName = singleRow[4].ToString(),
targetAttributeName = singleRow[5].ToString(),
multiActiveKeyIndicator = singleRow[6].ToString()
});
outputFileArray.Add(individualRow);
}
string json = JsonConvert.SerializeObject(outputFileArray, Formatting.Indented);
File.WriteAllText(FormBase.GlobalParameters.OutputPath + fileName + FormBase.GlobalParameters.JsonExtension, json);
}
/// <summary>
/// Saves the Business Key Component Part data set to disk as a JSON file in the default configuration directory
/// </summary>
internal static void SaveJsonInterfaceDrivingKey()
{
const string fileName = "interfaceDrivingKey";
// Get the information from the view
var sqlStatement = new StringBuilder();
sqlStatement.AppendLine(@"
SELECT
[SATELLITE_NAME]
,[HUB_NAME]
FROM [interface].[INTERFACE_DRIVING_KEY]
");
var conn = new SqlConnection {
ConnectionString = FormBase.ConfigurationSettings.ConnectionStringOmd
};
var inputDataTable = FormBase.GetDataTable(ref conn, sqlStatement.ToString());
// Make sure the output is sorted to persist in JSON
inputDataTable.DefaultView.Sort = "[SATELLITE_NAME] ASC, [HUB_NAME] ASC";
inputDataTable.TableName = fileName;
JArray outputFileArray = new JArray();
foreach (DataRow singleRow in inputDataTable.DefaultView.ToTable().Rows)
{
JObject individualRow = JObject.FromObject(new
{
satelliteName = singleRow[0].ToString(),
hubName = singleRow[1].ToString()
});
outputFileArray.Add(individualRow);
}
string json = JsonConvert.SerializeObject(outputFileArray, Formatting.Indented);
File.WriteAllText(FormBase.GlobalParameters.OutputPath + fileName + FormBase.GlobalParameters.JsonExtension, json);
}
public static List <DataObject> SetLineageRelatedDataObjectList(DataTable dataObjectMappingDataTable, string targetDataObjectName, JsonExportSetting jsonExportSetting)
{
List <DataObject> dataObjectList = new List <DataObject>();
if (jsonExportSetting.AddUpstreamDataObjectsAsRelatedDataObject == "True")
{
// Find the corresponding row in the Data Object Mapping grid
DataRow[] DataObjectMappings = dataObjectMappingDataTable.Select("[" + TableMappingMetadataColumns.SourceTable + "] = '" + targetDataObjectName + "'");
foreach (DataRow DataObjectMapping in DataObjectMappings)
{
var localDataObjectName = DataObjectMapping[TableMappingMetadataColumns.TargetTable.ToString()].ToString();
var localDataObjectConnectionInternalId = DataObjectMapping[TableMappingMetadataColumns.TargetConnection.ToString()].ToString();
TeamConnection localConnection = FormBase.GetTeamConnectionByConnectionId(localDataObjectConnectionInternalId);
// Set the name and further settings.
dataObjectList.Add(CreateDataObject(localDataObjectName, localConnection, jsonExportSetting));
}
}
return(dataObjectList);
}
internal static Dictionary <string, bool> ValidateLinkKeyOrder(Tuple <string, string, string> validationObject, string connectionString, int versionId, DataTable inputDataTable, DataTable physicalModelDataTable, string 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("SOURCE_TABLE = '" + validationObject.Item1 + "' AND [BUSINESS_KEY_ATTRIBUTE] = '" + hubBusinessKey.Replace("'", "''").Trim() + "' AND [TARGET_TABLE] NOT LIKE '" + FormBase.ConfigurationSettings.SatTablePrefixValue + "_%'");
// Derive the Hub surrogate key name, as this can be compared against the Link
string hubSurrogateKeyName;
foreach (DataRow row in selectionRows)
{
string hubTableName = row["TARGET_TABLE"].ToString();
hubSurrogateKeyName = hubTableName.Replace(FormBase.ConfigurationSettings.HubTablePrefixValue + '_', "") + "_" + FormBase.ConfigurationSettings.DwhKeyIdentifier;
hubKeyOrder.Add(businessKeyOrder, hubSurrogateKeyName);
}
//hubKeyOrder.Add(businessKeyOrder, hubSurrogateKeyName);
}
// Derive the Hub surrogate key name, as this can be compared against the Link
var linkKeyOrder = new Dictionary <int, string>();
if (evaluationMode == "physical")
{
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 [" + FormBase.ConfigurationSettings.IntegrationDatabaseName + "].sys.columns");
sqlStatementForLink.AppendLine(" WHERE OBJECT_NAME([object_id]) LIKE '" + FormBase.ConfigurationSettings.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.
var connTarget = new SqlConnection {
ConnectionString = FormBase.ConfigurationSettings.ConnectionStringInt
};
connTarget.Open();
var linkList = FormBase.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.ConfigurationSettings.DwhKeyIdentifier)
) // Exclude degenerate attributes from the order
{
linkKeyOrder.Add(linkHubSurrogateKeyPosition, linkHubSurrogateKeyName);
}
}
}
else // virtual
{
int linkHubSurrogateKeyPosition = 1;
var workingTable = new DataTable();
try
{
workingTable = physicalModelDataTable
.Select(
"TABLE_NAME LIKE '" + FormBase.ConfigurationSettings.LinkTablePrefixValue +
"_%' AND TABLE_NAME = '" + validationObject.Item2 + "' AND ORDINAL_POSITION > 4",
"ORDINAL_POSITION ASC").CopyToDataTable();
}
catch
{
//
}
if (workingTable.Rows.Count > 0)
{
foreach (DataRow row in workingTable.Rows)
{
var linkHubSurrogateKeyName = row["COLUMN_NAME"].ToString();
if (linkHubSurrogateKeyName.Contains(FormBase.ConfigurationSettings.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>
/// Returns a list of Business Key attributes as they are defined in the target Hub table.
/// </summary>
/// <param name="schemaName"></param>
/// <param name="tableName"></param>
/// <param name="versionId"></param>
/// <param name="queryMode"></param>
/// <returns></returns>
public static List <string> GetHubTargetBusinessKeyList(string schemaName, string tableName, int versionId, string queryMode)
{
// Obtain the business key as it is known in the target Hub table. Can be multiple due to composite keys.
var conn = new SqlConnection();
conn = queryMode == "physical" ? new SqlConnection {
ConnectionString = FormBase.ConfigurationSettings.ConnectionStringInt
} : new SqlConnection {
ConnectionString = FormBase.ConfigurationSettings.ConnectionStringOmd
};
try
{
conn.Open();
}
catch (Exception)
{
// SetTextDebug("An error has occurred defining the Hub Business Key in the model due to connectivity issues (connection string " + conn.ConnectionString + "). The associated message is " + exception.Message);
}
var sqlStatementForBusinessKeys = new StringBuilder();
var keyText = FormBase.ConfigurationSettings.DwhKeyIdentifier;
var localkeyLength = keyText.Length;
var localkeySubstring = localkeyLength + 1;
// Make sure brackets are removed
schemaName = schemaName.Replace("[", "").Replace("]", "");
tableName = tableName.Replace("[", "").Replace("]", "");
if (queryMode == "physical")
{
// Make sure the live database is hit when the checkbox is ticked
sqlStatementForBusinessKeys.AppendLine("SELECT COLUMN_NAME");
sqlStatementForBusinessKeys.AppendLine("FROM INFORMATION_SCHEMA.COLUMNS");
sqlStatementForBusinessKeys.AppendLine("WHERE SUBSTRING(COLUMN_NAME,LEN(COLUMN_NAME)-" + localkeyLength + "," + localkeySubstring + ")!='_" + FormBase.ConfigurationSettings.DwhKeyIdentifier + "'");
sqlStatementForBusinessKeys.AppendLine("AND TABLE_SCHEMA = '" + schemaName + "'");
sqlStatementForBusinessKeys.AppendLine(" AND TABLE_NAME= '" + tableName + "'");
sqlStatementForBusinessKeys.AppendLine(" AND COLUMN_NAME NOT IN ('" + FormBase.ConfigurationSettings.RecordSourceAttribute + "','" + FormBase.ConfigurationSettings.AlternativeRecordSourceAttribute + "','" + FormBase.ConfigurationSettings.AlternativeLoadDateTimeAttribute + "','" +
FormBase.ConfigurationSettings.AlternativeSatelliteLoadDateTimeAttribute + "','" + FormBase.ConfigurationSettings.EtlProcessAttribute + "','" + FormBase.ConfigurationSettings.LoadDateTimeAttribute + "')");
}
else
{
//Ignore version is not checked, so versioning is used - meaning the business key metadata is sourced from the version history metadata.
sqlStatementForBusinessKeys.AppendLine("SELECT COLUMN_NAME");
sqlStatementForBusinessKeys.AppendLine("FROM MD_VERSION_ATTRIBUTE");
sqlStatementForBusinessKeys.AppendLine("WHERE SUBSTRING(COLUMN_NAME,LEN(COLUMN_NAME)-" + localkeyLength + "," + localkeySubstring + ")!='_" + FormBase.ConfigurationSettings.DwhKeyIdentifier + "'");
sqlStatementForBusinessKeys.AppendLine(" AND TABLE_NAME= '" + tableName + "'");
sqlStatementForBusinessKeys.AppendLine(" AND SCHEMA_NAME= '" + schemaName + "'");
sqlStatementForBusinessKeys.AppendLine(" AND COLUMN_NAME NOT IN ('" + FormBase.ConfigurationSettings.RecordSourceAttribute + "','" + FormBase.ConfigurationSettings.AlternativeRecordSourceAttribute + "','" + FormBase.ConfigurationSettings.AlternativeLoadDateTimeAttribute + "','" + FormBase.ConfigurationSettings.AlternativeSatelliteLoadDateTimeAttribute + "','" +
FormBase.ConfigurationSettings.EtlProcessAttribute + "','" + FormBase.ConfigurationSettings.LoadDateTimeAttribute + "')");
sqlStatementForBusinessKeys.AppendLine(" AND VERSION_ID = " + versionId + "");
}
var keyList = FormBase.GetDataTable(ref conn, sqlStatementForBusinessKeys.ToString());
if (keyList == null)
{
//SetTextDebug("An error has occurred defining the Hub Business Key in the model for " + hubTableName + ". The Business Key was not found when querying the underlying metadata. This can be either that the attribute is missing in the metadata or in the table (depending if versioning is used). If the 'ignore versioning' option is checked, then the metadata will be retrieved directly from the data dictionary. Otherwise the metadata needs to be available in the repository (manage model metadata).");
}
var businessKeyList = new List <string>();
foreach (DataRow row in keyList.Rows)
{
if (!businessKeyList.Contains((string)row["COLUMN_NAME"]))
{
businessKeyList.Add((string)row["COLUMN_NAME"]);
}
}
return(businessKeyList);
}
private void ColourGridView()
{
var counter = 0;
var presentationLayerLabelArray = Utility.SplitLabelIntoArray(FormBase.TeamConfiguration.PresentationLayerLabels);
var transformationLabelArray = Utility.SplitLabelIntoArray(FormBase.TeamConfiguration.TransformationLabels);
foreach (DataGridViewRow row in Rows)
{
if (!row.IsNewRow)
{
// Target info
string targetDataObjectName =
row.Cells[(int)TableMappingMetadataColumns.TargetTable].Value.ToString();
var targetConnectionId =
row.Cells[(int)TableMappingMetadataColumns.TargetConnection].Value.ToString();
TeamConnection targetConnection = FormBase.GetTeamConnectionByConnectionId(targetConnectionId);
KeyValuePair <string, string> targetDataObjectFullyQualifiedKeyValuePair = MetadataHandling
.GetFullyQualifiedDataObjectName(targetDataObjectName, targetConnection).FirstOrDefault();
// Only the name (e.g. without the schema) should be evaluated.
string targetDataObjectNonQualifiedName = targetDataObjectFullyQualifiedKeyValuePair.Value;
var businessKeySyntax = row.Cells[(int)TableMappingMetadataColumns.BusinessKeyDefinition].Value;
if (targetDataObjectNonQualifiedName != null && businessKeySyntax != null && row.IsNewRow == false)
{
// Hub
if (
(FormBase.TeamConfiguration.TableNamingLocation == "Prefix" &&
targetDataObjectNonQualifiedName.StartsWith(FormBase.TeamConfiguration
.HubTablePrefixValue)) ||
(FormBase.TeamConfiguration.TableNamingLocation == "Suffix" &&
targetDataObjectNonQualifiedName.EndsWith(FormBase.TeamConfiguration
.HubTablePrefixValue))
)
{
this[(int)TableMappingMetadataColumns.TargetTable, counter].Style.BackColor =
Color.CornflowerBlue;
row.Cells[(int)TableMappingMetadataColumns.DrivingKeyDefinition].ReadOnly = true;
row.Cells[(int)TableMappingMetadataColumns.DrivingKeyDefinition].Style.BackColor =
Color.LightGray;
}
// Link-Sat
else if (
(FormBase.TeamConfiguration.TableNamingLocation == "Prefix" &&
targetDataObjectNonQualifiedName.StartsWith(FormBase.TeamConfiguration
.LsatTablePrefixValue)) ||
(FormBase.TeamConfiguration.TableNamingLocation == "Suffix" &&
targetDataObjectNonQualifiedName.EndsWith(FormBase.TeamConfiguration
.LsatTablePrefixValue))
)
{
this[(int)TableMappingMetadataColumns.TargetTable, counter].Style.BackColor = Color.Gold;
}
// Context
else if (
(FormBase.TeamConfiguration.TableNamingLocation == "Prefix" &&
targetDataObjectNonQualifiedName.StartsWith(FormBase.TeamConfiguration
.SatTablePrefixValue)) ||
(FormBase.TeamConfiguration.TableNamingLocation == "Suffix" &&
targetDataObjectNonQualifiedName.EndsWith(FormBase.TeamConfiguration
.SatTablePrefixValue))
)
{
this[(int)TableMappingMetadataColumns.TargetTable, counter].Style.BackColor = Color.Yellow;
row.Cells[(int)TableMappingMetadataColumns.DrivingKeyDefinition].ReadOnly = true;
row.Cells[(int)TableMappingMetadataColumns.DrivingKeyDefinition].Style.BackColor =
Color.LightGray;
}
// Natural Business Relationship
else if (
(FormBase.TeamConfiguration.TableNamingLocation == "Prefix" &&
targetDataObjectNonQualifiedName.StartsWith(FormBase.TeamConfiguration
.LinkTablePrefixValue)) ||
(FormBase.TeamConfiguration.TableNamingLocation == "Suffix" &&
targetDataObjectNonQualifiedName.EndsWith(FormBase.TeamConfiguration
.LinkTablePrefixValue))
)
{
this[(int)TableMappingMetadataColumns.TargetTable, counter].Style.BackColor =
Color.OrangeRed;
row.Cells[(int)TableMappingMetadataColumns.DrivingKeyDefinition].ReadOnly = true;
row.Cells[(int)TableMappingMetadataColumns.DrivingKeyDefinition].Style.BackColor =
Color.LightGray;
}
// PSA
else if (
(FormBase.TeamConfiguration.TableNamingLocation == "Prefix" &&
targetDataObjectNonQualifiedName.StartsWith(FormBase.TeamConfiguration
.PsaTablePrefixValue)) ||
(FormBase.TeamConfiguration.TableNamingLocation == "Suffix" &&
targetDataObjectNonQualifiedName.EndsWith(FormBase.TeamConfiguration
.PsaTablePrefixValue))
)
{
this[(int)TableMappingMetadataColumns.TargetTable, counter].Style.BackColor =
Color.AntiqueWhite;
row.Cells[(int)TableMappingMetadataColumns.DrivingKeyDefinition].ReadOnly = true;
row.Cells[(int)TableMappingMetadataColumns.DrivingKeyDefinition].Style.BackColor =
Color.LightGray;
}
// Staging
else if (
(FormBase.TeamConfiguration.TableNamingLocation == "Prefix" &&
targetDataObjectNonQualifiedName.StartsWith(FormBase.TeamConfiguration
.StgTablePrefixValue)) ||
(FormBase.TeamConfiguration.TableNamingLocation == "Suffix" &&
targetDataObjectNonQualifiedName.EndsWith(FormBase.TeamConfiguration
.StgTablePrefixValue))
)
{
this[(int)TableMappingMetadataColumns.TargetTable, counter].Style.BackColor =
Color.WhiteSmoke;
row.Cells[(int)TableMappingMetadataColumns.DrivingKeyDefinition].ReadOnly = true;
row.Cells[(int)TableMappingMetadataColumns.DrivingKeyDefinition].Style.BackColor =
Color.LightGray;
}
// Presentation Layer
else if (
(FormBase.TeamConfiguration.TableNamingLocation == "Prefix" &&
presentationLayerLabelArray.Any(s => targetDataObjectNonQualifiedName.StartsWith(s)))
||
(FormBase.TeamConfiguration.TableNamingLocation == "Suffix" &&
presentationLayerLabelArray.Any(s => targetDataObjectNonQualifiedName.EndsWith(s)))
)
{
this[(int)TableMappingMetadataColumns.TargetTable, counter].Style.BackColor =
Color.Aquamarine;
row.Cells[(int)TableMappingMetadataColumns.DrivingKeyDefinition].ReadOnly = true;
row.Cells[(int)TableMappingMetadataColumns.DrivingKeyDefinition].Style.BackColor =
Color.LightGray;
}
// Derived objects / transformations
else if (
(FormBase.TeamConfiguration.TableNamingLocation == "Prefix" &&
transformationLabelArray.Any(s => targetDataObjectNonQualifiedName.StartsWith(s)))
||
(FormBase.TeamConfiguration.TableNamingLocation == "Suffix" &&
transformationLabelArray.Any(s => targetDataObjectNonQualifiedName.EndsWith(s)))
)
{
this[(int)TableMappingMetadataColumns.TargetTable, counter].Style.BackColor =
Color.LightGreen;
row.Cells[(int)TableMappingMetadataColumns.DrivingKeyDefinition].ReadOnly = true;
row.Cells[(int)TableMappingMetadataColumns.DrivingKeyDefinition].Style.BackColor =
Color.LightGray;
}
else
{
// Catch
}
//Syntax highlighting for code
if (businessKeySyntax.ToString().Contains("CONCATENATE") ||
businessKeySyntax.ToString().Contains("COMPOSITE"))
{
this[(int)TableMappingMetadataColumns.BusinessKeyDefinition, counter].Style.ForeColor =
Color.DarkBlue;
this[(int)TableMappingMetadataColumns.BusinessKeyDefinition, counter].Style.Font =
new Font("Microsoft Sans Serif", 8.25F, FontStyle.Bold);
}
}
counter++;
}
}
}
