Joining Fact Tables

Hi,

I created a universe with two fact tables and eight Dimension tables in Star.

Now the user wants to link two fact tables. How it can be done?

Requirement One:
Both the fact tables (say F1 & F2) joined to the Dimension table say D1.
User wants to know count of common items by D1_C1 in D1.

Requirement Two:
Fact table F1 joined to Dimension say D2.
User wants to know fact items in F2 by D2_C1 in D2.


ravik (BOB member since 2004-06-02)

Generally speaking, linking two fact tables is usually a bad idea. You’d likely end up with a Chasm Trap.

Please do a couple of searches here in Designer on Chasm Trap and Fan Trap.


Anita Craig :us: (BOB member since 2002-06-17)

Hi

Example for the requirement one.

select Dim1.column1, avg(a.measuer1), avg(b.measure2), min(a.test_date), max(a.test_date), min(b.test_date), max(b.test_date), count(*)
from Fact_Table1 a, Fact_Table2 b
where a.hd_num = b.hd_num
and a.final_test = 'Y'
and b.final_test = 'Y'
group by Dim1.column1

When i try to do the abouve query, BO Full client is generating two queries. I tried to use Conmine queries but not getting the required results.
Could somebody explain that Any action to be done in the universe or there any way to resolve in Full Client.

[edited, used bbc formatting using the CODE option - Andreas]


ravik (BOB member since 2004-06-02)

Maybe that isn’t quite the right SQL? You have a SELECT Dim1.column1 – but Dim1 isn’t in the FROM list. Is it from Fact_Table1 (alias “a”)?


Anita Craig :us: (BOB member since 2002-06-17)

Hi,

I unable to generate the similar SQL in BO.

select Dim1.column1, avg(a.measuer1), avg(b.measure2), count(*) 
from Fact_Table1 a, Fact_Table2 b, Dim1 
where a.Dim1_column2= b.Dim1_column2
group by Dim1.column1

Fact_Table1 and Fact_Table2 are joined to Dim1 by Dim1.Key1

[Used CODE formatting, please use the formatting options when you post - Andreas]

Prob1: BO is genrating two queries, as soon as I select measures from two fact tables
prob2: I want to have details of common items (Column in Dimension)between two Fact tables.


ravik (BOB member since 2004-06-02)