Diapositiva 1

1

DATA WAREHOUSE
Chiara Delfante - chiara_at_csita.unige.it
2
Agenda

• Decision Support Systems (DSS)
• Datawarehouse and datawarehousing
• Case study CSITA

3
Knowledge as strategic weapon

• We have data

4
DATA ? INFORMATIONS

• too many data
• different sources of data
• redundancy and inconsistency

5
Decision Support System (DSS)

• DSS applications are system that help people make
  decision based on data

6
Features of DSS

• accessibility
• integration
• flexibility
• correctness and thoroughness

7
Data and Informations
(Transaction processing systems)

• (Analytical processing systems)

8
OLTP and OLAP

• more formally....

Application Oriented
On-Line Transaction Processing (OLTP)
User Oriented
On-Line Analytical Processing (OLAP)
9
On-Line Transaction Processing

• Database management system are typically used for
  on-line transaction processing
• OLTP applications normally automate clerical data
  processing tasks of an organization, like data
  entry and enqury, transaction handling, etc.
  (access, read, update)
• Database is current and consistency and
  recoverabilty are critical. Records are accessed
  one at a time
• OLTP operations are structured and repetitive
• OLTP operations require detailed and up-to-date
  data
• OLTP operations are short, atomic and isolated
  transactions

10
On-Line Analytical Processing

• On-Line Analytical Processing is essentially for
  decision support
• It's a consolidation of operational databases
• It always contains some element of time
• OLAP operations
• roll-up and drill-down increase the level of
  abstraction
• slice and dice selection and projection

11
Roll up and Drill down
12
Slice and Dice
Goods
Math
Physic
Department
Sw
Hw
Years
2002
13
OLTP vs OLAP
14
Two Environments

• Decisional

Operational
15
Data Warehouse

• Data Warehouse is a subject-oriented, integrated,
  time-variant and non-volatile collection of data
  in support of management's decision making
  process. (W.H. Inmon - 1990)
• Subject oriented oriented to the major subject
  areas of the corporation that have been defined
  in the data model.
• Integrated data collected in a data warehouse
  originates from different heterogeneous data
  sources.
• Time-variant the dimension Time is
  all-pervading in a data warehouse. The data
  stored is not the current value, but an evolution
  of the value in time.
• Non-volatile update of data does not occur
  frequently in the data warehouse. The data is
  loaded and accessed.

16
A separate database why ?

• there is not a unique database containing all
  interesting data
• database must be integrated
• we can't integrate data on-line
• interesting data must be aggregate and historical
• lack of efficency

17
Datawarehousing

• Datawarehousing is the process of constructing
  and using datawarehouse.
• environment support for DSS
• system to store data

18
Datawarehousing
METADATA
DSS
OLTP
DW

• Integration
• Aggregation
• Generation

19
Case study CSITA

• We provide technical services for
• Atheneum (telephony, network, mail, web,
  software, libraries, datawarehouse)
• Student (web services, self service, helpdesk)
• Administrative Department (PC, antivirus,
  software)
• Other Departments (web hosting, mail, software)
• Staff (helpdesk, training)

20
CSITA Datawarehousing

• Indicators to describe Atheneum activity
• Indicators for Ministry
• monitoring of
• students careers
• budget of different structures
• research activity

21
How we were
22
How we are (downsizing)
23
Data distributed for function
RCMANDA
24
Data distributed for area
RCMANDA
RCMANDA
Matr NumMan ImpPag 110 1 1000,00 110
2 300,00
Matr NumMan ImpPag 110 1 200,00 111
2 160,00
Server1
Server2
Financial DB Department 2
25
CSITA Data Warehouse

• NOT new data to existing users
• BUT existing data to new users
• (we have ? different server for each department
  but just one Atheneum Data Warehouse)

26
CSITA Datawarehousing software

• database Oracle 8.1.7 (AIX Unix version 4)
• administrative tool BusinessObject 5.1.3
• front-end (for users) BusinessObject 5.1.3
• web-oriented front-end (for users) WebI
• data sources MS SQL server, Oracle

27
CSITA WebI
28
CSITA BusinessObject