Microsoft Office Tutorials and References
In Depth Information
Note Table 7-1 does not include a column for week number. The table omits it because there
are several techniques for calculating the week number in a year, and different businesses have
different ways to make this calculation. More important, sometimes a week belongs to a year
that is different from the calendar year—the fiscal year, for example—even if only for a few
days of a year. In that case, you also need to define a WeekYear column that must be used for
browsing the weeks in a meaningful way. We preferred not to include a specific week
calculation to keep the Dates table simple and to avoid possible confusion introducing an algorithm
that might be different than that used in your company.
Now you can import this table in PowerPivot as a linked table. The result is shown in Figure 7-9.
FIguRE 7-9 The Dates table imported in PowerPivot as a linked table.
You can see that the month name contains the month number in front of it, so December is
described as 12 – December . It is useful to have the month names automatically sorted. However,
if you want to sort month names but also want to avoid the initial number, please take a look
at the section “Custom Sorting in PivotTables” in Chapter 8, “Mastering PivotTables,” where we
describe how to sort columns of a Dates table in a PivotTable.
You might want to change the data types of some columns in the Dates table. Whenever you
import the Excel table into PowerPivot, columns like Year, MonthNumber, and Day are usually
defined as Whole Number data types. For this reason, when you select one of these columns
in the PivotTable, the selected attribute is placed by default in the Values area of the PivotTable
and is aggregated when you use the Sum function. You might prefer to change the data types
of these columns to Text so that by default they are used to group data in rows.
If you want to test your new Calendar table, you should now import the SalesOrderHeader,
SalesOrderDetail, Customer, and Product tables from the AdventureWorks database into
the same PowerPivot model. Relationships between these tables are automatically detected
during the import. At this point, you need to create a relationship between the OrderDate
field of the SalesOrderHeader table and the Date field of the Calendar table you just imported.
Before starting, in PowerPivot you have to rename the Calendar table to OrderDate so that
it expresses the dates it represents. Then you click the Create Relationship button on the
Design tab of the ribbon and ill in the dialog box, as shown in Figure 7-10.