Docstoc

chiara

Document Sample
chiara Powered By Docstoc
					                                 `




              DATA WAREHOUSE
             Chiara Delfante - chiara@csita.unige.it




Data Warehouse – June 2006                             page 1
                               `


                             Agenda

         Decision Support Systems (DSS)


         Datawarehouse and datawarehousing


         Case study: CSITA




Data Warehouse – June 2006                   page 2
                                  `


                Knowledge as strategic weapon
      We have data:
              What's happened ?        Why ?



                             DATA


      What would be happened if... ?   What will be ?

Data Warehouse – June 2006                       page 3
                                `


                       DATA  INFORMATIONS

         too many data


         different sources of data


         redundancy and inconsistency




Data Warehouse – June 2006                   page 4
                               `


                Decision Support System (DSS)

     DSS applications are system that help people
     make decision based on data
                             Decision Support
                             Systems (DSS)



                                      useful
       data                           knowledge


Data Warehouse – June 2006                        page 5
                                    `


                             Features of DSS

         accessibility


         integration


         flexibility


         correctness and thoroughness

Data Warehouse – June 2006                     page 6
                                    `


                         Data and Informations



                DATA                     Operative Environment
               (Transaction processing
                 systems)


INFORMATION                              Decisional Environment
                 (Analytical processing systems)



Data Warehouse – June 2006                               page 7
                                        `


                               OLTP and OLAP

      more formally....
                     Application Oriented
                DATA                         Operative Environment
               On-Line Transaction Processing (OLTP)

                             User Oriented
INFORMATION                                  Decisional Environment
               On-Line Analytical Processing (OLAP)

Data Warehouse – June 2006                                   page 8
                                           `


                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




Data Warehouse – June 2006                                                 page 9
                                        `


                  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




Data Warehouse – June 2006                                         page 10
                                   `


                         Roll up and Drill down




Data Warehouse – June 2006                        page 11
                                     `


                              Slice and Dice

                             Goods
       Math

       Physic                        Department
                                                  Sw
                                                  Hw


                   2002              Years



Data Warehouse – June 2006                        page 12
                                  `


                             OLTP vs OLAP




Data Warehouse – June 2006                  page 13
                                     `


                             Two Environments
               Operational                  Decisional
          Appl. “A”

                                              Data
                                            Warehouse
                        Appl. “B”




                         Appl. “C”




Data Warehouse – June 2006                              page 14
                                      `


                             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.

Data Warehouse – June 2006                                        page 15
                                `


                    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


Data Warehouse – June 2006                        page 16
                                    `


                             Datawarehousing

     Datawarehousing is the process of constructing
     and using datawarehouse.


      environment support for DSS


      system to store data



Data Warehouse – June 2006                     page 17
                                    `


                             Datawarehousing

                                 METADATA

                                                           DSS

    OLTP
                                   DW


                 ➔ Integration              ➔   Query
                 ➔ Aggregation              ➔   Report
                 ➔ Generation               ➔   Analysis

Data Warehouse – June 2006                                 page 18
                                        `


                             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)




Data Warehouse – June 2006                                          page 19
                                   `


                      CSITA: Datawarehousing

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



Data Warehouse – June 2006                          page 20
                                  `


                             How we were




Data Warehouse – June 2006                 page 21
                                 `


                      How we are (downsizing)




Data Warehouse – June 2006                      page 22
                                   `


                   Data distributed for function
                                                 RCMANDA
        ANAG
                                           Matr   #Man      ImpPag
        Matr Surname Name Codstr
                                           110    1          10000
        110 Rossi       Paolo 10           110    2          16000
        120 Bianchi Paola 20
        123 Verdi      Franco 20
        .... ..... .....   ....

       Server1                         Server2


                  Staff Database             Financial Database

Data Warehouse – June 2006                                   page 23
                                      `


                       Data distributed for area
                                                    RCMANDA
               RCMANDA                        Matr   NumMan ImpPag
         Matr     NumMan ImpPag               110    1         1000,00
         110     1           200,00           110    2         300,00
         111     2           160,00



      Server1                             Server2

                                                Financial DB
                                                Department 2
Data Warehouse – June 2006                                      page 24
                                  `


                       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)


Data Warehouse – June 2006                           page 25
                              `


              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




Data Warehouse – June 2006                           page 26
                                  `


                             CSITA: WebI




Data Warehouse – June 2006                 page 27
                                  `


                        CSITA: BusinessObject




Data Warehouse – June 2006                      page 28