The Beauty of MLTS in OBIEE RPD

We can achieve complex logic in the physical query using MLTS in OBIEE RPD to achieve. This post intended to describe one of such cases.

Couple of days back, I came across a very special requirement (though not very ideal). 

• There are 2 Facts and a few Conformed Dimensions. For the purpose of this post let's take the tables we have mentioned earlier in Putting Multiple Rows into a Single Row. In this case ALLOCATION_F, SKILL_MATRIX_F and EMPLOYEE_D (as the conformed dimension). 

• These 2 facts are again joined with each other, with outer towards one of them. We consider SKILL_MATRIX_F is on the outer side. 

• There are both Measures and Attributes in the facts. In this case I am going to consider the foreign keys (without aggregation) as the attributes

• While columns are selected from only one of the facts or one fact and the conformed dimensions, the query doesn't include the other fact. However when columns (measure or attribute) columns are selected from both the facts, with / without the conformed dimension the query must contain both the facts outer joined.

• While the query is forming with both the facts and the conformed dimensions the data set should be like (Fact 1 + Conformed  Dim ) Left Outer Join (Fact 2 + Conformed Dim ). So that no record from Fact 1 (in our case SKILL_MATRIX_F) gets rejected. 

• Both the facts work with any combination of measure or attribute columns select with each other.

Our physical looks like,

I create 2 aliases of EMPLOYEE_D as EMPLOYEE_D1 and EMPLOYEE_D2 and join them with one fact each.

The reason for using 2 alias is to maintain the outer join between the facts. Otherwise while the query is being generated, it will be like (Fact 1 Left Outer Join Fact 2) Inner Join Conformed Dim. Which negates the outer join. But with 2 alias it will be like (Fact 1 + Conformed Dim Alias 1 ) Left Outer Join (Fact 2 + Conformed Dim Alias 2).

Next I create 1 logical fact and 1 logical dimension in the BMM.

I put both the aliases of the Conformed dimension in the same logical dimension. And create a single logical facts for both the physical facts. In the LTS I have renamed EMPLOYEE_D1 as Employee Dim Skill and EMPLOYEE_D2 as Employee Dim Allocation. All the columns in Dim - Employee are mapped to both the LTS.

In the fact I have 3 LTS. One from each of the facts and one combining both of them. I rename LTS for ALLOCATION_F as Allocation.

And SKILL_MATRIX_F as Skill.

I name the combined one as Skill + Allocation, where it is ALLOCATION_F Right Outer Join SKILL_MATRIX_F.

The logical fact and dimension are inner joined with each other.

In the logical fact each column is mapped from 2 LTS (individual fact and combined fact).

I add 2 measure columns as # of Skills (= count distinct SKILL_SID) and # of Projects (= count distinct PROJECT_SID). And I rename SKILL_SID as Skill Attribute and PROJECT_SID as Project Attribute, these 2 will be my attribute columns from the facts.

The most important part of the MLTS is the way these are ordered. For this given case in dimension the order will be,

1. Employee Dim Skill
2. Employee Dim Allocation

And in the fact the order of the MLTS will be,

1. Allocation
2. Skill + Allocation
3. Skill

Point to be noted I am reversing the order of Skill and Allocation in the fact and dimension.

Next I expose the columns in the Presentation Layer.

Now it is time to validate the functionality. For that I will be going through several test cases to cover all the possible scenarios.

Test Case 1: Employee Dimension + Measure from Skill Fact

select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select 0 as c1,

     D1.c2 as c2,

     D1.c1 as c3

from 

     (select count(distinct T82720.SKILL_SID) as c1,

               T83197.EMPLOYEE_NAME as c2

          from 

               EMPLOYEE_D T83197 /* EMPLOYEE_D1 */ ,

               SKILL_MATRIX_F T82720

          where  ( T82720.EMPLOYEE_SID = T83197.EMPLOYEE_SID ) 

          group by T83197.EMPLOYEE_NAME

     ) D1

order by c2 ) D1 where rownum <= 65001

Test Case 2: Employee Dimension + Measure from Allocation Fact

select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select 0 as c1,
     D1.c2 as c2,
     D1.c1 as c3
from 
     (select count(distinct T82710.PROJECT_SID) as c1,
               T83201.EMPLOYEE_NAME as c2
          from 
               EMPLOYEE_D T83201 /* EMPLOYEE_D2 */ ,
               ALLOCATION_F T82710
          where  ( T82710.EMPLOYEE_SID = T83201.EMPLOYEE_SID ) 
          group by T83201.EMPLOYEE_NAME
     ) D1
order by c2 ) D1 where rownum <= 65001

Test Case 3: Employee Dimension + Measure from Allocation Fact + Measure from Skill Fact

select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from ( select 0 as c1,
     D1.c3 as c2,
     D1.c2 as c3,
     D1.c1 as c4
from 
     (select count(distinct T82720.SKILL_SID) as c1,
               count(distinct T82710.PROJECT_SID) as c2,
               T83197.EMPLOYEE_NAME as c3
          from 
               EMPLOYEE_D T83197 /* EMPLOYEE_D1 */ ,
               SKILL_MATRIX_F T82720 left outer join ALLOCATION_F T82710 On T82710.ALL_SM_SID = T82720.ALL_SM_SID
          where  ( T82720.EMPLOYEE_SID = T83197.EMPLOYEE_SID ) 
          group by T83197.EMPLOYEE_NAME
     ) D1
order by c2 ) D1 where rownum <= 65001

