Categories
- All Categories
- 151 Oracle Analytics News
- 28 Oracle Analytics Videos
- 14.7K Oracle Analytics Forums
- 5.7K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 54 Oracle Analytics Trainings
- 12 Oracle Analytics Data Visualizations Challenge
- 4 Oracle Analytics Career
- 2 Oracle Analytics Industry
- Find Partners
- For Partners
Physical vs Logical Join? - OBIEE 11g
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?
Answers
-
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
0 -
That does help provide a lot of context - and yes you are correct I do have a long way to go to understanding all of this
I do have a question that I am trying to figure out
I was told to modify our RPD and to remove some columns that are "not used". One column was the HOME_FACULTY_KEY.
I went into our RPD in the BMM and removed the HOME_FACULTY_KEY from a Hierarchy no other changes. I just deleted HOME_FACULTY_KEY from one hierarchy
Before removing HOME_FACULTY_KEY the SQL that was generated was as follows
-------------------------------------------------------------------------------------------------------------------------------------------------------
WITH
SAWITH0 AS (select sum(T87596.ACTIVE_HIGHEST_APPL_COUNT) as c1,
sum(case when T87634.ACAD_YEAR_DESC = 'Acad 2016/17' then T87596.ACTIVE_HIGHEST_APPL_COUNT end ) as c2,
sum(case when T87634.ACAD_YEAR_DESC = 'Acad 2015/16' then T87596.ACTIVE_HIGHEST_APPL_COUNT end ) as c3,
T87712.HOME_FACULTY_SHORT_DESC as c4,
T87685.OUAC_CHOICE_GROUP_DESC as c5,
T87634.TERM_DESC as c6,
T87634.TERM_GENERIC_DESC as c7,
T87712.HOME_FACULTY_KEY as c8
from
DM_ADM_CHOICE_GROUP T87685 /* Dimension Admission Choice Group */ ,
DM_CNF_TERM T87634 /* Dimension Conformed Term */ ,
DM_CNF_PROG_ENTRY_LEVEL_DTL T87835 /* Dimension Conformed Program Entry Level Detail */ ,
DM_CNF_PROGRAM T87712 /* Dimension Conformed Program */ ,
DSQ_FACT_ADM_SUMM_SNAP_TYLY T87596 /* Fact Admission Summary Snapshot */
where ( T87596.TERM_KEY = T87634.TERM_KEY and T87596.PROGRAM_KEY = T87712.PROGRAM_KEY and T87596.OUAC_CHOICE_GROUP_KEY = T87685.OUAC_CHOICE_GROUP_KEY and T87596.PROG_ENTRY_LEVEL_DTL_KEY = T87835.PROG_ENTRY_LEVEL_DTL_KEY and T87634.TERM_GENERIC_DESC = 'Fall' and T87835.PROG_ENTRY_LEVEL_TEXT = 'Direct Entry' )
group by T87634.TERM_GENERIC_DESC, T87634.TERM_DESC, T87685.OUAC_CHOICE_GROUP_DESC, T87712.HOME_FACULTY_KEY, T87712.HOME_FACULTY_SHORT_DESC
having sum(T87596.ACTIVE_HIGHEST_APPL_COUNT) <> 0)
select 0 as c1,
D1.c4 as c2,
D1.c5 as c3,
D1.c6 as c4,
D1.c7 as c5,
case when D1.c7 = 'Fall' then floor((nvl(D1.c2 , 0) - nvl(D1.c3 , 0)) / nullif( D1.c3, 0)) * 100.0 when D1.c7 = 'Winter' then floor((nvl(D1.c2 , 0) - nvl(D1.c3 , 0)) / nullif( D1.c3, 0)) * 100.0 when D1.c7 = 'Summer' then floor((nvl(D1.c2 , 0) - nvl(D1.c3 , 0)) / nullif( D1.c3, 0)) * 100.0 else 0 end as c6,
case when D1.c7 = 'Fall' then nvl(D1.c2 , 0) - nvl(D1.c3 , 0) when D1.c7 = 'Winter' then nvl(D1.c2 , 0) - nvl(D1.c3 , 0) when D1.c7 = 'Summer' then nvl(D1.c2 , 0) - nvl(D1.c3 , 0) else 0 end as c7,
D1.c2 as c14,
D1.c8 as c15,
D1.c3 as c16
from
SAWITH0 D1
order by c5, c2, c3, c4, c15
-------------------------------------------------------------------------------------------------------------------------------------------------------
After I removed the HOME_FACULTY_KEY from the Hierarchy the SQL changed to this
-------------------------------------------------------------------------------------------------------------------------------------------------------
WITH
SAWITH0 AS (select sum(T87596.ACTIVE_HIGHEST_APPL_COUNT) as c1,
sum(case when T87634.ACAD_YEAR_DESC = 'Acad 2016/17' then T87596.ACTIVE_HIGHEST_APPL_COUNT end ) as c2,
sum(case when T87634.ACAD_YEAR_DESC = 'Acad 2015/16' then T87596.ACTIVE_HIGHEST_APPL_COUNT end ) as c3,
T87712.HOME_FACULTY_SHORT_DESC as c4,
T87685.OUAC_CHOICE_GROUP_DESC as c5,
T87634.TERM_DESC as c6,
T87634.TERM_GENERIC_DESC as c7
from
DM_ADM_CHOICE_GROUP T87685 /* Dimension Admission Choice Group */ ,
DM_CNF_TERM T87634 /* Dimension Conformed Term */ ,
DM_CNF_PROG_ENTRY_LEVEL_DTL T87835 /* Dimension Conformed Program Entry Level Detail */ ,
DM_CNF_PROGRAM T87712 /* Dimension Conformed Program */ ,
DSQ_FACT_ADM_SUMM_SNAP_TYLY T87596 /* Fact Admission Summary Snapshot */
where ( T87596.TERM_KEY = T87634.TERM_KEY and T87596.PROGRAM_KEY = T87712.PROGRAM_KEY and T87596.OUAC_CHOICE_GROUP_KEY = T87685.OUAC_CHOICE_GROUP_KEY and T87596.PROG_ENTRY_LEVEL_DTL_KEY = T87835.PROG_ENTRY_LEVEL_DTL_KEY and T87634.TERM_GENERIC_DESC = 'Fall' and T87835.PROG_ENTRY_LEVEL_TEXT = 'Direct Entry' )
group by T87634.TERM_GENERIC_DESC, T87634.TERM_DESC, T87685.OUAC_CHOICE_GROUP_DESC, T87712.HOME_FACULTY_SHORT_DESC
having sum(T87596.ACTIVE_HIGHEST_APPL_COUNT) <> 0),
SAWITH1 AS (select 0 as c1,
D1.c4 as c2,
D1.c5 as c3,
D1.c6 as c4,
D1.c7 as c5,
case when D1.c7 = 'Fall' then floor((nvl(D1.c2 , 0) - nvl(D1.c3 , 0)) / nullif( D1.c3, 0)) * 100.0 when D1.c7 = 'Winter' then floor((nvl(D1.c2 , 0) - nvl(D1.c3 , 0)) / nullif( D1.c3, 0)) * 100.0 when D1.c7 = 'Summer' then floor((nvl(D1.c2 , 0) - nvl(D1.c3 , 0)) / nullif( D1.c3, 0)) * 100.0 else 0 end as c6,
case when D1.c7 = 'Fall' then nvl(D1.c2 , 0) - nvl(D1.c3 , 0) when D1.c7 = 'Winter' then nvl(D1.c2 , 0) - nvl(D1.c3 , 0) when D1.c7 = 'Summer' then nvl(D1.c2 , 0) - nvl(D1.c3 , 0) else 0 end as c7,
D1.c2 as c16,
D1.c3 as c17
from
SAWITH0 D1)
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5, D1.c6 as c6, D1.c7 as c7, D1.c8 as c8, D1.c9 as c9, D1.c10 as c10, D1.c11 as c11, D1.c12 as c12, D1.c13 as c13, D1.c14 as c14, D1.c15 as c15 from ( select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4,
D1.c5 as c5,
D1.c6 as c6,
D1.c7 as c7,
case when D1.c5 = 'Fall' then floor((nvl(sum(D1.c16) over (partition by D1.c5) , 0) - nvl(sum(D1.c17) over (partition by D1.c5) , 0)) / nullif( sum(D1.c17) over (partition by D1.c5) , 0)) * 100.0 when D1.c5 = 'Winter' then floor((nvl(sum(D1.c16) over (partition by D1.c5) , 0) - nvl(sum(D1.c17) over (partition by D1.c5) , 0)) / nullif( sum(D1.c17) over (partition by D1.c5) , 0)) * 100.0 when D1.c5 = 'Summer' then floor((nvl(sum(D1.c16) over (partition by D1.c5) , 0) - nvl(sum(D1.c17) over (partition by D1.c5) , 0)) / nullif( sum(D1.c17) over (partition by D1.c5) , 0)) * 100.0 else 0 end as c8,
case when D1.c5 = 'Fall' then floor((nvl(sum(D1.c16) over (partition by D1.c5, D1.c2) , 0) - nvl(sum(D1.c17) over (partition by D1.c5, D1.c2) , 0)) / nullif( sum(D1.c17) over (partition by D1.c5, D1.c2) , 0)) * 100.0 when D1.c5 = 'Winter' then floor((nvl(sum(D1.c16) over (partition by D1.c5, D1.c2) , 0) - nvl(sum(D1.c17) over (partition by D1.c5, D1.c2) , 0)) / nullif( sum(D1.c17) over (partition by D1.c5, D1.c2) , 0)) * 100.0 when D1.c5 = 'Summer' then floor((nvl(sum(D1.c16) over (partition by D1.c5, D1.c2) , 0) - nvl(sum(D1.c17) over (partition by D1.c5, D1.c2) , 0)) / nullif( sum(D1.c17) over (partition by D1.c5, D1.c2) , 0)) * 100.0 else 0 end as c9,
case when D1.c5 = 'Fall' then floor((nvl(sum(D1.c16) over (partition by D1.c5, D1.c3, D1.c2) , 0) - nvl(sum(D1.c17) over (partition by D1.c5, D1.c3, D1.c2) , 0)) / nullif( sum(D1.c17) over (partition by D1.c5, D1.c3, D1.c2) , 0)) * 100.0 when D1.c5 = 'Winter' then floor((nvl(sum(D1.c16) over (partition by D1.c5, D1.c3, D1.c2) , 0) - nvl(sum(D1.c17) over (partition by D1.c5, D1.c3, D1.c2) , 0)) / nullif( sum(D1.c17) over (partition by D1.c5, D1.c3, D1.c2) , 0)) * 100.0 when D1.c5 = 'Summer' then floor((nvl(sum(D1.c16) over (partition by D1.c5, D1.c3, D1.c2) , 0) - nvl(sum(D1.c17) over (partition by D1.c5, D1.c3, D1.c2) , 0)) / nullif( sum(D1.c17) over (partition by D1.c5, D1.c3, D1.c2) , 0)) * 100.0 else 0 end as c10,
case when D1.c5 = 'Fall' then floor((nvl(sum(D1.c16) over (partition by D1.c5, D1.c3) , 0) - nvl(sum(D1.c17) over (partition by D1.c5, D1.c3) , 0)) / nullif( sum(D1.c17) over (partition by D1.c5, D1.c3) , 0)) * 100.0 when D1.c5 = 'Winter' then floor((nvl(sum(D1.c16) over (partition by D1.c5, D1.c3) , 0) - nvl(sum(D1.c17) over (partition by D1.c5, D1.c3) , 0)) / nullif( sum(D1.c17) over (partition by D1.c5, D1.c3) , 0)) * 100.0 when D1.c5 = 'Summer' then floor((nvl(sum(D1.c16) over (partition by D1.c5, D1.c3) , 0) - nvl(sum(D1.c17) over (partition by D1.c5, D1.c3) , 0)) / nullif( sum(D1.c17) over (partition by D1.c5, D1.c3) , 0)) * 100.0 else 0 end as c11,
case when D1.c5 = 'Fall' then nvl(sum(D1.c16) over (partition by D1.c5) , 0) - nvl(sum(D1.c17) over (partition by D1.c5) , 0) when D1.c5 = 'Winter' then nvl(sum(D1.c16) over (partition by D1.c5) , 0) - nvl(sum(D1.c17) over (partition by D1.c5) , 0) when D1.c5 = 'Summer' then nvl(sum(D1.c16) over (partition by D1.c5) , 0) - nvl(sum(D1.c17) over (partition by D1.c5) , 0) else 0 end as c12,
case when D1.c5 = 'Fall' then nvl(sum(D1.c16) over (partition by D1.c5, D1.c2) , 0) - nvl(sum(D1.c17) over (partition by D1.c5, D1.c2) , 0) when D1.c5 = 'Winter' then nvl(sum(D1.c16) over (partition by D1.c5, D1.c2) , 0) - nvl(sum(D1.c17) over (partition by D1.c5, D1.c2) , 0) when D1.c5 = 'Summer' then nvl(sum(D1.c16) over (partition by D1.c5, D1.c2) , 0) - nvl(sum(D1.c17) over (partition by D1.c5, D1.c2) , 0) else 0 end as c13,
case when D1.c5 = 'Fall' then nvl(sum(D1.c16) over (partition by D1.c5, D1.c3, D1.c2) , 0) - nvl(sum(D1.c17) over (partition by D1.c5, D1.c3, D1.c2) , 0) when D1.c5 = 'Winter' then nvl(sum(D1.c16) over (partition by D1.c5, D1.c3, D1.c2) , 0) - nvl(sum(D1.c17) over (partition by D1.c5, D1.c3, D1.c2) , 0) when D1.c5 = 'Summer' then nvl(sum(D1.c16) over (partition by D1.c5, D1.c3, D1.c2) , 0) - nvl(sum(D1.c17) over (partition by D1.c5, D1.c3, D1.c2) , 0) else 0 end as c14,
case when D1.c5 = 'Fall' then nvl(sum(D1.c16) over (partition by D1.c5, D1.c3) , 0) - nvl(sum(D1.c17) over (partition by D1.c5, D1.c3) , 0) when D1.c5 = 'Winter' then nvl(sum(D1.c16) over (partition by D1.c5, D1.c3) , 0) - nvl(sum(D1.c17) over (partition by D1.c5, D1.c3) , 0) when D1.c5 = 'Summer' then nvl(sum(D1.c16) over (partition by D1.c5, D1.c3) , 0) - nvl(sum(D1.c17) over (partition by D1.c5, D1.c3) , 0) else 0 end as c15
from
SAWITH1 D1
order by c1, c2, c3, c5, c4 ) D1 where rownum <= 10000000
-------------------------------------------------------------------------------------------------------------------------------------------------------
So I see that the HOME_FACULTY_KEY is no longer part of the SQL from just removing it from the Hierarchy but my result data has totally changed.
Now I have a few issues
HOME_FACULTY_KEY is actually not used in this report at all. Its not part of the presentation layer, its only in the physical and BMM layer. So why would the first SQL even use HOME_FACULTY_KEY?
Secondly, since I just removed HOME_FACULTY_KEY from the hierarchy I would have thought that the same SQL would be generated minus HOME_FACULTY_KEY - why would the SQL change so drastically?
Really stuck here as to why this is happening
0 -
Looks like that KEY was key to the logical/physical model ... don't just go deleting willy-nilly. Since the KEY was being used as a logical key in the hierarchy, you've now removed the BI server's instructions on how to parse the hierarchy and aggregate measures to that node level.
0