5
Given a star-schema model with Fact_Table supported by three dimension tables: Item_Dim, Store_Dim
and Date_Dim; a multidimensional query is issued to find sales and quantity figures with local selection
on items, stores and dates (see
Figure 4). There is no local selection on the fact table.
Item_Dim
Store
Item
Store_Dim
Date_Dim
Weather
Itemkey
Storekey
Datekey
Fact_Table
Sales
Quantity
Itemkey
Storekey
Datekey
Select store, item, date, weather, sum(sales), sum(quantity)
from item_dim, store_dim, date_dim, fact_table
where (item like 'Snow%' or item like 'Ice%')
and store like 'Ace Hardware%'
and weather in ('Flurries', 'Snow', 'Blizzard')
and itemkey=itemkey, storekey=storekey, datekey=datekey
group by store, item, date, weather
Date
No local selection on the fact table
Selection on the fact table comes
from the local selection and joins to
the dimension tables
PK
PK
PK
FK
FK
FK
L
o
c
a
l
s
e
l
e
c
t
i
o
n
Figure 4. Issuing a multidimensional query
The ad-hoc query requests sales for any winter items (for example, Snow shoes, Snow shovels, Ice
melting devices and Ice chippers) sold in any Acme Hardware store when the weather was wintery (that
is, Flurries, Snow and Blizzards) during some time period. Thus, any itemkey, storekey or datekey that
matches the criteria is a candidate for the join to the fact table.
Item_Dim
EVI 1
Store
Item
EVI 2
EVI 3
Store_Dim
Date_Dim
Weather
Itemkey
Storekey
Datekey
Fact_Table
Sales
Quantity
Itemkey
Storekey
Datekey
Itemkey
Storekey
Datekey
Select store, item, date, weather, sum(sales), sum(quantity)
from item_dim, store_dim, date_dim, fact_table
where (item like 'Snow%' or item like 'Ice%')
and store like 'Ace Hardware%'
and weather in ('Flurries', 'Snow', 'Blizzard')
and
itemkey
=itemkey,
storekey
=storekey,
datekey
=datekey
group by store, item, date, weather
Date
Single-column EVIs
over Fact_Table
foreign keys
Figure 5. Ad-hoc query for winter items
Single-column EVIs that are created over the foreign key columns of the fact table are usually optimal for
the SQE optimizer to implement its star-schema join techniques. If the column is unique or has high