public static void UpdateMetadataWithSpecificColumnLookups() { try { SqlConnection conn = new SqlConnection(Generator.Properties.Settings.Default.MetadataDatabase); string sql = @"SELECT DatabaseInfoId, DatabaseName, DatabaseDescription, ServerName FROM Metadata.DatabaseInfo A JOIN Metadata.DatabaseUse B on A.DatabaseUseId = B.DatabaseUseId WHERE B.DatabaseUse = 'SOURCE'"; List <DatabaseInfo> databases = conn.Query <DatabaseInfo>(sql).ToList(); foreach (DatabaseInfo database in databases) { Console.WriteLine("Updating metadata for {0}", database.DatabaseDescription); DataModel dm = PopulateFromMetadataDatabase.PopulateDataModelFromMetadataDatabase(database, conn); using (SqlConnection connection = new SqlConnection(Utilities.GetConnectionString(database))) { foreach (DatabaseObject table in dm.Objects.Where(x => x.DatabaseObjectType.Trim().ToUpper() == "U")) { bool updateTable = false; foreach (DatabaseColumn column in table.Columns.Where(c => c.SpecificLookupsDone == false)) { updateTable = true; break; } if (updateTable) { Console.WriteLine("\nTable '{0}'", table.DatabaseObjectName); foreach (DatabaseColumn column in table.Columns.Where(c => c.SpecificLookupsDone == false)) { PerformSpecificLookups(connection, table, column); } dm.UpdateColumnMetadataForTable(conn, table); } } } } } catch (Exception ex) { Console.WriteLine("Error in UpdateMetadataWithSpecificColumnLookups {0}", ex.Message); } }
public static void CreateObjectsFromMetadata(SqlConnection conn) { //try //{ Console.WriteLine(); Console.WriteLine("Creating objects for {0}", DatabaseName); // get a reference to the database so we can generate tables string sql = string.Format(@" select B.DatabaseInfoId, B.DatabaseName, B.DatabaseDescription, B.ServerName from Metadata.DatabaseInfo B join metadata.DatabaseUse A on A.DatabaseUseId = B.DatabaseUseId where A.DatabaseUse = 'TARGET' and B.DatabaseName = '{0}' ", DatabaseName); List <DatabaseInfo> databases = conn.Query <DatabaseInfo>(sql).ToList(); DatabaseInfo database = databases.First(); // we only have one database to generate for string[] staticDims = { "" }; DirectoryInfo dirPostDeployFolder = Utilities.GetAndCleanOutputDir(DatabaseName, @"Scripts\Post-Deploy"); DirectoryInfo dirLoadProcs = Utilities.GetAndCleanOutputDir(DatabaseName, LoadingSchema, "Stored Procedures"); DirectoryInfo dirLookupProcs = Utilities.GetAndCleanOutputDir(DatabaseName, LookupSchema, "Stored Procedures"); DirectoryInfo dirLoadViewStubs = Utilities.GetAndCleanOutputDir(DatabaseName, LoadingSchema, "Views"); DirectoryInfo dirTables = Utilities.GetAndCleanOutputDir(DatabaseName, TableSchema, "Tables"); DirectoryInfo dirDataMartViews = Utilities.GetAndCleanOutputDir(DatabaseName, DataMartSchema, "Views"); DirectoryInfo dirCubeViews = Utilities.GetAndCleanOutputDir(DatabaseName, CubeSchema, "Views"); DirectoryInfo dirExtractViews = Utilities.GetAndCleanOutputDir(DatabaseName, ExtractSchema, "Views"); string checkInsertViewsSP = string.Empty; string insertUnknowns = string.Empty; DataModel dm = PopulateFromMetadataDatabase.PopulateDataModelFromMetadataDatabase(database, conn); foreach (DatabaseObject table in dm.Objects.Where(x => x.SchemaName.ToUpper() == TableSchema.ToUpper() && x.DatabaseObjectType.Trim().ToUpper() == "U")) { // create an artificial sort order where the dimXXXid columns are at the beginning foreach (DatabaseColumn column in table.Columns.Where(x => x.DatabaseColumnName.ToLower().StartsWith("dim"))) { column.DatabaseColumnSortOrder = "0" + column.DatabaseColumnName; } string[] firstColumns = { "XXX" }; foreach (DatabaseColumn column in table.Columns.Where(x => firstColumns.Contains(x.DatabaseColumnName))) { column.DatabaseColumnSortOrder = "1" + column.DatabaseColumnName; } if (!staticDims.Contains(table.DatabaseObjectName) && ( (table.DatabaseObjectName.StartsWith("Dim") || table.DatabaseObjectName.StartsWith("Security") || table.DatabaseObjectName.StartsWith("Active") || table.DatabaseObjectName.StartsWith("Masked")) )) { // if we have a primary key in the table, then use standard merge template, otherwise use truncate and load int iCount = table.Columns.Where(column => column.IsPrimaryKey == true).Count(); if (iCount > 0) { TemplateCommon.StandardMergeSP(table, dirLoadProcs, LoadingSchema, null); } else { TemplateCommon.StandardInsertSP(table, dirLoadProcs, LoadingSchema, null); } } if (table.DatabaseObjectName.StartsWith("Fact")) { // if we have a primary key in the fact table, then use simple merge template, otherwise use truncate and load int iCount = table.Columns.Where(column => column.IsPrimaryKey == true).Count(); if (iCount > 0) { TemplateCommon.StandardMergeSP(table, dirLoadProcs, LoadingSchema, null); } else { TemplateCommon.StandardInsertSP(table, dirLoadProcs, LoadingSchema, null); } } if (table.DatabaseObjectName.StartsWith("Ref") || table.DatabaseObjectName.StartsWith("Bridge")) { TemplateCommon.StandardInsertSP(table, dirLoadProcs, LoadingSchema, null); } { IEnumerable <DatabaseColumn> loadViewFilteredColumns = table.Columns; //.Where(column => (column.DatabaseColumnName.ToLower() != DatabaseObject.updatedloadlogid) && // column.DatabaseColumnName.ToLower().EndsWith("key") && !column.IsIdentity); // Create stub of the load view CreateLoadViewStub(table, dirLoadViewStubs, LoadingSchema, table.GetColumnListSql(loadViewFilteredColumns), StagingAreaSchema); } { string[] excludeColumns = { "XXX" }; IEnumerable <DatabaseColumn> dataMartViewFilteredColumns = table.Columns.Where(column => (!DatabaseObject.allStandardColumns.Contains(column.DatabaseColumnName.ToLower()) && !excludeColumns.Contains(column.DatabaseColumnName.ToLower()) && !(column.DatabaseColumnName.ToLower().StartsWith("fact") && column.DatabaseColumnName.ToLower().EndsWith("key")))); // DataMart views CreateDataMartView(table, dirDataMartViews, DataMartSchema, table.GetViewColumnListSql(dataMartViewFilteredColumns)); // cube views CreateDataMartView(table, dirCubeViews, CubeSchema, table.GetColumnListSql(dataMartViewFilteredColumns)); } string[] excludeDimensions = { "" }; if (!excludeDimensions.Contains(table.DatabaseObjectName.ToLower())) { IEnumerable <DatabaseColumn> checkInsertViewFilteredColumns = table.Columns.Where(column => (!DatabaseObject.allStandardColumns.Contains(column.DatabaseColumnName.ToLower()) && !column.IsIdentity)); checkInsertViewsSP += TemplateComponents.CheckInsertView(LoadingSchema, table.DatabaseObjectName, table.GetColumnListSql(checkInsertViewFilteredColumns)); // include dimensions if (table.DatabaseObjectName.ToLower().StartsWith("dim")) { insertUnknowns += InsertUnknowns(LoadingSchema, table); } } } CheckInsertViews(dirLoadProcs, checkInsertViewsSP); InsertUnknownsScript(dirPostDeployFolder, insertUnknowns); foreach (DatabaseObject table in dm.Objects.Where(x => x.SchemaName.ToUpper() == LookupSchema.ToUpper() && x.DatabaseObjectType.Trim().ToUpper() == "U")) { if (table.DatabaseObjectName.StartsWith("Bridge")) { TemplateCommon.StandardInsertSP(table, dirLookupProcs, LookupSchema, "lookup"); } else { TemplateCommon.StandardMergeSP(table, dirLookupProcs, LookupSchema, "lookup"); } } Console.WriteLine("Done all " + DatabaseName); //} //catch (Exception ex) //{ // Console.WriteLine("Error in EssentialsAccountsDB.CreateObjectsFromMetadata {0}", ex.Message); //} }
public static void CreateObjectsFromMetadata(SqlConnection conn, bool saveRenames) { //try //{ Console.WriteLine(); Console.WriteLine("Creating objects for {0}", DatabaseName); // ensure that all custom updates to the metadata have been done before creating the tables by calling [Metadata].[CustomUpdateToColumnMetadata] using (SqlCommand cmd = new SqlCommand("Metadata.CustomUpdateToColumnMetadata", conn)) { cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandTimeout = 600; cmd.ExecuteNonQuery(); } // get the list of databases to generate tables string sql = @" SELECT B.DatabaseInfoId, B.DatabaseGroupId, B.DatabaseName, B.DatabaseDescription, B.ServerName, B.ImportMetadata, B.pkPrefix, B.DatabaseGroup FROM Metadata.DatabaseInfo B JOIN metadata.DatabaseUse A ON A.DatabaseUseId = B.DatabaseUseId WHERE A.DatabaseUse = 'SOURCE' "; string dataMartSchemaName = "eaDataMart"; DirectoryInfo dirDmTables = Utilities.GetAndCleanOutputDir("EssentialsAccountsDB", dataMartSchemaName, "Tables"); List <DatabaseInfo> databases = conn.Query <DatabaseInfo>(sql).ToList(); foreach (DatabaseInfo database in databases) { // Bring all database objects into memory DataModel dm = PopulateFromMetadataDatabase.PopulateDataModelFromMetadataDatabase(database, conn); sql = string.Format(@" SELECT DatabaseInfoId, SchemaName, StagingAreaSchema FROM Metadata.SchemaInfo A WHERE A.DatabaseInfoId = {0} ", database.DatabaseInfoId); database.Schemas = conn.Query <SchemaInfo>(sql).ToList(); foreach (SchemaInfo schema in database.Schemas) { DirectoryInfo dirTables = Utilities.GetAndCleanOutputDir(DatabaseName, schema.StagingAreaSchema, "Tables"); DirectoryInfo dirViews = Utilities.GetAndCleanOutputDir(DatabaseName, schema.StagingAreaSchema, "Views"); foreach (DatabaseObject table in dm.Objects.Where(x => (x.DatabaseObjectType.Trim().ToUpper() == "U" || x.DatabaseObjectType.Trim().ToUpper() == "V") && x.UseObject && x.SchemaName == schema.SchemaName)) { table.StagingAreaSchema = schema.StagingAreaSchema; // ensure this is populated CreateStgTableScript(database, table, dirTables); //CreateDataTranslationViews(database, table, dirViews); CreateDmTableScript(database, table, dirDmTables, dataMartSchemaName); } } } //} //catch (Exception ex) //{ // Console.WriteLine("Error in EssentialsAccountsSTG.CreateObjectsFromMetadata {0}", ex.Message); //} }