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:
2 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........
