/// <summary>
/// Return the Surrogate Key for a given table using the TEAM settings (i.e. prefix/suffix settings etc.).
/// </summary>
/// <param name="tableName"></param>
/// <returns>surrogateKey</returns>
public static string GetSurrogateKey(string tableName, TeamConnection teamConnection, TeamConfiguration configuration)
{
// Get the fully qualified name
KeyValuePair <string, string> fullyQualifiedName = GetFullyQualifiedDataObjectName(tableName, teamConnection).FirstOrDefault();
// Initialise the return value
string surrogateKey = "";
string newTableName = fullyQualifiedName.Value;
string keyLocation = configuration.DwhKeyIdentifier;
string[] prefixSuffixArray =
{
configuration.HubTablePrefixValue,
configuration.SatTablePrefixValue,
configuration.LinkTablePrefixValue,
configuration.LsatTablePrefixValue
};
if (newTableName != "Not applicable")
{
// Removing the table pre- or suffixes from the table name based on the TEAM configuration settings.
if (configuration.TableNamingLocation == "Prefix")
{
foreach (string prefixValue in prefixSuffixArray)
{
if (newTableName.StartsWith(prefixValue))
{
newTableName = newTableName.Replace(prefixValue, "");
}
}
}
else
{
foreach (string suffixValue in prefixSuffixArray)
{
if (newTableName.EndsWith(suffixValue))
{
newTableName = newTableName.Replace(suffixValue, "");
}
}
}
// Define the surrogate key using the table name and key prefix/suffix settings.
if (configuration.KeyNamingLocation == "Prefix")
{
surrogateKey = keyLocation + newTableName;
}
else
{
surrogateKey = newTableName + keyLocation;
}
}
return(surrogateKey);
}
/// <summary>
/// Create the sample / start dictionary of connections and commit to memory (global parameter connection dictionary).
/// </summary>
internal static Dictionary <string, TeamConnection> CreateDummyConnectionDictionary()
{
var localDictionary = new Dictionary <string, TeamConnection>();
// Metadata
var newTeamConnectionProfileMetadata = new TeamConnection
{
ConnectionInternalId = "MetadataConnectionInternalId",
ConnectionKey = "Metadata",
ConnectionName = "Metadata Repository",
ConnectionType = ConnectionTypes.Database,
ConnectionNotes = "Default metadata repository connection."
};
var newTeamDatabaseConnectionMetadata = new TeamDatabaseConnection
{
authenticationType = ServerAuthenticationTypes.SSPI,
DatabaseName = "900_Metadata",
SchemaName = "dbo",
NamedUserName = "******",
NamedUserPassword = "",
ServerName = "localhost"
};
newTeamConnectionProfileMetadata.DatabaseServer = newTeamDatabaseConnectionMetadata;
// Source
var newTeamConnectionProfileSource = new TeamConnection
{
//connectionInternalId = Utility.CreateMd5(new[] { Utility.GetRandomString(100), "Source" }, "%$@"),
ConnectionInternalId = "SourceConnectionInternalId",
ConnectionKey = "Source",
ConnectionName = "Source System",
ConnectionType = ConnectionTypes.Database,
ConnectionNotes = "Sample source system connection."
};
var newTeamDatabaseConnectionSource = new TeamDatabaseConnection
{
authenticationType = ServerAuthenticationTypes.SSPI,
DatabaseName = "000_Source",
SchemaName = "dbo",
NamedUserName = "******",
NamedUserPassword = "",
ServerName = "localhost"
};
newTeamConnectionProfileSource.DatabaseServer = newTeamDatabaseConnectionSource;
// Staging
var newTeamConnectionProfileStaging = new TeamConnection
{
//connectionInternalId = Utility.CreateMd5(new[] { Utility.GetRandomString(100), "Staging" }, "%$@"),
ConnectionInternalId = "StagingConnectionInternalId",
ConnectionKey = "Staging",
ConnectionName = "Staging / Landing Area",
ConnectionType = ConnectionTypes.Database,
ConnectionNotes = ""
};
var newTeamDatabaseConnectionStaging = new TeamDatabaseConnection
{
authenticationType = ServerAuthenticationTypes.SSPI,
DatabaseName = "100_Staging_Area",
SchemaName = "dbo",
NamedUserName = "******",
NamedUserPassword = "",
ServerName = "localhost"
};
newTeamConnectionProfileStaging.DatabaseServer = newTeamDatabaseConnectionStaging;
// PSA
var newTeamConnectionProfilePsa = new TeamConnection
{
//connectionInternalId = Utility.CreateMd5(new[] { Utility.GetRandomString(100), "PersistentStagingArea" }, "%$@"),
ConnectionInternalId = "PsaConnectionInternalId",
ConnectionKey = "PSA",
ConnectionName = "Persistent Staging Area",
ConnectionType = ConnectionTypes.Database,
ConnectionNotes = ""
};
var newTeamDatabaseConnectionPsa = new TeamDatabaseConnection
{
authenticationType = ServerAuthenticationTypes.SSPI,
DatabaseName = "150_Persistent_Staging_Area",
SchemaName = "dbo",
NamedUserName = "******",
NamedUserPassword = "",
ServerName = "localhost"
};
newTeamConnectionProfilePsa.DatabaseServer = newTeamDatabaseConnectionPsa;
// Integration
var newTeamConnectionProfileIntegration = new TeamConnection
{
//connectionInternalId = Utility.CreateMd5(new[] { Utility.GetRandomString(100), "Integration" }, "%$@"),
ConnectionInternalId = "IntegrationConnectionInternalId",
ConnectionKey = "Integration",
ConnectionName = "Integration Layer",
ConnectionType = ConnectionTypes.Database,
ConnectionNotes = ""
};
var newTeamDatabaseConnectionIntegration = new TeamDatabaseConnection
{
authenticationType = ServerAuthenticationTypes.SSPI,
DatabaseName = "200_Integration_Layer",
SchemaName = "dbo",
NamedUserName = "******",
NamedUserPassword = "",
ServerName = "localhost"
};
newTeamConnectionProfileIntegration.DatabaseServer = newTeamDatabaseConnectionIntegration;
// Presentation
var newTeamConnectionProfilePresentation = new TeamConnection
{
//connectionInternalId = Utility.CreateMd5(new[] { Utility.GetRandomString(100), "Presentation" }, "%$@"),
ConnectionInternalId = "PresentationConnectionInternalId",
ConnectionKey = "Presentation",
ConnectionName = "Presentation Layer",
ConnectionType = ConnectionTypes.Database,
ConnectionNotes = ""
};
var newTeamDatabaseConnectionPresentation = new TeamDatabaseConnection
{
authenticationType = ServerAuthenticationTypes.SSPI,
DatabaseName = "300_Presentation_Layer",
SchemaName = "dbo",
NamedUserName = "******",
NamedUserPassword = "",
ServerName = "localhost"
};
newTeamConnectionProfilePresentation.DatabaseServer = newTeamDatabaseConnectionPresentation;
// Compile the dictionary
localDictionary.Add(newTeamConnectionProfileMetadata.ConnectionInternalId, newTeamConnectionProfileMetadata);
localDictionary.Add(newTeamConnectionProfileSource.ConnectionInternalId, newTeamConnectionProfileSource);
localDictionary.Add(newTeamConnectionProfileStaging.ConnectionInternalId, newTeamConnectionProfileStaging);
localDictionary.Add(newTeamConnectionProfilePsa.ConnectionInternalId, newTeamConnectionProfilePsa);
localDictionary.Add(newTeamConnectionProfileIntegration.ConnectionInternalId, newTeamConnectionProfileIntegration);
localDictionary.Add(newTeamConnectionProfilePresentation.ConnectionInternalId, newTeamConnectionProfilePresentation);
// Commit to memory.
//FormBase.ConfigurationSettings.connectionDictionary = localDictionary;
return(localDictionary);
}
/// <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> GetHubTargetBusinessKeyListPhysical(string fullyQualifiedTableName, TeamConnection teamConnection, TeamConfiguration configuration)
{
// Obtain the business key as it is known in the target Hub table. Can be multiple due to composite keys.
var fullyQualifiedName = 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 = teamConnection.CreateSqlServerConnectionString(false)
};
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 COLUMN_NAME");
sqlStatementForBusinessKeys.AppendLine("FROM INFORMATION_SCHEMA.COLUMNS");
if (configuration.KeyNamingLocation == "Prefix")
{
sqlStatementForBusinessKeys.AppendLine($"WHERE SUBSTRING(COLUMN_NAME,1,{localkeyLength})!='{configuration.DwhKeyIdentifier}'");
}
else
{
sqlStatementForBusinessKeys.AppendLine($"WHERE SUBSTRING(COLUMN_NAME,LEN(COLUMN_NAME)-{localkeySubstring},{localkeyLength})!='{configuration.DwhKeyIdentifier}'");
}
sqlStatementForBusinessKeys.AppendLine("AND TABLE_SCHEMA = '" + schemaName + "'");
sqlStatementForBusinessKeys.AppendLine(" AND TABLE_NAME= '" + tableName + "'");
sqlStatementForBusinessKeys.AppendLine(" AND COLUMN_NAME NOT IN ('" +
configuration.RecordSourceAttribute + "','" +
configuration.AlternativeRecordSourceAttribute + "','" +
configuration.AlternativeLoadDateTimeAttribute + "','" +
configuration.AlternativeSatelliteLoadDateTimeAttribute + "','" +
configuration.EtlProcessAttribute + "','" +
configuration.LoadDateTimeAttribute + "')");
var keyList = Utility.GetDataTable(ref conn, sqlStatementForBusinessKeys.ToString());
List <string> businessKeyList = new List <string>();
if (keyList != null)
{
foreach (DataRow row in keyList.Rows)
{
if (!businessKeyList.Contains((string)row["COLUMN_NAME"]))
{
businessKeyList.Add((string)row["COLUMN_NAME"]);
}
}
}
return(businessKeyList);
}
/// <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);
}
/// <summary>
/// Returns a list of Business Key attributes as they are defined in the target Hub table (virtual setup)
/// </summary>
/// <param name="schemaName"></param>
/// <param name="tableName"></param>
/// <param name="versionId"></param>
/// <param name="queryMode"></param>
/// <returns></returns>
public static List <string> GetHubTargetBusinessKeyListVirtual(string fullyQualifiedTableName, TeamConnection teamConnection, int versionId, TeamConfiguration configuration)
{
// Obtain the business key as it is known in the target Hub table. Can be multiple due to composite keys.
var fullyQualifiedName = GetFullyQualifiedDataObjectName(fullyQualifiedTableName, teamConnection).FirstOrDefault();
// The metadata connection can be used.
var conn = new SqlConnection
{
ConnectionString = configuration.MetadataConnection.CreateSqlServerConnectionString(false)
};
try
{
conn.Open();
}
catch (Exception)
{
configuration.ConfigurationSettingsEventLog.Add(Event.CreateNewEvent(EventTypes.Error, $"The connection to the metadata repository 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("]", "");
//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 TMP_MD_VERSION_ATTRIBUTE");
sqlStatementForBusinessKeys.AppendLine("WHERE SUBSTRING(COLUMN_NAME,LEN(COLUMN_NAME)-" + localkeyLength +
"," + localkeySubstring + ")!='_" +
configuration.DwhKeyIdentifier + "'");
sqlStatementForBusinessKeys.AppendLine(" AND TABLE_NAME= '" + tableName + "'");
sqlStatementForBusinessKeys.AppendLine(" AND SCHEMA_NAME= '" + schemaName + "'");
sqlStatementForBusinessKeys.AppendLine(" AND COLUMN_NAME NOT IN ('" +
configuration.RecordSourceAttribute + "','" +
configuration.AlternativeRecordSourceAttribute +
"','" +
configuration.AlternativeLoadDateTimeAttribute +
"','" + configuration.AlternativeSatelliteLoadDateTimeAttribute + "','" +
configuration.EtlProcessAttribute + "','" +
configuration.LoadDateTimeAttribute + "')");
sqlStatementForBusinessKeys.AppendLine(" AND VERSION_ID = " + versionId + "");
var keyList = Utility.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);
}
/// <summary>
/// Separates the schema from the table name (if available), and returns both as individual values in a Dictionary key/value pair (key schema/ value table).
/// If no schema is defined, the connection information will be used to determine the schema. If all else fails 'dbo' will set as default.
/// </summary>
/// <param name="tableName"></param>
/// <param name="teamConnection"></param>
/// <returns></returns>
public static Dictionary <string, string> GetFullyQualifiedDataObjectName(string tableName, TeamConnection teamConnection)
{
Dictionary <string, string> fullyQualifiedTableName = new Dictionary <string, string>();
string schemaName = "";
string returnTableName = "";
if (tableName.Contains('.')) // Split the string
{
var splitName = tableName.Split('.').ToList();
schemaName = splitName[0];
returnTableName = splitName[1];
fullyQualifiedTableName.Add(schemaName, returnTableName);
}
else // Return the default (e.g. [dbo])
{
if (teamConnection is null)
{
schemaName = "dbo";
}
else
{
schemaName = teamConnection.DatabaseServer.SchemaName ?? "dbo";
}
returnTableName = tableName;
fullyQualifiedTableName.Add(schemaName, returnTableName);
}
return(fullyQualifiedTableName);
}
public TeamConfiguration()
{
ConnectionDictionary = new Dictionary <string, TeamConnection>();
MetadataConnection = new TeamConnection();
ConfigurationSettingsEventLog = new EventLog();
}