Example #1
0
        public List <TeamConnection> GetConnectionList(Dictionary <string, TeamConnection> connectionDictionary)
        {
            // Create the group nodes (systems)
            var connectionList = new List <TeamConnection>();

            var sourceConnectionList = DataTable.AsEnumerable().Select(r => r.Field <string>(TableMappingMetadataColumns.SourceConnection.ToString())).ToList();
            var targetConnectionList = DataTable.AsEnumerable().Select(r => r.Field <string>(TableMappingMetadataColumns.TargetConnection.ToString())).ToList();

            foreach (string connection in sourceConnectionList)
            {
                var connectionProfile = TeamConfiguration.GetTeamConnectionByInternalId(connection, connectionDictionary);

                if (!connectionList.Contains(connectionProfile))
                {
                    connectionList.Add(connectionProfile);
                }
            }

            foreach (string connection in targetConnectionList)
            {
                var connectionProfile = TeamConfiguration.GetTeamConnectionByInternalId(connection, connectionDictionary);

                if (!connectionList.Contains(connectionProfile))
                {
                    connectionList.Add(connectionProfile);
                }
            }

            return(connectionList);
        }
Example #2
0
        /// <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);
        }
Example #3
0
        /// <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);
        }
Example #4
0
        /// <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);
        }
Example #5
0
        /// <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);
        }
Example #6
0
        /// <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);
        }
Example #7
0
        /// <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);
        }
Example #8
0
        /// <summary>
        /// This method returns the ETL loading 'direction' based on the source and target mapping.
        /// </summary>
        /// <param name="sourceDataObjectName"></param>
        /// <param name="targetDataObjectName"></param>
        /// <param name="teamConfiguration"></param>
        /// <returns></returns>
        public static string GetDataObjectMappingLoadVector(string sourceDataObjectName, string targetDataObjectName, TeamConfiguration teamConfiguration)
        {
            // This is used to evaluate the correct connection for the generated ETL processes.

            TableTypes evaluatedSource = GetDataObjectType(sourceDataObjectName, "", teamConfiguration);
            TableTypes evaluatedTarget = GetDataObjectType(targetDataObjectName, "", teamConfiguration);

            string loadVector = "";

            if (evaluatedSource == TableTypes.StagingArea && evaluatedTarget == TableTypes.PersistentStagingArea)
            {
                loadVector = "Landing to Persistent Staging Area";
            }
            // If the source is not a DWH table, but the target is a DWH table then it's a base ('Raw') Data Warehouse ETL (load vector). - 'Staging Layer to Raw Data Warehouse'.
            else if (!new[] { TableTypes.CoreBusinessConcept, TableTypes.Context, TableTypes.NaturalBusinessRelationship, TableTypes.NaturalBusinessRelationshipContext, TableTypes.Derived }.Contains(evaluatedSource) && new[] { TableTypes.CoreBusinessConcept, TableTypes.Context, TableTypes.NaturalBusinessRelationship, TableTypes.NaturalBusinessRelationshipContext }.Contains(evaluatedTarget))
            {
                loadVector = "Staging Layer to Raw Data Warehouse";
            }
            // If the source is a DWH or Derived table, and the target is a DWH table then it's a Derived ('Business') DWH ETL - 'Raw Data Warehouse to Interpreted'.
            else if (new[] { TableTypes.CoreBusinessConcept, TableTypes.Context, TableTypes.NaturalBusinessRelationship, TableTypes.NaturalBusinessRelationshipContext, TableTypes.Derived }.Contains(evaluatedSource) && new[] { TableTypes.CoreBusinessConcept, TableTypes.Context, TableTypes.NaturalBusinessRelationship, TableTypes.NaturalBusinessRelationshipContext }.Contains(evaluatedTarget))
            {
                loadVector = "Raw Data Warehouse to Interpreted";
            }
            // If the source is a DWH table, but target is not a DWH table then it's a Presentation Layer ETL. - 'Data Warehouse to Presentation Layer'.
            else if
            (new[] { TableTypes.CoreBusinessConcept, TableTypes.Context, TableTypes.NaturalBusinessRelationship, TableTypes.NaturalBusinessRelationshipContext }.Contains(evaluatedSource)
             &&
             new[] { TableTypes.Presentation }.Contains(evaluatedTarget)
            )
            {
                loadVector = "Data Warehouse to Presentation Layer";
            }
            else if (evaluatedSource == TableTypes.Source && new[] { TableTypes.StagingArea, TableTypes.PersistentStagingArea }.Contains(evaluatedTarget))
            {
                loadVector = "Source to Staging Layer";
            }
            else // Return error
            {
                loadVector = "'" + evaluatedSource + "' to '" + evaluatedTarget + "'";
            }

            return(loadVector);
        }
