Measure from Dimension

User_YQZHD

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

Gianni Ceresa

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

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

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.