Data structuring – understanding the three golden rules

To get the best out of Power Pivot, we need to lay out our dataset in a way that will improve performance and help us get the right insights from our datasets. The following figure summarizes the ideal layout for a single dataset or table:

Figure 2.1 – The three golden rules for laying out your data in a single dataset

Let’s look at each of these rules in detail:

• Rule 1: Each column should have a single data type. As we learned in Chapter 1, Getting Started with Data Modeling, Power Pivot manages data using a columnar structure. It is therefore important to commit one data type to each column of your dataset.

  For example, if you have data for a payroll containing the names of staff members, one column should be dedicated to the names of staff with the same format and no other data type. Apart from this, there shouldn’t be any column in your dataset that contains...