# Finding Out Which MDX Calculations Are Being Evaluated By Your Query In Analysis Services Multidimensional, Part 1

Since Analysis Services 2012 there have been two trace events that provide a lot of information about what’s going on in the Formula Engine when you run a query in Analysis Services Multidimensional: Calculation Evaluation and Calculation Evaluation Detailed Information. The problem is that they are not properly documented anywhere and they provide so much information that it’s difficult to interpret what they are telling you. This post on Thomas Ivarrsson’s blog (which I strongly advise you to read before you carry on) with information provided by Akshai Mirchandani of the dev team  is the only place that has any details about them and unfortunately it’s by no means comprehensive.

I don’t have the knowledge to provide a full description of these two trace events, so instead in this series of posts I want to do something less ambitious but hopefully still useful: show how you can use them to find out which MDX calculations are being evaluated when you run a query, which is of course going to be useful if you are trying to tune that query. It’s not always as easy as you might think to work out which calculations are referenced by a query: for example financial cubes often have hundreds of calculated members and/or scoped assignments, many of which are dependent on other calculations.

Here’s a super-simple example to start off with. Imagine you have a cube with just one regular measure, Sales Amount, and just one calculated measure with the following definition:

```CREATE MEMBER
CURRENTCUBE.MEASURES.[Sales Forecast] AS
[Measures].[Sales Amount] * 2;

```

Now, consider the following query:

```WITH
MEMBER MEASURES.X as 123
SELECT
{[Measures].[Sales Forecast]}
ON 0
,
[Date].[Date].[Date].MEMBERS
ON 1
FROM
[test]
CELL PROPERTIES VALUE

```

The query returns the Sales Forecast calculated measure on columns and every member on the Date level of the Date hierarchy on rows – so not all that interesting. However there are two things to point out:

• The WITH clause has a calculated measure that isn’t used in the query. The reason I’ve put this in the query is to stop the Formula Engine from caching the results of any MDX calculations for longer than the lifetime of the query (see here for more details); it doesn’t affect the Storage Engine cache however. This means that every time the query is run you know that all the calculations will be evaluated and that you’ll be able to see any related activity in Profiler, and that you can run the query on a warm Storage Engine cache and won’t see many Storage Engine-related events.
• The CELL PROPERTIES clause only returns the VALUE property and not the FORMAT_STRING property which is normally returned as well. This reduces the number of Calculation Evaluation events that are raised in Profiler when the query runs and makes it easier to see the important information.

With a Profiler trace that includes the Calculation Evaluation and Calculation Evaluation Detailed Information events, when you run the query above you’ll see this:

There are a lot of events generated in the trace even for this simple query, but the important thing to look for is the line highlighted in the screenshot above: a Calculation Evaluation Detailed Information event with the following event subclass:

107 – RunEvalNode Finished Calculating Item

Any time you see this event you know that a calculation has been evaluated in bulk mode for a subcube (ie an area of cells) in your cube. You may see more than one RunEvalNode event for the same calculation in the same query if it was evaluated for more than one subcube.

The contents of the TextData column (which is displayed in the lower half of the screen in Profiler) for the RunEvalNode event in the trace shown above are as follows:

As you can see, it tells you the MDX expression that has been evaluated for the subcube. It also tells you the name of the calculated measure, but it’s the MDX expression that’s important here because scoped assignments that overlap with a single calculated measure could mean that many different MDX expressions must be evaluated for that calculated measure.

Now for the bad news: you won’t see a RunEvalNode event for any calculations that are evaluated in cell-by-cell mode. You probably know that inefficient or badly-written calculations are often evaluated in cell-by-cell mode, which is usually slower than bulk mode, but there are cases where the Formula Engine evaluates a perfectly good calculation in cell-by-cell mode because it’s the right thing to do. For example, take a look at the following query:

```WITH
MEMBER MEASURES.X as 123
SELECT
{[Measures].[Sales Forecast]}
ON 0
FROM
[test]
CELL PROPERTIES VALUE

```

