Part III: Creating Charts and Other Visualizations
makes judicious use of empty space for formatting, repeats data for aesthetic purposes, and
presents only high-level analysis.Although a spreadsheet report may look nice, it doesn’t make for an effective data model.
Why? The primary reason is that these reports offer no separation of data, analysis, and
presentation. You’re essentially locked into one analysis.Flat data files lend themselves nicely to data models
The next type of file format is a flat file. Flat files are data repositories 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. Furthermore, there’s no extra
spacing, and each row (or record) corresponds to a unique set of information.Flat files lend themselves nicely to data modeling in Excel because they can be detailed
enough to hold the data you need and still be conducive to a wide array of analysis with
simple formulas: SUM, AVERAGE, VLOOKUP, and SUMIF, just to name a few.Avoiding turning your data model into a database
In an effort to have as much data as possible at their fingertips, many Excel users bring
into their spreadsheets every piece of data on which they can lay their hands. You can spot
these people by the 40-megabyte files they send through e-mail. You’ve seen these spread-
sheets: two tabs that contain the presentation and six hidden tabs that contain thou-
sands of rows of data (most of which isn’t used). They essentially build a database in their
spreadsheet.What’s wrong with utilizing as much data as possible? Well, here are a few issues:■ Aggregating data within Excel increases the number of formulas. If you’re
bringing in all raw data, you have to aggregate that data in Excel. This inevita-
bly causes you to increase exponentially the number of formulas that you have to
employ and maintain. Remember that your data model is a vehicle for presenting
analyses, not processing raw data. The data that works best in reporting mecha-
nisms is what’s already been aggregated and summarized into useful views that can
be navigated and fed to dashboard components. Importing data that’s already been
aggregated as much as possible is far better. For example, if you need to report on
Revenue by Region and Month, there’s no need to import sales transactions into
your data model. Instead, use an aggregated table consisting of Region, Month, and
Sum of Revenue.■ (^) Your data model will be distributed with your dashboard. In other words,
because your dashboard is fed by your data model, you need to maintain the model
behind the scenes (likely in hidden tabs) when distributing the dashboard. Besides
the fact that it causes the file size to be unwieldy, including too much data in your
data model can actually degrade the performance of your dashboard. Why? When
you open an Excel file, the entire file is loaded into memory or RAM to ensure quick
data processing and access. The drawback to this behavior is that Excel requires