2 Replies Latest reply on Mar 17, 2017 5:54 PM by Thomas Dodds

    Fact to Fact Key

    Robert Angel

      Hi,

       

       

      I have 'inherited' a number of pre-built fact and dimension tables.

       

      The fact tables are joined to the dimension tables in the usual fashion.

       

      However, the Fact tables also have PKs and some of the Fact tables have FKs to other Fact tables.

       

      The facts are not aggregated facts, there is only very 'rough' parent and child relationships between them.

       

      I can see that holding a Fact FK in a Fact table is useful for facilitating fact to fact drilling.

       

      However, given that these are facts with very different dimension joins, other than where there are conformed dimensions, which is rare, I cannot see that it is useful for much else, wouldn't I need a bridge table between the two to solve the many to many linkage?

       

      I can also see that I could join the less detailed Fact Detention onto the

       

      Where the 'rough' relationship exists should I be creating any kind of joins between the FACT tables, and if so how?

       

       

      i.e.  Simplified Example

       

      Fact Detentions

       

      Contains Detention_Id PK, various FKs, measures

       

      Fact Detention Sheet

       

      Contains Detention_Sheet_Id PK, Detention FK (to the above),various other FKs, measures

       

      Fact Detention Reasons

       

      Contains Detention_Reasons_Id PK, Detention FK (to Fact Detentions), various other FKs, measures

       

      (and various others, all having the Detention_Id FK back to the Fact Detentions table)

       

       

      My thoughts are; -

       

      1. Combine the parent into the detail as a single LTS, as the key is always there - fairly simple and easy

       

      2. Join the Facts - feels entirely wrong

       

      3. Set the detail / parent as if they are fact aggregates, using the parent level as the source for the fields at both levels for the missing FKS, and the detail for the FKS at the detail level - feels wrong again, but I am struggling to articulate why.

       

       

      Any input appreciated!

       

       

      thanks,

       

      Robert.

        • 1. Re: Fact to Fact Key
          Christian Berg

          tl;dr

           

          1.) don't join facts

           

          2.) always go over conformed dimensionalities

           

          3.) just because keys are there physically doens't need you have to use them

           

          4.) you CAN use them both in the same LTS or have separate logical stars depending on your detailed needs

           

          5.) and you also CAN use them as sources for both levels of aggregation inside your columns or - when using 2 LTS in a single logical fact - have 2 columns each in the logical facts or - as stated abive have them totally separated

           

          Really depends on your dimensions around it. Many times the detail records have more - detailed - dimensions around then than the header record. Like X detail dates.

          • 2. Re: Fact to Fact Key
            Thomas Dodds

            Don't join facts to facts ... feels wrong because it is.

             

            Rules of Star Modeling:

            1. Every Dimension is related through a Fact
            2. Every Fact Measure has a Dimensional context

             

            Why don't we join facts?  Violates Rule 2 ... you end up with facts that don't have dimensional context - they only have factual context ... then you start down the road of filtering by fact ... OBIEE wasn't designed with those rules in mind - it won't behave.

             

            I see, despite the physical model, a logical model where you have:

             

            Dims

            • Detention - any attribute about the detention (no measures)
            • Detention Sheet - any attribute about the detention sheet (no measures)
            • Reason - attributes about the reasons
            • Date
            • Student
            • Faculty
            • Location
            • etc ...

             

            Facts

            • Detention - measures about the detention
            • Detention Sheet - measures about the detention sheet

             

            Conform the dimensions to the facts as much as possible ... from what I can see you have everything to build a logical star.

             

            To be sure I'd have to see the physical schema ... first glance looks promising though

            1 person found this helpful