First and foremost, if you are not already familiar with the Abbott and Costello routine by this name, go watch it. Now. I’ll post some intelligent things and some funny things on this blog, but nothing will match the combination of intelligence and humor of this classic routine.

For those still with us, or who’ve returned from the abyss of infinite videos that is youtube, we’ll  continue with our regularly scheduled program.

I’ve worked on a number of data warehouse and data mart projects over the years, and being a simple man, I’ve come across what I think is an easy way to work with your business users to get to the heart of the key data elements you need to contain within the warehouse. Believe it or not, you are already familiar with this too – you probably learned the six interrogatives in elementary school.

We’ll continue with the baseball theme and cover them in order. NOTE: As discussed in a previous article, before we begin, we must declare the grain. For this exercise, the grain will be a Plate Appearance. Also note that I’d probably design this a bit differently, but wanted to include some basic examples of what to look for as answers and where in the star schema they should be placed.

Who?

• Who is the batter? (DIM_BATTER)
• Who is the pitcher? (DIM_PITCHER)

What?

• What teams were involved? (DIM_TEAM)
• What was the weather like? (DIM_WEATHER)

When?

• When was the game? (DIM_DATE)
• When in the game? (PLATE_APPEARANCE_FACT.INNING)
• When in the inning? (PLATE_APPEARANCE_FACT.BATTER_SEQUENCE_NUMBER)
• When in the day? (PLATE_APPEARANCE_FACT.DAY_NIGHT_FLAG)

Where?

• Where was the game? (DIM_FIELD)

How?

• How did the Plate Appearance end? (DIM_OUTCOME)

Why?

The final question is the key. This is where analytics begin. If you have designed the warehouse correctly, your data analysts will be able to slice, dice, pivot, drill, poke, and prod their way through the data to begin understanding what is driving the business. While you don’t have to directly answer this question, if you’ve done your work well, then your end users will be able to answer it. Maybe a batter is better against left-handed pitchers, or in night games. Maybe a pitcher is better on nights where the barometric pressure is low. If you’ve built enough details into your warehouse, then your data analysts should be able to discern a pattern.