Level based Metrix - Content Level in OBIEE vs Fixed in Tableau

This is a continuation of my earlier post Level based Metrix - Content Level in OBIEE vs Exclude in Tableau , and I am going to focus on Fixed in Tableau.

To start with, I am creating a new calculation 'Company wise Revenue', which is the Sum of Revenue at the Company level of the Office hierarchy in the RPD BMM.

I add this column in an analysis in OBIEE. 

I am keeping all the 3 columns 

1. Revenue - Details level

2. Revenue (Sum for All Office) - Total level

3. Company wise Revenue - Company level

(For the hierachy refer to Level based Metrix - Content Level in OBIEE vs Exclude in Tableau )

We can compare these 3 columns side by side and can onserve how the 'Company wise Revenue' is summing up at Company level.

Next I calculate the same in Tableau using 'Fixed' expression, at Company level of the Office hierarchy in Tableau (for the hierachy refer to Level based Metrix - Content Level in OBIEE vs Exclude in Tableau ). 

And I test that column along with 'Revenue' and 'Revenue Exclude'.

The output is same as we have seen in OBIEE.

Can we open file created in Tableau Desktop in Tableau Public

I am relatively new to Tableau. I have created some TWBX file using tableau, and now I have a question, what happens once my license expires? by any means can I still open/work on those files?

And that brings me to this post.

I am using one of the TWBX files which I have created earlier, and I can open and use it using Tableau Desktop.

However, if I try to open the same file in Tableau Public, it is not allowing me.

Now I select 'Save to Tableau Public' from Server → Tableau Public.

And log into to my Tableau Public profile.

And publish this workbook.

Next I go to my Tableau Public profile and download the uploaded file in TWBX format.

Now I open Tableau Public client, I have 2 options to open the file.

1. Open - I can open the TWBX file I have downloaded.
2. Open from Tableau Public - I can directly open the file from my profile.

The file works fine in Tableau Public client.

However, if I do any change and want to save it, the only option is in Tableau Public.

Level based Metrix - Content Level in OBIEE vs Exclude in Tableau

In this post, my objective is to showcase Level based Metrix in using Content Level OBIEE vs Exclude in Tableau. However, I am not going to get into which one is better or worse. This more of exploring options in OBIEE and Tableau to reach to get the same result.

Level Based Matrix - We create a hierarchy of dimensions in reporting solutions and built Calculations / Metrics associated with a specific level. Examples of such calculations are Monthly Sales, Quarterly expense, Country wise revenue etc.

I am using 1 fact and 1 dimension. First, let me do it in OBIEE. I have created the joins in the physical layer.

I have brought those 2 tables in the BMM.

And created logical dimension/hierarchy for Dim - Office.

In the Fact - Revenue, I have kept the content level for Dim - Office at the details level like usual. 

I have 2 Revenue columns, 'Revenue', and 'Revenue (Sum for All Offices)'. Revenue is a straightforward logical column with aggregation as SUM, and Level not defined separately.

However, Revenue (Sum for All Offices) has level defined at the total level of Office. 

I test both the columns and the result is as desired.

Now it is time to replicate the same in Tableau. I have added those 2 tables and joined them.

Next, I have created Office hierarchy, and duplicated 'Revenue' as 'Revenue Exclude'. 

And I have used 'Exclude' expression, and have excluded all the levels of Office hierarchy.  

I test it 'Revenue', and 'Revenue Exclude' in a report. The output is same as we have seen in OBIEE.

There are 2 more expressions 'Include' and 'Fixed' in Tableau, which are also used for the same purpose. I will try to explore them on the next blog.

Using Driving table in OBIEE Complex Join

In OBIEE we can join tables present in different Schema and Connection Pool. However there is a possibility of performance issue in such case. Using Driving Table can be solution for that. 

In this blog I am going to demonstrate how we can use Driving Table, and what is the impact of it.

To start with I am using the fact SAMP_REVENUE_F from BISAMPLE schema. 

I am going to join it with SAMP_OFFICE_D in the same schema

And EMP_D which is present in a different schema. 

The concept of Driving table is useful specifically if we are joining 2 tables which are in different schema and one of them has less number of rows.

I am using 2 dimensions one present in the same schema and another in different schema to showcase the difference in join.

Next I have imported all 3 into the physical layer and joined them.

Next I create a BMM and put them into it and create logical fact and dimension tables. 

The Logical / Complex join between Fact - Revenue and Dim - Office is like a usual Logical / Complex join in BMM.

To showcase the difference in query with / without Driving table first I am doing the Complex join between Fact - Revenue and Dim - Employee is like a usual Logical / Complex join in BMM. 

Let me create a simple report to test it out.

In the session log we can see 2 different SQL queries have been fired for 2 different schema which have no connection in between.

Now let me define the Driving Table in the complex join. As in this case EMP_D has less number of rows, I am using it as the Driving Table.

And in the back end SQL query we can find a additional in statement using parameters.

And this makes the difference in performance.