Microsoft Office Tutorials and References
In Depth Information
Even if the query seems strange, its structure is pretty simple. It contains many tables, but the
final result has the same structure for the Customer table as you want to show in the user data
model. This query can be constructed using the query designer, and you can see in Figure 4-27
the relationships and filters panes of the query designer.
FIguRE 4-27 The query designer can build complex queries.
In the designer, we have not used the autodetect feature of PowerPivot because, having so
many tables, we suggest that you handle relationships by hand. Please, keep in mind that
to build such a complex query, you need to have a clear idea of what you are querying,
and to get it, you should start from the diagram with boxes and arrows, study it, and then
begin to write the query only when you have a clear idea of the goal you are achieving.
Note This example has been deliberately a complex one. The goal is not to scare the reader or to
direct you to one of the many good SQL books that are on the market. Instead, the focus here is
to understand that there is a big difference between how data is stored in the source data model
and how you want to handle it for your analytical purposes. Having the data in the correct form
makes it easier to produce interesting queries because they are easier to write using the PivotTable.
Moreover, if you ever are faced with such a complex architecture, it might be very useful to ask for
help from some SQL technician—you might ask that person to write the query for you.