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);
//}
}