Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Location
    Germany Düsseldorf
    Posts
    17

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

    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
    Attached Files Attached Files

  2. #2
    Join Date
    Oct 2003
    Posts
    268
    Why don't you use LoggedReason as the fact table and use a distinct count of FLAWKEY to create a "number of flaws" dimension?

    That may be problematic if some flaws don't have reasons.

    I'd like to hear the solution that you end up using.

  3. #3
    Join Date
    Mar 2004
    Location
    Germany Düsseldorf
    Posts
    17
    Thanks, but it is only a sample. In my application I must used LOGGEDFLAW as the fact table.

  4. #4
    Join Date
    Oct 2003
    Posts
    268
    use distinct count of FLAWKEY as the dimension and keep LOGGEDFLAW as the dimension.

    That will work perfectly for flaw count but other dimensions may be harder to achieve.

  5. #5
    Join Date
    Oct 2003
    Posts
    268
    That should have read:

    use distinct count of FLAWKEY as the FLAWCOUNT measure and keep LOGGEDFLAW as the fact table.

    Sorry, got my vocabulary mixed up.

  6. #6
    Join Date
    Mar 2004
    Location
    Germany Düsseldorf
    Posts
    17
    Sorry, I cannot use distinct count because I'm creating a local cube and distinct count is not supported in local cubes.
    Also distict count shows the number of differen Values in the measure property.

    Regards,
    Thorsten

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •