DWH

Processes, technologies and tools that are need to turn data into information, information into knowledge and knowledge into wisdom or plans that drive profitable business action.

On-line Analytical Processing

• Analytics as necessity for success and survival

• Data-driven business decision rather than intuition

• Entire business drivne by analytics

1. Descriptive Analytics: Display data that is relevant

2. Predictive Analytics: Discover insights in data

3. Prescriptive Analytics: Compute best decisions and present them as recommendations

• Distributed DB: Distributed queries across multiple databases -> Slow execution!

• Centralized DB: 1 single centralized DB -> Slow in operative Use

=> Need enterprise wide data model / architecture

• Technical heterogeneity: different systems, communication systems, archticture

• Structural and syntactic heterogeneity: different encoding, units, synonyms

• Semantic heterogeneity: different interpretation of data

Data Silos contain data that is only available for a specific part of the organization

• Federation: Point-to-Point Integration

  -> n application/ data sources -> n^2 connections

• Warehouse: Sources are translated from their local schema to a global scheme and copied to a central DB

  -> unidirectional flows

• Mediator (Virtual Warehouse): Turns user query into a sequence of source queries and assembles the results of these queries into an aggregated result

  -> Complex architecture

A storage architecture designed to hold data extracted from transaction systems, operational data stores and external sources. The ware house then combines that data in an aggregated, summery form suitable for enterprise wide data analysis and reporting for predefined business needs.

• Subject-Orientation: Data is not stored by operational applications but by business subjects -> data is grouped around subjects

  -> Simple data querying

• Integration: Contains consolidated data from several application/ DB

  • Stored Data that is not updated or deleted

  • Data inconsistencies are removed -> Need for standardizatiojn

• Non-volatile and time-variant:

  • No overwriting of “old” data

  • Contains historical data to

    • allow analysis of the past,

    • relate information to the presen

    • and enable forecasts for the future

OLTP = Online Transaction Processing:

• Operational Systems, which support the execution of business processes

OLAP = Online Analytical Processing:

• Analzying the data describing business transactions/ processes

• Objective: Turn raw transactional data -> strategic/ tactical/ operative information

• Optimized for small and short transactions

• Access to up-to-date, consistend DBs

• Avoidance of redundancies -> normalized schemas

• Queries with large result stes

• No immediate updates or inserts, but large periodic batch insets

• (Controlled) redundanca is a neccessity for performance reasons

• Goal: Response Time of seconds/ a few minutes

• Lock Conflicts: long-running OLAP reads may block OLTP writes

• Feshness of data

  • OLTP: up-to-date data

  • OLAP: reproducibility of analysis

• Precision:

  • OLTP: exct

  • OLAP: smaplind, statistical summaries, confidence intervals

DB of an application which supports one or more types of business transactions

DB that is able to store a single data extract of a subset of one Source DB

-> Data of Source DB is typically accessible via APIs and follow a pre-defined schema of a subset of data

DB that is able to store matching data extracts from various Landing Areas in an integrated form

-> Upload to DW from Staging Area happens once data from all Landing Areas are available

-> Scheme basically corresponds 1:1 with the DW schema

DB containing the history of all complete Staging Areas

-> Its integrated schema is frequently more or less in 3NF

Objective: DB containing data describing the (present or past) performance of one or more types of business transactions

• “Factured out“ information required by many applications to establish the context of data

  -> Typically ends up in the dimensional tables of star schema

• “Structured“ master data: customers, vendors, products, categorization, attributes/ codes

• Owned externally

• Owned internally

Data about Data in DWH

-> Necessary for

• using,

• building

• and administering a DWH

• Operational metadata

• Extraction and transformation metadata

• End-user metadata

• Retail: Customer Loyalty, Market Planning

• Manufacturing: Cost Reduction, Logistics Management

• Utilities: Asset Management, Resource Mangement

• Airlines: Route profitability, Yield Management

• Single stand-alone data mart: May be use if there is no need for integration

  • Separation of OLAP solution from a single integrated OLTP system

  • Local Reporting / OLAP solution for (non-integrated) OLTP system

• Several independent stand-alone data marts: “quick and dirty solution“ (-> soon to be replaced)

• A Conformed constellation of data marts (sharing common dimensions)

  -> DW Bus Architecture

  • No “explicit“ DW -> no common schema

  • Conformed facts and dimensions

  • Mapping through Master Data stores

• A single Integrated Data Warehouse feeding all data marts

  • Master Data can be seen as just another Source DB

  • Typically 1 landing area for each Source DB + integrated Staging Area

• Multiple Data Warehouses for different functions (problematic but not uncommon)

Extraction, Transformation, Loading

• Heterogeneity of legacy systems

• Strain on operative systems from management queries

• Lack of historization in operative systems

• Requirement of complex transformation rules

1. Extract information from OLTP sources

2. Transform it

3. Load into the DWH

• Checking of Data Quality

• Serves as backup source if a process failes

• Triger-based: change in source DB -> Extraction

• Replication based

• Log-based: analysis of logs to detect changes

• Timestamp-based:

• Snapshot-based: Periodicall copy complete data sets into a file and compare snapshots to detect changes

Extracted data is prepared for the loading into the DW

• Structural preparation: schema integration

