The Optimal Shape for Power Pivot Data

As regular readers of my blog would already know, I have just finished writing my book "Learn to Write DAX" and it will be released in November 2015. One of the topics in the book talks about the optimal shape of your data tables for DAX. It is such an important topic that I have decide to share some modified extracts from the book here. When I say "shape" of your data, I am taking about things like how many tables you import into Power Pivot, how many columns are in each table, which columns are in each of the tables etc. One reason data shape is important is because the shape of data in transactional systems (or relational databases) is seldom the ideal shape for Power Pivot. When the IT department executes an Enterprise BI project, one of the important first steps is to shape the data so it is optimal for reporting. This step is normally completely transparent to the end user (ie you) and hence the end user is shielded from the need to do this. But you need to understand data shaping if you want to have efficient and effective Power Pivot data models. Just copying what you have got in your source data is unlikely to be optimal.