Microsoft Office Tutorials and References
In Depth Information
Data Model Best Practices
You can include any information you think appropriate in your data model map. The idea is to give
yourself a handy reference that guides you through the elements in your data model.
Test your data model before building
This best practice is simple. Make sure that your data model does what it’s supposed to do before
building dashboard components on top of it. In that vein, here are a few things to watch for:
➤ Test your formulas to be sure that they’re working properly. Make sure your formulas
don’t produce errors and that each formula outputs expected results.
➤ Double-check your main data to be sure that it’s complete. Check that your data table
has not truncated when transferring to Excel. Also, be sure that each column of data you
need is present with appropriate data labels.
➤ Make sure all numeric formatting is appropriate. Be sure that the formatting of your data
is appropriate for the field. For example, check to see that dates are formatted as dates,
currency values are formatted properly, and that the correct number of decimal places is
displayed where needed.
The obvious goal here is to eliminate easily avoidable errors that may cause complications later.
Speaking of documenting your data model . . .
Another way to document the logic in your data model is to use comments and labels liberally. It’s
amazing how a few explanatory comments and labels can help clarify your worksheets. The general
idea here is that the logic in your model should be clear to you even after you’ve been away from
your data model for a long period of time.
Also, consider using colors to identify the ranges in your data model. Using colors in your data
model enables you to quickly look at a range of cells and get a basic indication of what that range
does. The general concept behind this best practice is that each color represents a range type. For
example, you could use yellow to represent staging tables used to feed the charts and the tables in
your presentation layer. You could use gray to represent formulas that aren’t to be altered or
touched, or purple to represent reference tables used for lookups and drop-down lists.
You can use any color you want; it’s up to you to give these colors meaning. The important thing is
that you have a visual distinction between the various ranges being used in your data model. If you
use different colors, it’s important to fully document what each color means.