Business Information Management - Data Management. Second part of Module 2 Data Warehousing. This document contains all the slides, notes from 2 students and extra information collected on Internet. I had a 15 out 20 for this subject.
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 ERDan
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 aboutuse data
orom past to maae decision in the outure
A Snowfaak SchkmaMork 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
Voordelen van het kopen van samenvattingen bij Stuvia op een rij:
√ Verzekerd van kwaliteit door reviews
Stuvia-klanten hebben meer dan 700.000 samenvattingen beoordeeld. Zo weet je zeker dat je de beste documenten koopt!
Snel en makkelijk kopen
Je betaalt supersnel en eenmalig met iDeal, Bancontact of creditcard voor de samenvatting. Zonder lidmaatschap.
Focus op de essentie
Samenvattingen worden geschreven voor en door anderen. Daarom zijn de samenvattingen altijd betrouwbaar en actueel. Zo kom je snel tot de kern!
Veelgestelde vragen
Wat krijg ik als ik dit document koop?
Je krijgt een PDF, die direct beschikbaar is na je aankoop. Het gekochte document is altijd, overal en oneindig toegankelijk via je profiel.
Tevredenheidsgarantie: hoe werkt dat?
Onze tevredenheidsgarantie zorgt ervoor dat je altijd een studiedocument vindt dat goed bij je past. Je vult een formulier in en onze klantenservice regelt de rest.
Van wie koop ik deze samenvatting?
Stuvia is een marktplaats, je koop dit document dus niet van ons, maar van verkoper shafaqsara. Stuvia faciliteert de betaling aan de verkoper.
Zit ik meteen vast aan een abonnement?
Nee, je koopt alleen deze samenvatting voor €3,49. Je zit daarna nergens aan vast.
