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