Microsoft Office Tutorials and References
In Depth Information
Chapter 11: Developing Your Data Model
So try thinking a bit about the end-to-end process. Where does the source data reside? How should
that data be organized? What calculations do you need to perform? How will those results be fed to
the dashboard? How will the dashboard be updated?
Obviously, the answers to these questions are situation-specific. But here is a good place to start.
Separating the data, analysis, and presentation layers
One of the key concepts of a data model is the organization of data into three layers: data, analysis,
and presentation. The basic idea is that you don’t want your data to become too tied into any one
particular way of presenting that data.
For example, think about a business invoice. The financial data on that invoice is not the true source
of that data. It’s merely a presentation of the actual data that’s stored in some database. That data
can then be organized and presented to you in many ways: in charts, in tables, on dashboards, or
even on websites. This sounds obvious, but Excel users often fuse the data, analysis, and presentation
layers together into one final project.
The best approach is to create three layers in your data model. You can think of these layers as three
different worksheets in an Excel workbook. Sometimes this also is a good way to organize your data
model. One sheet holds the raw data that feeds your report, one sheet serves as a staging area where
the calculations are performed, and one serves as the final presentation. Figure 11-1 illustrates the
three layers of an effective data model.
You don’t necessarily have to place your data, analysis, and presentation layers on
different worksheets. In small data models, you may find it easier to place your data in one
area of a worksheet while building your staging tables in another area of the same
Why even bother with the three-tiered data model? Imagine that you have only the table in Figure
11-2. Hard-coded tables, such as this one, are common. This table is a combination of data,
calculations, and presentation. Not only does this table tie you to a specific analysis but also there’s little to
no transparency into the content of the analysis. Also, what happens when you need to report by
quarters or when another dimension of analysis is needed? Do you import a table that consists of
more columns and rows? How does that affect your data model?
Taking the easy route and avoiding the extra work of separating the data, analysis, and presentation
layers can lead to more problems later. Take a moment to review each layer and the role it plays in
building out your dashboard model.
The data layer
As you can see in Figure 11-1, the data layer consists of the raw data that feeds your dashboard. The
data in the data layer is typically used “as is” from whatever source you derived it from. That is to say,
you perform no analysis in the data layer.