The Grocery Store Data Warehouse a Simple Example

Consider a grocery store chain data warehouse, an example adopted from [71, p.358]. Assume the first datamart is for the sales process. For simplicity, the OLAP query is formulated as the total sales by shop, month and year. Its underlying schema is shown in the HERM (Higher-order Entity-Relationship Model, [106]) diagram in Figure 5.3. The data schema for the data source is shown in the HERM diagram in Figure 5.4. There are five relations, namely, Shop DB, Product DB, Customer DB, Buys and Offer, of arity 4, 3, 4, 5 and 5, respectively. Based on the OLAP query, the schema for the data warehouse, a star schema [58], is shown in Figure 5.5. It is represented by a relational database

5.1. THE DATA WAREHOUSE GROUND MODEL Jane Qiong Zhao Sales money_sales quantity Time month year quarter Time month year quarter Shop sid address Region name state

Figure 5.3: Schema underlying an OLAP view

schema with four relation schemata Shop, Product, Purchase and Time with arities 6, 3, 7 and 7, respectively. As shown in Figure 5.5, cid is populated in Purchase for keeping the data at the atomic level, i.e. the most detailed level of the data in the data sources, which is a common principle in data warehouse design [58, 53].

Based on the general model shown above, the ground model for the grocery data warehouse is defined as follows. Note that we will use auxiliary functionsSump to indicate

summation of parameter p in a set of tuples. While it would be possible to define the ASMs without those functions (see Section 4.7), this is done for readability. In TASMs, these auxiliary functions will be replaced by explicit src (structural recursion) constructs.

ASM DB-ASM

IMPORT DW-ASM(Shop, Product, Time, Purchase,

extract purchase,extract shop,

extract product, extract time)

EXPORT

Store, Part, Customer DB, Buys, Offer SIGNATURE

Store(4), Part(3), Customer DB(4), Buys(5), Offer(5),

r-type(1) (static), req(0) (external) BODY

main = if r-type(req) = extract then

extract purchase || extract shop

extract product || extract time

endif

To extract data for the data warehouse relations, for example, the fact tablePurchase, the following SQL query could be used:

Part _Offer Store

Buys Customer

price date

cost

kind description sid _address

cid name

address time

quantity

pid name size

DOB

Figure 5.4: The operational database schema

select C.cid, P.pid, S.sid, Date(B.time) Sum(B.quantity) as quantity,

Sum(B.quantity) * O.priceas money sales, Sum(B.quantity) * (O.price - O.cost)as profit,

from Part, Store S, Buys B, Offer O, Cutomer DB C

where Date(B.time) = O.date and B.cid = C.cid

and B.pid = P.pid and B.sid = S.sid

group by C.cid, P.pid, S.sid, B.time

The DW ASM model is constructed as follows: ASM DW-ASM

IMPORT

DB-ASM(Store, Part, Buys, Offer),

OLAP-ASM(V sales, DM-V sales, the-datamart, the-matching-view, cre-

ate V sales)

EXPORT

Shop, Product, Time, Purchase,

extract purchase || extract shop

extract product || extract time

SIGNATURE

Shop(6), Product(3), Time(4), Purchase(7),

r-type(1) (static), req(0) (external) BODY

main = if r-type(req)=open-datamart

open datamart(the-datamart(req)) endif

extract purchase =forall i,p, s, d, p0_{,c with}

∃t, q.Buys(t, i, p, s, q) = 1 ∧ t.date=d ∧

∃n, a.Customerdb(i, n, a)6=⊥ ∧ ∃k, d0.Part(p, k, d0)6=⊥ ∧

∃a0_{, n}0_{, s}0_{.Store(s, n}0_{, s}0_{, a}0_{)6=⊥ ∧ Offer(p, s, d, p}0_{, c) = 1} do let Q=Sumq({(t, q)|(i, s, p, t, q)∈Buys∧

5.1. THE DATA WAREHOUSE GROUND MODEL Jane Qiong Zhao Purchase profit sales quantity name Time date month quarter year Shop town region phone state sid Product description _category pid day week cid

Figure 5.5: The data warehouse schema for sales

t.date=d}) S =Q∗p0 _{P =Q∗(p}0_−c) in Purchase(i, p, s, d, Q, S, P) := 1 enddo

extract shop = forall s, n, a with

∃s0_{.Store(s, n, s}0_{, a) = 1}

do let t=a.town, r=a.region, st=a.state, ph =a.phone in Shop(s, n, t, r, st, ph) := 1 enddo

extract product =forall p, k, d with

Part(p, k, d) 6= ⊥

do let p0 _{=p, c=k, d}0 _=d

in Product(p0_{, c, d}0_{) := 1 enddo}

extract time = forall t with

∃ c, p, s, q. Buys(c, p, s, q, t)6= ⊥

do let d=t.date, d0 _{=t.day, w =t.week,} q =t.quarter, m =t.month, y =t.year in Time(d, d0_{, w, q, m, t) := 1 enddo}

open datamart(dm) = casethe-matching-view(dm) of

V sales : create V sales;

forall s, r, st, m, q, y, S with V sales(s, r, st, m, q, y, S) = 1 do

DM-V sales(dm, s, r, st, m, q, y, S) := 1 enddo endcase

The following SQL query can be used to create the view of total sales by shop, month and year:

select S.sid, S.region, S.state, T.month, T.quarter, T.year, Sum(P.money sale) asmoney sale

from Shop S, Time T, Purchase P

where P.sid = S.sid and P.date = T.date

group by S.sid, S.region, S.state, T.month, T.quarter, T.year

The ASM OLAP model is the following.

ASM OLAP-ASM

IMPORT DW-ASM(Shop, Product, Time, Purchase) EXPORT V sales, DM-V sales, create V sales,

the-datamart, the-matching-view SIGNATURE V sales(7) DM-V sales(8), o-type(1), owner(1), issuer(1), the-datamart(1), the-view(1), the-matching-view(1), op(0) (external) BODY

main = if o-type(op) = login thenLOGIN

elsif if registered(issuer(op))=1 then if o-type(op) = open then OPEN

elsif o-type(op) = close then CLOSE

elsif o-type(op) = quit then QUIT

endif LOGIN = registered(issuer(op)):=1 OPEN = import dm datamart(dm) := 1 owner(dm) := issuer(op) the-matching-view(dm) := the-view(op) import reqst

let reqst= (open-datamart,dm) in request(reqst) := 1

end-import; end-import;

CLOSE =

5.1. THE DATA WAREHOUSE GROUND MODEL Jane Qiong Zhao

owner(the-datamart(op)) :=⊥ datamart(the-datamart(op)) :=⊥

QUIT =

letusr =issuer(op)in

forall dm with owner(dm) = usr

do close datamart(dm)

datamart(dm) :=⊥ owner(dm) :=⊥ enddo registered(usr) :=⊥

close datamart(dm) = case the-matching-view(dm) of

V sales : forall s, r, st, m, q, y, S with

DM-V sales(the-datamart(op), s, r, st, m, q, y, S) = 1 do

DM-V sales(the-datamart(op), s, r, st, m, q, y, S) :=⊥ enddo endcase

create V sales = forall s, r, st, m, q, y with

∃n, t, ph.Shop(s, n, t, r, st, ph) = 1∧ ∃d, d0_{, w.Time(d, d}0_{, w, m, q, y)6=⊥} do let S =Sums0 ({(i, s, p, d, s0_{)| ∃q}0_{, p}0_. Purchase(i, s, p, d, q0_{, s}0_{, p}0_{) = 1 ∧} d.month=m ∧d.year =y}) in V sales(s, r, st, m, q, y, S) := 1 enddo