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!

Dynamically Change SSIS For Each Loop container

Hello,
I would like to modify "Files" attribute of the Foreach Loop of type File 
Enumerator. This attribute is used to set the mask (for example *.txt) to 
specify which files to include in the selection. I need to be able to change 
this mask dynamically depending on package global variable. Is this possible?
Thank you!
Michael

Use the Expressions property, and create an expression for FileSpec property that references the global variable.|||

More details on How To get to the Extressions Property -

Open the ForEach Loop Editor by double clicking ForEach Loop Container.
Select Collection on left.
Click on the + sign on Expressions
Select FileSpec for Property and On Expression select the Global Variable Name. (which holds the file property such as *.txt)

Thanks,
Loonysan

Dynamic YTD based on user selection on Month

I have a user how wants to be able to see the YTD numbers based on the month they select in an attribute hierarchy with in the Time Dimension.

I would like to create a calculated member called YTD that they can use to get this information based on the Month they select in the Month attribute

The Time Dimensions are as follows: Fiscal time(Year, Qtr, Month) Month(Month)

Measure = Net Sales

View Format should be

Years

2000 2001 2002 2003 2004 2005

Customer $ $ $ $ $ $

YTD should work if the user drills down to Qtr or Month, if they selected a member in the Month attribute or not.

The Time dimension is only loaded with the years that are in the cube so if the user selects Dec but no data is in the cube for Dec 2005. 2005 should still show up on the report.

One of the issues I have run into is determining if the user is looking(selected) something from the Fiscal Hierarchy of from the Month Attirbute. If I use [Time].[Month].currentmember.name I get the name of the month the user selected in Month Attribute but if the user selects "All" months in the Month attribute but selects the Months from the Fiscal hierarchy I still get the month name and not the value "All".

Please Help.

Here is an example of a calculated member that I added to the "AdventureWorks" cube that will return the "Calendar" YTD internet sales amount based on what has been selected as the current member of the Date.Calendar hierarchy. The MDX will also work in AS2K.

Create Member CurrentCube.[Measures].[CYTD Internet Sales Amount]

AS

Aggregate(

PeriodsToDate(

[Date].[Calendar].[Calendar Year],

[Date].[Calendar].CurrentMember),

Measures.[Internet Sales Amount]),

Format_String = "Currency",

Non_Empty_Behavior = { Measures.[Internet Sales Amount] };

HTH,

- Steve