Level based Measures in OBIEE
A level-based measure is a column whose values are always calculated to a specific level of aggregation. Let us take an example of Products and Revenue table. A level based hierarchy is created for Products as follows:
By default, in the report when we drill down the products hierarchy from Brand level to Product level, the corresponding Revenue drills down to Product level.
However, if we want to view the revenue aggregated to the Brand level then:
● We create another measure called “Brand revenue” which is a duplicate of revenue in RPD.
● Click on its properties, in the level tab specify the logical level as “Product Brand” for Logical Dimension “H2 Product”. Save the RPD.
● When Brand revenue is dragged into our analysis, it shows Revenue for Brand level, no matter how far we drill down from the brand level.
Level Based Measures in Tableau
To get the similar kind of functionality in Tableau,
● we create the hierarchy for products.
● Drag Product hierarchy and Revenue into the view.
● Right click Revenue, click ‘Add table Calculation’.
● Click on ‘Customize’ button in the Table calculation window and write the formula “WINDOW_SUM(sum([Revenue]) )” in ‘Calculated Field’ window . Press Ok
● Select ‘advanced’ option in dropdown menu for ‘compute using’ field in Table calculation window.
● Push ‘Type’ and ‘Product’ to ‘Addressing’ column. Click OK & OK again to return to the view.
● We can now see that the Revenue is aggregated to the Brand level, even when Product Brand is drilled down to Product level.
Difference between OBIEE Level Based Measures and Tableau Table Calculations:
OBIEE will send a query to the database to calculate level based measures. Table calculations, on the other hand, are not executed by the database but rather calculated by Tableau on the
query results that get returned. If table calculation performance is a problem (possibly
because the result set being returned to Tableau is very large) consider pushing some aspects of the calculation back to the data source layer. One way to do this is to leverage an aggregated data extract.