Microsoft Office Tutorials and References
In Depth Information
Chapter 11: Developing Your Data Model
➤ Tabular data set: Ideal for pivot-table-driven data models. Figure 11-5 illustrates a tabular data
set. Note that the primary difference between a tabular data set, as shown in Figure 11-5, and a
flat data file is that the column labels don’t double as actual data. For instance, in Figure 11-4,
the month identifiers are integrated into the column labels. In Figure 11-5, the Sales Period
column contains the month identifier. This subtle difference in structure is what makes tabular
data sets optimal data sources for pivot tables. This structure ensures that key pivot table
functions, such as sorting and grouping, work the way they should.
Figure 11-5: A tabular data set.
The analysis layer
The analysis layer consists primarily of formulas that analyze and pull data from the data layer into
formatted tables (commonly referred to as staging tables ). These staging tables ultimately feed the
reporting components in your presentation layer. In short, the sheet that contains the analysis layer
becomes the staging area where data is summarized and shaped to feed the reporting components.
This setup offers a couple of benefits:
➤ You can easily update the entire data model simply by replacing the raw data with updated
data. The formulas in the analysis tab then continue to work with the latest data.
➤ You can create any added analyses easily by using different combinations of formulas on the
analysis tab. If you need data that doesn’t exist in the data layer, you can add a column to the
end of the raw data without disturbing the analysis or presentation layers.
The presentation layer
The presentation layer is your storefront. It contains all the charts, visualizations, and dashboard
components that you want your audience to see. The presentation layer is the most flexible because you
can choose a plethora of tools, graphics, and charts to create the theme and style of your dashboard.
Also, because the presentation layer feeds from the analysis layer, the data needed for each
component is always consistent in content and format.