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. 

Creating Tableau Reports on OBIEE RPD

Tableau has become one of the leading BI Reporting Tool in past few years. The key selling points are ease of use, attractive look and feel etc. However OBIEE RPD has the upper hand, when it comes to building the Logical Model.

For the projects where OBIEE is already present, what if the business decides to use the better reporting capabilities of Tableau. That means reworking and recreating the logical layer in Tableau.

What if we can combine both of them, and create Tableau reports on top of OBIEE RPD.

To start with, I have the Sample App RPD, and I have created a small subject area 'FOR_TABLEAU'.

Next is to create a ODBC System DSN of type Oracle BI Server 1.

I name it 'TableauOBIEEPOC'. I check 'Clustered DSN' option. Set the Primary Controller as my machine name (full), and the Port as 9706.

Next I give the OBIEE  Server Login ID and PW.

Then I select the default Subject Area as 'FOR_TABLEAU'. This step is important and without selecting the default Subject Area, Tableau will not be able to interact properly with OBIEE.

Once done it is time to connect from Tableau. I open my Tableau desktop, and go to Data → New Data Source. In the data source select Other Databases (ODBC).

This will bring up a new window, where we will provide the DSN name and OBIEE Login and Password.

Once authenticated we can Sign In.

Once logged in I can see the Facts and Dimensions I had earlier exposed in 'FOR_TABLEAU' subject area in OBIEE RPD.

For testing purpose let me try to create a simple report. I select Time and Base Facts. In the
join conditions I select any 2 columns from Dimension and Fact, otherwise it will through error. However the main join is going to happen in the RPD.

And I proceed to Sheet 1, and I can see all my fact and dimension columns are visible. Next I create a simple report with T05 Per Name Year and 1-Revenue.

To validate the result I create the same analysis in OBIEE also.

And the results match exactly, which shows our Tableau Analysis is working perfectly with OBIEE RPD.

Before I conclude, I would also like to mention that, there are various other ways of integrating OBIEE with Tableau, like Ritman Mead's 'Unify' to pull your OBIEE reporting data directly into your local Tableau environment. 

Oracle Synopsis - Excel based BI operated from Mobile

Oracle Synopsis is the first Mobile Analytics App that lets you quickly open and interact with spreadsheets and business data in a visual and intuitive way while you're on the go. It is a Standalone Mobile Based BI Reporting Tool which takes excel as data source and is available in both Android and IOS. The End Users like Travelling Salesmen, Medical Representatives, PMOs, etc... who have the need to share reports (mainly excel based) 24*7 do not need any technical training to use it or create and share reports.

This app can also be preferred because it is absolutely free.

To understand the working of Oracle Synopsis, I will not take an example from somewhere else, but from my wife who is working as a PMO is an MNC ... 👰.

Let us assume that our PMO i.e. my wife in this case, has gone for a vacation with no access to Laptop or Desktop and all of a sudden receives an urgent phone call from the BU Head asking for the headcount of Mid-level employees on different technologies. The data is to be given in a format in which there is a comparison between “People who are working on a project” and “People who are on bench”. Luckily, she has a recent excel dump containing details of all the employees, like ID, Account, Assignment Status, Department, Technology, Grade etc. But it would be difficult to get the counts from Excel manually.

In this situation, Oracle Synopsis comes in her rescue. She downloads "Oracle Synopsis" app from Goolge Play Store in her phone (assuming it's an Android phone) and installs it.

She opens the Excel file in Synopsis.

The tool, on its own identifies the numbers and aggregates them, coming up with some default visualizations.

Once she taps on the visualization, it takes her to the 2nd level; where she can change the Settings / Aggregations along with other customizations. In this case, she changes the aggregation to Count of Employee Code.

First, she changes the 'Name of the Sheet' by clicking the pencil icon besides "Sheet".

Next she clicks the "Setting" icon on the top which gives her a list of the various columns present in the sheet. For her requirement, she removes a couple of columns and keeps the rest.

Next, she enables the "Edit Column Labels" toggle button to change the Column Lable for 'Code' column to '# of Emp'.

 

She taps on '# of Emp' button which then takes her to a series of detailed Reports by different Dimensions.

She can browse through the charts, can tap on the chart to have a "Close-Up" View.

Once in the "Close-Up" View, she can Edit the chart by clicking the pencil icon. This gives her the option to choose from the different Chart Types, change the "Text" of the visualization, change the "Number" format in the visualization, etc... If she is not sure about what to select, then she can even ask the app to choose it for her by tapping on  the "Choose for Me" button.

She needs to share a report on the 'Billed vs On Bench Resources', so she selects the report '# of Emp' by 'Assignment Status'.

And adds 'Technology' field in the report. If required additional filters can also be added.

She changes the chart type to 'Radar' and this visualization seems to suit her requirements.

Now, she simply shares the details to her boss via Email or WhatsApp by tapping on the "Share" icon on the top-right side of the visualization.

As a result of this, her boss has not only the numbers but also some analysis on top of that, which saves the day, and makes her available even on vacation.

I have tried my level-best to explain the use of "Oracle Synopsis", but if I was unable to make somethings not clear, then I am demonstrating the same thing via the video below.

Hope it was useful and entertaining ... 😊