How to use two facts together-best practice?

building semantic layer
1

Is there a Best Practice to use to fact tables together in a report within Universe. I know you can use context for each fact but then they would be separated out?

ajq :hb:

anushajq (BOB member since 2005-02-22)

2

It is very rare that two “fact” tables could be joined in a SQL query and produce valid results. They’d have to be designed to be used together – such as if there were a table with basic journal entries that had a one-to-one relationship via a foreign key to an extended-journal-attributes table, for example.

If you give more specific details about your situation, you might get more specific feedback.

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

3

If you don’t want seperate queries, then consolidate the tables. This might not be an option for you, but this is what you are looking for IMO.

zack :us: (BOB member since 2007-08-02)

4

I am in a similar situation and I can explain my need in hope to get answer:

I am designing a Datamart for claims, and I have two fact tables: Claims (t1) and Claim Transaction (t2). I have universe for this DM and users want to see reports at claims level (t1) but they want to drill down to Transaction level (t2).
I made universe by having 2 fact tables and have context for the relationships. Eventhen when I try to run report in BOXI-Deski it asks me which context to choose (I want both but can’t) and finally it always brings 0 rows.

What I am doing wrong and what is the best way?

Please let me know if I need to explain more.

Thanks in Advance!

boarch (BOB member since 2008-04-04)

5

I am still a little confused by your situation…

  1. So do you have a typical star schema for both contexts?
  2. Have you joined the two fact tables to each other or only to their associated dimensions?
  3. If you have joined the fact tables only to their dimensions, and if you have left your universe parameters as the defaults, then you should be able to generate a report that has two SQL statements that are ‘Joined’ together. Is this what is happening to you?
  4. If you do have 2 SQLs that are joined together, then you should see an outer join type of behavior, where if one table does not have rows, you should at least see the data from the other table. Are you seeing this?

Judy

JMulders :us: (BOB member since 2002-06-20)

6

Thanks for reply, please see my comments in bold,

Gabe

boarch (BOB member since 2008-04-04)

7

Hi Gabe,

Hmmm…I don’t know off the top of my head. Usually in a universe, if you have multiple fact tables, you have multiple contexts. Then if users want to generate a report with objects from the multiple contexts, you would train them to go to the microcube and join the appropriate fields (as you can tell, more of an advanced topic than your typical ‘just click the OK button’ type of user).

If you really need this universe to be friendly for ad hoc users, then I’d vote to combine the two fact tables into one. If that’s not an option, why don’t you play around with the scenario I’ve described and see if it works for you? Also maybe someone else will chime in with a brilliant idea :slight_smile:

Good luck,
Judy

JMulders :us: (BOB member since 2002-06-20)