Monday, 22 October 2012

Aggregate Awareness in Universe


Lets see what is this aggregate awareness functionality and how it is useful in universe.
Lets say I have two tables customer and daily fact  with below data:

















If I create the report of customers daily sales, it will join customer and daily fact table using cid column and will give me 24 rows of data.
But if my requirement is to create only quarterly sales or just monthly sales than the data will still traverse through 24 rows of daily fact table.
For eg.  Monthly sales will give me 12 rows of data while quarterly will be 4 rows of data. But this is still getting data from detail fact which has 24 rows, which if hold million facts can slow down the performance.
So we create materialised views or summary tables in our database for better performance.
And I created monthly and quarterly fact in my sample database. Below are the aggregated tables that I can use in my universe.









Now, the question is, we have aggregate tables but how to use the same in my universe based on user queries dynamically.
Aggregate awareness functionality will help me in achieving the same.
It is a two step process:
1    Use aggregate aware function in objects that needs to be made aggregate aware.
      Setting incompatibilities of objects with tables in aggregate navigation section.


Below is the structure of my universe now:
















Now, I have made time dimensions i.e. Sales Dt, Sales Month and Sales Qtr and measue Sales as aggregate aware.
Aggregate function has syntax like:

@Aggregate_Aware(sum(aggregate table1),...,sum(aggregate tableN)) Defines a measure object using precalculated aggregate tables.

 Where table1 is highly aggregated(in our case quarterly fact),........and tableN is the least aggregate or    detailed one(in our case daily fact)






If you observe the definition in the select statement of Sales measure here, it is from highly aggregated table to detail one , same is done for the dimension table as well.









Now month is present in only two tables, hence only monthly and daily fact is used.









Sales Qtr object, quarter is present in all three fact tables; hence all the three are used.


We have used aggregate function in these tables, so we hope that system automatically will decide which table to refer dynamically, when a user selects or tries to run a query.
That means if user wants daily data than daily fact should be used, if monthly than monthly fact or if quarterly sales than quarterly fact.

Now, we have to set the incompatibilities.
This is done from Aggregate Navigation section under tools, in menu bar.
We have to make objects incompatible to tables.
For eg. Sales dt is incompatible to quarter and months table, similarly Sales month object is incompatible to quarter table.
You have to click the table and check the box in front of object to make it incompatible with the selected table.






















Lets see how the queries behave, when you select different set of objects:
Scenario 1:
I selected customer name and sales, and if you see the below query , it is behaving same as the one i expect it, as it is taking data from quarterly fact table.






















Scenario 2:  I added sales month object to it, and will remove quarter object.(it should only now point to monthly facts)















And if you observe, it is now using monthly facts only.

Scenario3:  Using sales dt object.
















This also worked correctly.
Note: if aggregate navigation have not been used than the measure sales will always point to quarterly fact, even if you use sales dt object in your query, which is wrong behavior.
Also it is required to link aggregate fact to dimension tables, else it would fetch Cartesian product for table which are not joined. In efashion universe the aggregate table are not joined to other because the table itself has all the dimension objects, which is not always the case. In real time aggregate fact will hold aggregate measures and foreign keys of dimension table only, that is why it has to be joined to dimension tables.

Hope this article helps in better understanding of this feature.....
Forgive me for the typo's........












