Working with Joins on Admin Tool…
(8 votes, average: 4.25 out of 5)
Loading ...Joins is a very common thing and most of the people don’t give much attention how and when we use Physical Joins and when we use coplex joins… Lets c how and why of joins…..
Primary-Foreign Key Joins
The PK-FK joins are generally used in Physical Layer. These joins are required to form the physical query using which the data from the underlying DB will be pulled. These are the joins between the PK FK columns in the facts and dimensions. These joins are used between defined columns between 2 tables. As shown in the pic below the jons are defined between 2 columns which can be seen from the expression.
We generally make use of PK-FK joins in Physical layer and these joins are called Physical Foreign Key Joins.
We can also make use of PK-FK joins in BMM layer or Logical Layer and these joins are called Logical Foreign Key Joins .
The PK-FK Joins in BMM are present when we drag the entire schema from Physical Layer to the BMM layer. These joins can be present in BMM in the case when we have only 1 Logical Table source(LTS) per Dimension in the BMM.
But as per the best practices we should restrict our use of Logical FK Joins in BMM because if there is a requirement to increase the number of LTS in a dimension in future, then, we can’t do that if we make use PK-FK joins in BMM.
Complex Joins
These joins are generally used in BMM layer. Specifying the Complex Joins is required so that the Siebel Analytics Server can have the necessary metadata to translate a logical request against the business model to SQL queries against the physical data sources. The complex join information provides the Analytics server with the many-to-one relationships between the logical tables. This logical join information is used when the Analytics server generates queries against the underlying databases.
A complex join takes in consideration the joins at the physical layers and accordingly makes the joins at the BMM layer i.e. This join is specified between 2 tables and we don’t specify any columns in the join, the Analytics server itself picks the joins based on the joins in the Physical Layer.
When we use a complex join in the Physical layer it acts as a one to one join and the join type (left, rite outer) and cardinality options are not present in that case. The complex joins in Physical Layer are used only in specific cases where we have to join the tables based on some conditions.
We have to select the columns of the tables between which we need to put the joins and we can place any conditions in the expression based on the requirement.
Hi Rohit,
1.Why do we use aliases on physical layer?
2.How MUD is done?
Hi,
Can we join two facts?
I ave one fact and four dimensions joined, and i want to get a column of another fact into the first fact. If i directly drag the column of another fact into the first fact which is in BMM layer, in my report the second fact’s column is not showig any result. If i join second fact with the first fact, then it is giving the values in report.
Could u pls clarify my doubt…
Can u please explain the difference between Physical Join and Complex join?
Yes, Sure JAY!!!
The Difference between Physical and complex joins is that the Physical joins are the joins which are placed on the Primary and Foreign Keys between the 2 tables. The physical joins are mostly used in Physical Layer. The OBIEE server creates its Physical query based on these joins which further hits the Database and fetches the data.
Whereas, Complex Joins are mostly used on the BMM layer and these are not created on PK & FK. The OBIEE server creates a logical requests based on these joins.
Kindly let me know in case of any further concerns or queries….
Hi,
Suppose, we implemented complex joins in physical layer and the the Analytics server issues query against the underlying database.
By seeing the query, how to identify whether the join is complex join or not?
Hi Rohit,
I am confused about complex join and unable to understand in which situation it should be used.
My understanding is as follows,
1. you can either have foreign key join or complex join.
2. in the physical layer, most of the time you use foreign key join, the only exception is when you have non-equi condition (for eg between…).
Can you please explain the Complex Join in detail.
Hi,
When we run a report, the BI Server generates 2 queries:
1) Logical
2) Physical
Now, the BI Server takes complex joins into consideration while generating the logical query and furthur the Physical query out of the logical query.
Generally, we make use of Complex joins in the BMM layer. In BMM, Complex Joins are always applied between the tables folders and not the logical table sources, hence, it facilitates the extendibility for future uses. We can add many LTS to the table folder without changing the joins in BMM. The compplex joins doesnt apply on any specific columns in BMM and the server takes the reference from the Physical layer and establish the joins between them.
We can also make use of the Complex joins in the Physical Layer in case of the non equal conditions eg: When we join on some non key columns and use non equality conditions.
Kindly let me know what else do u want to know about the complex joins or if you have any confusion.
Rohit,
Thank you so much, I have understood the concept to a certain extentd but can you explain it with one example (for example foreign key join is straight forward, you define it and when user select column from table having parent child relationship then it be applied, BUT HOW a complex join is defined and WHAT EFFECT it has on the physical query that is generated by BI server is not clear).
Regards,
Libral
Leave your response!
Recent Posts
Categories
Archives
Tags
Most Commented
Most Viewed