Minnu's Blog on Informatica & Data warehouse concepts


Monday, June 16, 2008

Dimension Roles (or) Data Warehouse Role Models

What to do when a single dimension appears several times in the same fact table?
Hopefully you dont create that dimension multiple time & load it multiple times. Are you?
A role in a data warehouse is a situation in which a single dimension appears several times in the same fact table. This can happen in a number of ways. As an example take Time dimension for some Retailer. In certain kinds of fact tables, Time can appear repeatedly. For instance, we may build a fact table to record the status and final disposition of a customer order. This kind of fact table is called an accumulating snapshot fact table where a fact record is updated multiple times based on the sequence of events i.e. happening in OLTP untill that fact record is complete (all col's are not null) . The dimensions of this table could be Order Date, Packaging Date, Shipping Date, Delivery Date, Payment Date, Return Date, Refer to Collection Date, Order Status, Customer, Product, Warehouse, and Promotion.
The simplest or only solution is we need to fool SQL into believing that there are seven independent date dimension tables by creating views/synonyms on same date dimension. We even need to go to the length of labeling all of the columns in each of the tables uniquely. If we don't label the columns uniquely, we get into the embarrassing position of not being able to tell the columns apart if several of them have been dragged into a report.
Another scenario is when you have a datamart for some XYZ Airways which also offers voyages. we have a requirement to represent journeys of a customer(while on some voyage program or say least a Connected Flight) all needed to have at least four "port" dimensions to properly describe the context of a journey segment. The dimensions of a frequent flyer flight segment fact table need to include Flight Date, Segment Origin Airport, Segment Destination Airport, Trip Origin Airport, Trip Destination Airport, Flight, Fare Class, and Customer.

The four Airport dimensions are four different roles played by a single underlying Airport table. We build and administer these exactly the way we did the seven Time tables in the previous example.
The telecommunications industry has many situations requiring the use of role models. With the advent of deregulation, a number of competing entities may all extract revenue from a single phone call. On a single call, these entities might include Source System Provider, Local Switch Provider, Long Distance Provider, and Added Value Service Provider.

These four entities need to be dimensions on every call. In the complex and evolving telecommunications industry, it may be very difficult and confusing to maintain four different partially overlapping tables of business entities. Some business entities will play several of these roles. It will be far easier to keep a single Business Entity table and use it repeatedly within a data warehouse role model framework