Tennick A. Practical PowerPivot & DAX Formulas for Excel 2010
Подождите немного. Документ загружается.
414 Practical PowerPivot & DAX Formulas for Excel 2010
Y
ou may have SSAS data mining structures and data mining models. You
normally query these objects using Data Mining eXtensions (DMX), perhaps
from SQL Server Management Studio (SSMS) or from SQL Server Reporting
Services (SSRS). Although this release of PowerPivot supports the graphical design of
SQL and MDX queries, it does not support the graphical design of DMX queries. You
can, however, write your own DMX. You do so by connecting to an SSAS source and
entering the DMX into the MDX Statement area of the Specify a MDX Query dialog,
in the Table Import Wizard. Another way is to embed your DMX query within a SQL
query and import the data returned from the outer SQL query. To do that, you connect
to a SQL Server source and enter the SQL/DMX into the SQL Statement area of
the Specify a SQL Query dialog of the Table Import Wizard. Alternatively, you can
query some data mining data from Excel itself—for this you will need to download the
data mining add-in for Excel (the data mining results are shown in an Excel worksheet
when you use the Table Tools/Analyze ribbon, rather than the Data Mining ribbon),
you can then import or link into the PowerPivot window from the Excel worksheet).
Stand-alone DMX, or DMX embedded in SQL, works for DMX Cases, Content,
Prediction, and other queries. This chapter includes sample code for a few Cases, Content,
and Prediction queries.
C
Key concepts DMX, linked servers, Cases queries, Content queries, Prediction
queries
Why Write DMX Queries?
DMX, or Data Mining eXtensions, is a standard language for extracting data from data
mining models. Specifically, it is the language used to query data mining structures and
data mining models in an SSAS database. By using DMX, you are no longer confined
to SSAS imports from cubes only—you can also import data from mining models. You
can write your own stand-alone DMX queries during data import. One other way to
get DMX imports working is to set up a SQL Server linked server to SSAS, and embed
the DMX within a SQL query. This appendix shows both approaches.
If you are interested in learning a bit more about DMX (in particular, DMX for
SQL Server Analysis Services), refer to my book, Practical DMX Queries for Microsoft
SQL Server Analysis Services 2008 (McGraw-Hill /Professional, 2010). Most of the
examples here are taken from that book.
Where to Create and Test DMX Queries
DMX is a standard language for querying data mining models. The DMX discussed
here is for SSAS. The DMX used by other vendors of data mining software may differ
in minor ways.
Appendix C: DMX Queries for PowerPivot 415
You need to create and then test your DMX queries—first, as pure DMX, and,
second, as DMX embedded inside SQL (specifically, T-SQL for SQL Server), if you
opt to use a linked server. The best place to do both is within SSMS. From the SSMS
Object Explorer, right-click on your SSAS database and choose New Query | DMX.
Write and validate your DMX and copy it to the Clipboard. If you prefer to embed
the DMX within SQL, then right-click on a SQL Server database (any one will do,
unless you need to reference a SQL Server table or view from the DMX) and choose
New Query. This will open the SQL query editor. Write a SQL wrapper and paste in
your DMX. Test and validate the SQL containing the DMX, before copying to the
Clipboard.
The examples here use both stand-alone DMX and a SQL Server SSAS linked server.
If you want to experiment with the latter, you will need to set up an SSAS linked server
to SQL Server. You can do this from the GUI or from a SQL query. From the GUI,
expand your SQL Server in the SSMS Object Explorer. Expand Server Objects, right-
click on Linked Servers, and choose New Linked Server. Make sure the Provider is set
to Microsoft OL DB Provider for Analysis Services 10.0. From a SQL query, enter
code similar to the following (your may need to change the @datasrc and @catalog
properties to point to your SSAS server and database, respectively):
exec sp_addlinkedserver
@server='MySSAS',
@srvproduct='',
@provider='MSOLAP',
-- change server as necessary
@datasrc='localhost',
-- change database as necessary
@catalog='Adventure Works DW 2008'
The @server property can be anything reasonable—it is not the name of your
SSAS server (that is the @datasrc property). Here, @server is MySSAS. This is the
name by which you reference the linked server. There may be one step involved—you
must allow SSAS to run in-process with SQL Server. To check or set this, follow these
steps:
1. In the SSMS Object Explorer, expand your SQL Server, then Server Objects,
followed by Linked Servers and Providers.
2. Right-click on the MSOLAP provider and choose Properties. This opens the
Provider Options dialog shown in Figure C-1.
3. If necessary, turn on the Allow Inprocess check box, and click OK.
416 Practical PowerPivot & DAX Formulas for Excel 2010
Where to Use DMX Queries in PowerPivot
For stand-alone DMX, you go through From Analysis Services or PowerPivot by
clicking From Database in the Get External Data group of the PowerPivot Home
ribbon. The steps are identical to those for writing MDX queries, which is documented
in Appendix B. For DMX embedded within SQL, you begin by clicking From Database
in the Get External Data group of the PowerPivot Home ribbon. In the drop-down
menu, choose From SQL Server (neither From Access nor From Analysis Services
Figure C-1 Provider Options dialog
Appendix C: DMX Queries for PowerPivot 417
nor PowerPivot is going to work). Connect to your SQL Server and any database (one
example requires the AdventureWorksDW2008 or the AdventureWorksDW database,
so you could connect appropriately if you are able to). The next dialog in the Table Import
Wizard is the Choose How to Import the Data dialog. This is shown in Figure C-2.
In this dialog you have to select the Write a Query That Will Specify the Data to
Import radio button. When you click Next, the Specify a SQL Query dialog appears.
The dialog is shown in Figure C-3.
Paste your code (copied from SSMS) into the SQL Statement box and then click
Validate, followed by Finish. Your data mining data is imported into a PowerPivot table.
Figure C-2 Choose How to Import the Data dialog
418 Practical PowerPivot & DAX Formulas for Excel 2010
DMX 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 data mining models 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). You will also need SQL Server itself,
if you opt for the linked server approach, in order to run the SQL that contains the
DMX. One of the examples references a SQL Server view (vTargetMail)—for that
example to work, whether as stand-alone or embedded DMX, you will need either the
AdventureWorksDW2008 or the AdventureWorksDW sample SQL Server database).
Figure C-3 Specify a SQL Query dialog
Appendix C: DMX Queries for PowerPivot 419
All of the embedded examples use a linked server called MySSAS—this name was
defined in the SQL to add a linked server, shown immediately above.
You may discover that not all of your DMX works during a PowerPivot import.
There are a couple of provisos. One, if the DMX returns a nested table, this will fail if
the DMX is embedded within SQL—you have to flatten nested tables using the DMX
keyword Flattened. Two, for embedded DMX only, if your DMX contains single
quotes, you must add another single quote to each single quote. Here is the generic
SQL wrapper syntax for embedded DMX:
select * from openquery(MySSAS,'')
Here, the name of the linked server is MySSAS—this does not have to match your
actual SSAS server name. You paste your DMX (presumably, tested as a stand-alone
DMX query first) between the single quotes. For each example, the pure stand-alone
DMX is shown first, followed by the DMX inside the SQL. For all of the examples
(apart from the first one, which fails), there is a screenshot of the resulting data in a
PowerPivot table.
NOTE
It is quite possible that your data may be different—the results of DMX queries can be affected by which data
SSAS chose to train the mining models.
Cases Query: Nested Table Failure
Many DMX queries return nested tables. If the DMX is embedded within SQL, the
outer SQL query will fail.
DMX: Stand-Alone
select * from mining structure [customer mining].cases
DMX: Embedded in SQL
select * from openquery(MySSAS,'select * from
mining structure [customer mining].cases')
The second query will fail within a PowerPivot import, as the DMX returns a nested
table that the outer SQL doesn’t like. There are no results for the second example, so it
can’t be used to import into a PowerPivot table—it will not validate during the Table
Import Wizard.
420 Practical PowerPivot & DAX Formulas for Excel 2010
Cases Query: Flattened Table Success
In the two examples here, the nested table is flattened.
DMX: Stand-Alone
select flattened * from mining structure [customer mining].cases
DMX: Embedded in SQL
select * from openquery(MySSAS,'select flattened * from
mining structure [customer mining].cases')
This is similar to the previous query—except that, this time, both examples work!
Please note the introduction of the DMX keyword Flattened. This is a simple Cases
query. The resulting PowerPivot table is shown in Figure C-4.
Cases Query on Specific Columns
Instead of returning all columns, you can ask for specific columns.
DMX: Stand-Alone
select flattened [customer counts] as Customer, (select [Subcategory],
[internet sales amount] as Sales from [subcategories])
from mining structure [customer mining].cases
Figure C-4 PowerPivot table
Appendix C: DMX Queries for PowerPivot 421
DMX: Embedded in SQL
select * from openquery(MySSAS,'select flattened [customer counts]
as Customer, (select [Subcategory], [internet sales amount] as Sales
from [subcategories]) from mining structure [customer mining].cases')
This is a Cases query returning specific columns. The resulting PowerPivot table is
shown in Figure C-5.
Content Query with DMX Subquery
This example, like the last one, is DMX within DMX (a DMX subquery).
DMX: Stand-Alone
select flattened node_caption as [Cluster], node_description as
Demographics,(select [attribute_name], [attribute_value], [Support],
[Probability] * 100 as [Probability] from node_distribution)
from [TM Clustering].content where node_type = 5
DMX: Embedded in SQL
select * from openquery(MySSAS,'select flattened node_caption as [Cluster],
node_description as Demographics,(select [attribute_name],
[attribute_value], [Support], [Probability] * 100 as [Probability]
from node_distribution) from [TM Clustering].content
where node_type = 5')
This is a Content query. The resulting PowerPivot table is shown in Figure C-6.
Figure C-5 PowerPivot table
422 Practical PowerPivot & DAX Formulas for Excel 2010
Content Query with Embedded Single Quotes
Single quotes within DMX queries require special treatment when the DMX is embedded
within SQL.
DMX: Stand-Alone
select flattened node_description as Demographics, (select [attribute_name],
[attribute_value], [Support], [Probability] * 100 as [Probability]
from node_distribution where [attribute_value] = '1')
from [TM Decision Tree].content
where node_type = 3 or node_type = 4
DMX: Embedded in SQL
select * from openquery(MySSAS,'select flattened node_description
as Demographics, (select [attribute_name], [attribute_value], [Support],
[Probability] * 100 as [Probability] from node_distribution
where [attribute_value] = ''1'') from [TM Decision Tree].content
where node_type = 3 or node_type = 4')
This is another Content query—note that embedded single quotes have been
doubled-up with an extra single quote in the second example. The resulting PowerPivot
table is shown in Figure C-7.
Figure C-6 PowerPivot table
Appendix C: DMX Queries for PowerPivot 423
Prediction Query with Embedded Single Quotes
This one needs a SQL Server view, vTargetMail, which is part of the
AdventureWorksDW2008 or the AdventureWorksDW database.
DMX: Stand-Alone
select TM.* , Predict([Bike Buyer]) as [Bike Buyer],
vba!format(PredictProbability([Bike Buyer]),'Percent') as [Probability]
from [TM Decision Tree] prediction join openquery ([Adventure Works DW],
'select Age, Gender, Region, NumberCarsOwned from vTargetMail') as TM
on [TM Decision Tree].[Age] = TM.[Age] and [TM Decision Tree].[Gender] =
TM.[Gender] and [TM Decision Tree].[Region] = TM.[Region]
and [TM Decision Tree].[Number Cars Owned] = TM.[NumberCarsOwned]
DMX: Embedded in SQL
select * from openquery(MySSAS,'select TM.* , Predict([Bike Buyer]) as
[Bike Buyer], vba!format(PredictProbability([Bike Buyer]),''Percent'') as
[Probability] from [TM Decision Tree] prediction join
openquery([Adventure Works DW],''select Age, Gender, Region,
NumberCarsOwned from vTargetMail'') as TM
on [TM Decision Tree].[Age] = TM.[Age] and [TM Decision Tree].[Gender] =
TM.[Gender] and [TM Decision Tree].[Region] = TM.[Region]
and [TM Decision Tree].[Number Cars Owned] = TM.[NumberCarsOwned]')
Figure C-7 PowerPivot table