• Content preparation: Data Cleaning and Integration

1. Transformation of extracted data into a consistent format (-> data type, units, encodings, etc.)

2. Data Cleaning: Handling of missing or redundant or inconstent or outdated values

3. Data Scrubbing: Usage of domain knowledge / business rules to detect “dirty” data

4. Data Auditing: Assessment of the data quality and usage of data mining techniques to detect rules and deviations

1. Filtering

   • automatice detection, automatioc correction

   • automatic - manual

   • manual - automatic

2. Harmonization

   • Syntactic harmonization -> Consistent format

   • Business harmonization -> Business indicators

3. Aggregation

   -> Using dimensional hierarchy tables and parallel hierarchies

4. Enrichment: Calcultion of business performance indicators and storage as attributes

• Buying ETL tool that offers various services

• Building your own solution based on file technology and/ or DBMS technology

In general Data Marts support analysis of 1 or more business processes/ transactions by e.g.

• running queries that need to look at a substantial amout of data

=> Maximizing query read performance through caching and Denormalization

1. Seperate analytical databases from operational databases

2. Determine how to measure the execution of a given business process (-> KPIs) and look at the context (-> dimensions) of this business process

3. Design corresponding Star or Snowflake Schema

4. Tune using indexes other physical design options

• Measure

• Context (category)

• Time

• + aggregation & selection

• Dimension Tables: Provide “business context” and are used for categorization

  • Not normalized

• Time Dimension: Special dimension (table) time

• Fact Tables: has a grain defining most atomic unit of data being captured (e.g. transaction level: measures in certain context + daily / weekyl / monthly / quarterly summaries)

  • Attributes:

    • measures (basic or derived)

    • surrogates -> foreign key to a dimension table

    • Primary Key

• Star Schema: Database schema where a single central table (fact table) is connected to one or more denormalized dimension tables.

• Snowflake Schema: Variation of Star Schema, where dimension tables are normalized. This means that the dimension tables are broken down into multiple related tables, rather than having all of the attributes in a single table

Pros:

• Small saving in storage space

• Normalized structures are easier to update and maintain

Cons:

• Schema becomes less intuitive -> complexity

• Browsing through contents becomes more difficult

• Decreased query performance due to additional joins

F ast:

A nalysis:

S hared:

M ultidimensional

I nformation: Scalability

-> FASMI

Slicing: filter conditions in 1 or more dimensions

-> E.g. Filter after a certain year

Dicing: Adding / Removing / Exchaning dimensions

Drill-Down: Going from a broader level of aggregation to a finer more detailed level

-> add a column to group by clause

Roll-Up: Going from a finer level of level of aggregation to a broader level

-> remove column from group by clause

Ranking: e.g. top performers, top risks, etc.

• R-OLAP: Relational OLAP

• M-OLAP: Multi-Dimensional OLAP

• H-OLAP: Hybrid OLAP

Combination or R-OLAP and M-OLAP

• Vertical Partinioning:

  • M-OLAP for aggregation (Roll-ups)

  • R-OLAP for Drill-Down

• Horizontal Partioning:

  • Certain of data in M-OLAP

  • Older or less frequently used data in R-OLAP

• Horizontal Partitioning: Split table into n disjoint parts with the same schema -> Union of all parts equals original table

  • By range

  • By hash (application of hash-function)

  • Round Robin: Archive oldest partition and reuse it for the latest data

  • By reference

• Optimization for scan/ selection operations: Bitmap Indexes

• Optimization for memory hierarchy: Vertical Partinioning/ Column Stores: Divide a table into multiple tables that contain fewer columns

  • Unique Key colume is replicated for joining

Extreme case of vertical partitioning: Division of each table into a set of 2-column tables (RID, attribute)

Specialized stores that use DSM to layout data on a disk

1. Specification: Assessment of current state and requirements

2. Platform, Software and Tool Selection: Analysis and evaluation of potential alternatives

3. Realization

   1. Conceptualization

   2. Implementation

   3. Execution

4. Operation

• Scalabiltiy: -> Data Volume, Complexity of Queries

• OLAP capabilities: -> Cupe operation

• Optimization for analytical queries

• Cloud or on-premise?

1. Focus on business

2. Dimensionally structure the data that is delivered to the business

3. Iteratively develop overall data warehouse environment in manageable lifecycle increments

Project = Single iteration of the lifecycle

Program = Ongoing coordination of resources, infrastructure, …

1. Technical Architecture Design

2. Product Selection and Installation

1. Dimensional Modeling (-> Enterprise data warehous bus matrix)

2. Physical Design (Design and set up of database environment)

3. Data Integration (ETL)

1. Reporting and analytics design

2. Reporting and analytics development

1. Identify Knowledge Workers

2. Conduct Interviews

3. Identify Source Data

4. Design Data Repositories (Enterprise DW + Departmental Data Marts)

5. Design ETL

• Evolving user and business requirements and new business rules

• An architectual approach is still necessary

• Management across interdependent BI projects is critical

• Conflict with top-down, slow-moving data governance

• Not necessarily cheaper

• HTAP (Hybrid Transaction/ Analytical Processing) Systems: more informed and real time decision making

  -> Combination of OLAP and OLTP system

• Cloud DWH/ BI

• Mobile BI