Aggregated and UnAggregated Objects

building semantic layer
1

Any one have any ideas on the following? Thanks up front for any input.

  1. I have an object called Amount. It is most definitely a measure and its definition is Sum(Amount) with a projection of Sum.

  2. I need to be able to place restrictions on the Unaggregated Amount so that it appears in the WHERE clause and not the HAVING clause. So I have another object called Amount (UnAggregated) and its definition is simply AMOUNT.

QUESTIONS.

A) Is this second object a measure or a dimension? If a measure I assume I set the aggregation to SUM.

B) Are there any good tips around about how one should name objects of this nature, i.e. where you need both the aggregated object and the unaggregated object.

c) How can I best organise my objects when I have a load of aggregated ones and unaggregated ones so that my universe doesn’t look cluttered.

d) Should one bend over backwards to use @Select when building objects instead of referring to the base table? I’m trying my best to minimise maintenance by pointing to objects that I’ve created but I don’t know if it’s overkill.
:?

Grantie :uk: (BOB member since 2004-07-01)

2

a) As for naming maybe:
“Summed Amount” (measure) versus “Detail Amount” (dimension)?

d) Whatever makes the most sense, do not overuse @Select, but use it where it really cuts down maintenance work.

Andreas :de: (BOB member since 2002-06-20)

3

Here’s how I’ve dealt with this issue. I leave the sum wrapper on the measure object to allow aggregation. I then create one or more predefined conditions (depending on users’ needs) that point directly to the database table, i.e., no sum function. This prevents the having clause and user doesn’t have to build their own condition (remembering to use the detail object).

Regarding using vs. not using @select, I think you should use it when it makes sense. This is a good example of when it doesn’t.

Melody Shackelford :us: (BOB member since 2002-08-20)

4

I’ve always found it useful to band the unaggregated field using a case statement.

e.g.account balance 0-50 .etc

JohnH :uk: (BOB member since 2003-08-19)