пятница, 19 сентября 2014 г.

Role-Playing Dimensions


This isn’t about dimensions performing Romeo or Juliet, but is meant to indicate that the same dimension can be used to act as multiple, similar dimensions. The obvious example is the time 

dimension, which can be used, or better, must be used to accommodate multiple occurrences of time and date. Looking at a typical sales fact table, you see order date, ship date, receive date, return date, and payment date—five dates, one dimension. Physically speaking, all these dates link to the same date dimension table; logically, of course, they don’t because you would end up with an empty result set in most of the cases where multiple date restrictions apply. Take for instance the query for retrieving the total order value of DVDs ordered in December 2007 that were not returned within five days after shipment. This query involves three types of dates, and when all the required date restrictions are being set on the same date dimension table, you end up with an empty result set, or at least a result set that doesn’t correctly answer the question. The SQL to illustrate the correct solution for this looks similar to the query we used when discussing table aliases (which is basically the same).


Now it’s easy to see that if you apply the last two restrictions to the same table, this would cause an empty result set; it’s not possible for a date to be bigger than the same date plus 5 days.
Another example related to the WCM database is the actor and director information. We could combine these two entities in a single role-playing artist or movie_person dimension. A lot of actors become directors later in their career and sometimes even the opposite occurs. Take for instance Quentin Tarantino, who started his career as a director and could later be found acting in his own movies as well.