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.
1 person found this helpful
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:
- Detention - any attribute about the detention (no measures)
- Detention Sheet - any attribute about the detention sheet (no measures)
- Reason - attributes about the reasons
- etc ...
- 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