Microsoft Office Tutorials and References
In Depth Information
Chapter 11: Developing Your Data Model
However, you’ll find that not all data makes for effective data modeling. For example, the data shown
in Figure 11-3 would make it impractical to apply any analysis outside what’s already there. For
instance, how would you calculate and present the average of all bike sales? How would you
calculate a list of the top ten best performing markets?
Figure 11-3: Not all data can be a good source for your data layer.
With this setup, you’re forced into very manual processes that are difficult to maintain month after month.
Any analysis outside the high-level ones already in the report is basic at best — even with fancy formulas.
Furthermore, what happens when you’re required to show bike sales by month? When your data model
requires analysis with data that isn’t in the worksheet report, you’re forced to search for other data.
Ideally, you want your data layer to come in one of two forms:
➤ Flat data tables: Data repositories are organized by row and column. Each row corresponds to a
set of data elements, or a record . Each column is a field. A field corresponds to a unique data
element in a record. Figure 11-4 contains the same data as the data shown in Figure 11-3, but is in
flat data table format. Flat tables lend themselves nicely to data modeling in Excel because they
can be detailed enough to hold the data that you need and still be conducive to a wide array of
simple formulas and calculations in your analysis layer — SUM, AVERAGE, VLOOKUP, and SUMIF,
just to name a few. Later in this chapter, we discuss functions that come in handy in a data model.
Figure 11-4: A flat data table.