Home > BISM, DAX, MDX, PowerPivot, SSAS > Introducing DAX Studio

Introducing DAX Studio


If you have tried to query a tabular model using DAX, you will soon realise that it is not an easy undertaking. Why is it hard? Well, for one, SSMS isn’t currently much of a friend for tabular models. When you connect to a tabular instance with SSMS the model is displayed in a multidimensional format. The only tabular meta-data which you can easily derive are some table names (dimension names), some field names based on attribute hierarchies and the names of measures (without context for the table that they are built in). This post looks at DAX Studio which is an Excel 2010 Addin available on CodePlex. It is designed for DAX and tabular models. When released, the installer can be down loaded from here.

What’s the Issue with SSMS?

SSMS does not show useful metadata for building DAX queries. Further, its functionality is suited to MDX queries with the drag and drop name placement showing the multidimensional naming standard. In this post, I have created a really simple tabular model with three tables as shown below. It has a sales fact and dimensions of Stores and dates and you can see that all the fields from the Sales table are hidden from client tools except for calculated values (‘Sales Amount’ and ‘Store Ratio’).

When I connect to the model in SSMS (as below), the model presents itself as a multidimensional model.

Some of the implications of using SSMS are that;

  1. The object explorer in SSMS shows only tables and you can’t easily determine fields.

     

  2. Visible table fields are shown as attribute hierarchies so (for example) we can surmise that the dates table has fields named Month and Year.

     

  3. When all the fields for a table are hidden (including hierarchies), the table is not shown as a dimension in the multidimensional explorer. This behaviour may be expected but it doesn’t allow you to see the structure for the table. In the object explorer, you can see that there are three tables (dates, Sales and Stores) which are the same tables as in the model. In the query window, tables in the model are exposed as dimensions to the extent that at least one table field (or user hierarchy) is visible. Note that the Sales table is not visible is the query browser because the table has no visible fields in the model.

     

  4. Measures are shown under a display folder with the same name as the table in which the measures were created. The measures defined in the Sales table appear in the Sales display folder. Again, this may be expected however, as a user, I cannot do not see a tabular view of the objects in the model (fields and calculations).

     

  5. Hidden fields and measures are not visible. Again while you may expect this, the nature of DAX may require you to query a hidden field from a table.

     

In general, the main concern for using SSMS is that you cannot see the model structure and metadata. You can derive some of it (for example, through attribute hierarchies), but SSMS you does not allow you convenient access to the model structure. Additionally, when you drag an object which you would expect to be a field (for example store_name in the above model), the field appears with the multidimensional naming convention ([Stores].[store_name]).

What’s the Solution

Dax Studio is an Excel 2010 Addin available on CodePlex that allows you to connect to any tabular model, examine the metadata and query it. Excel is a good launch application because it allows the PowerPivot pro’s to also query their models without the requirement of a tabular server. Once installed, you will notice that the Add-Ins tab on the ribbon appears (if you and DAX Studio is added as an icon). DAX Studio does not form part of the PowerPivot tab.

The tabular model used above is shown through DAX Studio and the model structure is immediately visible from the model explorer (metadata tab as shown below).


Query Results

As an Excel Addin, output are generally targeted at an excel sheet. You can set both the output method and target destination through the run button drop down and output results drop down. These are discussed below.

The Output Method (found with the Run button drop down) allows DAX table results to be outputted as a table (that is an excel table), Static Output (that is a Simple Sheet) or no result output (Validate which validates the command). These sample outputs are shown below.


Table Output

Static Output

Grid Output

 

The Output Target (identified below) specifies what sheet the results will be sent to (where an Excel sheet is the target of the output). By default results are sent to a sheet titled ‘DAXResults’. This is added if the sheet does not exist in your workbook.

Connecting to a Tabular Server

The current server connection is managed through the server icon and the active Model identified through the model drop down. These are both highlighted in the captures below.

When you connect to a server (dialog show below), allowed models are populated.

Server Connection Dialog

 

Connecting to PowerPivot Model

DAX Studio allows you to connect to a tabular model which is contained in a workbook that has a PowerPivot Model. In order to do this, you must launch DAX Studio from where the active cell is a PowerPivot Pivot Table. That is, select a cell in a Pivot Table that is derived from a PowerPivot model and then launch DAX Studio. You will notice that the model is titled Microsoft_SQLServer_AnalysisServer. You can see the outcome of these steps (1, 2 and 3) in the diagram below.

  1. Ensure that a PowerPivot Pivot Table is selected
  2. Launch DAX Studio (this icon is in the Addins Ribbon Tab)
  3. Note the Model name ‘Microsoft_SQLServer_AnalysisServer’

If you have launched DAX Studio from a PowerPivot Pivot Table, you can still connect to a tabular server and then reconnect to the Excel PowerPivot model at a later stage. You may have noticed that the connection dialog (above) specified no PowerPivot models in the Excel Workbook. If launched from a PowerPivot Pivot, this option is available and you can re-query the workbook model without reopening DAX Studio. This is shown in the two screen shots below

When DAX Studio is launched from a PowerPivot Pivot, the connection dialog identifies the workbook as a tabular model. You can still connect to a tabular server and then reconnect back to the workbook. Note the PowerPivot Model option in the connection dialog.

If you do not open DAX studio from a PowerPivot Pivot, a tabular model that is in the workbook is not available for use as a connection.

 

Other Cool Features

One other noteworthy feature of DAX Studio is the inbuilt support for SSAS Dynamic Management Views which can query both to Tabular and OLAP servers. The DMV tab exposes these schemas which (when dragged across) build the full select syntax (as shown below). I reiterate, the DMV queries can be run against existing OLAP servers (which you can connect to with the connection manager).

 

Categories: BISM, DAX, MDX, PowerPivot, SSAS Tags:
  1. ruve1k
    July 10, 2012 at 1:28 pm | #1

    This looks like an amazing tool.
    When can we get our hands on it?

    • July 10, 2012 at 7:37 pm | #2

      We are hoping to have the installer up in a week or so (so not to long)

  1. July 10, 2012 at 9:32 am | #1
  2. July 25, 2012 at 12:04 pm | #2
  3. July 30, 2012 at 5:31 pm | #3
  4. September 13, 2012 at 3:05 pm | #4
  5. October 26, 2012 at 7:52 pm | #5
  6. June 18, 2013 at 4:53 am | #6
  7. October 31, 2013 at 8:29 am | #7

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 158 other followers

%d bloggers like this: