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

Measure from Dimension

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

Hi Experts,

I have a scenario where a measure called 'Score' is stored in the dimension table and I need to display Grand Total which would be an 'Average' aggregation.

To model this in rpd, I have tried below options and I don't get any values. The dimension table is not being considered in the physical sql query when i view the log.

1) Use Dimensional table as additional logical table source for Fact logical table

I have changed the logical table source content level settings to 'Total' level for which dimensions are involved and also the metric level 'Levels' have been defined at the 'Total' level.

2) Created a new logical fact table with dimension table as the source

Let me know what would be the best practice to model this type of scenario.

Thanks

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    Hi,

    This is generally known as degenerated dimension as it's mainly a fact table containing attributes than the opposite.

    But it end up being the same thing in the end.

    It must be modelled using the object twice to have it as dimension and fact as well.

    Not sure why you set the content levels to Total and for what, so you maybe want to look twice into that.

  • User_YQZHD
    User_YQZHD Rank 2 - Community Beginner

    True Gianni. Since this was similar to Degenerate dimension concept, I have tried the 2nd option where new logical fact table was created with dimension as source. But that approach also ended with no results as that dimension table is being excluded from the physical sql.

  • Gianni Ceresa
    Gianni Ceresa Mod

    It's difficult to tell you something precise as the concept works, the theory of dimensional modelling too.

    So ... try to keep it simple: practice with just a mini-star being that and find out how to make it works, then you can integrate it with your existing model.