one-to-many relations between fact table and dimension table

[ThorstenB]

Hi,

we have a problem with "one-to-many relations between fact table and dimension table". Take the example of table "LOGGEDFLAW" which is related one-to-many to the table "LOGGEDREASON. "LOGGEDFLAW" includes the column "FLAWKEY" and "LOGGEDREASON" includes the column "REASONKEY" and essentiallay the column "FLAWKEY" as foreign key. Now assume that we have the following records in there:

LOGGEDFLAW
1) FLAW1
2) FLAW2

LOGGEDREASON
1) REASON1,FLAW1
2) REASON2,FLAW1
3) REASON3,FLAW2

Now assume, that "LOGGEDFLAW" is the facttable and "FLAWCOUNT" is the measure with the source column "FLAWKEY" in which we want to count the number of FLAWs. As you see in the example the number of FLAWs is 1 for "FLAW1" and "FLAW2". Microsoft Analysis Server generates the value of 2 for the number of FLAWs "FLAW1" because of the one-to-many relationship to the table "LOGGEDREASON".

The question: How is it possible to calculate the measure "FLAWCOUNT" correctly, ignoring the records generated by the one-to-many relationship?

Best regards,
Thorsten

 

[MDXer]

Ok if I understand you have a fact table named LoggedFlaw and a dimension Table named LoggedReason. Logged Flaw has a key to LoggedReason 1 Dimension member has many facts (which is perfectly fine and the way it should be). However LoggedReason also contains a a Reason value. What I would do is create a a fact table with surrogate keys for Flaw and Reason.

dimFlaw
FlawID (surrogate key)
FlawName

dimReason
ReasonID (Surrogate Key)
ReasonDescription

FlawFact
FlawID
ReasonID

you can then have an aggregate measure built off FlawID in your fact which will report the total number of flaws. You can also use a distinct count aggregate and get the total numer of distinct flaws, but be very careful in using distinct count. the table names above can be anything I usually preface a table with dim if it is a dimension or use Fact at the end if it is a fact table.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[ThorstenB]

This is only a simple example. We have a fix DB model and we cannot modifier it. We also cannot use distict count because we creating a local cube.
Is there no other way?

 

[MDXer]

without a proper foundation to build the cubes from you can't expect to build useable effecient cubes. If you can't change or create tables in your DB to have your data in the right format have you considered in using views?

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck