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
Turn this SQL in OBIEE join relations... Help please :(
Hi everyone
I got a problem to turn this simple sql into Join relations in OBIEE (Physical - BM - Presentation)
select c.*,b.*
from circ_path_inst a, path_chan_inst b,circ_path_inst c
where a.circ_path_inst_id = b.parent_path_inst_id
and b.member_path_inst_id=c.circ_path_inst_id
and a.circ_path_hum_id = 'ROMAITDG/ROMAITDG/S2T/000009'
As you can see, i have to show all datas from "c" table that is filtered with the same field (showed) from "a" table.
I'll call tables in this way to let you understand more easly (i hope)
-PIPPO (a)
-PLUTO (b)
-PIPPO2 (c)
I created an alias in Physical layer of PIPPO and i called it PIPPO2.
i joined those 3 tables in this way:
(Physical)
PIPPO (a.id) inner join PLUTO (b.parent_id)
PLUTO (b.member_id) inner join PIPPO2 (c.id)
PIPPO -> PLUTO -> PIPPO2
(Business Model)
PIPPO (a.id) inner join PLUTO (b.parent_id)
PIPPO2 (c.id) inner join PLUTO (b.member_id)
PIPPO -> PLUTO <- PIPPO2
when i try to launch report, error message appear: No fact table exist.
Can you tell me please how you could create relation between those tables? arrows (physical and bm) in wich direction?
I thought also to create a table with sql, but it is a Self Area and customer can make their own report changing fields.
Can you help me please?
Any idea?
Many many many thanks
Answers
-
Hi Barticchia,
1.) You have to think in models and not SQL. Sorry but that's just the way things are.
2.) You have to think dimensional modelling:
2.1.) What's your fact? What do you want to count/sum/average? Basically what do you want to aggregate or calculate?
2.2.) What are your dimensions? Meaning what do you want to aggregate BY?
3.) Fact-dimension relationships go from fact to dimension, so outwards and not inwards.
4.) "No fact table exist." is a clear sign that your model is messed up in terms of dimensionality and / or content levels of your logical table sources.
4.1.) Does one of your logical tables have the litte hash icon denoting that it's a fact and the others haven't - implying that they are logical dimensions?
4.2.) Have you created dimensional hierarchies for any of the objects?4.3.) If yes, you will have to check the LTS content levels for your dimensions and your fact
0 -
@Barticchia Did you give up on this? For the benefit of other forum users it is courteous to finish your threads. Sharing goes both ways.
0 -
Ciao Christian,
Sorry but i was waiting a notify through email and i didn't receive anything...
Anyway i found the problem that was in LTS... i added a dimension join into it.
Now it works fine.
just another question... i don't remember if in OBIEE 10 or OBIEE 11 there was a problem with arrow direction during join tables.
Normaly it's from fact to dimension F --> D in both layers (Physical and Business Model); i don't remember if in OBIEE 10 versions arrows direction was viceversa.... from D --> F to avoid error of no fact table.
I'm telling you bull...t or am i right?
Thanks again for you quickly response (it's unusual) and sorry for what happened.
Simone
0 -
Hi Simone,
Good to hear that it worked. Join direction logically was - or should always have been - F->D
Email notifications are normally defaulted to a "non-spammy" level and you will need to check your profile for details on whether you should get an email for each post made inside your question.
This is a public forum with a lot more traffic than your usual MOS Service Request.
0 -
Thanks for your help Chris
0 -
Any time. Thanks to you for closing the thread :-)
0 -
Ciao again Christian!
i'm writing you (and to all users in this forum) to ask you another question...
today end-user has tested solution that i found some weeks ago. it works (adding an alias filter field from it)... but he doesn't want it. he wants just one field.
Just to remind you, this is what he'd like to have as sql result:
select c.circ_id
from
pippo a, pluto b, pippo c
where
a.circ_id = b.parent_id
and b.member_id=c.circ_id
and a.circ_id = 'ROMAITDG/ROMAITDG/S2T/000009'
as i told you, i already solved it with creation of an alias (in RPD) and i told him to select c.circ_id (alias) in visualization, and a.circ_id in filter.
In this way join works fine and datas are correct.
But he wants the same field in visualization AND in Filtration.
Is there some way to... customize original field in rpd adding...i don't know... some kind of where condition?
i need that join moves with those sql "direction" using just one field
Do you have any idea how to solve it?
Many thanks
0 -
Hi Simone,
Are you allowed to tell the end-user he is dumb and doesn't understand what he is asking for?
Did he give you the SQL you posted or you wrote it to show what he is asking?
Because even a 3 years old can easily see that "c.circ_id" <> "a.circ_id" ...
Your end-users sounds a lot like this: https://www.youtube.com/watch?v=BKorP55Aqvg
It's logically impossible for the same object (presentation column in this case) to means 2 different things in the same analysis.
PS: it's a serious answer, not just making jokes about the situation ...
0 -
Ciao Gianni!!!
Your answer is EPIC XD.
unfortunately is all true and end-user has rejected my solution with alias.
he said that second field is "misleading" and test is KO.
Probably you know better than me that end-users ask to us impossible things... and our rule is to find work-around solutions.
i was checking inside the property of the object... and i saw that there is a WHERE condition... but i don't thing this can help me in some way...
today i suggested a new analysis with direct request to DB... in this way it works, but even this is not ok because is a self reporting area, and users are not skilled enought to use it.
I really don't have any idea how to solve it.
0 -
Ciao Simone,
If your need is to just have that query and nothing else (so forgetting about OBIEE etc.) you can do it as an opaque view in the RPD, drag and drop over the 3 layers and done. But this will be just like the directly DB request: paying an expensive license to do something that SQL Developer, a free tool, would do better.
It's totally impossible to have the same object acting as 2 different physical columns in the same analysis the way your end-user want, that's a fact. Send him the youtube link, maybe he will see the link with his request.
I understand your position as consultant, but at some point when something is impossible it's just impossible.
Is your client in Italy? Tell him to attend the ITOUG Tech Day 2017 in June in Milano (it's a free event), we will explain him what "impossible" means that logic matters, even more when defining requirements (we are maybe more free to tell him directly the things than you ).
0