It’s basically the same query as the one above but with the Rows axis removed, so it only returns a single cell. In Profiler you won’t see a RunEvalNode event because in this case the Sales Forecast calculation is evaluated in cell-by-cell mode.

That said you will see other events relating to the evaluation node for the Sales Forecast calculation, such as the Calculation Evaluation event shown here, the last for this node (NodeIndex=0, the same value that is shown in the IntegerData column) in the trace:

Notice also the LazyEvaluation tag which is 1, which indicates a calculation that is evaluated in cell-by-cell mode.

So, to sum up, there are two ways to see which calculations are referenced by your query. With a Profiler trace and that includes the Calculation Evaluation and Calculation Evaluation Detailed Information events:

1. If your calculation is evaluated in bulk mode you will see a Calculation Evaluation Detailed Information event with the Event Subclass 107 – RunEvalNode Finished Calculating Item.
2. If your calculation is evaluated in cell-by-cell mode you will see Calculation Evaluation events for the Init-Build-Prepare stages of the evaluation node.

In the next post in this series I’ll look at a more complex scenario that shows some unexpected behaviour by SSAS.

[I am extremely grateful to Akshai Mirchandani for answering a lot of questions relating to this topic. If you want to learn more about the internals of the Formula Engine there are two other useful resources: this post by Jeffery Wang, also of the dev team, and chapter 29 of the book “Microsoft SQL Server 2008 Analysis Services Unleashed”]

# The List.* M Functions And The equationCriteria Argument

Last week a reader left a comment on my blog asking what the third argument of the List.Contains() M function (somewhat cryptically called equationCriteria) does. I didn’t know, so I did some research and found out that lots of the List.* functions have the same argument. The documentation for List.Distinct() has a few examples but no real explanation of how they work. It also says:

…but there’s no link or indication where the documentation on ‘Parameter Values’ can be found. A bit more digging led me to the bottom of this page:

##### Equation criteria

Equation criteria for list values can be specified as either a

• A function value that is either
• A key selector that determines the value in the list to apply the equality criteria, or
• A comparer function that is used to specify the kind of comparison to apply. Built in comparer functions can be specified, see section for Comparer functions.
• A list value which has
• Exactly two items
• The first element is the key selector as specified above
• The second element is a comparer as specified above.

Still not exactly helpful, is it? After a bit of time testing, though, I think I’ve worked out how what’s possible with the equationCriteria argument and this blog post will, I hope, help any future M coders who are struggling with the same question. Let’s see some examples…

The basics

First of all, the basics. The following expression using List.Contains() returns TRUE because the text value “apples” appears in the list {“apples”, “oranges”, “pears”}:

```List.Contains({"apples", "oranges", "pears"}, "apples")
```

The following returns FALSE because the text value “grapes” does not appear in the list {“apples”, “oranges”, “pears”}:

```List.Contains({"apples", "oranges", "pears"}, "grapes")
```

However there are lots of different ways that text values can be compared and the equationCriteria argument allows you to specify which rules to follow.

Case sensitivity and culture

If you’ve written any M code you’ll know that it is case sensitive. As a result, the following returns FALSE:

```List.Contains({"apples", "oranges", "pears"}, "Apples")
```

What happens if you want to do a case-insensitive comparison though? This is where the Comparer functions come in. The Comparer.FromCulture() function returns a function that compares two values according to the rules of a given culture or locale and optionally ignore case, and can be used in the equationCriteria argument. The following example returns TRUE:

```List.Contains(
{"apples", "oranges", "pears"},
"Apples",
Comparer.FromCulture("en-GB", true)
)
```

In this case Comparer.FromCulture(“en-GB”, true) returns a function that compares two values for the English – Great Britain culture (for a full list of culture values, see the Language Tag column of the table on this page); the second, optional argument here makes the function ignore case when making the comparison. The function that Comparer.FromCulture() returns is then used by List.Contains() to make the comparison.

Rather than specify a culture you can also use the Culture.Current function to return the current system culture. For me, Culture.Current returns the value “en-GB” because I live in Great Britain and have my PC configured to use a British English locale:

