Aggregation is the process of summarizing the info based on level of data you want to look at your data.
Aggregate Awareness is a function in universe designer that makes use of aggregate tables.If you have summary tables and detailed tables and if you want the BO to pick up right table based on what kind of dimension objects you picked at the report level.
Aggregate Awareness improves the performance of a query by reducing the query fetch time.
It is used to specify the order of the aggregate tables from most summarized to most detailed.It tells the universe to by-pass the detail tables and pull aggregates from the summary tables.
Aggregate tables are also called as materialized views or summary tables in DB.
Aggregate Awareness is a function in universe designer that makes use of aggregate tables.If you have summary tables and detailed tables and if you want the BO to pick up right table based on what kind of dimension objects you picked at the report level.
Aggregate Awareness improves the performance of a query by reducing the query fetch time.
It is used to specify the order of the aggregate tables from most summarized to most detailed.It tells the universe to by-pass the detail tables and pull aggregates from the summary tables.
Aggregate tables are also called as materialized views or summary tables in DB.
Consider a customer table with name test_aggregate.dbo.customer CUST ID CUST Name
100 Ashish
101 X
102 Y
Consider a detail fact table as Detail Fact
CID SalesDate SalesMonth SalesQuarter Sales
100 - 1 Q1 100
100 1 Q1 150
100 2 Q1 -
100 2 Q1 -
100 - - -
- - - -
- - - -
- - - -
100 - - -
Assume there are total 24 Rows.
If I create a report using CID column on sales date it will give 24 rows of data.
But if my requirement is to create sales on quarterly or just monthly then it should traverse through all the 24 rows of data.
Example monthly sales will give you 12 rows of data and quarterly sales will give you 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 have to create summary tables.
Create a month summary table as Dbo.mothly_fact
CID SalesMonth SalesQuarter Sales
100 1 Q1 250
100 2 Q1 -
- - - -
- - - -
100 12 - -
Now we will get total 12 Rows.
Create a quarterly summary table as Dbo.quarterly_fact
CID SalesQuarter Sales
100 Q1 1350
100 Q2 2150
100 Q3 4000
100 Q4 6000
Now we will get total 4 Rows.
Now connect these 3 fact tables to the dimension table customer.
And Create measure objects as:
SalesMeasure=@aggregate_aware(sum(Dbo.quarterly_fact.sales),sum(Dbo.mothly_fact.sales),sum(DetailFact.sales))
And Create Dimension objects as:
SalesMth=@aggregate_aware(Dbo.mothly_fact.SalesMonth,DetailFact.SalesMonth)
Note : Month is present in only 2 tables hence Monthly and Daily fact tables are used.
SalesQtr=@aggregate_aware(Dbo.quarterly_fact.SalesQuarter)
SalesDt=@aggregte_aware(DetailFact.SalesDate)
Aggregate Navigation
Now set incompatibilities using aggregate navigation. We have to make objects incompatible to tables.
When Compatible : When an object is at the same or higher level of aggregation as the table then it is compatible with the table.
When Incompatible : When an object is at the lower level of aggregation than the table or it is not at all related to the table then it is incompatible.
Here SalesDt object is incompatible to tables Dbo.quarterly_fact and Dbo.mothly_fact.
Similarly SalesMth object is incompatible to table c.
So choose table Dbo.quarterly_fact and click the objects SalesDt and SalesMth.
Hence It will not scan the particular dimensions which increases the performance.
100 Ashish
101 X
102 Y
Consider a detail fact table as Detail Fact
CID SalesDate SalesMonth SalesQuarter Sales
100 - 1 Q1 100
100 1 Q1 150
100 2 Q1 -
100 2 Q1 -
100 - - -
- - - -
- - - -
- - - -
100 - - -
Assume there are total 24 Rows.
If I create a report using CID column on sales date it will give 24 rows of data.
But if my requirement is to create sales on quarterly or just monthly then it should traverse through all the 24 rows of data.
Example monthly sales will give you 12 rows of data and quarterly sales will give you 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 have to create summary tables.
Create a month summary table as Dbo.mothly_fact
CID SalesMonth SalesQuarter Sales
100 1 Q1 250
100 2 Q1 -
- - - -
- - - -
100 12 - -
Now we will get total 12 Rows.
Create a quarterly summary table as Dbo.quarterly_fact
CID SalesQuarter Sales
100 Q1 1350
100 Q2 2150
100 Q3 4000
100 Q4 6000
Now we will get total 4 Rows.
Now connect these 3 fact tables to the dimension table customer.
And Create measure objects as:
SalesMeasure=@aggregate_aware(sum(Dbo.quarterly_fact.sales),sum(Dbo.mothly_fact.sales),sum(DetailFact.sales))
And Create Dimension objects as:
SalesMth=@aggregate_aware(Dbo.mothly_fact.SalesMonth,DetailFact.SalesMonth)
Note : Month is present in only 2 tables hence Monthly and Daily fact tables are used.
SalesQtr=@aggregate_aware(Dbo.quarterly_fact.SalesQuarter)
SalesDt=@aggregte_aware(DetailFact.SalesDate)
Aggregate Navigation
Now set incompatibilities using aggregate navigation. We have to make objects incompatible to tables.
When Compatible : When an object is at the same or higher level of aggregation as the table then it is compatible with the table.
When Incompatible : When an object is at the lower level of aggregation than the table or it is not at all related to the table then it is incompatible.
Here SalesDt object is incompatible to tables Dbo.quarterly_fact and Dbo.mothly_fact.
Similarly SalesMth object is incompatible to table c.
So choose table Dbo.quarterly_fact and click the objects SalesDt and SalesMth.
Hence It will not scan the particular dimensions which increases the performance.
Some FAQs:
Is aggregate navigation required, If not used then?
The measure objects whatever defined will always point to most summarized table (Dbo.quarterly_fact) even if you use SalesDt object in your query which is wrong.
Is it required to link aggregate tables to dimension tables?Yes, it depends. Otherwise it would fetch Cartesian product for the tables which are not linked.
Why the aggregate tables in efashion universe are not linked then?
Because the table itself has all the dimension objects. In real time aggregate table holds measures and foreign key references
Is aggregate navigation required, If not used then?
The measure objects whatever defined will always point to most summarized table (Dbo.quarterly_fact) even if you use SalesDt object in your query which is wrong.
Is it required to link aggregate tables to dimension tables?Yes, it depends. Otherwise it would fetch Cartesian product for the tables which are not linked.
Why the aggregate tables in efashion universe are not linked then?
Because the table itself has all the dimension objects. In real time aggregate table holds measures and foreign key references