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 the work.

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 by hand.

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 MDM²?

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 want.

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 model.

Previous topic: "What is multidimensional modeling?"

Next topic: "What is Fact Technologies?"