DAX Studio Parameters Dialog

I recently updated the documentation on daxstudio.org to include a page on the Parameter Support, but I thought this might also benefit from a longer explanation.

It may surprise some people to hear this, but the DAX language has supported parameters in queries since it was first released. However while you can use parameters in a DAX query you cannot easily test parameterized DAX by supply the parameter values as variables like you can in T-SQL. In DAX the parameters are sent in a separate part of the XMLA as part of the XMLA command’s parameters collection. Historically one of the biggest issue with parameters in DAX (and MDX) has been the client tool support. From what I have experienced, they are really only used in Reporting Services reports. However with the addition of “paginated” reports to the PowerBI.com we may start to see a resurgence of these types of reports.

Up until recently the tooling around parameters in SSRS using DAX queries was not that great and took a bit of manual hacking between MDX and DMX to get it working. However now you can add a parameter to your DAX query in SSRS with a simple checkbox:

image

This generates a query like the following with the a parameter (highlighted in yellow). The pattern that the SSRS designer is relatively complex, but that’s because it’s building generic code that can also deal with multi-select parameters:

DEFINE
    VAR ProductColor1 =
        IF (
            PATHLENGTH ( @ProductColor ) = 1,
            IF ( @ProductColor <> "", @ProductColor, BLANK () ),
            IF (
                PATHITEM ( @ProductColor, 2 ) <> "",
                PATHITEM ( @ProductColor, 2 ),
                BLANK ()
            )
        )
    VAR ProductColor1ALL =
        PATHLENGTH ( @ProductColor ) > 1
            && PATHITEM ( @ProductColor, 1, 1 ) < 1
EVALUATE
SUMMARIZECOLUMNS (
    'Product'[Product Name],
    'Product'[Class],
    'Product'[Color],
    FILTER (
        VALUES ( 'Product'[Color] ),
        ( ( ProductColor1ALL
            || 'Product'[Color] = ProductColor1 ) )
    )
)

Then when your run your report the value for this parameter gets injected into the query as a string value.

This is all fine within the SSRS designer, but what happens when you need to do some performance tuning on the query? If you are using a tool like SSMS then you have no choice but to do a find and replace on the query text and replace the @ProductColor value with something like “Red” to test your query, but then you need to make sure to do the reverse of this operation before pasting your final query back into SSRS. This is a bit of a hassle with one parameter, but it can be a real pain and potentially error prone if you have 10 parameters?

XMLA Parameter Blocks

Well for a couple of years DAX Studio has supported XMLA parameter blocks so that you could run queries like the following. However the XMLA parameter syntax is a bit arcane and not the easiest thing to remember, but if you have an example to start from it’s not too bad.

image

Why did we choose the <Parameters> XMLA block as a format? Well if you create a trace using SQL Profiler that listens for QueryBegin events you will see text exactly like the query in the screenshot above, with the text of the query followed by the XMLA parameters block. This made it possible to capture SSRS queries from a SQL Profiler trace and then just paste the straight into DAX Studio and execute them.

This was a good start, but if you want to capture slow running queries you would probably setup or trace to listen to the QueryEnd events which contain the duration of the query, but does not include the XMLA parameter block. While you could listen for both the QueryBegin and QueryEnd events in order to be able to see both the durations and XMLA parameters you then need to scroll up and down to try and match the begin to the end events

The *New* Parameter Dialog

As of DAX Studio 2.8.0 we introduced a new Parameters Dialog, this should be triggered anytime you run a query that has parameters in the query, but no XMLA parameters block.

image

You can then run the query multiple times, filling in the value of the parameters each time. Or you can click the “Write Parameter XML” button in the bottom left to generate an XMLA parameter block. This will generate and XMLA parameter block and insert it underneath the query and then you can re-run the query multiple times without having to enter the values into a dialog box each time.

image

Merging Parameters

In addition to the ability to run queries with XMLA parameter blocks we also have the “Merge Parameters” button on the ribbon which will take an existing query with a parameter block and merge the parameter values into the query.

merge-parameters-before

This will take a query like the one above and producing the text below:

merge-parameters-after

This can be useful if you know that you just need to work on tuning a measure and you don’t want to bother with parameters. Or you could use this to paste the query into a unit testing framework like the excellent NBI 

Conclusion

So now with DAX Studio you have an easy way of working with parameters in DAX queries. Either for working with queries generated by the SSRS query builder or even just as a way of running your own queries with a range of different parameters.

Print | posted on Tuesday, February 19, 2019 9:31 AM

Comments on this post

comments powered by Disqus