Best 100+ Excel Hacks & Tips with Excel 2016. Take the course for only $10 !!

Arrow up
Arrow down

Building an Effective Data Model

An effective data model provides the foundation upon which your dashboard or report is built. One of the key concepts of a data model is the organization of data into three layers:

data, analysis, and presentation. These layers will be three separate worksheets in an Excel workbook. 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. 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 worksheet.

The data layer consists of the raw data that feeds your dashboard Ideally, you will want your data layer to come in one of two forms:

Flat data tables: Data organized by row and column. Each row corresponds to a set of records. Each column is a field. Flat tables can be detailed enough to hold the data that you need and they can be conducive to a wide array of simple formulas and calculations in your analysis layer.

Tabular data set: Ideal for pivot table driven data models. The main difference is that each row has one record contains all the identifiers. This difference in structure is what makes tabular data sets optimal data sources for pivot tables.


Now look at this data table and this Tabular data set. the quarter identifiers are integrated into the column labels as opposed to the Period column that contains the quarter identifier

The analysis layer

The analysis layer consists primarily of formulas that analyze and pull data from the data layer into formatted tables. These staging tables feed the reporting components in your presentation layer. So the analysis layer becomes the staging area where data is summarized to automatically update your dashboard.

The main benefit of having an Analysis worksheet is that when we change something in our Data Layer, it’ll change automatically in Analysis Layer, like calculations for example.

The presentation layer

The presentation layer is your store front. It contains all the charts, visualizations, and dashboard components that you want your audience to see. The presentation layer is flexible, as you can choose graphics, charts, different themes and styles of your


Thank you very much and waiting for your questions.

If you want to learn how to make amazing Dynamic Excel Charts, Dashboards and Pivot Charts in a few weeks, take our Udemy Course for only $25 !! (original price: $199 - 87% OFF!)




Last modified onMonday, 14 March 2016 17:57
Rate this item
(1 Vote)