Autoexists

Applies to: SQL Server Analysis Services Azure Analysis Services Fabric/Power BI Premium

The concept of autoexists limits the cube space to those cells that actually exist in the cube in contraposition to those that might exist as a result of creating all possible combinations of attribute hierarchy members from the same hierarchy. This is because members of one attribute hierarchy cannot exist with members of another attribute hierarchy in the same dimension. When two or more attribute hierarchies of the same dimension are used in a SELECT statement, Analysis Services evaluates the attributes' expressions to make sure that the members of those attributes are properly confined to meet the criteria of all other attributes.

For example, suppose you are working with attributes from the Geography dimension. If you have one expression that returns all members from the City attribute and another expression that confines members from the Country attribute to all countries/regions in Europe, then this will result in the City members being confined to only those cities that belong to countries/regions in Europe. This is because of the autoexists characteristic of Analysis Services. Autoexists only applies to attributes from the same dimension because it tries to prevent the dimension records excluded in one attribute expression from being included by the other attribute expressions. Autoexists can also be understood as the resulting intersection of the different attributes expressions over the dimension rows.

Cell Existence

The following cells always exist:

• The (All) member, of every hierarchy, when crossed with members of other hierarchies in the same dimension.

• Calculated members when crossed with their non-calculated siblings, or with the parents or descendants of their non-calculated siblings.

Providing Non-existing cells

A non-existing cell is a cell provided by the system as a response to a query or calculation that requests a cell that does not exist in the cube. For example, if you have a cube that has a City attribute hierarchy and a Country attribute hierarchy that belong to the Geography dimension, and an Internet Sales Amount measure, the space of this cube only includes those members that exist with each other. For example, if the City attribute hierarchy includes the cities New York, London, Paris, Tokyo, and Melbourne; and the Country attribute hierarchy includes the countries/regions United States, United Kingdom, France, Japan, and Australia; then the space of the cube does not include the space (cell) at the intersection of Paris and United States.

When querying cells that do not exist, non-existing cells return nulls; that is, they cannot contain calculations and you cannot define a calculation that writes to this space. For example, the following statement includes cells that do not exist.

SELECT [Customer].[Gender].[Gender].Members ON COLUMNS,  
{[Customer].[Customer].[Aaron A. Allen]  
   ,[Customer].[Customer].[Abigail Clark]} ON ROWS   
FROM [Adventure Works]  
WHERE Measures.[Internet Sales Amount]  

When you execute the previous query, the cell at the intersection of Aaron A. Allen and Female displays a null. Similarly, the cell at the intersection of Abigail Clark and Male displays a null. These cells do not exist and cannot contain a value, but cells that do not exist can appear in the result returned by a query.

When you use the Crossjoin (MDX) function to return the cross-product of attribute hierarchy members from attribute hierarchies in the same dimension, auto-exists limits those tuples being returned to the set of tuples that actually exist, rather than returning a full Cartesian product. For example, run and then examine the results from the execution of the following query.

SELECT CROSSJOIN  
   (  
      {[Customer].[Country].[United States]},  
         [Customer].[State-Province].Members  
  ) ON 0   
FROM [Adventure Works]  
WHERE Measures.[Internet Sales Amount]  

The previous query only returns cells for members from each attribute hierarchy in the query that exist with each other. The previous query can also be written using the new * variant of the * (Crossjoin) (MDX) function.

SELECT   
   [Customer].[Country].[United States] *   
      [Customer].[State-Province].Members  
ON 0   
FROM [Adventure Works]  
WHERE Measures.[Internet Sales Amount]  

The previous query could also be written in the following manner:

SELECT [Customer].[State-Province].Members  
ON 0   
FROM [Adventure Works]  
WHERE (Measures.[Internet Sales Amount],  
   [Customer].[Country].[United States])  

The cells values returned will be identical, although the metadata in the result set will be different. For example, with the previous query, the Country hierarchy was moved to the slicer axis (in the WHERE clause) and therefore does not appear explicitly in the result set.

Each of these three previous queries demonstrates the effect of the auto-exists behavior in SQL Server SQL Server Analysis Services.

Deep and Shallow Autoexists

Autoexists can be applied to the expressions as Deep or Shallow. Deep Autoexists means that all expressions will be evaluated to meet the deepest possible space after applying the slicer expressions, the sub select expressions in the axis, and so on. Shallow Autoexists means that external expressions are evaluated before the current expression and those results are passed to the current expression. The default setting is deep autoexists.

The following scenario and samples will help to illustrate the different types of Autoexistss. In the following examples two sets will be created: one as a calculated expression and the other as a constant expression.

//Obtain the Top 10 best reseller selling products by Name

with member [Measures].[PCT Discount] AS '[Measures].[Discount Amount]/[Measures].[Reseller Sales Amount]', FORMAT_STRING = 'Percent'

set Top10SellingProducts as 'topcount([Product].[Model Name].children, 10, [Measures].[Reseller Sales Amount])'

set Preferred10Products as '

{[Product].[Model Name].&[Mountain-200],

[Product].[Model Name].&[Road-250],

[Product].[Model Name].&[Mountain-100],

[Product].[Model Name].&[Road-650],

[Product].[Model Name].&[Touring-1000],

[Product].[Model Name].&[Road-550-W],

[Product].[Model Name].&[Road-350-W],

[Product].[Model Name].&[HL Mountain Frame],

[Product].[Model Name].&[Road-150],

[Product].[Model Name].&[Touring-3000]

}'