Test Case 4: Employee Dimension + Measure from Allocation Fact + Measure from Skill Fact + Attribute from Skill Fact

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,
     D1.c4 as c2,
     D1.c3 as c3,
     D1.c2 as c4,
     D1.c1 as c5
from 
     (select count(distinct T82720.SKILL_SID) as c1,
               count(distinct T82710.PROJECT_SID) as c2,
               T82720.SKILL_SID as c3,
               T83197.EMPLOYEE_NAME as c4
          from 
               EMPLOYEE_D T83197 /* EMPLOYEE_D1 */ ,
               SKILL_MATRIX_F T82720 left outer join ALLOCATION_F T82710 On T82710.ALL_SM_SID = T82720.ALL_SM_SID
          where  ( T82720.EMPLOYEE_SID = T83197.EMPLOYEE_SID ) 
          group by T82720.SKILL_SID, T83197.EMPLOYEE_NAME
     ) D1
order by c2, c3 ) D1 where rownum <= 65001

Test Case 5: Employee Dimension + Measure from Allocation Fact + Measure from Skill Fact + Attribute from Allocation Fact

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,
     D1.c4 as c2,
     D1.c3 as c3,
     D1.c2 as c4,
     D1.c1 as c5
from 
     (select count(distinct T82720.SKILL_SID) as c1,
               count(distinct T82710.PROJECT_SID) as c2,
               T82710.PROJECT_SID as c3,
               T83197.EMPLOYEE_NAME as c4
          from 
               EMPLOYEE_D T83197 /* EMPLOYEE_D1 */ ,
               SKILL_MATRIX_F T82720 left outer join ALLOCATION_F T82710 On T82710.ALL_SM_SID = T82720.ALL_SM_SID
          where  ( T82720.EMPLOYEE_SID = T83197.EMPLOYEE_SID ) 
          group by T82710.PROJECT_SID, T83197.EMPLOYEE_NAME
     ) D1
order by c2, c3 ) D1 where rownum <= 65001

Test Case 6: Measure from Allocation Fact + Measure from Skill Fact + Attribute from Skill Fact

select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from ( select 0 as c1,
     D1.c3 as c2,
     D1.c2 as c3,
     D1.c1 as c4
from 
     (select count(distinct T82720.SKILL_SID) as c1,
               count(distinct T82710.PROJECT_SID) as c2,
               T82720.SKILL_SID as c3
          from 
               SKILL_MATRIX_F T82720 left outer join ALLOCATION_F T82710 On T82710.ALL_SM_SID = T82720.ALL_SM_SID
          group by T82720.SKILL_SID
     ) D1
order by c2 ) D1 where rownum <= 65001

Test Case 7: Measure from Allocation Fact + Measure from Skill Fact + Attribute from Allocation Fact

select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from ( select 0 as c1,
     D1.c3 as c2,
     D1.c2 as c3,
     D1.c1 as c4
from 
     (select count(distinct T82720.SKILL_SID) as c1,
               count(distinct T82710.PROJECT_SID) as c2,
               T82710.PROJECT_SID as c3
          from 
               SKILL_MATRIX_F T82720 left outer join ALLOCATION_F T82710 On T82710.ALL_SM_SID = T82720.ALL_SM_SID
          group by T82710.PROJECT_SID
     ) D1
order by c2 ) D1 where rownum <= 65001

Test Case 8: Measure from Allocation Fact + Measure from Skill Fact

select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select distinct 0 as c1,
     D1.c2 as c2,
     D1.c1 as c3
from 
     (select count(distinct T82720.SKILL_SID) as c1,
               count(distinct T82710.PROJECT_SID) as c2
          from 
               SKILL_MATRIX_F T82720 left outer join ALLOCATION_F T82710 On T82710.ALL_SM_SID = T82720.ALL_SM_SID
     ) D1 ) D1 where rownum <= 65001

Test Case 9: Employee Dimension + Measure from Allocation Fact + Measure from Skill Fact + Attribute from Allocation Fact + Attribute from Skill Fact

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 from ( select 0 as c1,
     D1.c5 as c2,
     D1.c4 as c3,
     D1.c3 as c4,
     D1.c2 as c5,
     D1.c1 as c6
from 
     (select count(distinct T82720.SKILL_SID) as c1,
               count(distinct T82710.PROJECT_SID) as c2,
               T82720.SKILL_SID as c3,
               T82710.PROJECT_SID as c4,
               T83197.EMPLOYEE_NAME as c5
          from 
               EMPLOYEE_D T83197 /* EMPLOYEE_D1 */ ,
               SKILL_MATRIX_F T82720 left outer join ALLOCATION_F T82710 On T82710.ALL_SM_SID = T82720.ALL_SM_SID
          where  ( T82720.EMPLOYEE_SID = T83197.EMPLOYEE_SID ) 
          group by T82710.PROJECT_SID, T82720.SKILL_SID, T83197.EMPLOYEE_NAME
     ) D1
order by c2, c4, c3 ) D1 where rownum <= 65001

The design has worked in all the above test case.

I would like to mention again that, this is a special case that I have come across in one of the projects, and not a very ideal scenario. However it depicts how we can use MLTS in OBIEE RPD to achieve complex SQL logic.