Default join path

3310714

Hi,

In my repository, the physical diagram looks like below screenshot.  When a user selects attributes from the D_DATE and D_PRODUCT tables, the join goes through F_ORDER_DETAIL.   Is there a way to default the join path so OBI will choose the F_ORDER_SUMMARY joins?

FTisiot

IIRC I would say in the Business Model to order Fact Order Detail after Fact Order Summary

Joel

What is the difference between the Order Detail and Order Summary facts apart from the grain? If it is just the grain then you can create a logical Orders fact table using both physical facts as Logical Table sources. The BI server will know which Logical Table sources to use if you set up the content level properly for the Detail & Summary Logical Table sources in the BMM layer. What you're setting up is level based fragmentation.

3310714

Re-ordering didn't make any difference. 

3310714

For every record in Summary, there are multiple Detail records.  It's 1:N relationship.  Most of the Analyses are being done on the Summary table so the way it's setup allows faster queries.  

3310714

By setting the Priority Group for the Detail LTS to 1 (Summary LTS left at 0), OBI now uses the Summary table joins for the analyse. 

Gianni Ceresa

3310714 wrote:Re-ordering didn't make any difference. 

You didn't say which version you are using, but order is a selection criteria for same priority group LTS, so it was supposed to work (actually it worked in all the 11g and 12c I tested).

3310714

Hi Gianni,

We are using 12c.  Maybe I misunderstood the re-ordering suggestion.  Are you referring re-ordering multiple LTS inside the "Sources" folder? 

FTisiot

No, It's reordering Fact Tables within the Business Model

e.g. move Fact B before Fact A

FTisiot

1) Next time please mention that you are talking about two LTS for the same Logical Table

2) If your model is setup properly with correct hierarchies and Levels, then the aggregated table should be used automatically without the Priority Group

If you have to manually force the Priority Group it means your model is not setup properly

3310714

Hi FTisiot,

Based on my Diagram above, this is my BMM:

DATE

ORDER_DETAIL

ORDER_SUMMARY

PRODUCT

Each logical table has one LTS.  From your initial post, I did the re-ordering to:

DATE

ORDER_SUMMARY

ORDER_SUMMARY_DETAIL

PRODUCT

That didn't make any difference to join used in the query.  

Then, I updated the priority group:

DATE (0)

ORDER_DETAIL (1)

ORDER_SUMMARY (0)

PRODUCT (0)

Now it is using the joins from the ORDER_SUMMARY table when selecting columns from DATE and PRODUCT.