So, given our fact set and our multidimensional model, what kinds
of analysis can we do? If I asked you to tell me the average maximum
daily temperature across all cities in the United States on Saturday,
June 26, 1999, could you find the answer with this table? Of course
you could. You could pick out all the rows that are associated with
that DAY element and average their MAX_TEMP values together.
If I asked you to tell me the average maximum daily temperature
in San Francisco for the month of June 1999, could you tell me?
Yes. You could pick out all the rows that have San Francisco in
the CITY column and days in June 1999 in the DAY column, then average
their MAX_TEMP values together.
What if I asked you to find the 5 highest maximum daily temperatures
in West Coast states for the last three years? Assuming you have
the last three years' worth of data in your table and a good map,
you could tell me.
What if I asked you to find the 3 lowest maximum daily temperatures
in states starting with a "C" that occurred on a Tuesday in either
Q3 1998 or January of 1999, where the city must have had an average
of at least 75 degrees last summer? You could do it by hand, but
by now you're probably wondering how you can get a computer to do
Getting a computer to do the work is actually very easy. The technology
we advocate uses a relational database to do it for you.
Our chosen platform allows you to use just about any database you
like to store and process your data–if you've heard of it,
it's probably supported.
We don't want to get into the guts of relational databases in
this FAQ. Just think of it as being you, but able to sort and sift
through millions of facts every second. Obviously, you'll get a
faster answer if the machine does the searching than if you do it
Go back and take a peek at our
original fact table diagram. Notice that each element in the fact
header relates the base fact to a different dimension. Notice that
this method of encoding allows you to answer questions that are
as detailed as you like.
What if we wanted to be able to include information about the
minimum daily temperature in each city? No problem! We could just
add another column to our fact table, call it MIN_TEMP, that records
that information. Can you remember what what this new column would
be called in MDM2?
The story told by each fact would now be something like "For City
A on Day B, the maximum temperature was X degrees, and the minimum
temperature was Y degrees." We can now start asking questions about
both kinds of base facts, like: For microclimates with seasonal
lows of 30 degrees or less in Winter 1998, what is, by city, their
average maximum temperature for the last two weeks?
You could also increase your analytic capabilities by adding more
elements to the fact header. Doing so would give you new dimensions
to your model and add new avenues of insight into your business.
For example, you might add an ATMOSPHERIC CONDITION attribute, which
would become the root of an Atmosphere dimension. All you would
need to do is add an ATMOSPHERIC CONDITION column to your table,
and record the various elements for the new attribute (such as Sunny,
Mostly Sunny, Partly Cloudy, Cloudy, Overcast, Fog, etc.).
How does the story change? What new kinds of analysis can we do?
Notice how adding a new dimension doesn't take anything away from
our previous capabilities. It dramatically expands our capabilities
in new and unexpected ways. "In South America, how many days occurred
that involved some cloud cover, where the minimum temperature was
above 60 degrees and the maximum temperature was below or equal
to 75 degrees?"
As you can tell, the kinds of analysis you can do are only limited
by the way you store your data in facts and model the world in your
multidimensional model. We are presenting an extremely simple model
with just one kind of fact to make this easy for you to learn, but
you can have as many dimensions, attributes, and fact types as you
In the real world, you would encode things that happen in your
business into facts that relate to your own view of the world. No
two businesses, even close competitors, will have the same data
Previous topic: "What
is multidimensional modeling?"
Next topic: "What
is Fact Technologies?"
a base fact.