Fact and Dimension tables Design RRS feed

  • Question

  • I've a fact table with 2 measures (total transaction, unique transaction) and multiple FK dimensions.
    One of the dimensions is the Rejection Reason dimension which has values such as Duplicate, Out of Area, Invalid, etc.  I build the cube with this one fact table and multiple dimensions.  Everything works fine.  Now I've a new requirement to add a hierarchy to the Rejection Reason dimension.  For example, for an Invalid rejection code, there're multiple invalid reasons such as invalid email, invalid address, invalid name, etc.

    I'm not sure how to design this. I'd appreciate any suggestion.

    Thursday, January 15, 2009 6:15 PM

All replies

  • For the Rejection Reason dimension the ID must be the InvalidReadonID.

    You myst have attributes Invalid Reason which should act as key (the key property for Invalid Reason must me InvalidReasonID and name can be Invalid Reason) and Rejection Code in your dimension.

    Drag the Invalid Reason on the hierarchy pane and give the hierarchy a name say Rejection Reason Hierarchy. Drag Rejection Reason on top of Invalid Reason.
    Save it and deploy it.

    You should see Rejection Reason Hierarchy under the Rejection Reason dimension when you browse the cube.

    hopw this helps.
    Thursday, January 15, 2009 6:21 PM
  • Yes gautam is right,
    You can create hierarchy to particular dimension open the dimension and  In the Hierarchy and level tab you have to drag the way you want to set up your Hierarchy.
    Remember the level matter so set according to your requirement.

    Thursday, January 15, 2009 6:51 PM
  •  Gautham, thanks for a quick reply.  But I've problem with how to design the fact and dimension at this point.  This is what I've now.

    RejectionReasonKeyID    TotalCnt    UniqueCnt
    1                                      100           90
    2                                      200           150

    rejection reason dimension:
    RejectionReasonKeyID    RejectionCode    RejectionReason
    1                                      D                        Duplicate
    2                                      I                         Invalid

    If I change the rejection reason dimesion to this:

    RejectionReasonKeyID    RejectionCode    InvalidCode    RejectionReason   RejectionCategory
    1                                      D                                               Duplicate               Duplicate
    2                                      I                         IE                    Invalid Email          Invalid
    3                                      I                         IN                    Invalid Name         Invalid
    4                                      I                         IA                    Invalid Address      Invalid

    How do I reference the FK RejectionReasonKeyID in the fact table?   Lets say I've TotalCnt=200, UniqueCnt=150 for Invalid Reason; but the breakdown for invalid reason are, 100 invalid email, 50 invalid name, and 50 invalid address.  Do I need to add a TotalInvalid measure to the fact?
    Thursday, January 15, 2009 7:05 PM
  • Ash,
    How you have defined your relationship between Fact and dimension tables.
    Hope fully there is Primary and foreign key right?
    If not then to maintain integrity you have to that.
    For this dimension if you are creating or modifying your dimension and if there is any pk-fk relationship
    then u can't change anything then you have to delete the relationship and then modify .

    ONCE YOUR DIMENSION IS READY now define pk-fk  now
    But i think you have to use factless fact table concept(degenerated dimension),not sure though?

    Where InvalidCode and RejectionCategory are coming from? 

    Thursday, January 15, 2009 9:38 PM