select {[Measures].[Reseller Sales Amount], [Measures].[Discount Amount], [Measures].[PCT Discount]} on 0,

Top10SellingProducts on 1

from [Adventure Works]

The obtained result set is:

The obtained set of products seems to be the same as Preferred10Products; so, verifying the Preferred10Products set:

with member [Measures].[PCT Discount] AS '[Measures].[Discount Amount]/[Measures].[Reseller Sales Amount]', FORMAT_STRING = 'Percent'

set Top10SellingProducts as 'topcount([Product].[Model Name].children, 10, [Measures].[Reseller Sales Amount])'

set Preferred10Products as '

{[Product].[Model Name].&[Mountain-200],

[Product].[Model Name].&[Road-250],

[Product].[Model Name].&[Mountain-100],

[Product].[Model Name].&[Road-650],

[Product].[Model Name].&[Touring-1000],

[Product].[Model Name].&[Road-550-W],

[Product].[Model Name].&[Road-350-W],

[Product].[Model Name].&[HL Mountain Frame],

[Product].[Model Name].&[Road-150],

[Product].[Model Name].&[Touring-3000]

}'

select {[Measures].[Reseller Sales Amount], [Measures].[Discount Amount], [Measures].[PCT Discount]} on 0,

Preferred10Products on 1

from [Adventure Works]

As per the following results, both sets (Top10SellingProducts, Preferred10Products) are the same

The following example will illustrate the concept of deep Autoexists. In the example we are filtering Top10SellingProducts by [Product].[Product Line] attribute for those in [Mountain] group. Note that both attributes (slicer and axis) belong to the same dimension, [Product].

with member [Measures].[PCT Discount] AS '[Measures].[Discount Amount]/[Measures].[Reseller Sales Amount]', FORMAT_STRING = 'Percent'

set Top10SellingProducts as 'topcount([Product].[Model Name].children, 10, [Measures].[Reseller Sales Amount])'

// Preferred10Products set removed for clarity

select {[Measures].[Reseller Sales Amount], [Measures].[Discount Amount], [Measures].[PCT Discount]} on 0,

Top10SellingProducts on 1

from [Adventure Works]

where [Product].[Product Line].[Mountain]

Produces the following result set:

In the above result set we have seven newcomers to the list of Top10SellingProducts and Mountain-200, Mountain-100, and HL Mountain Frame have moved to the top of the list. In the previous result set those three values were interspersed.

This is called Deep Autoexists, because the Top10SellingProducts set is evaluated to meet the slicing conditions of the query. Deep Autoexists means that all expressions will be evaluated to meet the deepest possible space after applying the slicer expressions, the sub select expressions in the axis, and so on.

However, one might want to be able to do the analysis over the Top10SellingProducts as equivalent to Preferred10Products, as in the following example:

with member [Measures].[PCT Discount] AS '[Measures].[Discount Amount]/[Measures].[Reseller Sales Amount]', FORMAT_STRING = 'Percent'

set Top10SellingProducts as 'topcount([Product].[Model Name].children, 10, [Measures].[Reseller Sales Amount])'

set Preferred10Products as '

{[Product].[Model Name].&[Mountain-200],

[Product].[Model Name].&[Road-250],

[Product].[Model Name].&[Mountain-100],

[Product].[Model Name].&[Road-650],

[Product].[Model Name].&[Touring-1000],

[Product].[Model Name].&[Road-550-W],

[Product].[Model Name].&[Road-350-W],

[Product].[Model Name].&[HL Mountain Frame],

[Product].[Model Name].&[Road-150],

[Product].[Model Name].&[Touring-3000]

}'

select {[Measures].[Reseller Sales Amount], [Measures].[Discount Amount], [Measures].[PCT Discount]} on 0,

Preferred10Products on 1

from [Adventure Works]

where [Product].[Product Line].[Mountain]

Produces the following result set:

In the above results, the slicing gives a result that contains only those products from Preferred10Products that are part of the [Mountain] group in [Product].[Product Line]; as expected, because Preferred10Products is a constant expression.

This result set is also understood as Shallow Autoexists. This is because the expression is evaluated before the slicing clause. In the previous example, the expression was a constant expression for illustration purposes in order to introduce the concept.

Autoexists behavior can be modified at the session level using the Autoexists connection string property. The following example begins by opening a new session and adding the Autoexists=3 property to the connection string. You must open a new connection in order to do the example. Once the connection is established with the Autoexist setting it will remain in effect until that connection is finished.

with member [Measures].[PCT Discount] AS '[Measures].[Discount Amount]/[Measures].[Reseller Sales Amount]', FORMAT_STRING = 'Percent'

set Top10SellingProducts as 'topcount([Product].[Model Name].children, 10, [Measures].[Reseller Sales Amount])'

//Preferred10Products set removed for clarity

select {[Measures].[Reseller Sales Amount], [Measures].[Discount Amount], [Measures].[PCT Discount]} on 0,

Top10SellingProducts on 1

from [Adventure Works]

where [Product].[Product Line].[Mountain]

The following result set now shows the shallow behavior of Autoexists.

Autoexists behavior can be modified by using the AUTOEXISTS=[1|2|3] parameter in the connection string; see Supported XMLA Properties (XMLA) and ConnectionString for parameter usage.

See Also

Key Concepts in MDX (Analysis Services)
Cube Space
Tuples
Working with Members, Tuples, and Sets (MDX)
Visual Totals and Non Visual Totals
MDX Language Reference (MDX)
Multidimensional Expressions (MDX) Reference