The following example shows how Culture.Current can be used with Comparer.FromCulture and also returns TRUE, at least for me:

```List.Contains(
{"apples", "oranges", "pears"},
"Apples",
Comparer.FromCulture(
Culture.Current,
true
)
)
```

If you’re curious to see an example where different cultures produce different results here’s one I stole from this article on string comparisons and sorting in .NET. Apparently in English the character æ is treated the same as the combination of the two characters ae but this is not the case in Danish. As a result the following returns TRUE:

```List.Contains(
{"aepples", "oranges", "pears"},
"æpples",
Comparer.FromCulture(
"en-GB",
true
)
)
```

Whereas this returns FALSE:

```List.Contains(
{"aepples", "oranges", "pears"},
"æpples",
Comparer.FromCulture(
"da-DK",
true
)
)
```

Ordinal comparisons

If you don’t want all the uncertainty of cultures and case sensitivity you can just make an ordinal comparison, which will compare two strings by finding the unicode character value for each character in each string and compare those values. To do this you can use the Comparer.Ordinal() function. The following returns FALSE:

```List.Contains(
{"apples", "oranges", "pears"},
"Apples",
Comparer.Ordinal
)
```

…because “a” is not the same unicode character as “A”, and so “apples” and “Apples” are not treated as equal.

Custom comparer functions

As the documentation hints you can also write your own function to do the comparison. A comparer function is just – as far as I can see – a function that has two arguments and returns a logical value. Here’s an example of a custom function that takes two text values, x and y, and returns true if the first three characters of x are the same as y:

```(x as text, y as text)=>Text.Start(x,3)=y
```

It can be used with List.Contains() as in the following example, which returns TRUE:

```List.Contains(
{"apples", "oranges", "pears"},
"app",
(x as text, y as text)=>Text.Start(x,3)=y
)
```

What must be happening here is that the function is called three times, every value in the list {“apples”, “oranges”,”pears”} is being passed to the x argument and for each call “app” is passed to y; because the first three characters of “apples” are “app” the function returns true in this case, so List.Contains() returns true.

Key selectors

If you’re working with a list of records you might only want to do the comparison on one field in the record, and this is what key selectors allow you to do. The following example, which returns TRUE:

```List.Contains(
{[Fruit="apples", Colour="Red"],
[Fruit="oranges", Colour="Orange"],
[Fruit="pears", Colour="Green"]},
[Fruit="apples", Colour="Russet"],
each [Fruit]
)
```

…does so because it only compares the Fruit field in each record, and the Fruit fields in [Fruit=”apples”, Colour=”Red”] and [Fruit=”apples”, Colour=”Russet”] are indeed the same. However the following example returns FALSE:

```List.Contains(
{[Fruit="apples", Colour="Red"],
[Fruit="oranges", Colour="Orange"],
[Fruit="pears", Colour="Green"]},
[Fruit="apples", Colour="Russet"],
each [Colour]
)
```

…because the Colour “Russet” does not appear anywhere in the Colour field of any of the records in the first parameter.

Combining key selectors and comparison functions

Finally, as the documentation suggests, you can combine the above methods of comparison by passing a list containing two items to equationCriteria: the first item in the list must be a key selector, the second must be a comparer function. For example, the following returns TRUE:

```List.Contains(
{[Fruit="apples", Colour="Red"],
[Fruit="oranges", Colour="Orange"],
[Fruit="pears", Colour="Green"]},
[Fruit="Apples", Colour="Russet"],
{each [Fruit], Comparer.FromCulture("en-GB", true)}
)
```

…because it only looks at the Fruit field of each record, and it does a case-insensitive comparison using the en-GB culture, so “apples” and “Apples” are equal.

# More Detail On Detail Rows Expressions In SSAS Tabular V.Next

My second-favourite feature in SSAS Tabular v.next after Power Query integration is the Detail Rows expression property for measures – it not only brings drillthrough on measures to Tabular, it means that we can define meaningful drillthrough on any measure, no matter how it is calculated. There’s a basic description of the functionality in this blog post but I thought it would be useful to walk through a simple example showing how it can be used.

