DAX Studio (Beta) Released

The first installers for DAX Studio are released.

You can get them here

(x64) – http://daxstudio.codeplex.com/downloads/get/463653

(x32) – http://daxstudio.codeplex.com/downloads/get/463654

Advertisements

DAX II – Extending the use of Evaluate

In a previous post, I looked at evaluate as the method to return a table in a row-set form. However, the query syntax allows us to more precisely define the result set and this is the focus of this post.

Books on line specifies three optional arguments that can be used in query syntax to control, there are DEFINE, ORDER BY and START AT. The syntax follows the format;

 

[DEFINE { MEASURE <tableName>[<name>] = <expression> }

DAX Querying Part I

This post looks the basics for querying tabular models using a DAX query. While Tabular models can be queried with MDX, DAX is the native language for tabular databases and may provide better results than MDX. This post looks how to building a simple table in DAX so that a row set can be exposed.

The basis of record-set queries in DAX is the evaluate statement which returns the table that is defined in the functions argument. The argument can be any statement that forms a table. For example, we can simply pass a table to the statement and return all the rows and columns from it;

evaluate 
(  
    ‘dates’ 
)

 

More likely though, we will want to define table which is defined by our query, that is, we define the table which will return the results needed. As more and more DAX queries are used and the community develops its standards, it is likely that there will be a query pattern which forms. For now though, consider that the argument of evaluate can be anything that is returned as a table.

Consider selecting distinct years. Since the values function returns a table (of a single column), we can be use the values function as the argument for an evaluate.

evaluate 
(  
    values(‘dates'[year])
)

 

Similarly, summarize returns a table and produce the same results.

evaluate 
(  
    summarize
    ( 
        ‘dates’
        , ‘dates'[year]
    )
)

 

The key point here is that we need to define a table as part of the evaluate statement.

Extending the Defined Table with ADDCOLUMNS

Usually, we want our defined table to combine data from another table. There are a few ways we can go about this and one method is to use the ADDCOLUMNS function. As the name suggests, ADDCOLUMNS adds a calculated column to a table. Note that a new column is defined both its name and expression.

ADDCOLUMNS(<table>, <name>, <expression>[, <name>, <expression>]…)

 

It is interesting to note the evaluation context of the expression. While we may expect a calculation to be evaluated in the context of the row it is being added to, the context applies to the entire model. For example, consider summing the sales amount by year. Extending our yearly values table by adding a column to sum sales amount is evaluated in the context of the entire sales table (as below). This is akin to the query context in MDX for measures that are defined with the WITH MEMBER MEASURES.[Measure Name].

 

evaluate
(

    addcolumns
    (
        values(‘dates'[Year])
        , “yearly sales”sum(‘Sales'[amount])
    )

)

 

In order to change the evaluate context of the calculation to the row, we must use the calculate statement.

evaluate
(
    addcolumns
    (
        values(‘dates'[Year])
        , “yearly sales”calculate(sum(‘Sales'[amount]))
    )
)

 

Any filters that are applied with the calculation context are evaluated with the calculations context. Therefore (as expected), to retrieve a single store sales, we would include it in the calculate statement.

evaluate
(
    addcolumns
    (
        values(‘dates'[Year])
        , “yearly sales”calculate(sum(‘Sales'[amount]))
        
        , “store 1 sales”calculate(sum(‘Sales'[amount])
                                    , ‘Stores'[store_name]=“Store 1”
                                    )
    )

)

 

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).