Chapter 7. Creating Relational Models in Power Pivot
Chapter 6 introduced the fundamentals of Power Pivot as an effective tool for data analysis and reporting, especially when working with data from multiple sources. This chapter offers a demonstration on how to use Power Pivot for relational data modeling.
Connecting Data to Power Pivot
As highlighted in Chapter 6, the Data Model serves as the foundation of Power Pivot, facilitating the creation and management of table relationships for effective data computation and analysis. Power Pivot streamlines this task with an intuitive drag-and-drop interface. This chapter delves deeper into the Data Model, using the ch_07.xlsx file. This file contains a retail sales dataset often referenced within the analytics community and is found in the ch_07 folder among the book’s resources.
In the example from Chapter 6, the Data Model was predefined. In this chapter, we’ll need to define it manually.
Although direct connections to data sources via Power Pivot are feasible, it’s recommended to channel data through Power Query first. This approach provides a convenient platform to establish any recurring data cleaning procedures on these tables when necessary.
To get started, import the orders table into Power Query using the Data → From Table/Range steps. Bypass any data transformation steps and proceed to Close & Load → Close & Load To on the Home tab.
To load this query to Power Pivot, choose to create the data as a connection only, and then ...
Get Modern Data Analytics in Excel now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
