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
The benefits of buying summaries with Stuvia:
Guaranteed quality through customer reviews
Stuvia customers have reviewed more than 700,000 summaries. This how you know that you are buying the best documents.
Quick and easy check-out
You can quickly pay through credit card or Stuvia-credit for the summaries. There is no membership needed.
Focus on what matters
Your fellow students write the study notes themselves, which is why the documents are always reliable and up-to-date. This ensures you quickly get to the core!
Frequently asked questions
What do I get when I buy this document?
You get a PDF, available immediately after your purchase. The purchased document is accessible anytime, anywhere and indefinitely through your profile.
Satisfaction guarantee: how does it work?
Our satisfaction guarantee ensures that you always find a study document that suits you well. You fill out a form, and our customer service team takes care of the rest.
Who am I buying these notes from?
Stuvia is a marketplace, so you are not buying this document from us, but from seller shafaqsara. Stuvia facilitates payment to the seller.
Will I be stuck with a subscription?
No, you only buy these notes for $3.77. You're not tied to anything after your purchase.
