This post has been republished via RSS; it originally appeared at: SQL Server Integration Services (SSIS) articles.
First published on MSDN on Dec 03, 2015
A domain-based attribute (DBA) can have as its value any member of the specified domain entity. In the next release, we add the ability to constrain this list of possible values by filtering on the value of another DBA within the entity.
Consider the following entity diagram:
The Customer entity has a DBA that references the City entity, which references the StateProvince entity. When adding/modifying Customers, it can be inconvenient to select from a potentially large list of Cities from all StateProvinces. One can ameliorate this by using the DBA picker control, in the Web UI’s explorer page, to filter the cities by State, or filter by any other desired combination of City attributes. But some users have expressed the desire to add a State DBA to the Customer entity, and to keep it in sync with and have it filter allowable values for the City DBA:
Once could argue that this is a bad way to model the data as the new StateProvince DBA is redundant, denormalized, and makes possible data inconsistency (i.e. Customer.City.StateProvince could mismatch Customer.StateProvince). But since this is a frequent customer request we add support for it, to include preventing data inconsistency.
Users can now add a filter to a DBA definition. In the above example, the filter would be added to the Customer.City DBA. The filter specifies:
1. The parent DBA (Customer.StateProvince), and
2. A Derived Hierarchy (DH) level the parent-child relationship between the two entities (StateProvince and City). In the above example, the user must define a DH that includes a level for the City.StateProvince DBA.
The below screenshot shows how one selects a Filter when creating/editing a DBA in the Admin Web UI. The Filter drop-down list is populated with all potential parent attributes that meet the above two conditions.
The below screenshot shows how the Explorer page for the Customer entity enforces the filter: for any selected Customer, the list of cities only includes those pertaining to the currently selected StateProvince:
The Excel add-in does likewise:
Note that when a parent attribute has a blank value, the child drop down list will only include members whose DH level parent is blank. (i.e. those members that appear under the “Unused” node in the DH explorer page). In the above example, when Customer.StateProvince is blank, the City drop down list shows Cities where City.StateProvince is likewise blank.
Entity explorer:
Hierarchy explorer:
Many-to-Many
The above City-StateProvince example shows a one-to-many DH level, but a filter may also reference a many-to-many (M2M) DH level. Consider the below example:
Each Product has a Model and a Color. But there exists a mapping entity, AllowedColor, that specifies which Colors are valid for which Models. To filter the list of possible values for the Product.Color DBA based on the value of the Product.Model DBA:
1. Create an M2M DH that groups Colors by Model, mapped by AllowedColor.
2. Add a filter to the Product.Color DBA that specifies Product.Model as the parent DBA and the M2M DH level as the parent-child relationship.
Recursive
A filter may also specify a recursive DH (RDH) level:
In the above example, each Customer has a primary and secondary contact person who is an Employee. Each Employee has a Manager. To constrain the SecondaryContact person to be an Employee who is managed by the PrimaryContact person:
1. Create a RDH that groups Employees by Manager
2. Add a filter to Customer.SecondaryContact that specifies Customer.PrimaryContact as the parent DBA and the above RDH level as the parent-child relationship.
Alternately, one could flip the constraint by instead adding the filter to PrimaryContact such that it must be an Employee that is managed by the SecondaryContact.
Filter Constraints
· An attribute filter may not specify a DH level that is hidden.
· Attribute filters can only be added to Leaf attributes, but not deprecated Consolidated or Collection attributes.
· Attribute filters may be chained. E.g. StateProvince filters City filters PostalCode.
· Attribute filters may not form a circular dependency. E.g. A filters B filters A.