Writing DAX Summary Queries Using PowerPivot and Excel QueryTable Objects

Don’t get me wrong. Excel PivotTables are one of the best things since pizza and beer. (And considering how much I enjoy that particular combination of snack and beverage, that’s really saying something.) Nevertheless, there are times when you want to take full control, write your own DAX query, and display the results in a worksheet. In this blog, we’ll see how to do just that. If you would like to try it yourself using a workbook with AdventureWorks data warehouse data already loaded, you can download it here. This is a 13.4 MB file unzipped.

Excel actually provides two ways of querying PowerPivot table data, the VBA code way & the GUI way. We’ll start by looking at the more powerful and flexible way to accomplish this, which, not surprisingly, is also a bit more complex than the simpler GUI-only method.

How to Create DAX Summary Queries – The VBA Code Way

First, we will need to create a QueryTable object in VBA. You’ve used QueryTables before, though perhaps you were not aware, since the QueryTable was working behind the scenes. If you connected Excel to a data source using ODBC, Excel creates a QueryTable object that can be used to refresh the query when requested. We can easily create a query table object in VBA code, but there is a catch. QueryTables were designed to manage queries to be sent to external databases, not to the internal PowerPivot database. A typical snippet of VBA code might look like this:

Sub NothwindQueryTableTest()
Dim qt As QueryTable
Dim sTestQuery As String
sTestQuery = “SELECT * FROM Products”
With ActiveSheet.QueryTables.Add(Connection:=”ODBC; DSN=MS Access Database;” _
& “DBQ=K:\KnowledgebaseDatasets\Northwind.mdb; ” _
& “Driver={Driver do Microsoft Access (*.mdb)}”, _
Destination:=Sheets.Add.Range(“A1”))
.CommandType = xlCmdSql ‘CommandType specification not required for Microsoft Access
.CommandText = sTestQuery
.Refresh BackgroundQuery:=False ‘Forces execution of query upon QueryTable creation
End With
End Sub

Notice that this code specifies a connection string to an Access database when the QueryTable is created. If we try to do the same thing to connect to a PowerPivot database, the creation of the QueryTable fails with a vague “Application error”. So we use a trick.

The Trick

We can create a new QueryTable object using a fake connection string, since the connection is not opened until the associated query is executed. The fake connection string follows the rules of syntax, even though the database itself doesn’t exist. Once the QueryTable is created, we can change the connection property to the PowerPivot connection string we actually want to use.

The PowerPivot Connection String

The connection string for PowerPivot is easy to obtain and, fortunately, is always the same. One convenient method is to click on the “Connections” button on Excel’s Data tab. The connection we are looking for is “PowerPivot Data”. Selecting this connection and clicking “Properties” we see the Connection Properties dialog which, by default, shows up on its Usage tab.

Connection Properties Usage Tab
The Usage tab of the Connection Properties dialog allows use to control important features of the connection, including a refresh schedule.
Connection Properties Definition Tab
The Definition tab of the Connection Properties dialog shows the connection string, which we can copy and paste into VBA code.

Clicking on the Definition tab will bring us to what we want. The value seen in the connection string section can simply be copied and pasted into our VBA code.

Public Sub CreatePowerPivotQueryTableTest()
Dim sTestQuery As String
sTestQuery = “evaluate Geography”
‘ The PowerPivot Data connection string will not work for QueryTable instantiation, so we use a
‘ bogus connection string and then immediately change it
With ActiveSheet.QueryTables.Add(Connection:=”ODBC; DSN=MS Access Database;DBQ=Nonexistent.mdb; Driver={Driver do Microsoft Access (*.mdb)}”, Destination:=Sheets.Add.Range(“A1”))
.Connection = “OLEDB;Provider=MSOLAP.5;Persist Security Info=True;” _
& “Initial Catalog=Microsoft_SQLServer_AnalysisServices;Data Source=$Embedded$;” _
& “MDX Compatibility=1;Safety Options=2;ConnectTo=11.0;” _
& “MDX Missing Member Mode=Error;Optimize Response=3;Cell Error Mode=TextValue”
.CommandType = xlCmdDefault
.CommandText = sTestQuery
.Refresh BackgroundQuery:=False
End With

