Data Warehousing

Data Warehousing

1. Data Warehouse (DW)

   • Data Warehouse Administrator (DWA)
     • Familiar with high performance:
       • Software
       • Networks
       • Networking technologies
     • Possess:
       • Solid business insight
       • Decision-making skills
       • Excellent communication skills
   • Active Data Warehouse (ADW)
     • capture transactions when they:
       • Change
       • Integrate them into DW
       • Maintain the batch
       • Schedule cycle refresh
     • Automatization of routine:
       • Tasks
       • Decisions
     • Exports decisions automatically to the OLTP systems.
       • Online Transaction Processing System (OLTP)
         • Responsible for capturing and storing data related to:
           • day-to-day functions
             • ERP
             • CRM
             • SCM
             • POS
         • Adresses:
           • Critical business need
           • Automating daily business transactions
           • Running real-time reports and routine analysis
         • Provides ad hoc analysis of organizational data much more effectively and efficiently.
         • Heavily relys on
           • On-Line Analytical Processing (OLAP)
             • Uses the data captures by OLTP, and OLTP automates the business processes that are managed by decisions supported by OLAP
             • Operations rely on:
               • Cube
                 • Multidimensional data structure (actual or virtual) that allows fast analysis of data.
                 • Aims to overcome relational DB
                   • Not well suited for near instantaneous analysis of large amounts of data.
             • Drill down/up:
               • Specific OLAP technique where the user navigates among levels of data ranging from the most summarized (up) to the most detailed (down).
   • Real-time Data Warehouse (RDW)
     • Reflects the state of DW real time
     • Has low latenxy
       • Minutes to hours
       • As the latency gets smaller, the price goes up
   • Pool of data produced
   • Repository of information
     • Historic
     • Current
   • Structured to be directly used as:
     • Online Analytical Processing (OLAP)
     • Data mining
     • Querying
     • Reporting
     • Decision supporting activities
   • Main qualities
     • Subject-oriented
     • Integrated
     • Time-variant
     • Non-volatile

2. Data Marts

   • Subset of DW
     • Smaller than DW
   • Focuses on specific
     • Subject
     • Department
   • Types
     • Dependant DM
       • Created directly from DW
     • Independent DM
       • Small DW for:
         • Small business unit (SBU)
         • Department
   • NOT an
     • Enterprise Data Warehouses (EDW)
       • Large scale DW
       • Used in enterprises for decision support

3. Operational Data Stores (ODS)

   • Provides a resent customer informacion file (CIF)
   • Interim staging area for DW
   • Updated through business operations
   • Short-term decisions
     • Mission-critical information
   • Oper marts
     • Created when opperational data is analyzed multidimensionally

4. Metadata

   • Data about data
     • Description of:
       • Structure
       • Meaning
   • Contribute to:
     • Effective use
     • Ineffective use

5. Data integration

   • Permits data to be accessed and be accessible to an array of ETL through:
     • Data access
       • Access and extract data from any data source
     • Data federation
       • Integration of business views across multiple data stores
     • Change capture
       • Based on the identification, capture, and delivery of the changes made to enterprise data sources

6. Enterprise Applicaction Integration (EAI)

   • Pushes data from source systems into DW
   • Involves integrating application functionality
   • Focused on sharing functionality across systems
   • Facilitates:
     • Data acquisition directly into a near–real-time DW
     • Delivering decisions to the OLTP systems

7. Enterprise Information Integration (EII)

   • Evolving tool space that promises real-time data integration from a variety of sources:
     • Relational databases
     • Web services
     • Multimiensional databases
   • XML is the most important aspect since it allows:
     • Allows data to be tagged either at creation time or later.
       • These tags can be extended and modified to accommodate almost any area of knowledge.

8. Extraction, Transformation, Load (ETL)

   • Processes that tend to consume 70% of the project's time
   • Consists of
     • Extraction
       • Reading data from one or more databases
     • Transformation
       • Converting the extracted datainto the form in which it needs to be so that it can be placed into a DW or DB
     • Load
       • Putting the data into the DW

9. Dimensional Modeling

   • Schemas
     • Star
       • Most commonly used
       • Simplest
       • Contains a central fact table surrounded by and connected to several dimension tables
         • Fact table
           • Contains a large number of rows that correspond to observed facts and external links
           • Descriptive attributes needed for decision making
         • Dimension table
           • Contains classification and aggregation information about the central fact rows
           • Attributes that describe the data in the fact table
           • In querying
             • Used to slice and dice the numerical values in the fact table to address the requirements of an ad hoc information need
           • Denormalized into single tables
       • Provides:
         • Fast-query response time
         • Simplicity
         • Esay-mantainance
     • Snowflake
       • Logical arrangement of tables in a multidimensional database in such a way that the entity-relationship diagram resembles a snowflake in shape.
         • Represented by centralized fact table and dimensional tables
           • Dimensions:
             • Normalized into multiple related tables
   • Retrieval-based system that supports high-volume query access