Role-playing dimensions

[min.emerg]

Hi All,

I have a dimension (called AllocatingRegion) which stores the regions to which the transactions in my fact table can be allocated. Similarly, I also have a dimension (OriginatingRegion) which is also linked to the fact, and details the regions from which the transactions originated. These dimensions are structurally identical, and contain common but not identical data.

Would it be advisable to include the OriginatingRegion dimension's data in the AllocationRegion dimension if I am certain that the data in the OriginatingRegion dimension will always be a subset of the data in the the AllocationRegion dimension? If I am able to role-play this dimension, I would then have to add some sort of bit field that will enable me to distinguish between these two record types. Would the addition of this field not detract from the degree to which the dimension is actually role-playing, given that the data of the two types is not always identical?

Thanks.

[ngalemmo]

If you had region X and if, regardless of role, the data for region X is the same, then it should be one table, with different FKs off the fact depending on role and one row for region X. The role would be implied by the foreign key.

If the data for region X is different depending on the role, then the decision becomes more complicated... generally two separate tables are easier to understand and maintain, but it also raises the question of why would they be different?

Are the differences more related to the nature of the role, rather than the region... and if so, would those attributes be better suited in another dimension, independent of region?

[min.emerg]

Hi ngalemmo,

Thanks for the response.

The two dimensions differ more in their purpose from the point of view of the business than they do in terms of the data that they contain. Although dimension X will be a subset of (and identical in structure to) dimension Y, I think that I might split them into separate dimensions in an attempt to keep the design simple. Each dimension will only contain 20 - 30 records.

Would adding a field to the role-playing dimension to distinguish between records of type X and type Y be considered bad design? Does this addition not somewhat highlight the difference in the way that this similar data is used?

[ngalemmo]

If they are the same thing, then breaking them out into two tables is a bad design. Dimensional roles are determined by the context applied against facts, they have nothing to do with the dimension itself. If you try to create different physical dimension tables for every role, you basically wind up with a unmanageable mess. Also, you would not put flags in the dimension table to identify which roles they play, as this is unnecessary because the FK column in the fact identifies the specific role in context with the fact.

If the issue is presenting a model using whatever drawing tool you have, it is not uncommon to show different dimensions for each context, but the physical database is implemented using a single table for all rows. The rows themselves are idependent of the role.

[min.emerg]

Hi ngalemmo,

Thanks again for the response, and for clearing this up.

Am I correct in saying that it is not the purpose of a dimension to validate fact records by limiting the joins that are possible between the dimension and fact?

For instance, consider a situation where we have an Employee dimension that is role-playing, and a fact table that has many foreign keys to it (for instance, fact.ManagerKey, fact.SupervisorKey - seeing as all managers and supervisors are just employees). The Employee dimension is unable to restrict which employees are managers and supervisors because it doesn't distinguish between the two. If someone is both a manager and supervisor, and is later demoted to only being a supervisor, there is nothing to stop us from using this person's record in the employee dimension to link to both fact.ManagerKey and fact.SupervisorKey. Is this a problem?

I understand that the role is made apparent by the foreign keys from the fact to the dimension, and that if we do end up linking this person to both employee roles when they are only allowed to have a single role (supervisor, in this case), then our source data likely has problems.

[BoxesAndLines]

If a region is only participates in one role then you can add the column. But Nick is correct. It is bad design because the type of region is at a lower grain than your dimension. I can role type a region via a relationship as Allocating or Originating. I only have one column to indicate what role the region is playing. So if an Allocating region is never an Originating region and vice versa, you can add your column.

For your employee dimension, an employee can be an employee, a manager, or even a supervisor.

[ngalemmo]

For instance, consider a situation where we have an Employee dimension that is role-playing, and a fact table that has many foreign keys to it (for instance, fact.ManagerKey, fact.SupervisorKey - seeing as all managers and supervisors are just employees). The Employee dimension is unable to restrict which employees are managers and supervisors because it doesn't distinguish between the two. If someone is both a manager and supervisor, and is later demoted to only being a supervisor, there is nothing to stop us from using this person's record in the employee dimension to link to both fact.ManagerKey and fact.SupervisorKey. Is this a problem?

Its an issue for the HR system, not the data warehouse. If you receive a transaction into the data warehouse that identifies employee X as the manager relating to the transaction, why would you store anything different? Right or wrong, this is what the source system feed is telling you. If there is a problem with it, it needs to be corrected in the source.

Surely you can have attributes on the employee that identify their position and role, and you can use such attributes to produce data audits (to expose potential errors in the source data), but, generally speaking, why should the DW care?

In other words, you don't want to 'restrict' anything. The DW needs to be accurate, not the arbitor of truth. If there is a problem with the data, leverage the DW to discover and report it. Leave it up to the operational people to review and correct it in the source.

[min.emerg]

Thanks again for the responses - they were very helpful

[Sponsored content]