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