Microsoft Office Tutorials and References
In Depth Information
You can easily appreciate that this diagram is much easier to understand when compared with
the technical one. There is a single entity and all the technical relationships between tables
have been removed, leading to a simple structure that makes the user experience much better.
That said, you now have a starting point and a goal to achieve, although the road is pretty
hard. Because the structure is complex, you should not even think about loading all the tables
into PowerPivot and then using the RELATED function to rebuild the data model because this
would be a very time-consuming job. When the starting data model is a complex one, as this is,
the best, if not the only way, is to use SQL queries.
Moreover, the query is a complex one because it involves a high number of JOINS, all of which
need to be LEFT OUTER JOIN because you want to include all customers, whether or not they
have an address stored in the database. Here is the final result of the query:
Customer.CustomerID AS CustomerID,
Customer.AccountNumber AS AccountNumber,
Customer.CustomerType AS CustomerType,
Contact.FirstName AS FirstName,
Contact.LastName AS LastName,
Contact.MiddleName AS MiddleName,
Contact.EmailAddress AS EmailAddress,
Contact.Phone As Phone,
Address.AddressLine1 AS AddressLine1,
Address.AddressLine2 AS AddressLine2,
Address.City AS City,
StateProvince.StateProvinceCode AS StateProvinceCode,
StateProvince.Name As StateProvince,
cr.CountryRegionCode AS CountryRegionCode,
cr.Name AS CountryRegionName
Sales.Customer AS Customer
LEFT OUTER JOIN Sales.Individual AS Individual
ON Individual.CustomerID = Customer.CustomerID
LEFT OUTER JOIN Person.Contact AS Contact
ON Contact.ContactID = Individual.ContactID
LEFT OUTER JOIN Sales.CustomerAddress AS CustomerAddress
ON CustomerAddress.CustomerID = Customer.CustomerID
LEFT OUTER JOIN Person.AddressType AS T
ON CustomerAddress.AddressTypeID = T.AddressTypeID AND
T.Name = 'Primary'
LEFT OUTER JOIN Person.Address AS Address
ON Address.AddressID = CustomerAddress.AddressID
LEFT OUTER JOIN Person.StateProvince AS StateProvince
ON StateProvince.StateProvinceID = Address.StateProvinceID
LEFT OUTER JOIN Person.CountryRegion AS CR
ON CR.CountryRegionCode = StateProvince.CountryRegionCode
Search JabSto ::

Custom Search