Microsoft Office Tutorials and References
In Depth Information
Loading from Views
Before leaving the SQL database topic, let us spend some time on another kind of object that
you can use to load data from a SQL database: views . But before we describe this object, we
should briefly review the main characteristics of the two ways to load data we have used so
far: tables and queries.
■ Tables The big advantages of tables are that they are easier than queries to load and
that PowerPivot is able to automatically detect most relationships. On the other hand,
directly loading tables makes it necessary to understand the source data model and to
follow complex chains of relationships in PowerPivot, making the later data modeling
process in PowerPivot slightly complex.
■ Queries SQL queries produce a much cleaner result than tables do because you can
use SQL to perform denormalization, follow chains of relationships, and produce a final
result that makes the final data model much easier to query in PowerPivot. On the other
hand, SQL queries are not easy to write and if you decide to use the query editor, you
are likely to have some difficulties expressing exactly the query you want to write. SQL
was not written for end users; it is a query language aimed at technicians, and no query
editor will ever be able to make it easy to express a really complex query.
All that said, another structure can be exposed by relational databases, and it is an interesting
mix between a SQL query and a table: it is called a view.
A view is nothing but a SQL query stored in the database and which has a name. From the
PowerPivot point of view, a view is like a table: it contains rows divided into columns. The only
difference is that when PowerPivot asks for the data, the database does not return rows stored
in a table; instead it executes the SQL query and returns its result.
What makes views first-class citizens is that they address the gap between database
technicians and power users. Because the task to write SQL queries is better accomplished by IT
staff, you can ask IT staff to provide you with a set of views that handle the complexities of
SQL and expose a more user-friendly data model to you.
As an example of how views can help both the IT department and you, we have developed a
set of views in the AdventureWorks database under the prefix PowerPivotBook.
Note If you want to follow this example on your PC, you need to get access to the AdventureWorks
database by using SQL Server Management Studio and run the SQL query, which you can find in
CH05-SQL-Views.sql. If you have any problem accessing the SQL database using Management
Studio, you probably need to ask your IT department for some help.
If you load the PowerPivotBook.Products view into PowerPivot, for example, you get a very
nice data structure for the product table (see Figure 5-11).