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.
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.
Physical Foreign Key Joins Diag.
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.
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.
Logical Foreign Key Joins Diag.
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.
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.
Logical Join BMM Layer Diag.
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.
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.
Complex Join Physical Layer Diag.
Posts
