Tweet |

This is the first article in this series and we will be talking about the scenario where we pull columns from 2 different physical sources and 1 common dimension in the same analysis.

I selected the following columns

Let me now share the Aggregation rule and content level for both of the measure columns

So we see that none of the 2 measures are tagged to any level in the dimension and the aggregation rule is Sum.

Also note that both the measure columns are from the same logical fact.

Let us now look at the column source mappings of each of these measures.

We note that these columns are mapped to different tables/aliases in the physical layer.

Let us now look at the physical layer joins of the tables involved in this Analysis.

Let us now look at the physical query generated by this analysis.

WITH SAWITH0 AS (select sum(T42433.Cost_Fixed) as c1, T42409.Prod_Dsc as c2, T42409.Prod_Key as c3 from BISAMPLE.SAMP_PRODUCTS_D T42409 /* D10 Product (Dynamic Table) */ , BISAMPLE.SAMP_REVENUE_F T42433 /* F10 Billed Rev */ where ( T42409.Prod_Key = T42433.Prod_Key ) group by T42409.Prod_Dsc, T42409.Prod_Key), SAWITH1 AS (select sum(T68770.Revenue) as c1, T42409.Prod_Dsc as c2, T42409.Prod_Key as c3 from BISAMPLE.SAMP_PRODUCTS_D T42409 /* D10 Product (Dynamic Table) */ , BISAMPLE.SAMP_REVENUE_F T68770 /* F14 Rev. (Ship Dt Join) */ where ( T42409.Prod_Key = T68770.Prod_Key ) group by T42409.Prod_Dsc, T42409.Prod_Key), SAWITH2 AS (select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5 from (select 0 as c1, case when D1.c2 is not null then D1.c2 when D2.c2 is not null then D2.c2 end as c2, D1.c1 as c3, D2.c1 as c4, case when D1.c3 is not null then D1.c3 when D2.c3 is not null then D2.c3 end as c5, ROW_NUMBER() OVER (PARTITION BY case when D1.c2 is not null then D1.c2 when D2.c2 is not null then D2.c2 end , case when D1.c3 is not null then D1.c3 when D2.c3 is not null then D2.c3 end ORDER BY case when D1.c2 is not null then D1.c2 when D2.c2 is not null then D2.c2 end ASC, case when D1.c3 is not null then D1.c3 when D2.c3 is not null then D2.c3 end ASC) as c6 from SAWITH0 D1full outer joinSAWITH1 D2 On D1.c3 = D2.c3 ) D1 where ( D1.c6 = 1 ) ) select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from ( select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from SAWITH2 D1 order by c1, c2 ) D1 where rownum <= 5000001

```
```

__Lesson: We see that we have 2 with clauses joined by a full outer join. This is primarily because F10 Billed Rev and F14 Rev. (Ship Dt Join) are 2 LTS in the same Logical table. Since these LTS are not joined in the Logical table so these should essentially be calculated separately. However, the analysis demands that these be joined together. So what does OBIEE do? It calculates them separately and then joins them using a Full Outer Join.__

__
__

Let us now joinF10 Billed Rev and F14 Rev. (Ship Dt Join) in the physical layer and then join them in a single LTS as shown below.

```
```

Let us now look at the Business model and the generated query.

```
```

```
```

WITH SAWITH0 AS (select sum(T286916.Units) as c1, sum(T286892.Cost_Fixed) as c2, T286942.Prod_Dsc as c3 from SAMP_PRODUCTS_D T286942 /* D10 Product (Dynamic Table) */ , SAMP_REVENUE_F T286892 /* F10 Billed Rev */ , SAMP_REVENUE_F T286916 /* F14 Rev. (Ship Dt Join) */ where ( T286892.Bill_Qtr_Key = T286916.Bill_Qtr_Key and T286892.Cust_Key = T286916.Cust_Key and T286892.Order_Key = T286916.Order_Key and T286892.Prod_Key = T286916.Prod_Key and T286892.Prod_Key = T286942.Prod_Key and T286892.Empl_Key = T286916.Empl_Key and T286892.Bill_Mth_Key = T286916.Bill_Mth_Key and T286892.Shipto_Addr_Key = T286916.Shipto_Addr_Key and T286892.Office_Key = T286916.Office_Key and T286916.Prod_Key = T286942.Prod_Key ) group by T286942.Prod_Dsc) select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from ( select distinct 0 as c1, D1.c3 as c2, D1.c2 as c3, D1.c1 as c4 from SAWITH0 D1 order by c2 ) D1 where rownum <= 5000001

