databases and data warehouses -...
TRANSCRIPT
![Page 1: Databases and Data Warehouses - Gunadarmawsilfi.staff.gunadarma.ac.id/Downloads/files/34226/Database+and+D… · management systems: Clean data Query processing: multiple options](https://reader033.vdocuments.us/reader033/viewer/2022051809/6013d145bc07f65dd3277c23/html5/thumbnails/1.jpg)
Databases and
Data Warehouses
![Page 2: Databases and Data Warehouses - Gunadarmawsilfi.staff.gunadarma.ac.id/Downloads/files/34226/Database+and+D… · management systems: Clean data Query processing: multiple options](https://reader033.vdocuments.us/reader033/viewer/2022051809/6013d145bc07f65dd3277c23/html5/thumbnails/2.jpg)
Content
Concept Definitions of Databases,Data
Warehouses
Database models
History
Databases
Data Warehouses
OLTP vs. Data Warehouse
![Page 3: Databases and Data Warehouses - Gunadarmawsilfi.staff.gunadarma.ac.id/Downloads/files/34226/Database+and+D… · management systems: Clean data Query processing: multiple options](https://reader033.vdocuments.us/reader033/viewer/2022051809/6013d145bc07f65dd3277c23/html5/thumbnails/3.jpg)
Concept Definition
Database
a structured collection of
records or data
Data Warehouse
a logical collection of
information, gathered
from many different
operational databases,
that supports business
analysis activities and
decision-making tasks
![Page 4: Databases and Data Warehouses - Gunadarmawsilfi.staff.gunadarma.ac.id/Downloads/files/34226/Database+and+D… · management systems: Clean data Query processing: multiple options](https://reader033.vdocuments.us/reader033/viewer/2022051809/6013d145bc07f65dd3277c23/html5/thumbnails/4.jpg)
Database models
is the structure or format of a database, described in a
formal language supported by the database management
system
Database models
relational
flat
hierarchical
network
dimensional
object database
Data Warehouse
• relational database
model
![Page 5: Databases and Data Warehouses - Gunadarmawsilfi.staff.gunadarma.ac.id/Downloads/files/34226/Database+and+D… · management systems: Clean data Query processing: multiple options](https://reader033.vdocuments.us/reader033/viewer/2022051809/6013d145bc07f65dd3277c23/html5/thumbnails/5.jpg)
History
Database 1960 - the first database
management system
1970 - the first relational model
1980 - distributed database systems and database machines
1990 - object-oriented databases
2000 - XML database
Data Warehouse
It became a distinct type of
computer database during
the late 1980s and early
1990s
![Page 6: Databases and Data Warehouses - Gunadarmawsilfi.staff.gunadarma.ac.id/Downloads/files/34226/Database+and+D… · management systems: Clean data Query processing: multiple options](https://reader033.vdocuments.us/reader033/viewer/2022051809/6013d145bc07f65dd3277c23/html5/thumbnails/6.jpg)
Database
collection of related data
database management system (DBMS) is a collection of programs that enables users to create and maintain a database
used in many applications
used in all e-commerce sites to store product inventory and customer information
![Page 7: Databases and Data Warehouses - Gunadarmawsilfi.staff.gunadarma.ac.id/Downloads/files/34226/Database+and+D… · management systems: Clean data Query processing: multiple options](https://reader033.vdocuments.us/reader033/viewer/2022051809/6013d145bc07f65dd3277c23/html5/thumbnails/7.jpg)
Data Warehouses
“A data warehouse is simply a single,
complete, and consistent store of data
obtained from a variety of sources and
made available to end users in a way
they can understand and use it in a
business context.”
-- Barry Devlin, IBM Consultant
![Page 8: Databases and Data Warehouses - Gunadarmawsilfi.staff.gunadarma.ac.id/Downloads/files/34226/Database+and+D… · management systems: Clean data Query processing: multiple options](https://reader033.vdocuments.us/reader033/viewer/2022051809/6013d145bc07f65dd3277c23/html5/thumbnails/8.jpg)
Data Warehouses
a record of an enterprise's past
transactional and operational information
designed to favor efficient data analysis and
reporting
data warehousing is not meant for current
"live" data
![Page 9: Databases and Data Warehouses - Gunadarmawsilfi.staff.gunadarma.ac.id/Downloads/files/34226/Database+and+D… · management systems: Clean data Query processing: multiple options](https://reader033.vdocuments.us/reader033/viewer/2022051809/6013d145bc07f65dd3277c23/html5/thumbnails/9.jpg)
Data Warehouses
large amounts of data – sometimes subdivided into smaller
logical units (dependent data marts)
data storing in a data warehouses are tematically consistent
and concern concrete problem or institutions
![Page 10: Databases and Data Warehouses - Gunadarmawsilfi.staff.gunadarma.ac.id/Downloads/files/34226/Database+and+D… · management systems: Clean data Query processing: multiple options](https://reader033.vdocuments.us/reader033/viewer/2022051809/6013d145bc07f65dd3277c23/html5/thumbnails/10.jpg)
Data Warehouses
Components of a data
warehouse:
Sources -> Data Source Interaction
Data Transformation
Data Warehouse (Data Storage)
Reporting (Data Presentation)
Metadata
![Page 11: Databases and Data Warehouses - Gunadarmawsilfi.staff.gunadarma.ac.id/Downloads/files/34226/Database+and+D… · management systems: Clean data Query processing: multiple options](https://reader033.vdocuments.us/reader033/viewer/2022051809/6013d145bc07f65dd3277c23/html5/thumbnails/11.jpg)
![Page 12: Databases and Data Warehouses - Gunadarmawsilfi.staff.gunadarma.ac.id/Downloads/files/34226/Database+and+D… · management systems: Clean data Query processing: multiple options](https://reader033.vdocuments.us/reader033/viewer/2022051809/6013d145bc07f65dd3277c23/html5/thumbnails/12.jpg)
Data Warehouses
ADVANTAGES
complete control over the four main areas of data
management systems:
Clean data
Query processing: multiple options
Indexes: multiple types
Security: data and access
![Page 13: Databases and Data Warehouses - Gunadarmawsilfi.staff.gunadarma.ac.id/Downloads/files/34226/Database+and+D… · management systems: Clean data Query processing: multiple options](https://reader033.vdocuments.us/reader033/viewer/2022051809/6013d145bc07f65dd3277c23/html5/thumbnails/13.jpg)
Data Warehouses
DISADVANTAGES
Adding new data sources takes time and associated high cost
Data owners lose control over their data, raising ownership, security and privacy issues
Long initial implementation time and associated high cost
Difficult to accommodate changes in data types and ranges, data source schema, indexes and queries
![Page 14: Databases and Data Warehouses - Gunadarmawsilfi.staff.gunadarma.ac.id/Downloads/files/34226/Database+and+D… · management systems: Clean data Query processing: multiple options](https://reader033.vdocuments.us/reader033/viewer/2022051809/6013d145bc07f65dd3277c23/html5/thumbnails/14.jpg)
OLTP vs. OLAP
OLTP: On Line Transaction Processing
Describes processing at operational sites
OLAP: On Line Analytical Processing
Describes processing at warehouse
![Page 15: Databases and Data Warehouses - Gunadarmawsilfi.staff.gunadarma.ac.id/Downloads/files/34226/Database+and+D… · management systems: Clean data Query processing: multiple options](https://reader033.vdocuments.us/reader033/viewer/2022051809/6013d145bc07f65dd3277c23/html5/thumbnails/15.jpg)
OLTP Database
vs.
Data Warehouse
relational databases - groups data using common
attributes found in the data set
objectives are different
![Page 16: Databases and Data Warehouses - Gunadarmawsilfi.staff.gunadarma.ac.id/Downloads/files/34226/Database+and+D… · management systems: Clean data Query processing: multiple options](https://reader033.vdocuments.us/reader033/viewer/2022051809/6013d145bc07f65dd3277c23/html5/thumbnails/16.jpg)
OLTP database Data Warehouse
Designed for real
time business
operations
Designed for analysis of
business measures by
categories and
attributes
![Page 17: Databases and Data Warehouses - Gunadarmawsilfi.staff.gunadarma.ac.id/Downloads/files/34226/Database+and+D… · management systems: Clean data Query processing: multiple options](https://reader033.vdocuments.us/reader033/viewer/2022051809/6013d145bc07f65dd3277c23/html5/thumbnails/17.jpg)
OLTP database Data Warehouse
Mostly updates
Many small
transactions
Mb - Gb of data
Mostly reads
Queries are long and
complex
Gb - Tb of data
![Page 18: Databases and Data Warehouses - Gunadarmawsilfi.staff.gunadarma.ac.id/Downloads/files/34226/Database+and+D… · management systems: Clean data Query processing: multiple options](https://reader033.vdocuments.us/reader033/viewer/2022051809/6013d145bc07f65dd3277c23/html5/thumbnails/18.jpg)
OLTP database Data Warehouse
Current snapshot
Raw data
Thousands of users
(e.g., clerical users)
History
Summarized, reconciled
data
Hundreds of users (e.g.,
decision-makers,
analysts)
![Page 19: Databases and Data Warehouses - Gunadarmawsilfi.staff.gunadarma.ac.id/Downloads/files/34226/Database+and+D… · management systems: Clean data Query processing: multiple options](https://reader033.vdocuments.us/reader033/viewer/2022051809/6013d145bc07f65dd3277c23/html5/thumbnails/19.jpg)
SUMMARY
four questions for you
![Page 20: Databases and Data Warehouses - Gunadarmawsilfi.staff.gunadarma.ac.id/Downloads/files/34226/Database+and+D… · management systems: Clean data Query processing: multiple options](https://reader033.vdocuments.us/reader033/viewer/2022051809/6013d145bc07f65dd3277c23/html5/thumbnails/20.jpg)
Designed for real
time business
operations
Designed for analysis of
business measures by
categories and
attributes
1 2
![Page 21: Databases and Data Warehouses - Gunadarmawsilfi.staff.gunadarma.ac.id/Downloads/files/34226/Database+and+D… · management systems: Clean data Query processing: multiple options](https://reader033.vdocuments.us/reader033/viewer/2022051809/6013d145bc07f65dd3277c23/html5/thumbnails/21.jpg)
Designed for real
time business
operations
Designed for analysis of
business measures by
categories and
attributes
Data Warehouse OLTP database
![Page 22: Databases and Data Warehouses - Gunadarmawsilfi.staff.gunadarma.ac.id/Downloads/files/34226/Database+and+D… · management systems: Clean data Query processing: multiple options](https://reader033.vdocuments.us/reader033/viewer/2022051809/6013d145bc07f65dd3277c23/html5/thumbnails/22.jpg)
Optimized for bulk loads
and large, complex,
unpredictable queries
that access many
rows per table.
Optimized for a common
set of transactions,
usually adding or
retrieving a single row at
a time per table.
1 2
![Page 23: Databases and Data Warehouses - Gunadarmawsilfi.staff.gunadarma.ac.id/Downloads/files/34226/Database+and+D… · management systems: Clean data Query processing: multiple options](https://reader033.vdocuments.us/reader033/viewer/2022051809/6013d145bc07f65dd3277c23/html5/thumbnails/23.jpg)
Optimized for bulk loads
and large, complex,
unpredictable queries
that access many rows
per table.
Optimized for a common
set of transactions,
usually adding or
retrieving a single row at
a time per table.
OLTP database Data Warehouse
![Page 24: Databases and Data Warehouses - Gunadarmawsilfi.staff.gunadarma.ac.id/Downloads/files/34226/Database+and+D… · management systems: Clean data Query processing: multiple options](https://reader033.vdocuments.us/reader033/viewer/2022051809/6013d145bc07f65dd3277c23/html5/thumbnails/24.jpg)
Loaded with
consistent, valid
data; requires
no real time
validation.
Optimized for validation
of incoming data during
transactions; uses
validation data tables.
1 2
![Page 25: Databases and Data Warehouses - Gunadarmawsilfi.staff.gunadarma.ac.id/Downloads/files/34226/Database+and+D… · management systems: Clean data Query processing: multiple options](https://reader033.vdocuments.us/reader033/viewer/2022051809/6013d145bc07f65dd3277c23/html5/thumbnails/25.jpg)
Loaded with
consistent, valid
data; requires
no real time
validation.
Optimized for validation
of incoming data during
transactions; uses
validation data tables.
OLTP database Data Warehouse
![Page 26: Databases and Data Warehouses - Gunadarmawsilfi.staff.gunadarma.ac.id/Downloads/files/34226/Database+and+D… · management systems: Clean data Query processing: multiple options](https://reader033.vdocuments.us/reader033/viewer/2022051809/6013d145bc07f65dd3277c23/html5/thumbnails/26.jpg)
Supports thousands of
concurrent users.Supports few concurrent
users relative to OLTP.
1 2
![Page 27: Databases and Data Warehouses - Gunadarmawsilfi.staff.gunadarma.ac.id/Downloads/files/34226/Database+and+D… · management systems: Clean data Query processing: multiple options](https://reader033.vdocuments.us/reader033/viewer/2022051809/6013d145bc07f65dd3277c23/html5/thumbnails/27.jpg)
Supports thousands of
concurrent users.Supports few concurrent
users relative to OLTP.
Data Warehouse OLTP database
![Page 28: Databases and Data Warehouses - Gunadarmawsilfi.staff.gunadarma.ac.id/Downloads/files/34226/Database+and+D… · management systems: Clean data Query processing: multiple options](https://reader033.vdocuments.us/reader033/viewer/2022051809/6013d145bc07f65dd3277c23/html5/thumbnails/28.jpg)
Bandingkan databases
dan data warehouses
dengan OLTP and
OLAP. Dalam
Membandingkan gunakan
contoh kasus (bebas)
![Page 29: Databases and Data Warehouses - Gunadarmawsilfi.staff.gunadarma.ac.id/Downloads/files/34226/Database+and+D… · management systems: Clean data Query processing: multiple options](https://reader033.vdocuments.us/reader033/viewer/2022051809/6013d145bc07f65dd3277c23/html5/thumbnails/29.jpg)
Sources
www.wikipedia.com
www.exforsys.com/tutorials
www.toolbox.com (blog)
www.personal.uncc.edu
www.inf.unibz.it/~franconi/teaching/20
01/CS636/CS636-dw-intro.ppt
![Page 30: Databases and Data Warehouses - Gunadarmawsilfi.staff.gunadarma.ac.id/Downloads/files/34226/Database+and+D… · management systems: Clean data Query processing: multiple options](https://reader033.vdocuments.us/reader033/viewer/2022051809/6013d145bc07f65dd3277c23/html5/thumbnails/30.jpg)
for your attention!