Very Computer

by **Phi** » Mon, 05 Jun 2000 04:00:00

For an "impure" star-schema where a single fact can correspond to
multiple values from a dimension (for example, a single patient visit
to a hospital with multiple diagnosis), can anyone identify how to
"crack this nut" in the wonderful world of Microsoft OLAP services? I
found a White Paper on the Microsoft site which describes the use of
COUNT DISTINCT, but it sure sounds like an *UGLY* solution.

I can build cubes when an underlying star-schema has "proper" joins
with many on the fact side, and 1 on the dimension side, like this:

FACT >------ DIMENSION

but the special case of many on the DIMENSION side (just this one
dimension), like this:

FACT ------< DIMENSION

is really giving me hearburn!

Yes, I know what I describe is not, strictly speaking, a "star-schema"
- but contemporary texts, such as Ralph Kimbel's "Data Warehouse
Lifecycle Toolkit" describe this special case (and many others) as
essential in dimensional modelling.

Help!

by **olapt..** » Tue, 20 Jun 2000 04:00:00

Phil,
Maybe your dimension table should be a fact table? By creating a
seperate fact table from your dimension table you should be able to then
create a virtual cube from the two. You will need at least one common
dimension for the virtual cube to work properly, such as customer or
time.
Hope that works for you? I didnt' find the example online useful either

Best of luck

> For an "impure" star-schema where a single fact can correspond to
> multiple values from a dimension (for example, a single patient visit
> to a hospital with multiple diagnosis), can anyone identify how to
> "crack this nut" in the wonderful world of Microsoft OLAP services? I
> found a White Paper on the Microsoft site which describes the use of
> COUNT DISTINCT, but it sure sounds like an *UGLY* solution.

> I can build cubes when an underlying star-schema has "proper" joins
> with many on the fact side, and 1 on the dimension side, like this:

> FACT >------ DIMENSION

> but the special case of many on the DIMENSION side (just this one
> dimension), like this:

> FACT ------< DIMENSION

> is really giving me hearburn!

> Yes, I know what I describe is not, strictly speaking, a "star-schema"
> - but contemporary texts, such as Ralph Kimbel's "Data Warehouse
> Lifecycle Toolkit" describe this special case (and many others) as
> essential in dimensional modelling.

> Help!

Sent via Deja.com http://www.deja.com/
Before you buy.

by **Phi** » Wed, 21 Jun 2000 04:00:00

Hopefully you are right, but I just don't "get it". I can't find any
examples that get me up to "critical mass".

Ralph Kimball's "The Data Warehouse Lifecycle Toolkit" has 2 chapters
on dimensional modeling (based on star-schemas). The second chapter
describes many cases where having a dimension table on the "many" side
of relational join is needed (for example multiple diagnosis for a
single hospital-stay "fact", where diagnosis is a dimension).

Sigh...

>Phil,
>Maybe your dimension table should be a fact table? By creating a
>seperate fact table from your dimension table you should be able to then
>create a virtual cube from the two. You will need at least one common
>dimension for the virtual cube to work properly, such as customer or
>time.
>Hope that works for you? I didnt' find the example online useful either

>Best of luck

>> For an "impure" star-schema where a single fact can correspond to
>> multiple values from a dimension (for example, a single patient visit
>> to a hospital with multiple diagnosis), can anyone identify how to
>> "crack this nut" in the wonderful world of Microsoft OLAP services? I
>> found a White Paper on the Microsoft site which describes the use of
>> COUNT DISTINCT, but it sure sounds like an *UGLY* solution.

>> I can build cubes when an underlying star-schema has "proper" joins
>> with many on the fact side, and 1 on the dimension side, like this:

>> FACT >------ DIMENSION

>> but the special case of many on the DIMENSION side (just this one
>> dimension), like this:

>> FACT ------< DIMENSION

>> is really giving me hearburn!

>> Yes, I know what I describe is not, strictly speaking, a "star-schema"
>> - but contemporary texts, such as Ralph Kimbel's "Data Warehouse
>> Lifecycle Toolkit" describe this special case (and many others) as
>> essential in dimensional modelling.

>> Help!

>Sent via Deja.com http://www.deja.com/
>Before you buy.

Very Computer

MS OLAP cubes: many-to-many, COUNT DISTINCT, etc.

MS OLAP cubes: many-to-many, COUNT DISTINCT, etc.

MS OLAP cubes: many-to-many, COUNT DISTINCT, etc.

MS OLAP cubes: many-to-many, COUNT DISTINCT, etc.