__Moral of the Story:__

1. This join is a Fact to Fact join. This is not advisable from a datawarehouse modelling perspective but it results in improved query in certain cases.

2. The implications of this join on other Analysis should be thought out well before implementing the same. There might be some other analysis in which you might not want to follow the old access path.

```
```

`Let us now bring in the 2 fact sources as separate Logical tables in the BMM and see the result. Let me share the new BMM model`

```
```

```
```

Note thatF10 Billed Rev and F14 Rev. (Ship Dt Join)are separate Logical tables in the BMM.

```
```

`The resulting query is`

```
```

WITH SAWITH0 AS (select sum(T286892.Cost_Fixed) as c1, T286942.Prod_Dsc as c2 from SAMP_PRODUCTS_D T286942 /* D10 Product (Dynamic Table) */ , SAMP_REVENUE_F T286892 /* F10 Billed Rev */ where ( T286892.Prod_Key = T286942.Prod_Key ) group by T286942.Prod_Dsc), SAWITH1 AS (select sum(T286916.Units) as c1, T286942.Prod_Dsc as c2 from SAMP_PRODUCTS_D T286942 /* D10 Product (Dynamic Table) */ , SAMP_REVENUE_F T286916 /* F14 Rev. (Ship Dt Join) */ where ( T286916.Prod_Key = T286942.Prod_Key ) group by T286942.Prod_Dsc), SAWITH2 AS (select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from (select 0 as c1, case when D1.c2 is not null then D1.c2 when D2.c2 is not null then D2.c2 end as c2, D1.c1 as c3, D2.c1 as c4, ROW_NUMBER() OVER (PARTITION BY case when D1.c2 is not null then D1.c2 when D2.c2 is not null then D2.c2 end ORDER BY case when D1.c2 is not null then D1.c2 when D2.c2 is not null then D2.c2 end ASC) as c5 from SAWITH0 D1 full outer join SAWITH1 D2 On D1.c2 = D2.c2 ) D1 where ( D1.c5 = 1 ) ) select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from ( select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from SAWITH2 D1 order by c1, c2 ) D1 where rownum <= 5000001

`We see that the query is the same as that in the 1st case where we did not have the join. The reason? We don't have a join in the Business model between the 2 fact sources. So the BI server does not use the join between these sources in the Physical layer.`

```
```

I know what you are thinking. Why dont we join the 2 fact sources in the BMM? Well, in this case, SAMP_REVENUE_F is the source of both F10 Billed Rev and F14 Rev. (Ship Dt Join). So the join should ideally have a 1 to 1 cardinality. Now, joining with this cardinality will result in circular join.

`Many to Many join does not make sense in this scenario but let's say we did it, we will have to introduce a bridge table to fulfil the join. The last option is to make one tabe as fact and the other as dimension but this will cause the measure from the table acting as dimension to become null and void. OBIEE will not display this measure because measures cant be in dimensions.`

```
```

`The logical SQL will be`

```
```

SELECT 0 s_0, "Vishal"."Products"."P1 Product" s_1, "Vishal"."F10 Billed Rev"."11- Fixed Costs" s_2, "Vishal"."F14 Rev. (Ship Dt Join)"."7- Shipped Amount" s_3 FROM "Vishal" ORDER BY 1, 2 ASC NULLS LAST FETCH FIRST 5000001 ROWS ONLYand the physical SQL will be

WITH SAWITH0 AS (select sum(T286916.Units) as c1, T286942.Prod_Dsc as c2 from SAMP_PRODUCTS_D T286942 /* D10 Product (Dynamic Table) */ , SAMP_REVENUE_F T286916 /* F14 Rev. (Ship Dt Join) */ where ( T286916.Prod_Key = T286942.Prod_Key ) group by T286942.Prod_Dsc) select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from ( select distinct 0 as c1, D1.c2 as c2, cast(NULL as DOUBLE PRECISION ) as c3, D1.c1 as c4 from SAWITH0 D1 order by c2 ) D1 where rownum <= 5000001Here, F10 Billed Rev was acting as the dimension. You will also see the following error in your logs

Expression '11- Fixed Costs:[DAggr(F10 Billed Rev.11- Fixed Costs by [ D1 Products (Level Based Hier).P1 Product] )]' converted to NULL because [nQSError: 14020] None of the fact tables are compatible with the query request 11- Fixed Costs:[DAggr(F10 Billed Rev.11- Fixed Costs by [ D1 Products (Level Based Hier).P1 Product] )]Till next time ..

## No comments:

Post a Comment