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

#1

Hi,

Use Distinct Count, but it is slow oder change your cube design.
As for me your design is scurry.

Thanks,
Vladimir

"Thorsten Blawatt" <thorsten.blawatt (AT) syncos (DOT) com> schrieb im Newsbeitrag
news:uBOVF$KSEHA.624 (AT) TK2MSFTNGP11 (DOT) phx.gbl...

Quote:

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". In the attached ZIP File you find :

- a MDB File with the described example
- a screenshot from the cube constructed in AS
- a screenshot from the result table generated with AS.

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap

Re: one-to-many relations between fact table and dimension table - 06-16-2004 , 04:53 AM