private void CreateFactTableView(string tableName)
{
var primaryKeyColumns = _analyzer.GetPrimaryKeyColumns(tableName);
string primaryKeyColumnName = tableName + "Key";
string primaryKeyColumnClause = GetSurrogateKeyDefinition(_schema, tableName, primaryKeyColumns) + " AS [" + primaryKeyColumnName + "]";
var relationships = _analyzer.GetForeignKeyRelationships(tableName);
var foreignKeyColumnClauses = relationships.Select(_ => GetSurrogateKeyDefinition(null, _.PrimaryKeyCorrelationName,
_.RelationshipColumns.Select(c => c.PrimaryKeyColumn)) + " AS [" + ApplyLookupColumnQualifier(GetConventionBasedRelationshipName(_)) + "Key]");
foreach (var relationship in relationships)
{
if (!_factDependentTables.Contains(relationship.PrimaryKeyTable))
{
_factDependentTables.Add(relationship.PrimaryKeyTable);
}
}
var viewColumns = new List <string>();
viewColumns.Add(primaryKeyColumnClause);
viewColumns.AddRange(foreignKeyColumnClauses);
viewColumns.AddRange(_analyzer.GetNonKeyColumns(tableName).Select(_ => ToQualified(_schema, tableName, _.ColumnName)));
string sql = "SELECT"
+ string.Join(", ", viewColumns.Select(_ => $"\n\t{_}"))
+ $"\nFROM [{_schema}].[{tableName}]"
+ string.Join("", relationships.Select(_ => $"\n{GetRelationshipJoin(tableName, _)}"));
string viewName = "Fact" + tableName;
CreateView(_viewSchema, viewName, sql);
CreateClusteredIndex(viewName, primaryKeyColumnName);
}
private ViewDefinition GetViewDefinition(string tableName, ViewType?type = null)
{
if (type == null)
{
type = _lookupIdentifiers.Any(tableName.EndsWith) ? ViewType.Lookup : ViewType.Dimension;
}
var viewDefinition = new ViewDefinition(tableName, type.Value);
viewDefinition.ColumnGroups.Add(new ViewDefinition.PrimaryKey {
Columns = _analyzer.GetPrimaryKeyColumns(tableName)
});
foreach (var relationship in _analyzer.GetForeignKeyRelationships(tableName))
{
var keyType = ViewDefinition.ReferenceKeyType.DimensionReference;
if (type == ViewType.Dimension || type == ViewType.Lookup)
{
keyType = ViewDefinition.ReferenceKeyType.Denormalized;
}
else if (_lookupIdentifiers.Any(relationship.PrimaryKeyTable.EndsWith))
{
keyType = ViewDefinition.ReferenceKeyType.LookupReference;
}
viewDefinition.ColumnGroups.Add(new ViewDefinition.ReferenceKey
{
Relationship = relationship,
KeyType = keyType,
Columns = keyType == ViewDefinition.ReferenceKeyType.Denormalized
? _analyzer.GetNonKeyColumns(relationship.PrimaryKeyTable)
: new List <SchemaAnalyzer.ColumnDefinition>()
});
}
;
viewDefinition.ColumnGroups.Add(new ViewDefinition.ColumnGroupDefinition {
Columns = _analyzer.GetNonKeyColumns(tableName)
});
return(viewDefinition);
}