Besides choosing an aggregate table with an aggregate awareness feature we can do some other useful stuff. In this example an aggregate awareness is used to choose whether we want to include an outer join in a query.
Sometimes we have a requirement to show all dimension values even these without any measure values. Let’s take our eFashion data as an example. Imagine the scenario where some users want to display Stores and their Revenue but also show those Stores without Revenue. To do so we would introduce an outer join. We know that in many cases an outer join causes performance issues and that’s why we prefer to refrain from using that solution. With an aggregate awareness we have an option to include and exclude an outer join in a query without modifying a join definition each time.
To build this scenario l duplicated Shop_facts table in eFashion db and removed half of records from that table. As the result some stores will end up without any Revenue and when we pull Shop Name and Revenue objects into the report these stores will not show up. It’s because an inner join links tables where Shop Name and Revenue info reside. Now let’s create an alias for Shop_facts table and link it using an outer join. We will have a structure like this:
We need to define a context for each join and create a dummy object that would serve as switch to turn on/off an outer join. That dummy object, let‘s call it “All Stores”, will be incompatible with a fact table joined with an inner join.
The last step in our design would be to create the Revenue measure with the following definition:
@Aggregate_Aware( sum(Shop_facts2.Amount_sold) , sum( Shop_facts2__Alias.Amount_sold) )
Now watch what happens when we add and remove “All Stores” object in a report, specifically pay attention to a generated SQL :
SELECT Outlet_Lookup2.Shop_name, sum(Shop_facts2.Amount_sold) FROM Shop_facts2 'lNNER JOIN Outlet_Lookup Outlet_Lookup2 ON (Outlet_Lookup2.Shop__id=Shop_facts2.Shop_id) GROUP BY Outlet_Lookup2.Shop_name SELECT Outlet_lookup2.Shop_name, sum( Shop_facts2_Alias.Amount_sold), 'All Stores' FROM Shop_facts2 Shop_facts2_Alias RIGHT OUTER JOIN Outlet_Lookup Outlet_Lookp2 ON (Outlet_Lookup2.Shop_id=Shop_facts2_Alias.Shop_id) GROUP BY Outlet_Lookup2.Shop__na me, 'All Stores‘
By adding “All Stores” object we added an outer join and we pulled all stores even these without Revenue. By removing that object we brought in only Stores with Revenue.