Attributes that use the same lookup table: Attribute roles

Attribute roles allow you to use the same data to define and support two separate attributes. Suppose you define two attributes that have the same data definition but play different roles in your business model. For example, in the following image, notice that the attributes Origin Airport and Destination Airport are defined using the same lookup table, LU_AIRPORT, and column, AIRPORT_ID.

Although it makes sense to see JFK as either an origin or destination airport on a report, it is understood that destination airport data differs from origin airport data. You need to support the logical concepts of an origin airport and a destination airport, but you do not want to create two separate lookup tables with identical data. Creating two separate lookup tables would create redundancy, and thus take up more storage space and be harder to maintain.

When multiple attributes are defined using the same lookup table and column, the attributes are essentially playing different attribute roles. How an attribute plays multiple roles depends on the specific attribute.

The Origin Airport and Destination Airport attributes share the same attribute forms, or various aspects about them, such as description, location, and so on. In the fact table, however, a separate column exists for each of their roles; the fact columns are ORIGIN_AIRPORT_ID and DESTINATION_AIRPORT_ID, as shown below.

If a report designer places both the Origin Airport and Destination Airport attributes on a report to obtain the number of flights that originated from MIA and arrived at LGA, an empty result set is returned. This occurs because the SQL statement tries to obtain the description of an airport that is both MIA and LGA at the same time (Airport_ID = "MIA" AND Airport_ID = "LGA").

If you identify that one of your attributes needs to play multiple roles, you must create an attribute in the logical model for each of the roles, as explained in Specifying attribute roles. This ensures that a report with attributes playing multiple roles returns correct data.

In the following diagram, State is another example of an attribute that can have two roles since it relates to both the Vendor and Store attributes. In one case, it refers to the location of a vendor. In the other, it refers to the location of a store. The State attribute is therefore said to be playing two roles.

In an OLTP system, roles are most often implemented as a single table, as shown in the above diagram. In the data warehouse, a query involving both Vendor State and Store State needs to use the State table twice in the same query. For example, a report is created to display vendors from Arkansas who sold to New York stores. The results may be blank if the data warehouse structure was set up incorrectly. The SQL statement tries to obtain the description of a state that is both Arkansas and New York simultaneously, generating the empty result set.