1. INTRODUCCIÓN
1.3. MARCO TEÓRICO
2.4.1. Programa de intervención LESIFO
We use the refinement-based method introduced in Chapter 5 to incorporate the new OLAP view into the existing ground model, which is constructed in section 5.1, by ap- plying the following steps:
1. Add a new rule to the OLAP ASM: we add a new rule for customer purchase report.
This rule will be exported for DW ASM to use.
create V customer purchase = forall c, ln, f n, s, p, m, q, y with
∃n, a, ph.(s, n, a, ph)∈Shop CRM∧ ∃de, ca.(p, de, ca)∈Product∧ ∃d, d0, w.(d, d0, w, m, q, y)∈Time∧ ∃a0.(c, ln, f n, a0)∈Customer DW do let S =src[0, πs0,+] ({(c, s, p, d, s0)| ∃dic. (i, s, p, d, s0, dic)∈Purchase CRM ∧ d.month=m ∧d.year =y}) D=src[0, πdic,+] ({(i, s, p, d, dic)| ∃s0. (i, s, p, d, s0, dic)∈Purchase CRM ∧ d.month=m ∧d.year =y}) in V customer purchase(c, ln, f n, s, p, m, q, y, S, D) := 1 enddo
7.1. ADDING A NEW DATA MART Jane Qiong Zhao
rule 13, to add the OLAP view V customer purchase and the corresponding data mart view DM V customer purchase. These two functions will be exported and imported for DW ASM to use.
OLAP-ASM∗BV customer purchase
=cid×last name×f irst name×shop×product ×month×qtr×year×sales×discount→ {1l}
OLAP-ASM∗BDM-V customer purchase
=dm×cid×last name×f irst name×shop×product ×month×qtr×year×sales×discount→ {1l}
3. Add new controlled function(s) to the DW ASM: apply the schema transformation
rule 13, to add the following functions to DW ASM, as the current data model does not support them. We will export the schema Customer DW, and import it in DB ASM. Note that time and product do not need to be added since they are identical to the existing relations.
DW-ASM∗BAddress =
t address:town×region×state
DW-ASM∗BCustomer DW
=cid×last name×f irst name×Address→ {1l}
DW-ASM∗Bshop CRM
=sid×name×Address×phone→ {1l}
DW-ASM∗BPurchase CRM
=cid×sid×pis×date×sales×discount→ {1l}
4. Integrate controlled functions on the DW ASM: as we have added two new functions
in step 3, we carry out the integration process, i.e. the 6-step process, as described in Section 6.4.
(a) Homogenisation of the schemata: in this step, we apply Rule 1 to break the address of the shop into town, region and state as follows:
DW-ASMBShop CRM =sid×name×Address×phone→ {1l} DW-ASM∗ B Shop CRMnew=sid×name×town
×region×state×phone→ {1l}
(b) Adding inter-schema integrity constraints: not required for our example. (c) Modulisation: not required in our example.
(d) Type integration: in this step, we apply Rule 18 to integrate Shop with Shop CRMnew, Purchase with Purchase CRM, as follows:
DW-ASMB Shop =
sid×name×town×region×state×phone→ {1l} Shop CRMnew =
sid×name×town×region×state×phone→ {1l} DW-ASM∗ B Shop
new=
sid×name×town×region×state×phone→ {1l} ϕ
which is a special case of type integration for two identical schemas, and DW-ASMB Purchase =
sid×cid×pid×date× qty×sales×prof it→ {1l} Purchase CRM =
sid×cid×pid×date× sales×discount→ {1l} DW-ASM∗ B Purchasenew =
sid×cid×pid×date×
qty×sales×prof it×discount→ {1l} ϕ
The side condition ϕ:
K1 =K2∧R1[K1∪X] = h(R2[K2∪Y]),
is satisfied, with the keys in both schemas being sid×cid×pid×date, and h the identity function.
(e) Path integration: not required in our example.
(f) Other integrity constraints: not required in our example.
Instead of exporting and importing the schemata with the new names, we can rename the schemata Shopnew back to Shop and Purchasenew back to Purchase, so we avoid
the changes in export and import. The new schema after the integration is shown as Figure 7.2.
5. Add controlled functions to the DB ASM: we combine this step with the schema
integration to extend the schema Buys to track the discount. This can be done by applying Rule 14 as follows:
DB-ASMBBuys =time×cid×sid×pid×quantity → {1l}
DB-ASM∗ BBuysnew=time×cid×sid×pid×quantity×discount → {1l}
Again we will rename Buysnew back to Buys to avoid unnecessary changes. The new
7.1. ADDING A NEW DATA MART Jane Qiong Zhao Purchase cid first-name discount sales name Customer address Time date month quarter year Shop town region phone state sid Product description category pid day week
last-namequantity profit
Figure 7.2: The data warehouse schema after incorporating CRM
Part Offer Store
Buys Customer
price date
cost
kind description sid address
cid name
address time
quantity discount
6. Change the rules on DW ASM: in this step we propagate changes into the impacted rules, e.g. extract purchase, a new rule for Customer DW, and open datamart(dm) as follows:
extract purchase =forall i,p, s, d, p0,c with
∃t.(i, p, s, t, p0, c, d)∈π
cid,pid,sid,time,price,cost,discount
(Buys./Customer DB ./Part./Store./Offer)∧t.date=d do let Q=src[0, πq,+]({(t, q)|(i, s, p, t, q)∈Buys∧t.date=d})
D=src[0, πd,+]({(t, d)|(i, s, p, t, d)∈Buys∧
t.date=d}) S=Q∗p0−D P =Q∗(p0−c)−D in Purchase(i, p, s, d, Q, S, P, D) := 1 enddo
extract customer =forall i, n, d, a with
(i, n, d, a)∈ Customer DB
do Customer DW(i, n.f irst, n.last, a) := 1enddo
open datamart(dm) = casethe-matching-view(dm) of
V sales : create V sales;
forall s, r, st, m, q, y, S with (s, r, st, m, q, y, S)∈ V salesdo
DM-V sales(dm, s, r, st, m, q, y, S) := 1 enddo V customer purchase : create V customer purchase;
forall c, ln, f n, s, p, m, q, y, S, D with
(c, ln, f n, s, p, m, q, y, S, D)∈ V customer purchase do
DM-V customer purchase(dm, c, ln, f n, s, p, m, q, y, S, D) := 1 enddo endcase
close datamart(dm) = case the-matching-view(dm) of
V sales : forall s, r, st, m, q, y, S with
(the-datamart(op), s, r, st, m, q, y, S)∈ DM-V sales do
DM-V sales(the-datamart(op), s, r, st, m, q, y, S) :=⊥ enddo enddo endcase
We export the rule extract customer and import it in DB ASM.
7. Change the rules on DB ASM: we change the main rule to include data extraction
for Customer DW as follows:
main = if r-type(req) = extract then
extract purchase || extract shop || extract product
extract time || extract customer
endif
8. Change the functions/rules on OLAP ASM: due to the newly added OLAP view and
the renaming of schemata, the rulesclose datamart andcreate V customer purchase
will be changed as follows:
close datamart(dm) = case the-matching-view(dm) of
V sales : forall s, r, st, m, q, y, S with
7.1. ADDING A NEW DATA MART Jane Qiong Zhao
DM-V sales(the-datamart(op), s, r, st, m, q, y, S) := ⊥ enddo V customer purchase : forall c, ln, f n, s, p, m, q, y, S, D with
(the-datamart(op), c, ln, f n, s, p, m, q, y, S, D)∈ DM-V customer purchase do DM-V customer purchase(the-datamart(op), c, ln, f n, s, p, m, q, y, S, D) :=⊥ enddo endcase
create V customer purchase = forall c, ln, f n, s, p, m, q, y with
∃n, t, r, st, ph.Shop(s, n, t, r, st, ph) = 1∧ ∃de, ca.(p, de, ca)∈Product∧
∃d, d0, w.(d, d0, w, m, q, y)∈Time∧ ∃a0.(c, ln, f n, a0)∈Customer DW do let S =src[0, πs0,+] ({(c, s, p, d, s0)| ∃q0, p0, dic. (i, s, p, d, q0, s0, p0, dic)∈Purchase ∧ d.month=m ∧d.year =y}) D=src[0, πdic,+] ({(i, s, p, d, dic)| ∃q0, p0, s0. (i, s, p, d, q0, s0, p0, dic)∈Purchase ∧ d.month=m ∧d.year =y}) in V customer purchase(c, ln, f n, s, p, m, q, y, S, D) := 1 enddo
The three refined ASM models after the CRM is incorporated are given as follows: TASM DB-ASM
IMPORT
DW-ASM(Shop, Product, Time, Purchase, Customer DW,
extract purchase,extract shop,
extract product, extract time, extract customer)
EXPORT
Store, Part, Buys, Offer SIGNATURE
Store:sid×name×size×address→ {1l}, Part:pid×kind×description→ {1l},
Customer DB:cid×name×dob×address→ {1l },
Buys:time×cid×sid×pid×quantity×discount → {1l }, Offer:pid×sid×date×price×cost→ {1l },
r-type (external), req (external) BODY
main = if r-type(req) = extract then
extract purchase || extract shop
extract product || extract time || extract customer
endif
TASM DW-ASM IMPORT
OLAP-ASM(V sales, DM-V sales, V customer purchase, DM-V customer purchase, the-datamart, the-matching-view,
create V sales,create V customer purchase )
EXPORT
Shop, Product, Time, Purchase, Customer DW
extract purchase,extract shop,
extract product, extract time, extract customer
SIGNATURE
Shop:sid×name×town×region×state×phone→ {1l}, Product:pid×category×description→ {1l },
Time:date×day×week×month×quarter×year → {1l }, Customer DW: cid×last name×f irst name×address→ {1l} Purchase:cid×sid×pid×date×qty×sales×prof it→ {1l}, r-type (external), req (external)
BODY
main =if r-type(req)=open-datamart then
open datamart(the-datamart(req)) endif
extract purchase =forall i,p, s, d, p0,c with
∃t.(i, p, s, t, p0, c, d)∈π
cid,pid,sid,time,price,cost,discount
(Buys./Customer DB ./Part./Store./Offer)∧t.date=d do let Q=src[0, πq,+]({(t, q)|(i, s, p, t, q)∈Buys∧t.date=d})
D=src[0, πd,+]({(t, d)|(i, s, p, t, d)∈Buys∧
t.date=d}) S=Q∗p0−D P =Q∗(p0−c)−D in Purchase(i, p, s, d, Q, S, P, D) := 1 enddo
extract shop = forall s, n, a with
∃s0.(s, n, s0, a)∈Store
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
(p, k, d)∈ Part
do let p0 =p, c=k, d0 =d
in Product(p0, c, d0) := 1 enddo
extract time = forall t with
∃ c, p, s, q.(c, p, s, q, t) ∈ Buys
do if Time(t.date, t.day, t.week, t.quarter, t.month, t.year) = ⊥ then Time(t.date, t.day, t.week, t.quarter, t.month, t.year) := 1 enddo
extract customer =forall i, n, d, a with
(i, n, d, a)∈ Customer DB
do let i0 =i f n=n.f irst ln=n.last a0 =a in Customer DW(i0, f n, ln, a0) := 1 enddo
7.1. ADDING A NEW DATA MART Jane Qiong Zhao
open datamart(dm) = casethe-matching-view(dm) of
V sales : create V sales;
forall s, r, st, m, q, y, S with (s, r, st, m, q, y, S)∈ V salesdo
DM-V sales(dm, s, r, st, m, q, y, S) := 1 enddo V customer purchase : create V customer purchase;
forall c, ln, f n, s, p, m, q, y, S, D with
(c, ln, f n, s, p, m, q, y, S, D)∈ V customer purchase do
DM-V customer purchase(dm, c, ln, f n, s, p, m, q, y, S, D) := 1enddo endcase
TASM OLAP-ASM
IMPORT DW-ASM(Shop, Product, Time, Purchase) EXPORT V sales, DM-V sales, create V sales,
V customer purchase, DM-V customer purchase,create V customer purchase, the-datamart, the-matching-view
SIGNATURE
V sales:sid×region×state×month×quarter×year× sales×prof it→ {1l},
DM-V sales:dm×sid×region×state×month×quarter× year×sales→ {1l } ,
V customer purchase:cid×last name×f irst name×shop×product ×month×qtr×year×sales×discount→ {1l}
DM-V customer purchase:dm×cid×last name×f irst name×shop×product ×month×qtr×year×sales×discount→ {1l}
o-type:op→ {open, close, quit}, owner:datamart→user, issuer:op→user,
the-datamart:op→datamart, the-view:op →view,
the-matching-view:datamart→view, op:operation (external)
BODY
main = if o-type(op) = login then LOGIN
elsif if registered(issuer(op))=1then if o-type(op) = open then OPEN
elsif o-type(op) =close then CLOSE
elsif o-type(op) =quit thenQUIT
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 =
close datamart(the-datamart(op));
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
(the-datamart(op), s, r, st, m, q, y, S)∈ DM-V sales do
DM-V sales(the-datamart(op), s, r, st, m, q, y, S) := ⊥ enddo V customer purchase : forall c, ln, f n, s, p, m, q, y, S, D with
(the-datamart(op), c, ln, f n, s, p, m, q, y, S, D)∈ DM-V customer purchase do
DM-V customer purchase(the-datamart(op),
c, ln, f n, s, p, m, q, y, S, D) :=⊥ enddo endcase
create V sales = forall s, r, st, m, q, y with
∃n, t, ph.(s, n, t, r, st, ph)∈Shop ∧ ∃d, d0, w.(d, d0, w, m, q, y)∈ Time do let S =src[0, πs0,+] ({(i, s, p, d, s0)| ∃q0, p0. (i, s, p, d, q0, s0, p0)∈Purchase ∧ d.month=m ∧d.year =y}) in V sales(s, r, st, m, q, y, S) := 1 enddo
create V customer purchase = forall c, ln, f n, s, p, m, q, y with
∃n, t, r, st, ph.Shop(s, n, t, r, st, ph) = 1∧ ∃de, ca.(p, de, ca)∈Product∧
∃d, d0, w.(d, d0, w, m, q, y)∈Time∧ ∃a0.(c, ln, f n, a0)∈Customer DW do let S =src[0, πs0,+]
({(c, s, p, d, s0)| ∃q0, p0, dic.
7.2. DYNAMIC DATA WAREHOUSE DESIGN Jane Qiong Zhao