Tennick A. Practical PowerPivot & DAX Formulas for Excel 2010
Подождите немного. Документ загружается.
394 Practical PowerPivot & DAX Formulas for Excel 2010
Denormalizing Data
As an alternative to the DAX RELATED() function, you can denormalize your data
before it reaches PowerPivot with a SQL Inner Join (PowerPivot table result shown in
Figure A-11):
select EnglishProductName, EnglishProductSubcategoryName
from DimProduct inner join DimProductSubcategory
on DimProduct.ProductSubcategoryKey =
DimProductSubcategory.ProductSubcategoryKey
Self-Join
The Northwind Employees table is joined to itself. This is called a self-join, and this type
of join is not supported in PowerPivot. If you wanted to browse employee sales by manager
in a pivot table, you have to join the table to itself in a SQL query. Here’s the syntax for
joining the Employees table to itself (PowerPivot table result shown in Figure A-12):
select E.EmployeeID, E.LastName, E.FirstName,
M.LastName + ', ' + M.FirstName as Manager from employees as E
left outer join employees as M
on E.ReportsTo = M.EmployeeID
Please note this is a left outer join, not an inner join. An inner join would not return
Andrew Fuller as an employee—because he is the CEO of Northwind and has no
manager! You may want to include him, as he has sales in the Orders table. This query
is against the Northwind and not the AdventureWorksDW2008 database.
Figure A-11 Denormalized data
Appendix A: SQL Queries for PowerPivot 395
Grouping Data
You may want to group imported data, and perhaps use SQL aggregate functions, such
as SUM(), rather than the DAX equivalents. Following is an example using the Group
By clause (PowerPivot table result shown in Figure A-13):
select ProductKey, OrderDateKey, sum(OrderQuantity)As SumOrderQuantity
from FactInternetSales group by ProductKey, OrderDateKey
Stored Procedure
Often, you may not have permissions to query tables directly. Perhaps your SQL Server
DBA only allows access to data through stored procedures—if that is so, you can’t write
queries against the tables, nor can you import the tables directly. Here’s some sample
Figure A-12 Self-join data
Figure A-13 Grouping data
396 Practical PowerPivot & DAX Formulas for Excel 2010
code to create a parameterized stored procedure (sometimes stored procedures can
return data faster than a normal Select statement):
create procedure spGetCustomersByEducation
@education nvarchar(40)
as
begin
set nocount on
select Firstname, Lastname, EnglishEducation, EnglishOccupation
from DimCustomer
where englisheducation = @education
end
Whether you create your own stored procedures, or use those created by others, you
need to call the stored procedure to return the data. Here’s an example that returns data
from the stored procedure created in the preceding code (PowerPivot table result shown
in Figure A-14):
exec spGetCustomersByEducation @Education = 'Partial College'
SQL Queries for Excel
You may be surprised to learn that you can write SQL queries against Excel worksheets!
You might also ask why anyone should want to do so, when PowerPivot supports
imports from Excel worksheets directly. The answer may be that you want to include
complex filters and/or join the Excel data to a table from another source. T-SQL supports
the concept of a linked server—the linked server does not have to be SQL Server, yet
you can query it from T-SQL. Here is some sample code to set up Excel as a linked
Figure A-14 Stored procedure result data
Appendix A: SQL Queries for PowerPivot 397
server using T-SQL (please note, this is no longer generic SQL and may not work in
other SQL dialects):
exec sp_addLinkedServer @server='MyExcel',
@srvproduct='ACE 12.0',
@provider='Microsoft.ACE.OLEDB.12.0',
-- this is a comment
-- in the next line, specify a valid path to an Excel workbook
@datasrc='C:\users\art\documents\book1.xlsx',
@provstr='Excel 12.0;HDR=No'
There are three occurrences of “12.0” in the code. The number you specify may be
different. You can establish the correct number (it’s actually the Office version number)
by looking at linked server providers in Object Explorer. After setting up the linked
server, you can then query the Excel workbook using SQL:
select * from myexcel...sheet1$
If you are tempted to try this, there are a few things to consider. First, your SQL
Server DBA may disapprove of permanent linked servers (there are alternatives, such
as Openrowset). Second, you’ll need some sensible data in the workbook—it’s helpful
to have column headers in the first row (if you do, replace HDR=No with HDR=Yes in
the above syntax). Third, there are three dots between the linked server name and the
worksheet name in the preceding syntax (for SQL Server users, Excel does not have a
concept of a database name, nor a schema name—the dots are placeholders). Fourth,
instead of the worksheet name (sheet1$), you can use the name of an Excel named
range—it’s also possible to query cells and unnamed ranges.
Of course, there is rather more to SQL than this! This appendix is designed to help
readers who are not familiar with SQL get started with writing their own SQL queries
for PowerPivot.
This page intentionally left blank
MDX Queries
for PowerPivot
Appendix B
400 Practical PowerPivot & DAX Formulas for Excel 2010
M
ultiDimensional eXpressions (MDX) is a very powerful query language for
extracting data from cubes. While PowerPivot can generate sophisticated
MDX for you, you may want the total control that writing your own MDX
gives you. You can use MDX to import from either an SSAS cube or a PowerPivot
model that has been published to PowerPivot for SharePoint. This appendix demonstrates
some of the fundamentals of MDX. It also shows how best to adapt your MDX for
PowerPivot.
C
Key concepts Cube dimensions and measures, Crossjoin() function,
navigating hierarchies, ranges, sorting, filtering, calculated measures, key
performance indicators (KPIs), natural user hierarchies, attribute hierarchies
Why Write MDX Queries?
MDX is a standard language for extracting data from a multidimensional database.
Specifically, it is the language used to query cubes in an SSAS database or to extract
data from a PowerPivot model that has been published to PowerPivot for SharePoint.
Both are supported from PowerPivot for Excel. Hyperion Essbase and SAP BW cubes
also use MDX, but these are not directly supported by PowerPivot.
Writing your own MDX gives you more power and flexibility. There is a graphical
query designer for MDX in PowerPivot—it’s very good, but sometimes you need just
that little extra control. In addition, you can avail yourself of the extra power of the
SSMS MDX query editor. There is one small proviso—sometimes, the MDX you write
in SSMS is better expressed in a slightly different way for PowerPivot. This appendix
contains a couple of examples that show this.
If you are interested in learning a bit more about MDX (in particular, MDX for
SQL Server Analysis Services), you are referred to my book Practical MDX Queries for
Microsoft SQL Server Analysis Services 2008 (McGraw-Hill/Professional, 2010). Most
of the examples in this appendix are adapted from that book—the rewritten examples
(rewritten to provide better data specifically for PowerPivot) are new. All of the
examples are against an SSAS cube, not a PowerPivot model published to SharePoint,
although the principles are the same.
Where to Create and Test MDX Queries
MDX is a standard language for querying cubes. The MDX discussed here is for
SSAS. The MDX used by other vendors of multidimensional databases may differ in
minor ways. In PowerPivot, you can only use MDX directly against SSAS cubes or
PowerPivot models on SharePoint.
It’s probably a good idea to write and test your MDX queries in SSMS. If they don’t
work there, they are not going to work in PowerPivot.
Appendix B: MDX Queries for PowerPivot 401
Where to Use MDX Queries in PowerPivot
You start from the Home ribbon in PowerPivot. Click From Database in the Get
External Data group on the ribbon. In the resulting drop-down, choose From Analysis
Services or PowerPivot. You’ll need to set up the connection to an SSAS server and a
database (although you could alternatively provide the URL to a PowerPivot workbook
in SharePoint) before clicking Next. The next dialog is entitled Specify a MDX Query.
This dialog is shown in Figure B-1. Within this dialog, type or paste your MDX (it’s
a good idea to copy it from SSMS) into the MDX Statement text box. Click Validate,
and then click Finish.
Figure B-1 Specify a MDX Query dialog
402 Practical PowerPivot & DAX Formulas for Excel 2010
MDX Examples
If you want to try these examples, you will need SQL Server Analysis Services 2008 or
2008 R2, and the Adventure Works DW 2008 multidimensional database (available
from www.codeplex.com), which contains the Adventure Works cube used in the
following queries. You should also be able to adapt many of the queries to work with
SQL Server Analysis Services 2005 and the Adventure Works DW multidimensional
database (also available from www.codeplex.com). The MDX syntax is simply given,
with nothing much by way of explanation. A full analysis of MDX is beyond the scope
of this book.
A Basic MDX Query
select [Date].[Calendar].[Calendar Year] on columns
from [Adventure Works]
select {[Date].[Calendar].[Calendar Year].members} on columns
from [Adventure Works]
In this example, you have two versions of the same query. The first is informal syntax,
and the second is more formal, which many consider to be better practice. There is
no measure in this query. Therefore, it uses the default measure for the cube, Reseller
Sales Amount. The resulting cellset looks great in SSMS, but not in PowerPivot. The
years are returned as column headers, not as part of the data in the PowerPivot table. It
would be nice to see the years in the rows, not as column headers. Column headers are
metadata, and rows are data. This is a common problem when importing via an MDX
query. The solution is not to place non-measure dimension members on the Columns
axis of the MDX query (see the next query). The resulting table is shown in Figure B-2.
Having non-measure dimensions on the Columns axis is not allowed in the MDX
graphical query designer in PowerPivot. There is a Design button in the Specify a
MDX Query dialog. This opens the graphical designer, where you can click on the last
button on the toolbar (Design Mode) to switch to text mode. If you paste in this query,
and try to execute the query, you receive an error message indicating that the designer
Figure B-2 PowerPivot table
Appendix B: MDX Queries for PowerPivot 403
only supports measures on columns. The error message is shown in Figure B-3. It’s
probably better not to do this, unless you decide not to use the graphical query designer!
But the error is a sensible one—non-measure dimensions on columns don’t look good
in PowerPivot, and they are of limited use in a pivot table. The next query is much better.
A Basic MDX Query Rewritten to Give Better Results
select {[Measures].[Reseller Sales Amount]} on columns,
{[Date].[Calendar].[Calendar Year].members} on rows
from [Adventure Works]
This is a far nicer query. It looks better as a PowerPivot table and will make a
subsequent pivot table more meaningful. The non-measure dimension (Date) has
been placed on the Rows axis. The default measure is made explicit and placed on the
Columns axis. The resulting table is shown in Figure B-4.
Figure B-3 Error message
Figure B-4 PowerPivot table