Report model denormalization: Why, How, and When

Relational databases are often heavily normalized to improve performance, reduce storage requirements, and ensure data consistency. While performance may be relevant to report execution, neither of these reasons is relevant when presenting the schema to an end-user at design time. Hence denormalization is often a major feature of a well-designed SQL Server 2005 report model.

There are three ways of denormalizing the database schema in a report model to provide more intuitive experience for your end users. They are: entity inheritance, role expansion, and lookup entities.

Entity Inheritance
This type of denormalization should be used when one entity "is" another entity. For example, a Sales Person "is" an Employee, so users would like to see all the attributes and roles of an Employee whenever they navigate to Sales Person. It is also true at report design-time that any given Employee may also be a Sales Person, so the user may also want to see all attributes and roles of Sales Person whenever they navigate to Employee, just in case. In this release, Report Builder will always show the attributes and roles of all entities that are either direct ancestors of the current entity or direct descendents. Uncles, aunts, cousins, etc. are displayed only in the Advanced Explorer mode as special related nodes in the "Entities" panel of the explorer.

Entity inheritance is specified by setting the Inheritance property on a derived entity to point to its "parent" entity, and specifying the foreign key constraint in the underlying DSV that can be used to match up a derived instance with the correct instance of the parent. Note that model generation will initially create a Role to represent this relationship, and if you choose to use inheritance, you should delete the auto-generated role. More information on entity inheritance.

Specifying inheritance where none was specified previously will not break existing reports; doing the opposite will. Obviously, deleting the role that inheritance is meant to replace will also break existing reports. If this is an issue, consider hiding the deprecated role so it will cease to be displayed, but continue to function for existing reports.

Role Expansion
This type of denormalization should be used when an entity contains information that is really considered part of a related entity, but for purely storage-related reasons has been moved into a separate table. Two examples are sparse satellite tables (e.g. AdditionalCustomerInfo) and shared schema tables (e.g. ContactInfo or AddressInfo). In both cases, the table doesn't represent a separate "thing" in the user's mind. It's just a bunch of extra attributes. When Report Builder encounters a role on the current entity that is marked for inline expansion, it displays all attributes and roles of the related entity as if they were attributes and roles of the current entity.

Role expansion is specified by setting the ExpandInline property on a role to True. Note that for shared schema tables (e.g. ContactInfo), you should also use the HiddenFields property on the role to hide the roles to other entities that use the same schema.

Changing the value of the ExpandInline property will not break existing reports and thus may be done at any time.

Lookup Entities
This type of denormalization should be used when an entity corresponds to a "lookup" table, i.e. one that exists only to define a set of valid values for some column on another entity or entities. Classic examples of lookup tables are Country, Status, Category, etc. Note that lookups often come in a series (e.g. Subcategory->Category), often referred to as a hierarchy. When Report Builder encounters a role whose related entity is a lookup entity, it displays the identifying attribute of that entity as if it were an attribute on the current entity. If any roles on the lookup entity are marked for lookup promotion, and their related entities are also lookup entities, their identifying attributes are "promoted" up to the current entity as well.

A lookup entity is specified by setting the IsLookup property on an entity to True. For a hierarchy or series of lookup entities, set this property to True on all of the lookup entities, and set the PromoteLookup property on the intervening roles to True as well. You may also want to set the ContextualName property to Merge or Role on the identifying attribute of a lookup entity, if the attribute has a generic name like "Name". More information on lookup entities. Also, if you have a column in your database that does not have a lookup table, but you still want users to be able to choose values from a list, set the ValueSelection property to Dropdown or List on the corresponding attribute in the model.

Changing the value of the IsLookup, PromoteLookup, ContextualName, or ValueSelection properties will not break existing reports and thus may be done at any time.

These three methods should allow you to create a very natural and intuitive report model navigation experience for your end users.

Comments

• Anonymous
  September 28, 2006
  Many factors combine to make report models highly likely to change and evolve over time. Sometimes the...
• Anonymous
  June 08, 2008
  Almost every post I made about Report Builder, be it the pros and cons of creating a report model on
• Anonymous
  March 05, 2009
  I've been playing around with a report model we use internally here in the SQL Server product group.
• Anonymous
  March 05, 2009
  I've been playing around with a report model we use internally here in the SQL Server product group.
• Anonymous
  March 05, 2009
  Many factors combine to make report models highly likely to change and evolve over time. Sometimes the