Example #9
0
        /// <summary>
        /// This method returns the type of table (classification) as an enumerator based on the name and active conventions.
        /// Requires fully qualified name, or at least ignores schemas in the name.
        /// </summary>
        /// <param name="dataObjectName"></param>
        /// <param name="additionalInformation"></param>
        /// <param name="configuration"></param>
        /// <returns></returns>
        public static TableTypes GetDataObjectType(string dataObjectName, string additionalInformation, TeamConfiguration configuration)
        {
            TableTypes localType;

            var presentationLayerLabelArray = Utility.SplitLabelIntoArray(configuration.PresentationLayerLabels);
            var transformationLabelArray    = Utility.SplitLabelIntoArray(configuration.TransformationLabels);

            // Remove schema, if one is set
            dataObjectName = GetNonQualifiedTableName(dataObjectName);

            switch (configuration.TableNamingLocation)
            {
            // I.e. HUB_CUSTOMER
            case "Prefix" when dataObjectName.StartsWith(configuration.SatTablePrefixValue):
                localType = TableTypes.Context;

                break;

            case "Prefix" when dataObjectName.StartsWith(configuration.HubTablePrefixValue):
                localType = TableTypes.CoreBusinessConcept;

                break;

            case "Prefix" when dataObjectName.StartsWith(configuration.LinkTablePrefixValue):
                localType = TableTypes.NaturalBusinessRelationship;

                break;

            case "Prefix" when(dataObjectName.StartsWith(configuration.LsatTablePrefixValue) && additionalInformation == ""):
                localType = TableTypes.NaturalBusinessRelationshipContext;
                break;

            case "Prefix" when(dataObjectName.StartsWith(configuration.LsatTablePrefixValue) && additionalInformation != ""):
                localType = TableTypes.NaturalBusinessRelationshipContextDrivingKey;
                break;

            case "Prefix" when dataObjectName.StartsWith(configuration.StgTablePrefixValue):
                localType = TableTypes.StagingArea;

                break;

            case "Prefix" when dataObjectName.StartsWith(configuration.PsaTablePrefixValue):
                localType = TableTypes.PersistentStagingArea;

                break;

            // Presentation Layer
            case "Prefix" when presentationLayerLabelArray.Any(s => dataObjectName.StartsWith(s)):
                localType = TableTypes.Presentation;

                break;

            // Derived or transformation
            case "Prefix" when transformationLabelArray.Any(s => dataObjectName.StartsWith(s)):
                localType = TableTypes.Derived;

                break;

            // Source
            case "Prefix":
                localType = TableTypes.Source;
                break;

            // Suffix
            // I.e. CUSTOMER_HUB
            case "Suffix" when dataObjectName.EndsWith(configuration.SatTablePrefixValue):
                localType = TableTypes.Context;

                break;

            case "Suffix" when dataObjectName.EndsWith(configuration.HubTablePrefixValue):
                localType = TableTypes.CoreBusinessConcept;

                break;

            case "Suffix" when dataObjectName.EndsWith(configuration.LinkTablePrefixValue):
                localType = TableTypes.NaturalBusinessRelationship;

                break;

            case "Suffix" when(dataObjectName.EndsWith(configuration.LsatTablePrefixValue) && additionalInformation == ""):
                localType = TableTypes.NaturalBusinessRelationshipContext;
                break;

            case "Suffix" when(dataObjectName.EndsWith(configuration.LsatTablePrefixValue) && additionalInformation != ""):
                localType = TableTypes.NaturalBusinessRelationshipContextDrivingKey;
                break;

            case "Suffix" when dataObjectName.EndsWith(configuration.StgTablePrefixValue):
                localType = TableTypes.StagingArea;

                break;

            case "Suffix" when dataObjectName.EndsWith(configuration.PsaTablePrefixValue):
                localType = TableTypes.PersistentStagingArea;

                break;

            // Presentation Layer
            case "Suffix" when presentationLayerLabelArray.Any(s => dataObjectName.EndsWith(s)):
                localType = TableTypes.Presentation;

                break;

            // Transformation / derived
            case "Suffix" when transformationLabelArray.Any(s => dataObjectName.EndsWith(s)):
                localType = TableTypes.Derived;

                break;

            case "Suffix":
                localType = TableTypes.Source;
                break;

            default:
                localType = TableTypes.Unknown;
                break;
            }
            // Return the table type
            return(localType);
        }