in BismNormalizer/BismNormalizer/TabularCompare/TabularMetadata/TabularModel.cs [1066:1252]
public void CleanUpAggregations()
{
//modelTablesWithRls to be used for Rule 11 below:
List<string> modelTablesWithRls = new List<string>();
foreach (Role role in _roles)
{
foreach (TablePermission tablePermission in role.TomRole.TablePermissions)
{
if (!String.IsNullOrEmpty(tablePermission.FilterExpression))
{
modelTablesWithRls.Add(tablePermission.Name);
}
}
}
foreach (Table table in _tables)
{
bool foundViolation = false;
string warningMessage = "";
foreach (Column column in table.TomTable.Columns)
{
if (!foundViolation)
{
/* Check aggs refer to valid base tables/columns
*/
if (column.AlternateOf?.BaseTable != null)
{
if (!_database.Model.Tables.ContainsName(column.AlternateOf.BaseTable.Name))
{
//Base table doesn't exist
foundViolation = true;
warningMessage = $"Removed aggregations on table {table.Name} because summarization {column.AlternateOf.Summarization.ToString()} on column {column.Name} (considering changes) refers to detail table that does not exist [table:{column.AlternateOf.BaseTable.Name}].\n";
break;
}
}
else if (column.AlternateOf?.BaseColumn != null)
{
if (_database.Model.Tables.ContainsName(column.AlternateOf.BaseColumn.Table?.Name))
{
//the referenced table is there, how about the referenced column?
if (!_database.Model.Tables.Find(column.AlternateOf.BaseColumn.Table.Name).Columns.ContainsName(column.AlternateOf.BaseColumn.Name))
{
//Base column does not exist
foundViolation = true;
warningMessage = $"Removed aggregations on table {table.Name} because summarization {column.AlternateOf.Summarization.ToString()} on column {column.Name} (considering changes) refers to detail column that does not exist [table:{column.AlternateOf.BaseColumn.Table.Name}/column:{column.AlternateOf.BaseColumn.Name}].\n";
break;
}
}
else
{
//Base table does not exist
foundViolation = true;
warningMessage = $"Removed aggregations on table {table.Name} because summarization {column.AlternateOf.Summarization.ToString()} on column {column.Name} (considering changes) refers to detail table that does not exist [table:{column.AlternateOf.BaseColumn.Table.Name}].\n";
break;
}
}
string detailTableName = null;
if (!foundViolation && column.AlternateOf != null)
{
detailTableName = (column.AlternateOf.BaseTable != null ? column.AlternateOf.BaseTable.Name : column.AlternateOf.BaseColumn.Table.Name);
}
Table detailTable = _tables.FindByName(detailTableName);
if (!foundViolation && column.AlternateOf != null && column.AlternateOf.Summarization != SummarizationType.GroupBy && modelTablesWithRls.Count > 0 && detailTable != null)
{
/* Rule 11: RLS expressions that can filter the agg table, must also be able to filter the detail table(s) using an active relationship
*/
//Get list of filtering RLS tables that filter the agg table
List<string> rlsTablesFilteringAgg = new List<string>(); //RLS tables that filter the agg table
//beginTable might be the From or the To table in TOM Relationship object; it depends on CrossFilterDirection.
RelationshipChainsFromRoot referencedTableCollection = new RelationshipChainsFromRoot();
foreach (Relationship filteringRelationship in table.FindFilteredRelationships(checkSecurityBehavior: true))
{
// EndTable can be either the From or the To table of a Relationship object depending on CrossFilteringBehavior/SecurityBehavior
string endTableName = GetEndTableName(table, filteringRelationship, out bool biDi);
RelationshipLink rootLink = new RelationshipLink(table, _tables.FindByName(endTableName), true, "", false, filteringRelationship, biDi);
ValidateLinkForAggsRls(rootLink, referencedTableCollection, modelTablesWithRls, rlsTablesFilteringAgg);
}
//If the agg table itself has RLS on it, then consider it a table that is filtering the agg too
if (modelTablesWithRls.Contains(table.Name))
{
rlsTablesFilteringAgg.Add(table.Name);
}
if (rlsTablesFilteringAgg.Count > 0)
{
//Get list of filtering RLS tables on the detail table
List<string> rlsTablesFilteringDetail = new List<string>(); //RLS tables that filter the detail table
//beginTable might be the From or the To table in TOM Relationship object; it depends on CrossFilterDirection.
referencedTableCollection = new RelationshipChainsFromRoot();
foreach (Relationship filteringRelationship in detailTable.FindFilteredRelationships(checkSecurityBehavior: true))
{
// EndTable can be either the From or the To table of a Relationship object depending on CrossFilteringBehavior/SecurityBehavior
string endTableName = GetEndTableName(detailTable, filteringRelationship, out bool biDi);
RelationshipLink rootLink = new RelationshipLink(detailTable, _tables.FindByName(endTableName), true, "", false, filteringRelationship, biDi);
ValidateLinkForAggsRls(rootLink, referencedTableCollection, modelTablesWithRls, rlsTablesFilteringDetail);
}
//For each agg table, check any RLS filter tables also covers the detail table
foreach (string rlsTableFilteringAgg in rlsTablesFilteringAgg)
{
if (!rlsTablesFilteringDetail.Contains(rlsTableFilteringAgg))
{
foundViolation = true;
warningMessage = $"Removed aggregations on table {table.Name} because summarization {column.AlternateOf.Summarization.ToString()} on column {column.Name} (considering changes) RLS filter on table {rlsTableFilteringAgg} that filters the agg, but does not filter detail table {detailTableName}.\n";
break;
}
}
}
}
if (!foundViolation && column.AlternateOf != null)
{
/* Rule 10: Relationships between aggregation tables and other (non-aggregation) tables are not allowed if the aggregation table is on the filtering side of a relationship (active or inactive relationships).
This rule applies whether relationships are weak or strong, whether BiDi or not [including to-many BiDi, not just to-one]
*/
//beginTable might be the From or the To table in TOM Relationship object; it depends on CrossFilterDirection.
RelationshipChainsFromRoot referencedTableCollection = new RelationshipChainsFromRoot();
foreach (Relationship filteringRelationship in table.FindFilteringRelationships())
{
// EndTable can be either the From or the To table of a Relationship object depending on CrossFilteringBehavior/SecurityBehavior
string endTableName = GetEndTableName(table, filteringRelationship, out bool biDi);
Table endTable = _tables.FindByName(endTableName);
if (endTable != null)
{
bool endTableContainsAggs = false;
foreach (Column col in endTable.TomTable.Columns)
{
if (col.AlternateOf != null)
{
//End table has at least 1 agg so we are good
endTableContainsAggs = true;
break;
}
}
if (!endTableContainsAggs)
{
foundViolation = true;
warningMessage = $"Removed aggregations on table {table.Name} because summarization {column.AlternateOf.Summarization.ToString()} on column {column.Name} (considering changes) the agg table is on the filtering side of a relationship to a table ({endTable.Name}) that does not contain aggregations, which is not allowed.\n";
break;
}
}
}
}
if (!foundViolation && column.AlternateOf != null && detailTable != null)
{
/* Rule 3: Chained aggregations are disallowed
*/
foreach (Column detailColumn in detailTable.TomTable.Columns)
{
if (detailColumn.AlternateOf != null)
{
foundViolation = true;
warningMessage = $"Removed aggregations on table {table.Name} because summarization {column.AlternateOf.Summarization.ToString()} on column {column.Name} (considering changes) the detail table {detailTableName} also contains aggregations, which is not allowed.\n";
break;
}
}
}
}
}
//Clear all aggs on the agg table
if (foundViolation)
{
_parentComparison.OnValidationMessage(new ValidationMessageEventArgs(warningMessage, ValidationMessageType.AggregationDependency, ValidationMessageStatus.Warning));
foreach (Column column in table.TomTable.Columns)
{
if (column.AlternateOf != null)
{
column.AlternateOf = null;
}
}
}
}
}