Wednesday, July 25, 2007

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.

8 comments:

jw said...

The last example is actually a product bug which will be fixed in the next major release. When IgnoreUnrelatedDimensions is true, the [Sales Amount Quota] for all days in January should return the first quarter number instead of the all periods number.

HCCBI said...

Thanks for the comment, JW. The Sales Amount Quota example seemed a little odd to me when I was writing this.

Bryan

EK Onerhime said...

I've been playing around with this setting, your blog was a great help.

Sibley said...

Hi,

Thanks for writing such an interesting article. It is not easy to take the right employee especially if we don’t have great human resource division inside our company. Special division of human resource usually required but some companies think that is not necessary thing. Trianz is a client-oriented organization that provides an integrated set of Consulting, IT and BPO solutions, each enabled by innovative and proprietary global execution models.

Trianz firmly believes that the flawless execution of business, technology and operational initiatives is a key ingredient of business success. Their mission is to partner with business leaders, who share the belief that Execution Matters. They understand top management vision and objectives, visualize business results and translate these to the execution of strategy using relevant technology and process outsourcing.

Thanks,
- Sibley

Ronald Kraijesteijn said...

Thanks, I was looking for this. I had two measuregroups with one group 2 dimension and one group 1 member. I get wrong results. After changing this setting, everything works fine.

teodorom said...

Yes, but ... what happens if one sets a default (different from [All Member]) to a dimension "orthogonal" to that Measure Group ?
Nothing works !!!
Please help !!!

Hennie said...

Great post. I was also looking into this.

Sam Kane said...

Here are this and some other articles on Unrelated dimensions

http://ssas-wiki.com/w/Articles#Unrelated_dimensions