VARIATIONS ON A THEME

only for RuBoard - do not distribute or recompile

One way of ensuring that the data warehouse correctly joins fact data to dimensions and dimensional hierarchies is to detach the superordinate dimensions from the hierarchy and reattach them directly to the fact table.

Pursuing the example presented earlier, in which a salesperson is able to change departments and a department is able to move from one site to another, what follows is the traditional approach of resolving many-to-many relationships by the use of intersection entities, as the diagram in Figure 4.11 shows.

Figure 4.11. Traditional resolution of m:n relationships.

This approach need not be limited to the resolution of time- related relationships within dimensional hierarchies. It can also be used to resolve the problem of time-varying attributes within a dimension. So if it were required, for instance, to track a salesperson's salary over time, a separate dimension could be created as shown in Figure 4.12.

Figure 4.12. Representation of temporal attributes by attaching them to the dimension.

The identifier for the salary dimension would be the salesperson's identifier concatenated with a date. The salary amount would be a nonidentifying attribute.

  Salary  (  Salesperson_Id, StartDate  , EndDate, Salary_Amount)

The same approach could be used with all time-varying attributes.

Another approach is to disconnect the hierarchies and then attach the dimensions to the fact table directly, as is shown in Figure 4.13.

Figure 4.13. Representation of temporal hierarchies by attaching them to the facts.

This means that the date that is attached to each fact will, automatically, apply to all the levels of the dimensional hierarchy. The facts would have a foreign key referring to each of the levels of the hierarchy.

We are left with a choice as to how we treat the changing attributes. The salary attribute in Figure 4.12 could be treated in the same way as before (i.e., as a separate dimension related to the salesperson dimension). Alternatively, it could be attached directly to the fact table in the same way as the other dimensions as shown in Figure 4.14.

Figure 4.14. Representation of temporal attributes by attaching them to the facts.

A further variation on this approach is to include the salary amount as a nonidentifying attribute of the fact table, as follows:

  Sales  (  Site_Id, Dept_Id, Salesman_Id, Time_Id,  Salary_Amount, Sales_Quantity, Sales_Value)

This approach eliminates dimensional hierarchies and, therefore, removes the problem with type two of extraneous cascaded inserts when changes occur in the hierarchy. However, this is by no means a complete solution as it does nothing to resolve the problems of time within the hierarchical structures. The question How many customers do we have? is not addressed by this solution. It is presented as a means of implementing Type 2 without incurring the penalties associated with slowly changing hierarchies.

There is a further drawback with this approach. The tables involved in the hierarchy are now related only via the fact table. Therefore, the hierarchy cannot be reconstructed so long as no sales records exist for any relationship between the dimensions.

only for RuBoard - do not distribute or recompile