Best practices with Power Pivot
To get the best out of your Power Pivot and data model, there are some best practices you need to adopt to ensure optimum performance. We discuss some of these best practices here:
- Ideally, all datasets that are added to the data model should be named tables. This makes it easy to identify the tables when creating your DAX formulas.
- Update your source data to limit the number of columns and rows you import into Power Pivot. This will improve performance and give you a better response for your calculations. You can achieve this by normalizing your data. We will discuss this in the next chapter.
- Avoid creating calculations that shape and transform your data in Power Pivot. You can do all the data transformation and shaping in Power Query and then after, load it to Power Pivot. We will discuss Power Query in detail later in the book.
- Use the Diagram view in View to get an overview of your datasets and how they connect to each other and the Data view to audit or explore the content of each dataset.
- Ensure that the data type in each column is consistently formatted. For example, a column that contains dates should not have text input.
Sticking to these rules will greatly improve the performance of Power Pivot.
