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

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

    deniska

      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

      -Year

      -Quart

        -Month

       

      Dim Product

      -Categoty

      -Product

      Measures

      -Cost(essbase)

      -Weight(essbase)

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

       

      Example

      Period-Year Cost(essbase)

      2016             1000$

      2017             2000$

       

       

      Period-Year Cost(essbase) Document Number

      2016              null                    123                            

                                                      343                            

                                                       233                          

      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

       

      or

      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

       

      or

      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                            

                                                          343                            

                                                          233                          

      2017              2000$                    878