Introduction
This chapter is designed as an introduction to tabular modeling by using PowerPivot. It shows the process by which a user imports data into PowerPivot for Excel, creates relationships between the datasets, and then reports on it.
The data used in this chapter is based on the orders of the fictitious bicycle company (named Adventure Works). Our data includes six datasets and they are:
- Product list: This shows some generic information about the products being sold (for example, the name, color, and size of the product). Each product is identified by a
product_id
value. - Product subcategories list: This shows a subcategory that a product belongs to. The list shows the
product_id
value and the associated subcategory (by ID and Name). - Product categories list: This shows the product category that a product belongs to. The list shows the
product_id
value and the associated category (by ID and Name). - Orders list: This shows what orders have been placed by customers. The list includes an entry for each product that has been ordered. This data simulates a detailed extract from an operational source system.
- Customer list: This gives us information about the customer (for example, their names, countries, and states) by customer number.
- Dates list: This simply lists consecutive days defining information such as the month name, year, and half-year period of the date.
The tabular modeling lifecycle revolves around three primary steps. These are:
- Getting the data into the model
- Defining the relationships among tables
- Defining calculations based on business logic
This chapter examines these steps and allows the reader to become familiar with the tabular (PowerPivot) design environment.