Question about @Select, @Where

[robpotter]

We have measures defined in Universe as well as variables that are derived those measures defined in WebI. Since these are employed in standard reports, there will be maintaince issues.

However, I would like to migrate the defintions of variables from WebI to Universe. I was thinking of using @Select and/or @Where functions to reuse formulas of other measure objects in order to define the numerators and denominators of "derived" measures (i.e. percents, ratios, etc.) located in the Universe- whereby we would be able to deprecate the corresponding variables within WebI.

What I want to know is where both @Select and @Where functions are necessary within formula of new metrics? I believe that @Select is all that is required, such as employed below:

Measure New = @Select(Measure1) / @Select(Measure2)

Please let me know if this aproach to object reuse is incorrect, and there are better alternatives or caveats.

Thanks.

Robert

 

[skrandel]

I'm confused. What do you mean migrate from WebI to Universe.

@Select(Measure1) simply means "duplicate the SQL in the Select portion of Measure1". You would only use this in the select portion of a new object.

@Where(Measure1) simply means "duplicate the SQL in the Where portion of Measure1". You would only use this in the Where portion of a new object or in a condition object. **NOTE**** Rarely does it make sense to put anything in the Where portion of any object.

What you've suggested may work, but it really depends. If Measure1 is already aggregated, you may not get the result you desire.

Also, division in the universe is dangerous. If you are going to do division or averages or percentages, you have be very careful about how these aggregate at the report level. Generally, these are best left as report variables.




Steve Krandel
Knightsbridge Solutions
formerly BASE Consulting Group

http://www.knightsbridge.com

 

[robpotter]

Thank you Steve,

What we are really interested in achieving is finding alternatives to relying on report varibles for defining "derived" metrics to reduce report maintenance.

Of course, BOBJ does projection from microcube to bloc and the variables (aka derived metrics) must be applied after aggregation of base metrics in bloc.
Are there design methodologies that would allow report variables to reference definitions stored in universe or point to report variables stored in master reports? Thereby, these definition could be maintained centrally but calculated subsequent to projection?

Thanks again.

Robert

 

[skrandel]

Robert, although what you are suggesting is a great idea, there is no facility to have locally calculated variables defined in the universe

Steve Krandel
Knightsbridge Solutions
formerly BASE Consulting Group

http://www.knightsbridge.com