Oracle Communities

Oracle Analytics and AI

Child Item

Query containing calculation from another Fact Table

Hello

I have two Fact Tables F1 and F2 they are joined in the model , But i am trying to make a calcuation and it is not working

I have a query like this

Select F1.a

F1.b

(select sum (F2.x) from F2 where F2.y = F1.c and F2.z = F1.d) ,

From F1

where

F1.e = 'w'

Is there a way to add a calculatiojn of F2.x in the logical table F1 ?

Thanks

Find more posts tagged with

Accepted answers

All comments

Robert Angel

Hi Camilo,

if there is a key between the two tables, or a bridge with a key(s) between the two tables such that every entry in the driving fact has a 1:1 join then all you have to do is drag the column across to the other fact and your query will work okay.

If your keys don't do that for you, then the result will not be correct.

Hope this helps,

Robert.

Martin van Donselaar

What is your model like? If you have joined the facts directly then you will not get the results you want.

Facts should be joined by Conformed Dimensions. Create a common dimension(s) between the two facts and you will be fine.

Robert Angel

"Facts should be joined by Conformed Dimensions" - well, if all the requirement is is to have the measure of one fact in the other fact table, and the tables are either 1:1 or 1:M then there is no reason you cannot alias the physical table that is used for the 'other fact' and use this directly to join and add the column in question in - provided you are assured that you will NOT change the granularity of the driving fact as a consequence.

Martin van Donselaar

Well, in my opinion having to join to facts together, even if they are 1:1 generally means you have to reconsider your model design, but from a technically point of view you are correct.

Robert Angel

I agree on both counts, but was giving an answer from a purely OBIEE point of view, as the BI developer unfortunately you cannot always tell the DW developers what to do...

Camilo Flores

Thanks For your answers, I am performing some tests

The tables are joined by conformed dimensions, however it does not resove the problem , i wil explain below

1.- When i just use fields from the 2 fact tables in the report , one of the values is NULL

2.- When i added a column of a conformed dimension (i used year ) into the report , then it shows a value for the fields of both FactTables.

3.- Thwe prioblem is , that iI don't need the report by year or by any measures, Actually they also have in common a status dimension, but i need to ask for Fact 1 = status A and Fact 2 = Status B, i can't join by this field.

4.- In the original Query the tables are joined by Event_number, whihc is not in any Dimnension is a Fact table field

Should i start by creating a bridge table with the Event Numbers ?

Thanks

Camilo

Robert Angel

Hi Camilo,

if there is no direct join between your two facts then your only option is to create a bridge table or sub-optimally an opaque view to emulate the same.

By direct, I mean an trail which will not result in a Cartesian product when you utilise it.

Thomas Dodds

"as the BI developer unfortunately you cannot always tell the DW developers what to do..."

^ USE of information DRIVES physical data DESIGN ... if the BI architect has no control over the physical data design -- it's a setup for failure.

You MUST tell them WHAT - leave it to them to come up with HOW ...

[Deleted User]

Can't put it better than Thomas already did.

So basically you're saying "unfortunately the brain surgeon cannot always tell the anaesthetist what to do"? Sweet. I'd like to be at that operation with popcorn and a camera. Hilarious disaster.

Robert Angel

Sure, but here in the real world I have been on more than one project where the DW was 'off the shelf' and the client was unwilling to pay for changes...