40 comments:

  1. @Gaurav your explanation with proper screenshot and examples are great help for us... its always gives us a clear understanding of the topics... thanks for the great work and keep posting. Great Work highly appreciated.

    ReplyDelete
  2. good explanation....thank u very much dude

    ReplyDelete
  3. great great and great explanation...i found this today only after searching 3 days.

    thanks a lot.

    ReplyDelete
  4. nice explanation keep it up buddy....

    ReplyDelete
  5. Question: You have defined the object Defination for Sales month, sales Qtr but you didn't define object defination for Sales Dt.

    Please let me know the object defination for the Sales Dt.

    Thanks in advance

    ReplyDelete
    Replies
    1. Sales Dt is a date object only. It is not made aggregate aware, reason its at the lowest level. Did I answer your question !!

      Delete
  6. nice explanation!!

    ReplyDelete
  7. very nice explnation

    ReplyDelete
  8. very nice explanation, keep it up!

    ReplyDelete
  9. Very Nice explanation.

    Why dont you start posts on Information Design Tool.

    ReplyDelete
  10. Very Nice and Useful info.

    Could be the first one explains this concepts in more elaborated manner.

    Thanks to you.

    It would be great if you can start posting articles on Information Design Tool

    Thanks,
    Kanana

    ReplyDelete
  11. Awesome!!
    It would be great if you start posting more blogs on BO.
    Great understanding and clears minute doubts.

    ReplyDelete
  12. Thanks you very much for ur explanation....

    and i have a small doubt...

    as u said quarter fact will be in 3 tables...

    now i am retrieving quarter data ..in this case quarter data is retrieved from which table..?( from daily aggregate table,from monthly aggregated,or from quarterly aggregated )

    Thanks in advance..

    ReplyDelete
    Replies
    1. Hi,

      It depends on with which combinations you want to retrieve quarter data.
      If it is just quarter dimension than the results will be taken from the table having highest aggregation of this data.
      And the highest aggregation in my example is the quarterly fact table. So data will be retrieve from quarterly fact table.

      Thanks
      Gaurav

      Delete
    2. Hi Gaurav,

      Very NIce explanation.

      I have a doubt, If I create 3 separate measures(Daily Sales, Monthly Sales and Qtr Sales) from their respective tables, then there is no need of Aggregate function..? Am I right..

      Thanks,

      Delete
    3. Hi Venkat,

      If you create three different measures than definitely no need for creating aggregate awareness. But this will not benefit the end user and they always have to select the correct measure and should be aware of the underline table structure.

      Thanks
      Gaurav

      Delete
    4. So,the Aggregate Awareness is only required when the reports are developed by business users( Adhoc - Reports)..So that time obviously we have to give one measure name instead of 3 measures, otherwise the business user will get confused.

      If the requirement is Canned Reports , either it can be 1 or 3...

      So it is( Aggregate Awareness not Aggregate tables) not going to improve any performance, by using this.

      Delete
    5. Aggregate awareness as the name suggest "awareness" will objects aware of the aggregate tables in the database.
      So if you have aggregate tables in your DB, you will always get the summarised data as compared to detailed one. And you make the universe object aware to use those aggregate table at run time, if only summary info is required.

      Delete
  13. great work!! keep goin :)

    ReplyDelete
  14. @Gaurav,

    Kudos for such a neat and clean explanation.
    Well just a clarification the syntax of @Aggregate_Aware (sum(...),Sum(...)). For Daily sales the screen shot has it right but coming to Montly and Quaterly the syntax does not have Sum. Is it a typo error or that is how it should be written.

    ReplyDelete
    Replies
    1. Oh yes...thats a typo....

      Thanks
      Gaurav

      Delete
  15. Hey Gaurav,

    Its really nice explanation and thanks for your valuable time. Is it possible to add few more dimension tables and see how aggregate awareness functions works with the screen shots..

    Thanks

    ReplyDelete
  16. Hi Gaurav,

    When two Many to one is converging on single look up, does CHASM TRAP is not existing here when I pull Sales from two fact tables??

    ReplyDelete
  17. Good explanation, screen shorts helped a lot. :-)

    ReplyDelete
  18. Really good one..:)

    ReplyDelete
  19. Hi Gaurav,
    if we already have aggregated table in the database,
    instead of using aggregate awareness why can't we drag it directly.

    ReplyDelete
    Replies
    1. You will be able to use aggregated table only for the dimensions for which the data is aggregated for other dimensions you need to use the fact data with that level of detail.
      For eg. if you have dimensions year,quarter and month and aggregate fact table at year level. You can use year dimension and fact from aggregate fact, but how will you derive quarter and month data from this fact, for that you need to look into lesser aggregate table.So either you create multiple measures like yearly_sales,quarterly_sales, monthly sales and use with dimension but this does not give you that dynamism, which can be done just by using sales object and let BO take care from which table it has to be derived.(based on aggregate awareness).

      Hope I answered your query.

      Thanks
      Gaurav

      Delete
    2. all credit to Gaurav...i now understand the aggr. awareness concept.

      Thanks,
      Ashok

      Delete
  20. very good explanation

    ReplyDelete
  21. Last point is very important, as in real time scenarios we have to always connect the aggregated facts to dimensions and need to take care it with proper contexts.

    ReplyDelete
  22. Hi Gaurav,
    is it possible use the aggregate aware in conjunction
    with the the drill down?
    So you drill for example Year->Month
    and automatically BO jump on Monthly aggregate table.

    Thanks
    Mauro

    ReplyDelete
  23. Hii Gaurav ,

    Nice Explanation with scrren shots...

    ReplyDelete
  24. Very Useful Information Thank u so much Gaurav..

    ReplyDelete
  25. Thank you so much Gaurav, your article really helped me!

    ReplyDelete