End Sub

Works with MDX, Too

Our examples have illustrated a simple DAX expression querying an entire table. In the next installment, we shall use QueryTables to look at some more interesting queries. It is important to note, however, that queries executed against a PowerPivot database can also be written in the MDX language, which provides a powerful alternative to DAX for some queries.

How to Create DAX Summary Queries – The GUI Way

Now that we have seen how to create a QueryTable from scratch, we will look at the “easy” way to test queries. This method only works if there is already a query table on a worksheet. Probably the easiest way to obtain a QueryTable connected to PowerPivot is to double-click on a cell within a PowerPivot pivot table that supports drilldown. When you double-click, a new worksheet will be created with results relevant to the cell you double-clicked on. Right now, we don’t care at all about the results; we are only interested in the query table results. If you right-click anywhere within a range defined by a QueryTable, you will see “Table” on the context menu. Choosing “Edit Query” from the submenu provides the Edit Ole DB Query dialog that, despite its name, allows you to enter a completely new DAX or MDX query. (If you right-click on a range created by our QueryTable code rather than a table created by a PowerPivot drilldown, there is no “Table”, so when you right-click you will immediately see “Edit Query…”) The query will be executed when you click OK.

Table  Edit  Query Menu
A QueryTable, in this case supporting an Excel Table, provides an Edit Query dialog.
Edit OleDb Query Dialog
The Edit Query dialog shows you the PowerPivot connection string and the query supporting the current QueryTable. You can edit this query or replace it with a completely new one.

Using QueryTables to Learn More About DAX.

ComputerUser001

Although the user interface is meager and does not provide any sort of syntax coloring, we can still use this dialog to test queries and functions. There is, however, one very important limitation. Because the results are to be copied into a worksheet, the DAX expression must return a row-and-column result. There may be just one row and just one column, but the data must still be presented in row-and-column format.

When writing simple test queries in DAX, it is often valuable to use Evaluate() in combination with Row(). We have already seen that evaluate with a PowerPivot table name as an argument returns the table. This is an easy way to see if things are working, but it is dangerous if you have a large table. You can swamp your RAM and you workbook; it will not be pretty.

Row() allows us to generate scalar results from some function we wish to learn about and get results in a row format for display in a worksheet. For example, if we wished to see how the weekday() function worked, we could create a DAX expression that returns one row and one column with the heading “Weekday Test”.

EVALUATE(ROW(“Weekday Test”, WEEKDAY(“02/06/2012”)))

Weekday Function Test
Testing the Weekday() Function using Evaluate() and Row()

If you would like a taste of a slightly more sophisticated query, let’s add a computed column to the ProductCategory table:

EVALUATE( ADDCOLUMNS(ProductCategory

, “Reseller Sales”, SUMX(RELATEDTABLE(ResellerSales), ResellerSales[SalesAmount])

, “Internet Sales”, SUMX(RELATEDTABLE(InternetSales), InternetSales[SalesAmount]) ) )

Here’s a look at the last three columns of the result:

AddColumn Function Test
Using AddColumn to add calculated columns to table output.

The Bottom Line:

QueryTables are not only of great practical importance, but they can also provide an easy platform for learning about DAX and experimenting with DAX functions.

In the next installment, we’ll look at some useful DAX focusing on the aptly named Summarize() function.

Please do not hesitate to drop me a note if you have any comments or questions.

PS – If you would like to learn more about using PowerPivot for Excel & how to analyse date with the DAX language, check out the Learning Tree course – PowerPivot for Excel: Mining Data for Business Intelligence.

Type to search blog.learningtree.com

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.