Summary Data Warehousing: Part 2

DataWarehousing 2
1 Star and Snowfaak Schkma

 Why using a Star or Snowfaak Schkma?
 First stkp in data warehouse development: the ERD is uskd to dksign onk or mork star
or snowfaak schkmas.
 The operatonal databases/data models can be used as a source oor structuring into a
star or snowfaae schema! (Be aware that most oo the tme data in the DW comes orom
multple sources).
 The restructuring (orom a normalized database to a star or snowfaae schema) is
necessary to quicaly answkr to questons orom management.
Explains why we follow the pateenn
For example: What is the aveeage eevenue pee customee (oe foe customees) in the
Benelux? By how much has the eevenue dueing the quaetee inceeased (oe deceeased)?
Is theee a difeeence in a libeaey boeeowing between membees who aee youngee and
oldee than 40? Which events made us eaen the most? etc.
 These questons can also be answered by direct queries, reports and calculatons on the
data in the operatonal database.
Two main reasons oor DW and not directly use operaton databases
The structure oo operaton database is not optmal oor queries: complex and huge
You may have the data that you need is spread over diferent databases: bring
everything together
 But thk skarch prockss nkkds to bk optmiikd and there may (and probably are)
multple operatonal databases in a same organizaton, so that:
o Data must be ckntraliikd in a data warehouse;
o The data warehouse’s data need to be rkstructurkd in ordkr to kfcikntly pkrform
qukriks, reports and OLAP.
 The star schema and snowfaae schema are the standard modkls in a data warehouse.
Both are conceptual models that are translated into relatonal logic models (and
ultmately, relatonal data warehouses).
 In what oollows we discuss the development oo such models, startng orom an
operatonal ERD.

A Star Schkma
 Exists as a conckptual model (ERD) oor graphical representaton purposes (e.g. in Visual
Paradigm oo Visio or other tools). The resultng schema is then translated into a physical
model into a database system.
 Typical database management systems (DBMS) are used oor data warehousing: Oeacle,
SQL Seevee (Miceosof), MySQL (open source), ... The MS Access DBMS cannot be used
oor prooessional data warehouse because it is too limited.
 A data warehouse is a specially designed database and its running sofware are
“ordinary” relatonal database systems also used oor operatonal databases.




1

,Dkrivaton of a Star Schkma from an opkratonal databask diagramn
 The (operatonal) data model hereunder is not a truk conckptual ERD because we have
primary aeys and ooreign aeys explicitly modeled; we nevertheless use the crow ooot
notaton into the relatonships to show the multplicites
Io this was a real ERD, “Writes” would not be an entty, but a relatonship




Borrowing can
be an What is the
interestng oact mechanism
table behind: who is
borrowing, what, Not a real ERDan
when, etc. operatonal database
orom a lib




Star Schkma for Analytcs on Borrowings
 Notce:
 We have, oor clarity reasons, preceded the table names by "D_" (Dimension) or "F_"
(Facts) (conventon oor this course, no internatonal standard);
 The tables “Membeeship” “Weites” and “Subject” are omitted. The feld
“SubjectName” oo “Subject” is added to “Book”;
 The table “Date” is added to provide a tme dimension;

Normally only relevant data is included in the data warehouse

 All linas are oo the type “mandatory one to mandatory many”
In DW we only include data that are relevant oor our business, decision maaing



2

, Many relatonships at the level
oo oact and one at level oo
dimension




Intkrprktaton of thk Star Schkma
 The example schema can ultmately provide answers to questons liae:
o What aee the numbee of boeeowings pee beanch, pee membee, pee subject?
o Did the membees ovee 50 boeeow moee than youngee membees? Did youngee
membees have a difeeent peefeeence than oldee membees? (See the member’s birth
date and the booa’s subject)
o Which authoes aee veey populae (many boeeowings)?
o Have beanches with a eelatvely laegee budget moee boeeowings?
 These questons represent the informaton nkkdkd from managkrs.
 For sure the data warehouse’s data is not a dirkct answkr to these questons
 The data nkkds to bk analyikd (using SQL and reportng, via OLAP). This will be
discussed in Module 2.
All these data is necessary oor the decision maaing and this is what BI is aboutuse data
orom past to maae decision in the outure

A Snowfaak SchkmaMork normaliiaton than STAR
 Suppose that management also wishes to answer the oollowing questons:
o What aee the boeeowings by peovince? Which is the best “peefoeming” peovince? The
“weakest” peovince? In a Belgian context: do Flemish people boeeow moee oe less
than Walloon ones? Etc.
o This inoormaton can be obtained by using a star schema, but it requires some more
design! (cites groupkd by peovince, eegion, ...)

3