Aggregate awareness

[Former Member]

i want to understand aggregate awareness with practical approach .

Case 1: i have a sales table "Sales_daily " loaded daily at database level . Now client requirement is to see the yearly sales , quarterly sales and monthly sales . How will i proceed then . I have a time dimension hierarchy as Year , Quarter,Month,Daily.

There's no aggregated yearly , monthly and quarterly sales table available at database level.

but the logic to calculate are provide as

monthly sales = sum of all sales within a particular month

quarterly sales =sum of all sales within a particular quarter

yearly sales =sum of all sales within a particular year.

How can i use the @aggregate_awareness function at designer level using the above aggregation logic given.

Case2 . If i have yearly , quarterly , monthly aggregated sales tables available a databse level , then how will i use @aggregate_awareness function in universe &How iam i going to link Sales_daily ,Sales_monthly, Sales_quarterly, Sales_yearly tables in universe.

If say i can use @aggregate_awareness function in both cases which is efficient way (Query performance wise )

[PPaolo · Accepted solution]

Hello,

the Aggregate Awareness feature allows you to choose one particular table within the ones which contain the data you need.

In your case 1, as you have one only table, you don't need Aggregate Awareness.

If in your table you have 3 columns for the time dimension (year, month, day) you can build an object on each of them.

When selecting those objects in the query panel along with the sales measure the SQL will contain the correct group by (year, month, day).

In case 2 you can define the sales measure and each time dimension with the Aggregate Aware function. In the function you will put all possible tables where the sales and time values be found (from highest aggregate to lowest -year to day-).

You then define the incompatibilities (e.g. the Day object is not compatible with the table aggregated by Year - meaning that in the Year table you won't find information at day level).

When creating the query in the query panel, the algorithm will find out based on the incompatibility list and on the Aggregate Awareness definition, which is the highest possible level of aggregation for the query.

Performance: in case 1 you don't need aggregate awareness so there is no comparison between the two scenarios.

In general Aggregate Awareness is useful if the database vendor suggests to have aggregate tables in the database to speed up queries. We heard some vendors saying that in their architecture they prefer to build indexes (aggregate indexes) instead of aggregate tables.

In this situation, the database will check the query, which is always requesting the lowest level, and see if any index exist where the aggregation calculation is already done. This is completely transparent to the SAP BusinessObjects universe.

There is a very good example on how to setup Aggregate Awareness in the Designer guide. In the XI3.1 release check the Designer guide, chapter 9, on the section 'Using Aggregate Tables'.

Hope that it helps

Regards

PPaolo

[Former Member · Accepted solution]

Hi PPaolo,

Thanks for the reply. Let me just summarize what you wrote and let me know if i got it correct.

1)To use @aggregate awareness , i must have single sales table in database having sales ,year, month ,quarter as mandatory columns and sales be aggregated at yearly, monthly etc

and if i only have sales table loaded daily at database level , i won't be able to use aggregate awareness. iam correct

2)in case 2nd where i have 4 tables at db level i.e. Sales_daily ,Sales_monthly, Sales_quarterly, Sales_yearly and all tables have sales column

then at universe level to use @aggregate awareness i will create 3 new objects with below definitions

agg_sales_yr = @Aggregate_Awareness(sum(Sales_yearly.sales),sum(Sales_quarterly.sales),sum(Sales_monthly.sales),sum(Sales_daily.sales))

agg_sales_qtr = @Aggregate_Awareness(sum(Sales_quarterly.sales),sum(Sales_monthly.sales),sum(Sales_daily.sales))

agg_sales_mth = @Aggregate_Awareness(sum(Sales_monthly.sales),sum(Sales_daily.sales))

and ofcourse i will consider the incompatibility between all objects and Sales_monthly, Sales_quarterly, Sales_yearly. Correct me if iam wrong .

[PPaolo · Accepted solution]

Hello,

not exactly. One of the benefits of the semantic layer is that you don't have to define one measure per aggregation level.

You just define the measure once (Sales), then based on the query you execute (e.g. Sales by year, sales by month, sales by day) the semantic layer will figure out how to calculate what you need. You don't have to define a specific Sales object by level (YR_Sales, MTH_Sales, DAY_Sales).

What I am saying is that:

+ if you have a single table in your database then you don't need to use @Aggregate_Awareness.

You define your measure on that single table : Sales=Sum(sales_column), then you define your dimensions with the data you have available (e.g. if you have a column with Year then your year object will be Year=Year_column, if you have a date column then Year=A_function_which_manipulates_dates_in_your_DB(date_column,'YYYY') , you'll do the same with Month and Day.

+ if you have multiple tables with various levels of aggregations in your database then you can use the @aggregat_awareness.

You define your measure and your dimensions once only using the @Aggregate_Awareness function.

e.g. Sales=@Aggregate_Awareness(agg_year_table.sales,agg_month_table.sales,no_agg_table.sales)

e.g. Day=no_agg_table.day

e.g. Month=@Aggregate_Awareness(agg_month_table.month,no_agg_table.month)

Then you define the incompatibility map (e.g. when you use the object DAY you cannot use the Year or Month aggregation table).

Now, in both cases (single or multiple tables), you have defined only a single SALES, YEAR, MONTH, DAY objects, you don't need to define SALES_YR, SALES_MTH, SALES_DAY.

When you run the query, depending on what you have in your query panel the Semantic Layer will either calculate the correct value (case 1) or find out the best table for the value(case 2) based on the dimensions you have chosen.

If you look at the sample universe EFASHION which gets installed with Universe Designer, you will see how Aggregate_awareness is set up and used. Try to run some queries with different time levels and see what the SQL looks like.

Hope that it helps

PPaolo

[Former Member · Accepted solution]

Thanks PPaolo for your help. i will try to analyze the sample universe.. Thank you once again..

[Former Member · Accepted solution]

eFashion is not the best universe to learn to use the aggregate aware feature, as the implementation found there is not typical. A better example is the Motors universe used for the Designer training course.

In that database, there is a sales table that contains invoices for cars sold. This is at the daily level.

There is also a summary table called QUARTER_FIGURES which is built off of the daily table, but summed up to the quarter / model / showroom level.

Finally, there is an additional summary table called ANNUAL_FIGURES which is built off of the daily table, summed up to the year / maker / showroom level.

In this structure, I have one object called "Total Sales" that looks like this:

@Aggregate_Aware(sum(ANNUAL_FIGURES.SALES)

, sum(QUARTER_FIGURES.SALES)

, sum(SALES.SALE_TOTAL))

That's not exactly correct, but you see the idea. If I can, I want to use the Annual Figures table because it has the fewest rows. If, however, I select the Quarter as part of my query results, I can't use the annual table because quarter doesn't exist. As a result, I have to switch to (navigate to) the quarter figures table instead.

The magic that drives all of this involves contexts as well as incompatible objects, defined by the Tools - Aggregate Navigation menu option in Designer. It's extremely powerful, but can be complex to set up. And as I said, eFashion is not really (in my opinion) a good example to learn from.

[Former Member · Accepted solution]

I Implemented aggregate Tables, to make use of aggregate tables in a database. These are tables that contain pre-calculated data. so that it will automatically generate SQL Ex: you do not need to write like GROUP BY Logic, with this aggregate tables it will create a1,a2,a3 by writing simple logics itself with appending @aggregate function. In another way, it appends SUM of function,