Tennick A. Practical PowerPivot & DAX Formulas for Excel 2010
Подождите немного. Документ загружается.
This page intentionally left blank
SQL Queries
for PowerPivot
Appendix A
386 Practical PowerPivot & DAX Formulas for Excel 2010
T
his is a short appendix. It’s aimed at those readers who need a brief introduction to
SQL, with some basic syntax examples. We also discuss the reasons for writing
your own queries to import data into PowerPivot, rather than simply importing
complete tables. A few query fundamentals are covered: filtering, sorting, grouping, and
denormalizing data with joins and self-joins. There are also examples of using a stored
procedure and writing SQL queries against Excel.
C
Key concepts SQL, selecting columns, calculated columns, filtering data, sorting
data, grouping data, denormalizing data with joins and self-joins, stored procedures,
querying Excel
Why Write SQL Queries?
Structured Query Language (SQL) is a standard language for extracting data from
a relational database. When you connect to many of the relational data sources in
PowerPivot, you have the option to write your own queries, rather than simply choosing
table names. Among other sources, this would apply to SQL Server, Access, Oracle,
and IBM DB2. All of these products support the SQL language in queries. The SQL
language is generic and widely popular—and there is an agreed standard called ANSI
SQL. Most vendors adhere very closely to this standard, although each dialect of SQL
(for example, T-SQL in SQL Server or PL/SQL in Oracle) may depart slightly from
that standard and incorporate many proprietary extensions. This appendix concentrates
on writing SQL queries for SQL Server (that is, in T-SQL or Transact-SQL dialect).
However, most of the syntax is generic and also relevant for other software, and you
should be able to adapt the queries for your own database vendor (of course, you will
have to change database, table, and column names!).
If you decide to write your own queries, you can reduce the amount of work you
have to do in the PowerPivot window. This has a number of potential benefits. One, for
calculated columns, you may be more familiar with doing this in SQL, rather than in
DAX, and hence, find it easier. Two, you can use stored procedures to import the data.
Three, you can handle self-joins, which are not supported in this release of PowerPivot.
Four, the evaluation and recalculation of some calculated columns may be faster, if
done in SQL rather than DAX. Five, you may be able to prepare data in a way that is
not possible in PowerPivot (for example, outer joins or complex filters). Six, you may
be able to access other external data sources from your queries that are not options in
PowerPivot (for example, you can use DMX inside a T-SQL linked server query to
extract data from a data mining model).
Appendix A: SQL Queries for PowerPivot 387
If you are interested in learning a bit more about SQL (in particular, T-SQL for
SQL Server), refer to my book Practical SQL Queries for Microsoft SQL Server 2008
published by McGraw-Hill (although nearly all of the SQL in the book is generic).
Almost all of the examples here are taken from that book.
Where to Create and Test SQL Queries
You could enter the SQL directly in the PowerPivot Table Import Wizard. However,
many software vendors provide elegant and powerful query editors. These often provide
substantial benefits in entering, testing, and debugging your queries. This appendix
concentrates on SQL Server, which has a client tool called SQL Server Management
Studio (SSMS). This tool has color-coding, line numbering, word wrap, IntelliSense,
context-sensitive help, debugging aids, drag-and-drop of object names, drag-and-drop
of functions, metadata view of a database and its objects, and easy visualization of
query results. So, maybe it’s better to prepare your query in SSMS (or the query editor
applicable to your database vendor) first. Once you are happy with the results, then it’s
a simple copy-and-paste into PowerPivot.
Where to Use SQL Queries in PowerPivot
If you are importing from SQL Server or Access, click From Database in the Get
External Data group of the Home ribbon. You then choose either From SQL Server
or From Access in the drop-down menu. If you wish to import from another relational
source, click From Other Sources in the Get External Data group and choose the
relevant source. All of these are entry points into the Table Import Wizard. After you
set up connection information, you will see the Choose How to Import the Data dialog.
The dialog is shown in Figure A-1. In this dialog, choose Write a Query That Will
Specify the Data to Import and click Next. The next dialog is Specify a SQL Query.
This dialog is shown in Figure A-2. In this dialog, type or copy and paste your SQL
into the SQL Statement text box. There is also a Design button that will take you into
a graphical query designer (SQL Server import only) that can generate some of the
SQL for you to get you started. When your query is finished, click Validate to check
the syntax, and then click Finish. The data retrieved by your query will be displayed in
a new PowerPivot table.
388 Practical PowerPivot & DAX Formulas for Excel 2010
If you get it wrong, you can alter the SQL retrospectively. You do so by clicking the
Table Properties button in the Properties group of the Design ribbon. This opens the
Edit Table Properties dialog, where you can amend your SQL statement.
Figure A-1 Choose How to Import the Data dialog
Appendix A: SQL Queries for PowerPivot 389
Figure A-2 Specify a SQL Query dialog
SQL Examples
If you want to try these examples, you will need SQL Server 2008 or 2008 R2, and the
AdventureWorksDW2008 relational database (available from www.codeplex.com). You
should also be able to adapt many of the queries to work with SQL Server 2005 and
the AdventureWorksDW relational database (also available from www.codeplex.com),
or the new SQL Server 2008 R2 ContosoRetailDW sample database. One example
(on self-joins) uses Northwind, so if you followed the practicals in Chapters 1 and 2,
you can amend your PowerPivot model.
390 Practical PowerPivot & DAX Formulas for Excel 2010
Selecting Specific Columns
This is a simple column list (PowerPivot table result shown in Figure A-3):
select FirstName, LastName from DimCustomer
Using a T-SQL Function
Here, a simple function is used to manipulate data (PowerPivot table result shown in
Figure A-4):
select Birthdate, datediff(yy,birthdate,GETDATE()) as Age
from DimCustomer
Unless you use an asterisk for all columns, every column must have a name. For
example, the second column here would result in an error without the alias, Age. The
appearance of the Birthdate column is governed by the setting in the Format drop-down
on the Home ribbon.
Figure A-4 A SQL calculated column
Figure A-3 Specific columns
Appendix A: SQL Queries for PowerPivot 391
Suppressing Duplicates
This query eliminates duplicate values (PowerPivot table result shown in Figure A-5):
select distinct EnglishOccupation from DimCustomer
Creating Buckets
This technique is very useful when you want to reduce a large range of values into a small
range of bucket values. Some people call this discretization, or even bucketization!
The PowerPivot table result is shown in Figure A-6.
select
LastName,
case
when yearlyincome <= 30000 then 'Low'
when yearlyincome <= 70000 then 'Medium'
else 'High'
end
as [Income Group]
from DimCustomer
Figure A-5 Distinct values
Figure A-6 Buckets of data
392 Practical PowerPivot & DAX Formulas for Excel 2010
Implementing a Filter
This is an example of using the classic SQL Where clause to filter data (PowerPivot
table result shown in Figure A-7):
select FirstName, LastName, EnglishEducation, EnglishOccupation
from DimCustomer
where englisheducation not in
('partial college','high school','graduate degree')
A More Complex Filter
Here is another filtering query (PowerPivot table result shown in Figure A-8):
select FirstName, LastName, EnglishEducation, EnglishOccupation
from DimCustomer
where (englisheducation = 'Partial College' and
englishoccupation = 'Clerical') or englishoccupation = 'Manual'
Figure A-8 Another filtering example
Figure A-7 Filtering data
Appendix A: SQL Queries for PowerPivot 393
Using Wildcards
In this query, a wildcard has been used to filter the data (PowerPivot table result shown
in Figure A-9):
select FirstName, LastName from DimCustomer
where LastName like 'Li%'
Sorting Records
Here, you are retrieving the top ten percent by income (PowerPivot table result shown
in Figure A-10):
select top 10 percent LastName, FirstName, YearlyIncome from DimCustomer
order by YearlyIncome desc
Figure A-9 Wildcard search
Figure A-10 Top 10 percent earners