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