0 Replies Latest reply on May 21, 2018 8:57 AM by deniska

    Complex (or not) formula in the Business Layer of repository


      Im using OBIEE 12c.

      Is it possible to create Calculated Measure which shows the value of another Measure depending on whether or not was chosen another Measure


      Let me explain

      My model federates data from Essbase Cube(Cost, Weight) and Relational Database(Detail data like: Document numbers, Users and their Measures like Count Distinct)

      For Example


      Dim Period





      Dim Product






      -Doc Number(Oracle Rel data)

      -User(Oracle Rel data)

      -Number of Documents(count distinct(Doc Number))

      -Number of Users(count distinct(Users))

      -Cost(Oracle Rel data)*

      -Weight(Oracle Rel data)*


      The values of the last two Measures are equal to values of Cost(Essbase) and Weight(Essbase). They are used when Measure like Doc Number or User was chosen because in that case Cost(essbase) and Weight(essbase) are null.



      Period-Year Cost(essbase)

      2016             1000$

      2017             2000$



      Period-Year Cost(essbase) Document Number

      2016              null                    123                            



      2017              null                     878                             



      Period-Year Cost(essbase) Document Number Cost(Oracle Rel data)

      2016              null                    123                           300$

                                                      343                           300$

                                                      233                           400$

      2017              null                    878                            2000$


      But Two Cost Measures are not convenient for end users

      I want

      1. Add Calculated Measure Cost(calculated) which will show the value of Cost(essbase) when Measures Doc Number(Oracle Rel data) or User(Oracle Rel data) wasnt chosen and Cost(Oracle Rel data) when one of these Measures was chosen



      2. Add Calculated Measure Cost(calculated) which will show the value of Cost(essbase) when it is not null and Cost(Oracle Rel data) if Cost(essbase) is null

      IFNull expression doesnt work properly for me because it always sends query to relational and multidimensional database simultaneously



      3.Make some settings which allow Cost(essbase) to always show aggregated value for known dimensions

      Period-Year Cost(essbase) Document Number

      2016              1000$                    123                            



      2017              2000$                    878