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.
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.
