GROUP BY in logical column

User_YQZHD

Hi Experts,

I have created a formula in the analysis like below

COUNT(Orders BY Segment, Year)

Can I incorporate the above logic into logical column in bmm layer?

Thanks

Pedro F

Assuming Orders is a measure and you have the proper hierarchies defined for those 2 dimensions, you can create a level based measure that will give you what you want.

https://gerardnico.com/wiki/dat/obiee/obis/measure_level_based

User_YQZHD

Pedro,

Thanks for the reply. In my case, all the columns are coming from one dimension table. May be I have to separate them into different logical fact and dim tables and then create the level based measure.

But can I create one measure at different levels i.e segment and year?

Thanks

Robert Angel

Whilst you can 'pin' a measure to as many dimension hierarchies at whatever level you need I would caution that you may find that the numbers may not be correct if you have a dynamic data population.

i.e. if you form has prompts/ filters you may find that the level based measure does not produce the right figures, but your analysis based count by will.

Others may have found ways around this, but this has been my experience in the past when I was trying to create share of segment, share of market measures which dynamically restated based on the current total data population driven by prompts and filter functionality.

In short, try it, but do test heavily on a number of scenarios before relying on it in a production environment - which should be the caveat with all BI functionality.....

Sherry George

You could use the EVALUATE_ANALYTIC function in RPD. Give it a try.

https://www.rittmanmead.com/blog/2010/10/oracle-bi-ee-11g-calling-database-analytic-functions-evaluate/

Gianni Ceresa

3547198 wrote:Thanks for the reply. In my case, all the columns are coming from one dimension table. May be I have to separate them into different logical fact and dim tables and then create the level based measure.

Remove the "maybe".

The logical model is supposed to be a real model, a start schema. If all the columns come from the same table doesn't matter, that's a detail for the physical layer. In the logical layer they are supposed to be dimensions (and segment and year are generally 2 different dimensions).

In that case, with a proper model, you can then add all the level based measure you want mixing all the required granularity and dimensions.

User_YQZHD

Thanks for the reply Gianni. Will give a shot at it.