Microsoft Office Tutorials and References
In Depth Information
Data Model Best Practices
lines of data. As time goes on, you build a robust process complete with all the formulas, pivot
tables, and macros you need to analyze the data that’s stored in your neatly maintained tab. Now
what happens after one year? Do you start a new tab? How do you analyze two data on two
different tabs as one entity? Are your formulas still good? Do you have to write new macros?
You can avoid such issues by importing only aggregated and summarized data that’s useful to the
core purpose of your dashboard.
Use tabs to document and organize your data model
Wanting to keep your data model limited to one worksheet tab is natural. In our opinion, keeping
track of one tab is much simpler than using different tabs. However, limiting your data model to one
tab has its drawbacks, including the following:
➤ Limits the quality of your analysis. Because only so much text can fit on a tab, using one
tab imposes real-estate restrictions that can limit your analyses. Consider adding tabs to your
data model to provide additional data and analysis that may not fit on just one tab.
➤ Makes for a confusing data model. When working with a large quantity of data, you need
plenty of staging tables to aggregate and shape the raw data so that it can be fed to your
dashboard components. If you use only one tab, you’re forced to position these staging
tables below or to the right of your data. Although this may provide all the elements needed
to feed your presentation layer, a good deal of scrolling is necessary to view all the elements
positioned in a wide range of areas. This makes the data model difficult to understand and
maintain. Use separate tabs to hold your staging tables, particularly in data models that
contain large quantities of data that take a lot of real estate.
➤ Limits the amount of documentation you can include. You’ll find that your data models
easily become a complex system of intertwining links among components, input ranges,
output ranges, and formulas. Sure, it all makes sense while you’re building your data model,
but try coming back to it after a few months. You’ll find that you’ve forgotten what each data
range does and how each range interacts with the final presentation layer. To avoid this
problem, consider adding a data model map tab to your data model. The map tab essentially
summarizes the key ranges in the data model and allows you to document how each range
interacts with the dashboard components in the final presentation layer. As you can see in
Figure 11-6, the data model map is nothing fancy; just a table that lists some key information
about each range in the model.
Figure 11-6: A data model map provides documentation that outlines how your data model works.