Multidimensional modeling (MDM) gets its name from the idea of
dimensions. Dimensions are categories of categories. If that
doesn't make sense, don't worry. It's easier to show dimensions
than to explain them with words, so look at the example below.
In our maximum daily temperature table, there
are two dimensions represented. One is related to Geography, and
one is related to Time. Each base fact (the maximum temperature
recorded) is related to a CITY and a DAY. CITY and DAY are each
An attribute is a category. CITY is a category idea. There
are many things in the real world called "cities." Los Angeles,
Seattle, Phoenix, and San Francisco are just a few actual cities.
In MDM, each of these is an attribute element (or simply
"element"), an actual instance of the category idea CITY.
What are the elements of the attribute DAY? A common beginning
mistake is to think of the days of the week: Sunday, Monday, Tuesday,
Wednesday, Thursday, Friday, and Saturday. In actuality, a day is
a uniquely identifiable segment of time that is one day in length.
An example would be Saturday, June 26, 1999. It is impossible for
two different days to both be Saturday, June 26, 1999.
Using this table, it is easy to find out information about the
maximum daily temperatures for any group of cities or group of days.
Are cities the only way we think about geography? Are days the only
way we think about time? Of course not. Both the Geography dimension
and Time dimension will have other attributes in them. The exact
attributes (and the elements they contain) will depend on how we
choose to model the world.
For example, we might want to look at our temperature data in
terms of states. Every CITY is part of a STATE. We can therefore
create a new attribute, STATE, and add it to the Geography dimension.
When we do, the model for the Geography dimension will look like
Each box represents an attribute. The line between the boxes represents
the relationship between the two attributes. In this case,
the relationship is one-to-many. One STATE relates to many
CITIES. One-to-many relationships are very common in multidimensional
Many-to-many relationships are also common. If we wished
for it to be possible for one CITY to belong in more than one STATE1,
we would use this type of relationship. Let's assume that the US
Post Office's method of splitting cities on state borders into separate
cities for mail delivery purposes is good enough, and stick with
a one-to-many relationship.
What if I wanted to look at my data by COUNTY? Where would a COUNTY
attribute go on the model we have so far? Also, what if someone
in your company wishes to look at the data by "microclimate", where
a microclimate is some area of geography that can cover several
neighboring cities, and generally is not bounded by state lines?
Here's a hint: You'll need two more attributes in your Geography
dimension. Take a second to sketch it out, then look at the solution
we would recommend.
Don't peek! When you're ready, click
here to see the answer.
Note that for each element in any attribute, there are several
facts that relate to it. On a given day, there will be many cities
worth of data recorded. For a given city, there will be many days
worth of data. For a given state, there will be many counties within
it, each of which will contain many cities, each of which will have
many facts associated with it. The independent nature of fact header
elements is at the heart of multidimensional modeling's power, because
it allows for extremely flexible multidimensional analysis.
Previous topic: "What
is a fact?"
Next topic: "What
is multidimensional analysis?"
might wish to do this if we want to consider cities more as population
densities than map boundaries. Many cities (e.g. St. Louis, Kansas
City, Tahoe) are on the border between two states, and "spill across"
from one state to another. If you're interested in the details of
how this changes the data model and the database schema requirements
(and we assume you are since you're still reading the footnote),
contact us and we'll discuss it.