IgnoreUnrelatedDimensions

By Bryan C. Smith

Imagine you have a highly simplified Adventure Works cube. In this cube, you have two measure groups, Reseller Sales and Internet Sales. Reseller Sales has relationships with the Geography and Date dimensions and has one measure, Reseller Sales Amount. Internet Sales has a relationship with just the Date dimension and a single measure, Internet Sales Amount. To simplify things even further, imagine the Geography dimension has a single attribute hierarchy, Country, and Date has three attribute hierarchies, Calendar Year, Calendar Quarter, and Date.

Every reference to data within this cube is based on a coordinate system. Each attribute hierarchy in the cube represents an axis in the coordinate. For our purposes, let's say the coordinates are in the format of ([Geography].[Country], [Date].[Calendar Year], [Date].[Calendar Quarter], [Date].[Date]). (For the sake of brevity, I've removed the attribute hierarchy from the Measures dimension.) Our MDX SELECT statements then assemble coordinates which SSAS resolve to return a cellset:

select


[Measures].[Reseller Sales Amount] on 0,


Geography.Country.Members
on 1


from [Adventure Works]


where ([Date].[Calendar Year].[CY 2003])

;

	Reseller Sales Amount		Coordinates
All Geographies	$32,202,669.43	?	('All Geographies', 'CY 2003', 'All Times', 'All Times')
Australia	$847,430.96	?	('Australia', 'CY 2003', 'All Times', 'All Times')
Canada	$5,651,305.43	?	('Canada', 'CY 2003', 'All Times', 'All Times')
France	$2,373,804.04	?	('France', 'CY 2003', 'All Times', 'All Times')
Germany	$1,098,866.68	?	('Germany', 'CY 2003', 'All Times', 'All Times')
United Kingdom	$2,160,145.83	?	('United Kingdom', 'CY 2003', 'All Times', 'All Times')
United States	$20,071,116.48	?	('United States', 'CY 2003', 'All Times', 'All Times')

As was mentioned at the top of this entry, the Internet Sales Amount measure group does not have a relationship with the Geography dimension. Still, the cube's coordinate structure, ([Geography].[Country], [Date].[Calendar Year], [Date].[Calendar Quarter], [Date].[Date]), must be employed. With the measure group's IgnoreUnrelatedDimensions property set to True, which is the default setting for this property, the All member is substituted in place of any specified members from unrelated dimensions:

select

[Measures].[Internet Sales Amount] on 0,

Geography.Country.Members
on 1

from [Adventure Works]

where ([Date].[Calendar Year].[CY 2003])

;

	Internet Sales Amount		Coordinates
All Geographies	$9,791,060.30	?	('All Geographies', 'CY 2003', , 'All Times', 'All Times')
Australia	$9,791,060.30	?	('All Geographies', 'CY 2003', , 'All Times', 'All Times')
Canada	$9,791,060.30	?	('All Geographies', 'CY 2003', , 'All Times', 'All Times')
France	$9,791,060.30	?	('All Geographies', 'CY 2003', , 'All Times', 'All Times')
Germany	$9,791,060.30	?	('All Geographies', 'CY 2003', , 'All Times', 'All Times')
United Kingdom	$9,791,060.30	?	('All Geographies', 'CY 2003', , 'All Times', 'All Times')
United States	$9,791,060.30	?	('All Geographies', 'CY 2003', , 'All Times', 'All Times')

When the IgnoreUnrelatedDimensions property is set to False, SSAS does not perform any substitutions:

select

[Measures].[Internet Sales Amount] on 0,

Geography.Country.Members
on 1

from [Adventure Works]

where ([Date].[Calendar Year].[CY 2003])

;

	Internet Sales Amount		Coordinates
All Geographies	$9,791,060.30	?	('All Geographies', 'CY 2003', , 'All Times', 'All Times')
Australia	(null)	?	('Australia', 'CY 2003', , 'All Times', 'All Times')
Canada	(null)	?	('Canada', 'CY 2003', , 'All Times', 'All Times')
France	(null)	?	('France', 'CY 2003', , 'All Times', 'All Times')
Germany	(null)	?	('Germany', 'CY 2003', , 'All Times', 'All Times')
United Kingdom	(null)	?	('United Kingdom', 'CY 2003', , 'All Times', 'All Times')
United States	(null)	?	('United States', 'CY 2003', , 'All Times', 'All Times')

So, what about the situation where a measure group is only partially related to the attribute hierarchies within a dimension? Let's expand this simple cube to include a Sales Targets measure group. The Sales Targets measure group has a single measure, Sales Amount Quota, and has a relationship to just the Date dimension. Unlike the other measure groups which are related to the Date dimension at the Date attribute level, the Sales Targets measure group is related at the Calendar Quarter attribute level.

With IgnoreUnrelatedDimensions set to False on the Sales Targets measure group, we see the following behavior. Note, the number of rows has been limited with the HEAD function for display purposes only:

select

[Measures].[Sales Amount Quota] on 0,

HEAD([Date].[Date].Members,10) on 1

from [Adventure Works]

where ([Date].[Calendar Year].[CY 2003])

;

	Sales Amount Quota
All Periods	$38,782,000.00
January 1, 2003	(null)
January 2, 2003	(null)
January 3, 2003	(null)
January 4, 2003	(null)
January 5, 2003	(null)
January 6, 2003	(null)
January 7, 2003	(null)
January 8, 2003	(null)
January 9, 2003	(null)

Executing the same query with IngoreUnrelatedDimensions set to True, returns the following:

	Sales Amount Quota
All Periods	$38,782,000.00
January 1, 2003	$38,782,000.00
January 2, 2003	$38,782,000.00
January 3, 2003	$38,782,000.00
January 4, 2003	$38,782,000.00
January 5, 2003	$38,782,000.00
January 6, 2003	$38,782,000.00
January 7, 2003	$38,782,000.00
January 8, 2003	$38,782,000.00
January 9, 2003	$38,782,000.00

Retrieving data at the Calendar Quarter level at which the relationship exists returns the following:

select

[Measures].[Sales Amount Quota] on 0,

HEAD([Date].[Calendar Quarter].Members,10) on 1

from [Adventure Works]

where ([Date].[Calendar Year].[CY 2003])

;

	Sales Amount Quota
All Periods	$38,782,000.00
Q1 CY 2003	$5,913,000.00
Q2 CY 2003	$8,039,000.00
Q3 CY 2003	$13,733,000.00
Q4 CY 2003	$11,097,000.00

What we can take from this is that when IgnoreUnrelatedDimensions is set to True, axis members associated with specific attribute hierarchies with which the measure group does not have a relationship are converted to the All member.