Each member in my attribute has the same value as All (when used with a particular measure)

This only seems to occur in one area, and unfortunately (or fortunately, meaning it's not a bug) I cannot get anything in adventure works to display the problem.

In my cube, if I look at [Customer].[Location Tree].[All], but also say where Customer.[Organization Type].&[School], the sales value of [all] is presenting itself like the organization type slicing wasn't even used.

ie:

(the total sales from this query is correct in my mind)

select [sales] on 0,
Customer.Territory.AllMembers on 1
from cube
where Customer.[Organization Type].&[School]

but the all value out of this query is truly "All".. it doesn't seem to make use of the filtering:

select [sales] on 0,
[Customer].[Location Tree].[All] on 1
from cube
where Customer.[Organization Type].&[School]

One thing to note is that in the first query, the territory members are filtered by the organization type I chose - so part of it is getting sliced, just not the [all] of the location tree.

Please check whether the query works as you want with SP2 CTP - this entry from Mosha's blog explains the changes made in SP2 for these type of scenarios:

http://www.sqljunkies.com/WebLog/mosha/archive/2006/11/1.aspx

>>

Slicer and axes interaction in MDX Part 1 - coordinate overwrites

...

Well, the solution here is simple. We realized that the way this shipped in AS2005 is clearly broken and inconsistent, and this is why this is fixed in SP2. SP2 should become available really soon now, and the change to the interaction between axes and WHERE is one of the most important changes there.

...

>>

|||

Thank you for the response, Deepak.

Since ctp shouldn't go on a production machine, I really would hate to install it there.

I've tried writing the query different ways, but to my dismay, it still gives the total value of all sales. Then I tried something that I should have done sooner:

select [sales] on 0,
Customer.[Organization Type].Members on 1
from cube

For each row, this shows the total amount of sales! So something's linked wrong and I will take it back to the cube developer again. If you have an idea of the general area to change, please let me know.

If I look in the cube designer, and then 'dimension usage', the customer dimension has a regular relationship to the sales fact table. I'm not sure of where else to look. Other attributes from the customer dimension will slice as desired, but not the [Organization Type].

|||

I've renamed the post subject to better reflect the updated problem:
Each member in my attribute has the same value as All (when used with a particular measure - [sales])

The measure in question, aforementioned as 'sales', does not split out at all when looking at a member of [Organization Type].

To provide more info: The granularity of the customer dimension is down to a customer id, but the measure has a regular relationship type to the customer dimension at the Territory level. Is there something more that needs to be done? I've tried to create new attribute relationships between the all items in the customer location tree with the Customer.[Organization Type], but the results did not change.

If anyone has gets a spark of an idea, please let me know. Thank you!

|||

Since the granularity attribute of the Customer dimension for this measure group (ie. Territory) is above the customer id key attribute, has the [Organization Type] attribute been explicitly related to Territory?

http://msdn2.microsoft.com/en-us/library/ms365371.aspx

>>

SQL Server 2005 Books Online

Defining a Regular Relationship and Regular Relationship Properties

...

When you specify the granularity attribute to be an attribute other than the key attribute, you must guarantee that all other attributes in the dimension are directly or indirectly linked to this other attribute through attribute relationships.

...

>>

|||Territory had [Organization Type] as an attribute relationship and this did not seem to help. I can see if making [Organization Type] have Territory as a relationship would help.|||But the attribute relationship that you define should reflect the real relationships which exist in your dimension - for example, the Sales Targets measure group in Adventure Works relates to the Date dimension at the Calendar Quarter granularity. Calendar Quarter has an attribute relationship to Calendar Semester, which in turn rolls up to Calendar Year. Could you describe your Customer dimension data attributes, and how they are related?|||

Sure.

Customer Dimension:
(these 3 attributes are also visible outisde of the hierarchy)
Customer Location Tree (holds a hierarchy that splits geographical areas)
Section
Sub Section - related to section
Territory - related to sub sectiion

Organization Type - should relate to Territory, as its members can filter out territories. A territory can only have a single organization type.

in the fact table for [sales], the [sales] measure (just renamed for obscurity), only goes down to the territory level.

|||Just to clarify - does [Customer Location Tree] define a Parent-Child hierarchy, because that might have something to do with it?|||

The [Customer].[Location Tree] is a hierarchy, and it contains the 3 levels I mentioned before as well as Parent Customer and then the lowest level, Customer.

The 'Customer' (ID/Key, or lowest level) has attribute relationships to -every- attribute in the customer dimension. I'm not sure if this was done by default or if someone added them.

|||Surprisingly, adding the linking again from Territory to Organization Type (flexible), it was found to work correctly. I'm not sure of any differences from before, but at least it works now!