Excel Dashboards and Reports, 2nd Edition

Chapter 11: Developing Your Data Model

In This Chapter

• Setting up the data, analysis, and presentation layers

• Applying data model best practices

• Leveraging Excel functions to deliver data

• Using Excel tables that expand with data

A data model provides the foundation upon which your dashboard or report is built. When you collect and analyze data, you’re essentially building a data model that feeds your presentation. In this chapter, we discuss how to build and manage an efficient data model. Although you’ll discover how to build cool dashboard components in later chapters, they won’t do you any good if you can’t construct an effective data model. On that note, let’s get started.

Building a Data Model

Building an effective data model isn’t as complicated as you may think. The problem is that most people spend little time thinking about the data model that supports a final presentation. If they think about it at all, they usually start by imagining a mock-up of the finished dashboard and work backward from there.

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 ...