Oracle Analytics and AI

chillychin

Im quite new to this and I have been googling around and trying to understand how logical joins work.

Currently I have in my logical layer a table that has two sources.

When I right click on the table to see the table and direct joins I see both tables, individually connected to another fact table. So 4 tables, with only two joins.

Neither of these dimension tables are linked to each other (at least not in the diagram).

Both of the dimension tables have a "faculty_key" to them. The faculty_key is an actual key in table A and is needed.

In the table B I was told to remove this, as "faculty_key" is not a key to table B, and shouldnt exist in a dimension table.

When I remove the faculty_key from the dimension table B, some of my reports start to bomb out saying that the fact table does not have the level of granularity needed.

I am guessing that somehow the faculty_key is needed to create some sort of a join between the fact table and table B extending to table A.

What I am confused about is that I do not see a join of any sort between table B and A. Faculty_key is not used for display or join purposes when I look at the SQL that is issued.

If a logical table has multiple sources - does that create an "implicit" join between all the source tables for the logical table?

Why would a logical table have multiple sources?

Vipulbhatia29

well you need to certainly learn a lot about obiee. Having said that its not that difficult.

so from the text above i assume you have these question:

why do u need logical join when you already have physical joins?

whats the significance of multiple logical tables sources.

so lets say you have a city table and a state table and revenue fact table.

now city table is at a lower granularity than state. So the join would be between state and city and then city and revenue fact at the physical level as revenue fact table is at the city level granularity. But as an end user if you want to display on a report as geography and the end user doesn care whether its 2 separate dim tables or not. How would you display that?

you will need to create one logical table and add 2 sources to it city and state. And then you create a logical dimension hierarchy on it. So now you see the purpose of multiple logical table sources?

i Just touched an iota of its purpose and you will learn more once you go deep.

for the purpose of logical layer. Physical layer is where u define joins, keys etc and defining keys is reqd in physical. However, in logical layer you define the cardinality of the tables, dimension hierarchy, outer joins, calculations custom columns. you can even join two stars in logical layer. Its because of this logical layer that the obiee can build a single report on multiple facts revolving non conformed dimensions by setting the aggregation level.

again because it was a subjective question, you will need to go deep.

in case you need an answer to specific problem please do not hesitat to post.

PLease mark it answered if you find this helpful

thanks

vipul bhatia