5 Replies Latest reply: Sep 11, 2010 1:28 AM by Dave Rathbun RSS

Aggregate awareness

Varun Sharma
Currently Being Moderated

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 )

  • Re: Aggregate awareness
    Pierpaolo VEZZOSI
    Currently Being Moderated

    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

    • Re: Aggregate awareness
      Varun Sharma
      Currently Being Moderated

      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 .

      • Re: Aggregate awareness
        Pierpaolo VEZZOSI
        Currently Being Moderated

        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

        • Re: Aggregate awareness
          Varun Sharma
          Currently Being Moderated

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

          • Re: Aggregate awareness
            Dave Rathbun
            Currently Being Moderated

            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.

Actions