Min Measure on a complex Fact / Dimension.. - sitexcit

Guys,

I have a problem I've been wrestling with for awhile - I have a hack
fix that will probably work, but I'm sure someone has come across this
before:

I have a FactAccountProduct table:

AccountID
ProductKey

And a DimProduct table:

ProductKey
ModelID
CapabilityID
SubCapabililtyID
MaturityID
MaturityValueID

Since an Account can have more than one ProductKey and a ProductKey
can be held by multiple Accounts, this is a many-to-many relationship
that I have defined in my cube. It works great for other measures, et.

Now, I need a Measure that will show me the Minimum MaturityValueID -
regardless of the Product level being viewed. I have tried a simple
measure with the Min aggregation, but this always shows zero in the
cube browser, unless I am actually viewing the MaturityValueID
attribute - then it matches it exactly.

Example:

Account            Model          Capability         SubCap
Maturity         MaturityValueID     Min Mat Value ID
Acme Co.         Wesson       Technical          Software
Basic            1                          1
Acme Co.         Wesson       Technical          Hardware
Standard       2                           2
Acme Co.         Wesson       Technical          Network
Rational        3                           3

That works fine, but duh! :O I need it to work at the next level and
higher:

Account            Model          Capability         Min Mat Value
ID         and a Max Mat Value later! :P
Acme Co.         Wesson       Technical          1 (for
Basic)                3 (for Rational)

Instead Min Maturity Value ID measure always shows zero! :( I know
that there is something messed up with the Dimension Usage here - I
can't get the Maturity Value ID value from an table other than
DimProduct and the only common field between DimProduct and
FactAccountProduct is the ProductKey - but this is incomplete because
the AccountID isn't taken into account! FactAccountProduct is a
composite key - both ProductKey and TPID form the key.

I've tried setting up a "Regular" dimension usage between DimProduct
and FactAccountProduct with the ProductKey, but this gives me zero's
(this is a valid min value though, its "Unknown") all the time and I'm
sure its because of this composite key.

I know I could do something like this:

FactAccountProduct

AccountID
ProductKey
MaturityValueID

But this seems like a hack.. is it? I would appreciate any guidance
from the gurus! Is a calculated measure needed instead?

Thanx!

J'son

Min Measure on a complex Fact / Dimension.. - Deepak Puri

Assuming that there is a measure group on the DimProduct table like
[FactProduct], with a "min" cube measure like [MinMaturity], are the
dimension relationships as follows:

- Fact relation between Product dimension and Product measure group

- Regular relation between Product dimension and [FactAccountProduct]
measure group, on the [ProductKey] attribute

- Regular relation between Account dimension and[FactAccountProduct]
measure group, on the [AccountID] attribute

- Many-Many relation between Account dimension and Product measure
group, with [FactAccountProduct] as the intermediate measure group


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***