Microsoft Office Tutorials and References
In Depth Information
The PowerPivot Query Designer
If you want to remove normalization from a data model, you can use the query designer of
the Table Import Wizard in PowerPivot to produce denormalized SQL queries in the original
data model. Nevertheless, before you start designing a query, it is important to understand
what a SQL query is.
A SQL query is a statement written in a special language, called SQL, which is understood by
most of the modern databases. We are not going to describe in detail how to write a SQL
query because there are a lot of good books that cover the topic much better then we could
do in a few paragraphs. For the purposes of this topic, we are interested in how to use the
PowerPivot query designer, which requires that you understand just the basics of SQL and
handles much of the hard work for you.
A very simple SQL query looks like this:
Prod.ProductId AS ProductID,
Prod.Name AS ProductName,
Sub.Name AS SubcategoryName
Production.Product AS Prod
INNER JOIN Production.ProductSubcategory AS Sub
ON Prod.ProductSubcategoryID = Sub.ProductSubcategoryID
Sub.Name = 'Helmets'
Let us describe this example in greater detail. A query is composed of several parts, the most
common and important of which are these:
■ SELECT In the first part of the query, you declare which columns you are interested in
reading. You can refer to more than one table in the column list of the SELECT element;
this is particularly useful when we need to get data from tables that have relationships
■ FROM In this part of the query, you declare which tables you want the database to read
to gather the columns declared in the first section. If more than one table is referenced,
as is the case in the example, you should specify how to follow relationships among the
different tables. You do this using the JOIN predicate.
■ WHERE The last part of the query lets you create filtering conditions, so that you do
not retrieve all rows. In the example, we asked for rows that contain Helmets as the
Now that you have seen the overall structure of a query, you are going to create this query
and load its data into PowerPivot to appreciate the difference between the query result and
the original tables.