How create named set across dimensions...

If I want to create a named set for a set of "accounts" that limits the accounts to a subset based on two other dimensions, how do I do it? I tried...

CREATE SET [Accounts Created in Last 30 Days from NorthEast]
AS {
([Accounts].[Account].Members,
[Sales Region].&[NorthEast],
[Date Account Created].[Is Last 30 Days].&[True]
)

And it returned the right set of subset of accounts, but I ran into two problems:

1) in addition to the account column showing up in query results, it returned the [Sales Region] and [Date Account Created] columns, which in retrospect makes sense because the "dimensionality" of the set includes all three dimensions. and because of this,

2) when I try to use the set in Excel 2007 pivottables, it doesn't show the set in the pivottable list of sets under the accounts dimension.

Any help would be great as I'm kinda stuck right now on being able to easily build excel 2007 reports. Happy Day...

I think Excel only show single hierarchical sets. In order to do what you are looking for, I think you should use the following:

CREATE SET [Accounts Created in Last 30 Days from NorthEast]
AS Exists([Accounts].[Account].Members,
{[Sales Region].&[NorthEast]}*{[Date Account Created].[Is Last 30 Days].&[True]})

HTH,

Mosha (http://www.mosha.com/msolap)

|||

First, thanks a ton for the quick response. It really helped me understand more about Exists.

Second, I tried this, but it doesn't seem to be working the way I'd expect it. For example, if I run the following query:

SELECT
{[Sales Region].Members} on columns,
{[Accounts Created in Last 30 Days from NorthEast] } on rows
FROM [CUBE]

I would expect it to show only accounts along the rows where the region is the NorthEast because that's how the set was defined. It doesn't. When I do this, it lists many more accounts in other regions. It appears as though any time I include the set in a query it ignores the "filter" dimensions that I put in the exists if they aren't attributes of the [Accounts] dimension...in this case the [Sales Region] and [Date Account Created] dimensions. Am I missing something or do I need to move every attribute from the related dimensions into the [Accounts] so they are attributes of [Accounts]. Seem like this would totally defeat the reason for having broken out the dimensions in the first place.

Thanks for any help can give as I'm kinda frustrated with this one......

|||

Indeed, Exists only works with attributes from the same dimension, because it performs join based on the dimension table. If you have separate dimensions, then the only table to use for join is the fact table, in which case you need to use NonEmpty function if you want to take into account calculations inside the cube, or Exists(,,measuregroupname) function if you want to go purely after the fact table (the latter is more rare). More discussion on this subject is here: http://www.sqljunkies.com/WebLog/mosha/archive/2006/10/09/nonempty_exists_necj.aspx

So assuming NonEmpty, your query would be

WITH SET [Accounts Created in Last 30 Days from NorthEast] AS

NonEmpty([Accounts].[Account].Members, {[Sales Region].&[NorthEast]}*{[Date Account Created].[Is Last 30 Days].&[True]})

SELECT
{[Sales Region].Members} on columns,
{[Accounts Created in Last 30 Days from NorthEast] } on rows
FROM [CUBE]

HTH,

Mosha (http://www.mosha.com/msolap)

|||

WOW...thanks again for quick reply.

Is this true if have snowflaked dimensions? Right now I have it where

FACT > [Accounts]

[Accounts] > [Sales Region]

[Accounts] > [Account Date Created]

Is the root of all my pain that I haven't designed this so...

FACT > [Accounts]

FACT > [Sales Region]

FACT > [Account Date Created]

I was assuming that because Accounts is related directly to the other dimensions that this would work.

I REALLY DO APPRECIATE YOUR HELP.

|||

Exists works inside dimension regardless whether it is snowflake or not. Based on your schema, it seems like you should've defined Sales Region and Account Date Created as attributes inside Accounts dimension, not as separate dimensionss