Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 211 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
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
Answers
-
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.
0 -
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.
0 -
"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.
0 -
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.
0 -
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...
0 -
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
0 -
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.
0 -
"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 ...
0 -
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.
0 -
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...
0