There were not very many changes to MDX syntax in AS2008, and Vidas Matelis described most of them in his blog here (for his list of all changes in AS2008 check this blog entry). I just noticed that there is at least one more change which Vidas didn’t include, but which is somewhat important: ability to define CREATE SUBCUBE and subselects in non visual mode.

To understand this, we need to understand what CREATE SUBCUBE does. It really does two things:

1. Implicit Exists between the set in CREATE SUBCUBE and query axis, query named sets including top level set functions (for more details see blog "Slicer and axis interaction in MDX Part 2 - Implicit Exists" – even though it talks about WHERE clause, everything from that entry equally applies to subselects and CREATE SUBCUBE as well)

2. Applies visual totals to the values of physical measures even within expressions if there are no coordinate overwrites.

Let’s look into this deeper. Consider the following example:

CREATE SUBCUBE [Adventure Works] AS 
 (SELECT 
    {
       [Customer].[Customer Geography].[City].&[Redmond]&[WA]
      ,[Customer].[Customer Geography].[City].&[Seattle]&[WA]
    } ON 0 
  FROM [Adventure Works])

We are creating filter to include only two cities – Redmond and Seattle. Now if we send the following query:

WITH 
 MEMBER [Measures].[Gross Margin] AS [Measures].[Internet Gross Profit]/[Measures].[Internet Sales Amount]
   ,FORMAT_STRING = 'Percent' 
SELECT {[Measures].[Internet Sales Amount], [Measures].[Internet Gross Profit],[Measures].[Gross Margin]} ON 0
, [Customer].[City].MEMBERS ON 1
FROM [Adventure Works]

We will observe two things. First, we only get two cities on row axis – that’s the implicit exists part. Second, the values for physical measure Internet Sales Amount, cube defined calculated measure Internet Gross Profit and query defined calculated measure Gross Margin – are all visual totals, i.e. they all are computed using data for only two cities defined by the CREATE SUBCUBE command.

AS2008 adds capability to only keep implicit exists behavior, and to return real totals as opposed to visual totals. The syntax is described in MSDN documentation and in our example it will be (note that NON VISUAL keywords)

CREATE SUBCUBE [Adventure Works] AS NON VISUAL
 (SELECT 
    {
       [Customer].[Customer Geography].[City].&[Redmond]&[WA]
      ,[Customer].[Customer Geography].[City].&[Seattle]&[WA]
    } ON 0 
  FROM [Adventure Works])

Now if we execute the same query, the result is different:

The implicit exists still happens, we see only two cities, but there is no visual totals anymore. Both physical and calculated measures are computed now as if there were no CREATE SUBCUBE. Same functionality applies also to subselects (documented on MSDN here).

Why is this important ? Remember, that Excel 2007 uses subselects to implement multiselect functionality. However, since subselects only supported visual totals mode before AS2008, Excel team was forced to remove ability to show non-visual totals in PivotTables. This caused users dissatisfaction, since users wanted to have control over numbers in PivotTables, just like with previous versions of Excel and Analysis Services. This problem is well documented, for example in "Common Questions Around Excel 2007 OLAP PivotTables" blog entry by Allan Folting from Excel team. So implementing NON VISUAL mode in AS2008 is a key enabling feature to Excel to be able to restore this PivotTables functionality in the future releases.