A better way to model inheritance
I've been playing around with a report model we use internally here in the SQL Server product group. At a conceptual level, the data being modeled makes heavy use of inheritance (EntityA "is a" EntityB), but in working with the model and with Report Builder, I'm finding some significant advantages to using the Role.ExpandInline property instead of the Entity.Inheritance property. As I described in an earlier post, both are options for denormalizing or "flattening" the underlying schema.
How to do it
Using the Role.ExpandInline property to model inheritance is just as easy as, if not easier than, using the Entity.Inheritance property. Remember that when a relationship is defined in the DSV between the parent entity and the child or derived entity, a pair of roles are generated in the model initially and bound to that relationship.
When using Entity.Inheritance to model inheritance, you need to:
- Delete the generated roles
- Set the Inheritance property on the child entity to point to the parent entity
- Set the Inheritance binding property to the relationship defined in the DSV
When using Role.ExpandInline to model inheritance, all you need to do is:
- On the child->parent role, set ExpandInline=true.
- On the parent->child role, rename the role "As <child entity name>".
Also, make sure the child->parent role has Cardinality=One, and the parent->child role has Cardinality=OptionalOne. These should be true regardless of whether you choose to expand the role inline.
Advantages of using Role.ExpandInline
Beyond the initial convenience in expressing an inheritance relationship using Role.ExpandInline, there are several more substantial advantages I see to modeling the concept this way:
1. You can choose where the ancestor entities' fields appear in the field list of the current entity.
When using Entity.Inheritance, all ancestor fields are automatically inserted at the top of the field list of the child entity. The main problem with this is that the auto-generated Count field for the derived entity is no longer at the top of the list where users expect it to be. Instead, they will see the Count field for the most distant ancestor first, with the Count fields for the intervening entities and finally the current entity scattered further down the field list.
When using Role.ExpandInline, you can move the role that represents the inheritance to at any position in the field list of the current entity (even a sub-folder if that makes sense), and the ancestor entities’ fields will be inserted there.
Example: Suppose we have a Person entity whose field list starts with #Persons (typical). Suppose we also have an Employee entity that inherits from Person. Ideally, the Employee field list will be displayed as #Employees, followed by Person fields, and finally other Employee fields. When using Entity.Inheritance, this is not supported. When using Role.ExpandInline, this is easy – simply move the Employee->Person role immediately below the #Employees attribute in the Employee entity field list, and the Person fields will be “expanded inline” there.
2. You have fine-grained control over which of the parent entity's fields are visible in the current entity.
When there is more than one derived entity, it is common for many fields on the parent entity to be relevant to only some of the child entities. If Entity.Inheritiance is used, there is no way to prevent all parent entity fields from being shown all the time. This can be confusing in the cases where they are irrelevant.
When using Role.ExpandInline, you can use the HiddenFields collection on the role that represents the inheritance to control exactly which fields from the parent entity will be visible in the child entity.
Example: Suppose the Person entity mentioned above has three derived entities: Employee, CustomerContact, and VendorContact. It also contains (among other things) a #Persons field and a LastContacted field. The former is relevant to all Persons, but only when they are being treated as Persons. When treated as a derived entity, the derived entity Count field should be used instead. Also, suppose the LastContacted field is present on Person because it is common to CustomerContact and VendorContact, but it is not relevant to Employee. Ideally, the #Persons and LastContacted fields would be omitted in the Employee field list, while only the #Persons field would be omitted from the CustomerContact and VendorContact field lists. When using Entity.Inheritance, this is not supported. When using Role.ExpandInline, this is easy – just use the Role.HiddenField collection in each child entity to define exactly which parent entity fields are hidden in that context.
3. You have fine-grained control over which non-direct ancestor entities related by inheritance (“uncles”) are accessible from the current entity.
When using Entity.Inheritance, you can use the Entity.DisjointInheritance property to control whether the special “As <entity-name>” pseudo-roles are displayed for treating instances of the current entity as instances of a non-direct ancestor entity related by inheritance. However, this is an all-or-nothing option. If DisjointInheritance is false, all non-direct ancestor entities related by inheritance are displayed in all child entity contexts; if true, none of them are displayed in any child entity context.
When using Role.ExpandInline, you can use the Role.HiddenFields collections to define exactly which inheritance roles from the parent entity are hidden in for each child entity context.
Example: Suppose that by company policy, Employees were allowed to be customers, but not vendors. On the other hand, it was perfectly fine for non-employee vendors to also be customers. Because of these constraints, it would make sense to display an “As CustomerContact” role in the Employee context, but not “As VendorContact”. In contast, we would want to display an “As CustomerContact” role in the VendorContact context. When using Entity.Inheritance, this is not supported. When using Role.ExpandInline, this is easy – just use the Role.HiddenFields collections in each child entity to define exactly which inheritance roles from the parent entity make sense in that context.
4. You can prevent the fields of all descendant entities from being added to the field list for the current entity.
When using Entity.Inheritance, the fields from all descendant entities are always inserted at the bottom of the field list of the current entity. Even with only a small number of derived entities, the field list can quickly become quite long and confusing, since there is no clear indicator which fields are associated with which derived entity. The list may even contain multiple fields with the same name (but different meanings), which would be especially confusing.
When using Role.ExpandInline (on the role from the child entity to the parent entity), the reverse role from the parent entity to the child entity is just a role by default, so none of the fields for the child entity appear in the parent entity context. For clarity, you can rename the reverse role “As <entity-name>”, similar to the pseudo-roles metioned earlier.
Example: Continuing the examples above, the Person->Employee role should be renamed “As Employee”, the Person->CustomerContact role should be renamed “As CustomerContact”, and the Person->VendorContact role should be renamed “As VendorContact”.
5. You can inherit from more than one entity.
When using Entity.Inheritance, you can specify at most one parent entity related by inheritance. This is often not an important limitation, but occasionally the data really demands that an entity inherit from more than one parent entity, because it just makes a lot more sense to present it to the user that way.
When using Role.ExpandInline, there is (obviously) no constraint on how many roles can be expanded. Inherit from as many entities as you want.
Example: Suppose the Employee entity inherits from both Person and ProjectResource. When using Entity.Inheritance, this is not supported. When using Role.ExpandInline, this is easy – just expand both roles.
6. You can change your mind later about which relationships to model as inheritance.
When using Entity.Inheritance, the queries generated against your model contain no explicit navigation between entities related by inheritance. This means that adding or removing inheritance later can break existing queries. Now, technically, adding inheritance later will not break queries if you keep around the old role that the inheritance replaced (hidden of course to avoid confusion). But there is no workaround for the opposite situation – if you want to remove inheritance later, you can certainly add a new role to represent the relationship, but if you keep around the inheritance as well to avoid breaking existing queries, there is no way to hide it, and the resulting behavior will be very confusing to users.
When using Role.ExpandInline, you can change your mind at any time. The queries will be the same whether you choose to expand the role or not, so modeling a relationship as inheritance (or not) will have no impact on either new or existing queries.
Disadvantages of using Role.ExpandInline
At this point, I can only think of one disadvantage to using Role.ExpandInline to model inheritance.
1. Users will not have the “Is A” operator available in the filter dialog.
When using Entity.Inheritance, a special “Is A” filter operator is available in the filter dialog when filtering on entities that participate in inheritance relationships. This operator allows you to test whether a particular instance of one entity maps to an instance of another entity related somehow by inheritance. Direct ancestors are not included in the list of options, as it is assume answer is always “true” in those cases. However, descendents and non-direct ancestors (uncles, cousins, etc.) are listed.
When using Role.ExpandInline, this operator is not available. Instead, the user would need to add the related entity and check if it is “empty” (or null).
Example: Suppose the user wants to create a filter condition that tests whether a particular Person is also a VendorContact. When using Entity.Inheritance, the user could use the “Is A” filter condition operator to do so. When using Role.ExpandInline, the “Is A” operator would not be displayed. The user would have to drag in the “As Vendor Contact” role instead, and check if it is empty (or null).
Conclusion
As you can see, there seems to be a clear winner here. So much so, that we are considering deprecating the current Entity.Inheritance construct in a future release, and creating a new construct that explicitly models inheritance similar to the ExpandInline approach. When/if we do this, we would presumably also remove the one current disadvantage associated with it by adding explicit support for it in the filter dialog.
Comments
- Anonymous
March 31, 2009
This is a very good article. I am new to SQL and Reporting Services and it really helped me understand how I can use inheritance to denormalize the data for report models.However, I did come across a problem while setting up Role Expansion type of inheritance on the entities in my report model.I followed the instructions below""On the child->parent role, set ExpandInline=true.On the parent->child role, rename the role "As <child entity name>"."When I publish the model, I get the following warning:"The Relation property of the Role "Mrc Orders" refers to the Source end of the Relation "ORDER_SNAPDOC_FK", which is not bound to a set of uniquely constrained columns for the Table "dbo_Mrc_Orders". Roles with Cardinality of One or OptionalOne require relations bound to uniquely constrained columns of the table."(Mrc_Orders is the name of the parent table in this case).Any ideas on why this is happening would be greatly appreciated.