Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

GROUP BY in logical column

Received Response
52
Views
6
Comments
User_YQZHD
User_YQZHD Rank 2 - Community Beginner
edited Aug 13, 2024 5:55PM in Oracle Analytics Cloud and Server

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

Answers

  • Pedro F
    Pedro F Rank 6 - Analytics Lead

    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
    User_YQZHD Rank 2 - Community Beginner

    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
    Robert Angel Rank 8 - Analytics Strategist

    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
    Sherry George Rank 7 - Analytics Coach

    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
    Gianni Ceresa Mod 
    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
    User_YQZHD Rank 2 - Community Beginner

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