Re: [OBIEE EMG] Limitations on aggregations - alternatives?

[Christian Berg]

Good morning Michael,

I don't have one of those shiny toys to play around, but the problems you're talking about are definitely still there up to 6.5.

ad 1.) COUNT DISTINCT is the only aggregation type supporting different aggregation rules by LTS but also lose the possibility as soon as you switch to an aggregation based on dimensions (it's the one or the other).
For all other aggregation types - if you want to keep one single logical fact column and not multiply them by level and aggrregation logic - you will need to make your lowest-rain source match the aggregation rule for the higher levels.
E.g. transform the lowest-level COUNT into a SUM(1) or the likes. Granted, that can mean some nasty calculations when the count isn't actually happening on the grain itself or ETLing some new fake measure.

ad 2.) As stated above, out-of-the-box this isn't possible (correct me if I'd missed something guys since that would actually be quite sweet functionality). So far it's one or the other for COUNT DISTINCT and only "based on dim" for all others.

You're definitely hitting cube territory here, so one could question Oracles willingness and incentive to invest into further functional extensions there compared to just telling you: use an Essbase cube and define different calculations and aggregations there.

Cheers,
Christian

On Sun, Nov 4, 2012 at 2:46 PM, Michael Wilcke <michael...@gmail.com> wrote:

Hi there,

because of Exalytics, building aggregates is getting more attention. However, there are some limitations in the current version (we use 11.1.1.6.2 BP1). 

1. Consider a logical fact column with "count" as the aggregation rule. If you build aggregates for this measure at higher levels you will have sum up these aggregated counts. Hence, you need to change the aggregation rule to "sum" for those LTS, that contain aggregated counts.

With "count distinct" there is such an option: it is called "LTS override". But I'm missing this option for "count".

Did someone else ran into this so far? Are there any workarounds?

2. In our case, that measure is semi-additive with an aggregation rule "based on dimensions". The rule is "count" for "other Dimensions" and "last" for the time dimension. This rule needs to be changed to "sum" for "other dimensions" and "last" for the time dimension, whenever aggregates are involved.

I see no way to configure this in admin tool, even not for "count distinct".

Again: Did someone else ran into this so far? Are there any workarounds?

Thanks - Mike

[Stewart Bryson]

I agree with Christian here. I use a measure with a value of 1 instead of doing a COUNT, and then set the aggregation rule to SUM. Does this work for your scenario?

--
You received this message because you are subscribed to the Google
Groups "OBIEE Enterprise Methodology Group" group.
To post to this group, send email to
obiee-enterpri...@googlegroups.com
To unsubscribe from this group, send email to
obiee-enterprise-met...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/obiee-enterprise-methodology?hl=en
 
All content to the OBIEE EMG lies under the Creative Commons Attribution 3.0 Unported License (http://creativecommons.org/licenses/by/3.0/). Any content sourced must be attributed back to the OBIEE EMG with a link to the Google Group (http://groups.google.com/group/obiee-enterprise-methodology).

[je...@brewpalace.com]

OBI will automatically convert Count() into a Sum() for any aggregates; its been able to do that forever.  The other problems are as others have responded already.

Jeff M.

---

From: Michael Wilcke <michael...@gmail.com>
To: obiee-enterpri...@googlegroups.com
Sent: Sunday, November 4, 2012 5:46 AM
Subject: [OBIEE EMG] Limitations on aggregations - alternatives?