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
Les avantages d'acheter des résumés chez Stuvia:
Qualité garantie par les avis des clients
Les clients de Stuvia ont évalués plus de 700 000 résumés. C'est comme ça que vous savez que vous achetez les meilleurs documents.
L’achat facile et rapide
Vous pouvez payer rapidement avec iDeal, carte de crédit ou Stuvia-crédit pour les résumés. Il n'y a pas d'adhésion nécessaire.
Focus sur l’essentiel
Vos camarades écrivent eux-mêmes les notes d’étude, c’est pourquoi les documents sont toujours fiables et à jour. Cela garantit que vous arrivez rapidement au coeur du matériel.
Foire aux questions
Qu'est-ce que j'obtiens en achetant ce document ?
Vous obtenez un PDF, disponible immédiatement après votre achat. Le document acheté est accessible à tout moment, n'importe où et indéfiniment via votre profil.
Garantie de remboursement : comment ça marche ?
Notre garantie de satisfaction garantit que vous trouverez toujours un document d'étude qui vous convient. Vous remplissez un formulaire et notre équipe du service client s'occupe du reste.
Auprès de qui est-ce que j'achète ce résumé ?
Stuvia est une place de marché. Alors, vous n'achetez donc pas ce document chez nous, mais auprès du vendeur shafaqsara. Stuvia facilite les paiements au vendeur.
Est-ce que j'aurai un abonnement?
Non, vous n'achetez ce résumé que pour €3,49. Vous n'êtes lié à rien après votre achat.
