Fact to Fact Key

Question

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.

Thomas Dodds · Answer

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

Rules of Star Modeling:

* Every Dimension is related through a Fact
* 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

[Deleted User] · Answer

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.