Other uses of aggregate awareness

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:

pic7.1

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.

pic7.2

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 :

pic7.3

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

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

pic7.5

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s