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?
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.wil...@gmail.com>wrote:
> 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?
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?
On Nov 5, 2012, at 3:24 AM, Christian Berg <christian.b.b...@gmail.com> wrote:
> 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.wil...@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
> -- > 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-enterprise-methodology@googlegroups.com
> To unsubscribe from this group, send email to
> obiee-enterprise-methodology+unsubscribe@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/obiee-enterprise-methodology?hl=en
> 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?
> On Nov 5, 2012, at 3:24 AM, Christian Berg <christia...@gmail.com<javascript:>> > wrote:
> 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<javascript:> > > 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
> -- > 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 <javascript:> > To unsubscribe from this group, send email to > obiee-enterprise-methodology+unsubscribe@googlegroups.com <javascript:> > For more options, visit this group at > http://groups.google.com/group/obiee-enterprise-methodology?hl=en
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.wil...@gmail.com> To: obiee-enterprise-methodology@googlegroups.com Sent: Sunday, November 4, 2012 5:46 AM Subject: [OBIEE EMG] Limitations on aggregations - alternatives?
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 -- 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-enterprise-methodology@googlegroups.com To unsubscribe from this group, send email to obiee-enterprise-methodology+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/obiee-enterprise-methodology?hl=en
