How to implement "AND NOT IN" in a many-to-many relationship dimension?

Hello,

I have 2 tables, reason and complain. 1 complain can have multiple reasons and a reason can be linked to multiple complains.

Reason Complain_ID

-

A 1

A 2

B 1

B 3

C 1

D 2

In the analysis Services, I have a Reason dimension, and when I browse for the number of complains per reason, the browser shows:

Reason Complain Count

--

A 2

B 2

C 1

D 1

But, how can I query the Analysis Service to count only complains for Reason B, C, D, BUT NOT count complains that includes Reason A... Something like "... AND NOT IN...." that we can do in SQL.

The result I'm expecting is something like the following:

Reason Complaini Count

--

B 1 (only complain_id 3 does not include Reason A)

Thank you very much,

Sincerely,

Annie

I found the document "The Many-to-Many Revolution" by Marco Russo, and it solved my problem.

Thanks everyone~~

How to implement "AND NOT IN" in a many-to-many relationship dimension?

Hello,

I have 2 tables, reason and complain. 1 complain can have multiple reasons and a reason can be linked to multiple complains.

Reason Complain_ID

-

A 1

A 2

B 1

B 3

C 1

D 2

In the analysis Services, I have a Reason dimension, and when I browse for the number of complains per reason, the browser shows:

Reason Complain Count

--

A 2

B 2

C 1

D 1

But, how can I query the Analysis Service to count only complains for Reason B, C, D, BUT NOT count complains that includes Reason A... Something like "... AND NOT IN...." that we can do in SQL.

The result I'm expecting is something like the following:

Reason Complaini Count

--

B 1 (only complain_id 3 does not include Reason A)

Thank you very much,

Sincerely,

Annie

I found the document "The Many-to-Many Revolution" by Marco Russo, and it solved my problem.

Thanks everyone~~

How to identify IDENTITY column on a linked server table?

Using SQL2000 (latest SP and patches)...
I'm trying to identify the IDENTITY column of a table in a database on a
linked server. Ordinarily I'd just use sp_columns, but since it's a linked
database I have to use sp_columns_ex, and it doesn't return the IDENTITY
column identifier!
Help!! :) Any ideas? Thanks!
JackHave you tried
exec linkerserver.db.dbo.sp_help tablename
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jack Black" <jackblackisback@.hotmail.com> wrote in message
news:%23e438$y0DHA.404@.tk2msftngp13.phx.gbl...
> Using SQL2000 (latest SP and patches)...
> I'm trying to identify the IDENTITY column of a table in a database on a
> linked server. Ordinarily I'd just use sp_columns, but since it's a
linked
> database I have to use sp_columns_ex, and it doesn't return the IDENTITY
> column identifier!
> Help!! :) Any ideas? Thanks!
> Jack
>|||Thanks for responding! :)
Yeah, I've tried that... Suffice to say the scripting language I'm using
(ColdFusion) doesn't support multiple resultsets without using their stored
procedure tag (CFSTOREDPROC), so I would have to build the query into a
stored proc. Normally not a problem, but CFSTOREDPROC is hardwired to
execute all sp's with a call command, so the syntax executed would be "CALL
EXEC ...", resulting in a failed call. And since CALL doesn't work with
linked server syntax, I can't use it.
I'm trying to avoid requiring a local DSN pointing to the remote SQL server,
but I think it's coming down to needing that because of CF's shortcomings...
Any other thoughts welcome!! :)
Jack
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:u2sBBC40DHA.716@.TK2MSFTNGP12.phx.gbl...
> Have you tried
> exec linkerserver.db.dbo.sp_help tablename
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Jack Black" <jackblackisback@.hotmail.com> wrote in message
> news:%23e438$y0DHA.404@.tk2msftngp13.phx.gbl...
> > Using SQL2000 (latest SP and patches)...
> >
> > I'm trying to identify the IDENTITY column of a table in a database on a
> > linked server. Ordinarily I'd just use sp_columns, but since it's a
> linked
> > database I have to use sp_columns_ex, and it doesn't return the IDENTITY
> > column identifier!
> >
> > Help!! :) Any ideas? Thanks!
> > Jack
> >
> >
>