Microsoft Office Tutorials and References
In Depth Information
■ Other information about the address (that is, address line, zip code, and so on) are stored
in the Person_Address table, which has a relationship with Sales_CustomerAddress, which
in turn has a relationship with Sales_Customer, forming a complex relationship that looks
very similar to a chained one.
■ Person_Address refers to Person_StateProvince, which in turn is in relation to Person_
CountryRegion. They form a chained relationship that lets us recover the name of the
country through a couple of steps, from Person_Address to Person CountryRegion.
You cannot expose such a complexity through PowerPivot because it would turn the PivotTable
into a nightmare. So let us roll up our sleeves and start the analysis of this topic to understand
what to do to make the model easier to query.
■ The Sales_Individual table contains some demographic information. When the
information is useful, it should be added to the Sales_Customer table, thus removing the
Sales_Individual table from our data model.
■ The Person_Contact information can be denormalized in the Sales_Customer table so
that it is available directly in the Sales_Customer table.
■ The Person_AddressType table is a simple descriptive one, so it can be denormalized, if
you need to do that. Moreover, if (as we are supposing) we are not interested in having
all the addresses of a customer, we can take only the primary address of a customer and
then denormalize all of the address information in the Sales_Customer table.
■ Because we need to denormalize the address, we denormalize all the chained
relationships, starting from the Person_Address and reaching Person_CountryRegion; we move
all the information from those tables directly into the Sales_Customer table.
Because a picture is worth a thousand words, in Figure 4-26 you can see the data model as
you would like it to appear in PowerPivot.
FIguRE 4-26 The customer diagram, in its logical form.