/// <summary>
/// Using the TableMapping, create a list of Core Business Concept / Natural Business Relationship links.
/// </summary>
/// <param name="configurationSetting"></param>
/// <returns></returns>
public List <Tuple <string, string> > BusinessConceptRelationshipList(TeamConfiguration configurationSetting)
{
DataView sourceContainerView = new DataView((DataTable)DataTable);
DataTable distinctValues = sourceContainerView.ToTable(true, TableMappingMetadataColumns.SourceTable.ToString());
var businessConceptRelationshipList = new List <Tuple <string, string> >();
foreach (DataRow sourceContainerRow in distinctValues.Rows)
{
string sourceContainer = (string)sourceContainerRow[TableMappingMetadataColumns.SourceTable.ToString()];
foreach (DataRow row in DataTable.Rows)
{
string sourceObject = (string)row[TableMappingMetadataColumns.SourceTable.ToString()];
if (sourceContainer == sourceObject)
{
//var sourceObject = (string) row[TableMappingMetadataColumns.SourceTable.ToString()];
var targetObject = (string)row[TableMappingMetadataColumns.TargetTable.ToString()];
//var sourceObjectType = MetadataHandling.GetTableType(sourceObject, "", TeamConfigurationSettings);
var targetObjectType = MetadataHandling.GetDataObjectType(targetObject, "", configurationSetting);
var targetObjectBusinessKey =
(string)row[TableMappingMetadataColumns.BusinessKeyDefinition.ToString()];
if (targetObjectType == MetadataHandling.TableTypes.CoreBusinessConcept)
{
// Retrieve the related objects to a CBC.
var cbcResults = from localRow in DataTable.AsEnumerable()
where localRow.Field <string>(TableMappingMetadataColumns.SourceTable.ToString()) ==
sourceObject && // Is in the same source cluster
localRow.Field <string>(TableMappingMetadataColumns.BusinessKeyDefinition.ToString())
.Contains(targetObjectBusinessKey) && // Contains a part of the business key
localRow.Field <string>(TableMappingMetadataColumns.TargetTable.ToString()) !=
targetObject && // Is not itself
MetadataHandling.GetDataObjectType(
localRow.Field <string>(TableMappingMetadataColumns.TargetTable.ToString()), "",
configurationSetting) ==
MetadataHandling.TableTypes.NaturalBusinessRelationship // Is a NBR.
select localRow;
foreach (DataRow detailRow in cbcResults)
{
var targetObjectDetail = (string)detailRow[TableMappingMetadataColumns.TargetTable.ToString()];
businessConceptRelationshipList.Add(new Tuple <string, string>(targetObject, targetObjectDetail));
}
}
}
}
}
return(businessConceptRelationshipList);
}
/// <summary>
/// Using the Table Mapping, create a list of Subject Areas and their contents.
/// </summary>
/// <param name="configurationSetting"></param>
/// <returns></returns>
public List <Tuple <string, string, string> > SubjectAreaList(TeamConfiguration configurationSetting)
{
var subjectAreaList = new List <Tuple <string, string, string> >();
foreach (DataRow row in DataTable.Rows)
{
string sourceObject = (string)row[TableMappingMetadataColumns.SourceTable.ToString()];
string targetObject = (string)row[TableMappingMetadataColumns.TargetTable.ToString()];
var targetObjectType = MetadataHandling.GetDataObjectType(targetObject, "", configurationSetting);
var targetObjectBusinessKey = (string)row[TableMappingMetadataColumns.BusinessKeyDefinition.ToString()];
if (targetObjectType == MetadataHandling.TableTypes.CoreBusinessConcept)
{
string subjectArea = targetObject.Replace(configurationSetting.HubTablePrefixValue + "_", "");
// Retrieve the related objects (Context Tables in this case)
var results = from localRow in DataTable.AsEnumerable()
where localRow.Field <string>(TableMappingMetadataColumns.SourceTable.ToString()) == sourceObject && // Is in the same source cluster
localRow.Field <string>(TableMappingMetadataColumns.BusinessKeyDefinition.ToString()).Contains(targetObjectBusinessKey) && // Contains a part of the business key
//localRow.Field<string>(TableMappingMetadataColumns.TargetTable.ToString()) != targetObject && // Is not itself
MetadataHandling.GetDataObjectType(localRow.Field <string>(TableMappingMetadataColumns.TargetTable.ToString()), "",
configurationSetting) != MetadataHandling.TableTypes.NaturalBusinessRelationship &&
MetadataHandling.GetDataObjectType(localRow.Field <string>(TableMappingMetadataColumns.TargetTable.ToString()), "",
configurationSetting) != MetadataHandling.TableTypes.NaturalBusinessRelationshipContext
select localRow;
foreach (DataRow detailRow in results)
{
var targetObjectDetail = (string)detailRow[TableMappingMetadataColumns.TargetTable.ToString()];
bool tupleAlreadyExists = subjectAreaList.Any(m => m.Item1 == subjectArea && m.Item2 == targetObject && m.Item3 == targetObjectDetail);
if (!tupleAlreadyExists)
{
subjectAreaList.Add(new Tuple <string, string, string>(subjectArea, targetObject, targetObjectDetail));
}
}
}
if (targetObjectType == MetadataHandling.TableTypes.NaturalBusinessRelationship)
{
string subjectArea = targetObject.Replace(configurationSetting.LinkTablePrefixValue + "_", "");
// Retrieve the related objects (relationship context tables in this case)
var results = from localRow in DataTable.AsEnumerable()
where localRow.Field <string>(TableMappingMetadataColumns.SourceTable.ToString()) == sourceObject && // Is in the same source cluster
localRow.Field <string>(TableMappingMetadataColumns.BusinessKeyDefinition.ToString()).Contains(targetObjectBusinessKey) && // Contains a part of the business key
//localRow.Field<string>(TableMappingMetadataColumns.TargetTable.ToString()) != targetObject && // Is not itself
MetadataHandling.GetDataObjectType(localRow.Field <string>(TableMappingMetadataColumns.TargetTable.ToString()), "",
configurationSetting) != MetadataHandling.TableTypes.CoreBusinessConcept // Is a relationship context table.
select localRow;
foreach (DataRow detailRow in results)
{
var targetObjectDetail = (string)detailRow[TableMappingMetadataColumns.TargetTable.ToString()];
bool tupleAlreadyExists = subjectAreaList.Any(m => m.Item1 == subjectArea && m.Item2 == targetObject && m.Item3 == targetObjectDetail);
if (!tupleAlreadyExists)
{
subjectAreaList.Add(new Tuple <string, string, string>(subjectArea, targetObject, targetObjectDetail));
}
}
}
}
return(subjectAreaList);
}
/// <summary>
/// Returns a list of Business Key attributes as they are defined in the target Hub table (physical setup).
/// </summary>
/// <param name="schemaName"></param>
/// <param name="tableName"></param>
/// <param name="versionId"></param>
/// <param name="queryMode"></param>
/// <returns></returns>
public static List <string> GetRegularTableBusinessKeyListPhysical(string fullyQualifiedTableName, TeamConnection teamConnection, string connectionstring, TeamConfiguration configuration)
{
// Obtain the business key as it is known in the target Hub table. Can be multiple due to composite keys.
var fullyQualifiedName = MetadataHandling.GetFullyQualifiedDataObjectName(fullyQualifiedTableName, teamConnection).FirstOrDefault();
// If the querymode is physical the real connection needs to be asserted based on the connection associated with the table.
var conn = new SqlConnection
{
ConnectionString = connectionstring
};
try
{
conn.Open();
}
catch (Exception)
{
configuration.ConfigurationSettingsEventLog.Add(Event.CreateNewEvent(EventTypes.Error, $"The connection to the database for object {fullyQualifiedTableName} could not be established via {conn.ConnectionString}."));
}
var sqlStatementForBusinessKeys = new StringBuilder();
var keyText = configuration.DwhKeyIdentifier;
var localkeyLength = keyText.Length;
var localkeySubstring = localkeyLength + 1;
// Make sure brackets are removed
var schemaName = fullyQualifiedName.Key?.Replace("[", "").Replace("]", "");
var tableName = fullyQualifiedName.Value?.Replace("[", "").Replace("]", "");
// Make sure the live database is hit when the checkbox is ticked
sqlStatementForBusinessKeys.AppendLine("SELECT");
sqlStatementForBusinessKeys.AppendLine(" SCHEMA_NAME(TAB.SCHEMA_ID) AS[SCHEMA_NAME],");
sqlStatementForBusinessKeys.AppendLine(" PK.[NAME] AS PK_NAME,");
sqlStatementForBusinessKeys.AppendLine(" IC.INDEX_COLUMN_ID AS COLUMN_ID,");
sqlStatementForBusinessKeys.AppendLine(" COL.[NAME] AS COLUMN_NAME,");
sqlStatementForBusinessKeys.AppendLine(" TAB.[NAME] AS TABLE_NAME");
sqlStatementForBusinessKeys.AppendLine("FROM SYS.TABLES TAB");
sqlStatementForBusinessKeys.AppendLine("INNER JOIN SYS.INDEXES PK");
sqlStatementForBusinessKeys.AppendLine(" ON TAB.OBJECT_ID = PK.OBJECT_ID");
sqlStatementForBusinessKeys.AppendLine(" AND PK.IS_PRIMARY_KEY = 1");
sqlStatementForBusinessKeys.AppendLine("INNER JOIN SYS.INDEX_COLUMNS IC");
sqlStatementForBusinessKeys.AppendLine(" ON IC.OBJECT_ID = PK.OBJECT_ID");
sqlStatementForBusinessKeys.AppendLine(" AND IC.INDEX_ID = PK.INDEX_ID");
sqlStatementForBusinessKeys.AppendLine("INNER JOIN SYS.COLUMNS COL");
sqlStatementForBusinessKeys.AppendLine(" ON PK.OBJECT_ID = COL.OBJECT_ID");
sqlStatementForBusinessKeys.AppendLine(" AND COL.COLUMN_ID = IC.COLUMN_ID");
sqlStatementForBusinessKeys.AppendLine("WHERE");
sqlStatementForBusinessKeys.AppendLine(" SCHEMA_NAME(TAB.SCHEMA_ID)= '" + schemaName + "'");
sqlStatementForBusinessKeys.AppendLine(" AND TABLE_NAME= '" + tableName + "'");
sqlStatementForBusinessKeys.AppendLine("ORDER BY");
sqlStatementForBusinessKeys.AppendLine(" SCHEMA_NAME(TAB.SCHEMA_ID),");
sqlStatementForBusinessKeys.AppendLine(" PK.[NAME],");
sqlStatementForBusinessKeys.AppendLine(" IC.INDEX_COLUMN_ID");
var tableKeyList = Utility.GetDataTable(ref conn, sqlStatementForBusinessKeys.ToString());
if (tableKeyList == 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 keyList = new List <string>();
foreach (DataRow row in tableKeyList.Rows)
{
if (!keyList.Contains((string)row["COLUMN_NAME"]))
{
keyList.Add((string)row["COLUMN_NAME"]);
}
}
return(keyList);
}
