how to excluded the intersection of 2 dimensions ?

Hi,

I have the following simplified problem.

I have a fact table and two dimension tables, colors and weekdays.

I can make a query to select all except red and all except on monday but I like to make a query to select all except red mondays ?

They query tool doesn't give you really the options.

Any suggestions ?

Constantijn Enders

You can do it in MDX, but only a few query tools will allow you to construct queries like this. I think vendors tend to refer to this capability as 'asymmetic sets'; I can only think of one tool I've seen recently that did this (Intelligencia - http://www.it-workplace.co.uk/i4wfeatures.aspx) but I'm sure that if you hunt around there will be others. Does anyone else know of one?

Here's an MDX query to prove it's possible:

select

measures.[internet sales amount] on 0,

except(

[Date].[Day Name].[Day Name].members

*

[Product].[Color].[Color].members

,{([Date].[Day Name].&[2], [Product].[Color].&[Red])}

) on 1

from [Adventure Works]

HTH,

Chris

How to do this...in mdx query?

In a MDX query how to create a new member within the same dimension. The following is my MDX query:

OLAP cube: AP Statistics by Cancer Centre

Dimension: DIM_Fiscal_Year

Attribute: Fiscal Year

Attribute: Fiscal Year Full

WITH MEMBER [Measures].[ParameterCaption] AS '[DIM_Fiscal_Year].[Fiscal Year].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER [Measures].[ParameterValue] AS '[DIM_Fiscal_Year].[Fiscal Year].CURRENTMEMBER.UNIQUENAME'

MEMBER [Measures].[ParameterLevel] AS '[DIM_Fiscal_Year].[Fiscal Year].CURRENTMEMBER.LEVEL.ORDINAL'

MEMBER [Measures].[FY] AS '[DIM_Fiscal_Year].[Fiscal Year Full].CURRENTMEMBER.MEMBER_CAPTION'

SELECT {[Measures].[FY], [Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , {ORDER({FILTER([DIM_Fiscal_Year].[Fiscal Year].MEMBERS,[Measures].[ParameterLevel]=1)},([Measures].[ParameterCaption]), DESC)} ON ROWS FROM [AP Statistics by Cancer Centre]

New member is in Red, It returns the value "All" for the entire "FY" column.

2008 All 2008 [DIM_Fiscal_Year].[Fiscal Year].&[2008] 1

2007 All 2007 [DIM_Fiscal_Year].[Fiscal Year].&[2007] 1

2006 All 2006 [DIM_Fiscal_Year].[Fiscal Year].&[2006] 1

Thanks

Could you explain the structure of [DIM_Fiscal_Year] with examples - and is any relationship defined between the [Fiscal Year] and [Fiscal Year Full] attributes? From the results above, it looks like [Fiscal Year Full] is not related to [Fiscal Year].

|||

The reason I have the "fiscal_year" and "fiscal_year_full" is because 'fiscal_year" is a 4 digit year of the fiscal year and was used to create the "Time" Dimension and fiscal_year_full is the full description of the fiscal year. The following is an example:

fiscal year.........2007

fiscal year full....2006-07

I use the fiscal year full to display in the report.

|||

In that case, if you relate "fiscal year full" to "fiscal year" via an attribute relationship (while removing "fiscal year full" from its existing attribute relationship), then the appropriate "fiscal year full" member should be selected when you select a "fiscal year" member. One way to do this would be to drag "fiscal year full" under "fiscal year", as described below:

SQL Server 2005 Books Online

Defining and Configuring an Attribute Relationship

...

You can create an attribute relationship between any two attributes in a dimension. With the Attributes pane of Dimension Designer set to tree view, drag the attribute that you want to relate to another attribute onto the <new attribute relationship> field under the attribute.

...