Consider a simple SSAS Tabular model with two tables in it. First, a table containing sales data called Sales:

Second, a date table called Date:

[Note: dates are in DD/MM/YYYY format]

There are two measures with the following definitions:

```Total Sales:=SUM(Sales[Sales])

Total YTD Sales:=TOTALYTD([Total Sales], 'Date'[Date])
```

The measure Total YTD Sales gives the running total of sales from the beginning of the current year. I know it doesn’t follow Marco and Alberto’s best-practice pattern but I wanted to keep things simple on the DAX front…

Browsing the model in an Excel PivotTable gives the following result:

At this point if you double-click on cell C6 in the PivotTable you get the following, not very useful result, on a new worksheet:

What drillthrough does in SSAS Multidimensional, and what the new Detail Rows Expression property in SSAS Tabular v.next does, is allow an end user to see the detail-level data (usually the rows in the fact table) that was aggregated to give the value the user clicked on in the original PivotTable.

For the Total Sales measure, this property can be set with a DAX expression that returns a table something like this:

```SELECTCOLUMNS(
'Sales',
"Date", 'Sales'[Date],
"Sales Value", [Total Sales]
)
```

[For more details on the SelectColumns() function, see here]

Now when you click on cell C6 in the PivotTable you get the result of the table expression above filtered by the context of the cell you’ve clicked on – in this case, the date 4/1/2017. What appears in the new worksheet is data from the row from the Sales table for 4/1/2017:

This is already better than SSAS Multidimensional drillthrough because as a developer you have control over the column headers displayed in this table (in Multidimensional drillthrough the column names come out in a ridiculously user-unfriendly format) and the order that they are displayed in (which is equally painful to control in Multidimensional).

Now, consider cell D6 in the PivotTable, the cell that shows the year-to-date sales amount for 4/1/2017. If a user double-clicked on this cell they would expect to see all of the rows from the Sales table from 1/1/2017 to 4/1/2017, the rows whose sales have been aggregate to give the YTD total.

This can be achieved using the following expression in the Detail Rows Expression for the Total YTD Sales measure:

```CALCULATETABLE(
SELECTCOLUMNS(
'Sales',
"Date",
'Sales'[Date],
"Sales Value",
[Total Sales]
),
DATESYTD('Date'[Date])
)
```

Now, double-clicking on cell D6 in the PivotTable gives the following table:

It may not look all that impressive, but there are few words that can describe how happy this makes me feel. This is exactly what is not possible with drillthrough in SSAS Multidimensional, and why drillthrough in Multidimensional has always been so frustrating to use. It’s great to see the feature properly implemented in Tabular.

# Record.AddField(), Functions And The Delayed Option In M

Today I was looking at the Record.AddField() M function and saw it had a mysterious – and badly documented – optional fourth argument called delayed. Of course I had to work out what it did, and having done so I thought I’d blog about it in case someone found it useful (and however obscure the topic, there’s always someone out there who finds this stuff useful).

Imagine you have an M function called ReturnANumber that has no arguments at all and returns the number 999:

```() as number => 999
```

