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

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

Post 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!

 
 
 

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

Post 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.

 
 
 

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

Post 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.

 
 
 

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

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!

2. Stuff4Telco

3. Counting Distinct in OLAP Cube problem?

4. Can I take a hot DB into standby mode?

5. Tricky Group by, count, distinct etc etc... sigh

6. Connecting SQL Server

7. Seagate Info cubes / MS Olap cubes

8. SQL Count

9. Office Web Components - Create a cube with distinct count

10. Distinct count in local cube

11. DISTINCT COUNT in Cube Editor on Dim columns

12. Process the Cube - Distinct Count

13. I need mulitple distinct counts in one cube.