Aggregate aware -confused

building semantic layer
1

Can somebody give me a simple explanation of how to use AA?
I know it is to be used with summary and details tables and forcing BO to use one or the other but I really do not understand how to implement it.
Any pointers/links would be most appreciated.

prishma123 (BOB member since 2010-02-22)

2

Did you observe the e-fashion universe?

Arjun (BOB member since 2008-07-28)

3

For me, the efashion Universe is a terrible example of the use of @Aggregate_Aware, as it shows its use with Dimensions and unlinked, isolated tables. I suspect that its use with Dimensions is not recommended (others please confirm or otherwise).

I have only ever used @Aggregate_Aware with Measures, where there are fact tables at different levels of granularity, each being in its own Context. e.g. With a Monthly and Daily Sales table.

anorak :uk: (BOB member since 2002-09-13)

4

I didnt understand the e-fashion example.

prishma123 (BOB member since 2010-02-22)

5

Don’t worry about it…

A simple example of using Aggregate Awareness:

  1. You have a Universe with a daily detailed fact table and a monthly summary, and a Context defined for each.
  2. Each of these tables has a sales column, defined as MONTHLY_SUMMARY.SALES and DAILY_DETAIL.SALES respectively.
  3. Now queries are more efficient if they use the smaller aggregated Monthly table, so you define your ‘Sales’ Object as:
@Aggregate_Aware(Sum(MONTHLY_SUMMARY.SALES),Sum(DAILY_DETAIL.SALES))

By placing them in this order, the Monthly table will be used whenever possible.
4. You set your incompatible objects using Aggregate Navigation. So, for example a ‘Day’ Object would be incompatible with the Monthly table, as it is a monthly aggregate, not held at day level.

Hope that this makes sense.

anorak :uk: (BOB member since 2002-09-13)

6

I got it! Thanks for your simple but effective explanation :smiley:

prishma123 (BOB member since 2010-02-22)

7

That’s what I have been doing too. However, I think the idea in that demo universe of having a stand-alone summary table with no joins can give you a maximum performance boost - essentially a lot of reports can run on a single table. You also do not need a summary context, which could be seen as an upside as well. I wonder what Kimball and other American dimension modeling gurus say on this :wink:

But it does make the universe somewhat messy and terrible to demo :wink: -you end up with @aggregate_aware not just in measures but in lot of dimension objects as well.

Pasi Tervo :finland: (BOB member since 2002-09-03)

8

In setting up my Agg Aware, I have got one table called orders.

This I have joined to two tables like this:-

Orders => Daily orders(contains summary data for orders per day)

Orders => Monthly orders(contains summary data for orders per month)

Orders contains details such as order ID, desc, Price

How would I go about setting AA in here? I am not sure what to next?

prishma123 (BOB member since 2010-02-22)

9

Great example. A slightly different question. Have you used aggregate aware on an object from a dimension table instead of a fact table? I’m hearing there is a usage for it, but I’m not understanding the benefits of doing that. I understand using Aggregate Aware on any object from a fact table, but not sure why I would do it on an object that’s only in the universe/database once and residing on a dimension table. Let me know your thoughts.

rjsmom1 (BOB member since 2011-02-24)

10

That’s the key. If there is more than one source for the dimension value, then it can be aggregate aware. If it only appears once, then as you say there’s no point. The eFashion universe has an example where the dimension value appears more than once. It’s not a great example of an aggregate aware implementation :slight_smile: but it does show that.

Dave Rathbun :us: (BOB member since 2002-06-06)

11

So based on the example I have given, how would AA be set up based on the simple objects I have provided?

prishma123 (BOB member since 2010-02-22)

12

Say you have three tables:
tByYear, tByMonth, tDetails

In the tables, you have the columns:
tByYear.Year
tByYear.OrdAmountYr

tByMonth.YrMonth
tByMonth.OrdAmountMo

tDetails.OrderDate
tDetails.OrderAmount

I have created these Objects :-

tByYear.Year –dim
tByMonth.YrMonth -dim
tDetails.OrderDate –dim

Order Amount –measure

@AggregateAware( Sum(tByYear.OrdAmountYr), Sum(tByMonth.OrdAmountMo), Sum(tDetails.OrderAmount) )

Is the AA object I have created above for order amount correct? Do you only have one measure object defined even though there are three in my tables?

prishma123 (BOB member since 2010-02-22)

13

my only question here as am learning on my own. Looking at the eFashion universe the aggregate aware is not linked to anything. Only the original fact table object is modified using the AA.

Now, I need to understand should I not join it to any table?? And not add it to any existing context or create a new one?

Thanks in advance, but am just trying to get the basic straight in this context.

Thanks.

sulabhagra :uk: (BOB member since 2007-03-05)

14

Hi at all.

Is it correct that AA measure must be set to “delegated to database”. Because it has to re-execute query every time you add/remove dimensions? Is it right ?

n.milella (BOB member since 2011-08-24)

15

That is incorrect.

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

16

Why it is incorrect ?

n.milella (BOB member since 2011-08-24)

17

Because it just is.
Why would you have to use database delegated measure for projection justb because it is an aggregate_aware measure?? Nonsense.

Aggregate_aware is nothing else but the feature to switch to a different table according to the rules set via aggregate navigation. You can even make dimensions aggregate_aware.

When to use database delegated measure:
Whenver you have a ration (e.g. profit margin in %, customer satisfaction in %, etc.).
Note: It would have been better if SAP had provided global Universe variables instead (true 2-phase calculation), these would behave nicely with respect to: no need to refresh, when projecting inside the Webi report, and can be calculated even if dimension values are grouped (via a formula) inside the Webi report.

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

18

OK.
Suppose that you have one AA measure like this:

@Aggregate_Aware(Sum(MONTHLY_SUMMARY.SALES),Sum(DAILY_DETAIL.SALES))

If a create report by DAY it queries daily table;
if a create a report by MONTH it queries monthly table.

But this should be dynamic. Users make analysis both day and month. So they make reports with DAY e MONTH.

Here is my doubt: if they remove MONTH column , BO sum all metrics in memory without re-execute query. It means that you does not benefit by AA.

I hope is clear. :slight_smile:

thank you for your help !

n.milella (BOB member since 2011-08-24)

19

Correct, and since it is a fully additive measure in your scenario, one only has to execute the query at the daily grain (actually BusObjects is figuring this out automagically) and then - inside the report - project the measure (in your scenario via SUM) to the monthly grain, quarterly grain or yearly grain. There is absolutely no need to re-execute the query.

If you want to display the meaure at a daily grain to begin with how in the world would a fact table by grain of Month help you anyway?

So what do you think is faster? :wink: Summing up in memory or re-executing a database query? ,-)

Note:
I would recommend using materialized views with the query rewrite option in Oracle for example, which is totally transparent to any front-end tool such as Universe Designer, etc. No need for aggregate awareness, aggregate navigation, etc. then :slight_smile:

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

20

ok thank you !

n.milella (BOB member since 2011-08-24)