Now imagine that you want to return this function in a record (which is exactly what #shared does). The following expression:

```[MyFunction = ReturnANumber]
```

…returns a record with one field whose value is of type function:

But what if you wanted the field to contain the number that the function returns, not a reference to the function itself? The delayed option of Record.AddField() allows you to do this: if you set it to true, you get the value the function returns.

```Record.AddField([], "MyFunction", ReturnANumber, true)
```

Setting delayed to false does the same as the first example above:

```Record.AddField([], "MyFunction", ReturnANumber, false)
```

Another way to get the same result as setting delayed to true is to use Function.Invoke():

```[MyFunction = Function.Invoke(ReturnANumber,{})]
```

Now I need to think of a real-world use for this…

# Web.Contents(), Caching And The ExcludedFromCacheKey Option In Power BI And Power Query

When you’re using the Web.Contents() M function to call a web service from Power Query or Power BI, you don’t necessarily get one HTTP request each time you call the function: some caching takes place, so that if you make the same request multiple times your query won’t waste time asking for the same data over and over. In this post I’m going to share the results of some tests I made to show how caching works with Web.Contents() and what factors influence it.

For my tests I built a simple web service in Microsoft Flow, similar to the one I blogged about here, that accepts a HTTP POST request and calls a stored procedure in an Azure SQL Database. The stored procedure then updates a table in the database and this in turn allows me to count the number of times the web service is called. Finally, the web service returns the value 0 if the stored procedure has executed successfully.

This web service can then be called from either Power Query or Power BI using the Web.Contents() function, something like this (because the URL for the web service is very long I stored it in a parameter called WebServiceURL):

```let
Source = Web.Contents(
WebServiceURL,
[Content=Text.ToBinary("Hello")]
),
#"Imported JSON" = Json.Document(Source,1252)
in
#"Imported JSON"
```

The output of the query when run in Power Query and loaded to an Excel table is this:

The first important thing to point out is that the above query, when refreshed in the latest versions of Power Query (I’m running Excel 2016 build 7571.2109) and Power BI (build 2.41.4581.361- November 2016 release), results in a single call to the web service. It might seem like I’m stating the obvious but in the past I’ve seen plenty of cases where a data source has been queried multiple times by Power Query/Power BI even if I was only expecting it to be queried once.

Now, let’s look at a query that calls this web service several times. Here’s the query above converted to a function called fnCallWebService:

```() =>
let
Source = Web.Contents(
WebServiceURL,
[Content=Text.ToBinary("Hello")]),
#"Imported JSON" = Json.Document(Source,1252)
in
#"Imported JSON"
```

Here’s a query that calls this function once for each row of the following table:

```let
Source = Excel.CurrentWorkbook(){[Name="MyTable"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Row", Int64.Type}}),
#"Changed Type",
"fnCallWebService",
each fnCallWebService())
in
#"Invoked Custom Function"
```

In the query above I used the Invoke Custom Function button to call the function for each row in the source table and put the value returned by the function in a new column. The output is this:

Even though the function is called four times, once for each row in the original table, that does not mean that the web service is called four times – it isn’t, it’s only called once. In this case Power BI/Power Query knows that each of the four calls to the function is making an identical request to the web service and so it only goes to the web service once, and thereafter uses a cached result the other three times.

One way to stop this caching from taking place is to add an HTTP header to the request to the web service and pass a different value to that header for each call. Here’s another version of my function, now called fnCallWebServiceWithHeaders, which this time takes a number as a parameter and then passes that number to the web service via a header called MyHeader:

```(RowNum as number) => let
Source = Web.Contents(
WebServiceURL,
[Content=Text.ToBinary("Hello"),
#"Imported JSON" = Json.Document(Source,1252)
in
#"Imported JSON"
```

Now if I call this function for each row of the table, and for each call pass the value in the [Row] column through to the function like so:

```let
Source = Excel.CurrentWorkbook(){[Name="MyTable"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Row", Int64.Type}}),
#"Changed Type",
in
#"Invoked Custom Function"
```

…the web service gets hit four times. The presence of a different value for the MyHeader header in each request is enough to prevent any caching from taking place.

It is possible, however, to get Power BI/Power Query to ignore one or more headers when working out whether caching should take place using the ExcludedFromCacheKey option in Web.Contents(). Here’s one more version of my function, now called fnCallWebServiceWithHeadersExlCache, which uses this option:

```(RowNum as number) =>
let
Source = Web.Contents(
WebServiceURL,
[Content=Text.ToBinary("Hello"),
#"Imported JSON" = Json.Document(Source,1252)
in
#"Imported JSON"
```

The ExcludedFromCacheKey option takes a list of text values which represent the names of headers that are to be ignored when considering which requests can be cached. In the example above my list contains just the one header, MyHeader, and when this is used in a query like so:

```let
Source = Excel.CurrentWorkbook(){[Name="MyTable"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Row", Int64.Type}}),