LECTURER: JOHN DOE
BUSINESS INTELLIGENCE I
TOPIC OUTLINE
Motivation and Introduction 1
Data Provisioning 2
Data Warehouse 3
Modeling Multidimensional Dataspaces 4
Analysis Systems 5
TOPIC OUTLINE
Distribution and Access 6
Current and Future Business Intelligence Application Areas 7
LECTURER: JOHN DOE
BUSINESS INTELLIGENCE
UNIT 3
DATA WAREHOUSE
STUDY GOALS
− Understand how data from different operational
systems are integrated company-wide.
− Analyze the sub-steps that are necessary for this.
− Understand which components are involved in this.
EXPLAIN SIMPLY
1. Describe the difference between operational and
dispositive systems.
2. Explain the different architecture types (different
OLTP and DWH).
3. Explain why data warehouses are created in addition
to already existing transaction-oriented systems.
DEFINITION
− Extraction
− Selection of a section of the data from relevant sources
− Performed in the relevant sources
− Transformation
− Preparation and adaptation of data to specified schema and quality
requirements
− Performed in a temporary workspace (data staging area)
− Loading
− Physical transfer of data from the work area to the data warehouse
− Including any necessary aggregations
− Performed in the data warehouse database
− Complex part of data warehousing
− Multiplicity of operational sources
− Overcoming heterogeneity between data sources (DBMS, schemas, data)
− Ensuring high quality warehouse data
Source of the image: LoggaWiggler(w.d.),
PROCESS PHASES ACCORDING TO KEMPER AND BAARS
Business Intelligence and Analytics
Information Components for
Provision and − Visualisation and presentation
Distribution − Information distribution
− Information access
Components for
Information − Concept-oriented analyses
Generation − Model-oriented analyses (Advanced & Predictive Analytics)
− Report-oriented analyses (Reporting & OLAP)
− Data Query & Exploration
Data Mart
Data Provision
Core Data Warehouse Data Lake
Operational Datastore
Operative und
Social
external Systems with SCM E-Proc CRM Web Partner
Media
structured and non-
Information
structured Data CAx PPS PDM/PLM (I)IOT Open Data
Provider
Source of the image: Vaas, 2022, based on Baars & Kemper, 2021, p. 10.
OVERVIEW OF THE COMPONENTS INVOLVED: ETL PROCESS
DWH - Reference Component Model for the Architecture of Data Warehouse Systems
Data Mart
Loading Components DWH-Manager
C-Data Warehouse
Loading Components
Metadata- Manager Repository
Working Area Transformators
Extractors
Monitor
Data Source
Source of the image: Vaas, 2022, based on Bauer & Günzel, 2009, p. 36.
MONITORING
Monitoring strategies based on:
− Trigger
− Changes are written to tables or files and tracked
Monitoring
− Replication strategy
− Configuration of replication services
− Writing away changes in tables
− Timestamp Cooperative
Sources
Uncooperative
sources
− A timestamp is assigned to each record
− Contains last update time
− Logs Trigger- Replication- Access of Snapshot-
based based internal data based
− Analysis of log files
− Snapshots
− Delta calculation between snapshots Timestamp-
Log-based
based
Source of the text: Bauer & Günzel, 2009.
Source of the image: Vaas, 2022, based on Köppen et al., 2014, p. 33.
EXTRACTION
Time interval
What? − Depending on the dynamics and amount of data to be
− Complete dataset extracted
− Delta extraction − Depending on the number of running ETL jobs per
night/day (system load)
− Snapshot extraction
How? Human
− Extract files − Explicitly initiated by DBA or developer
− SQL commands − Demand-oriented (e.g., extension of a product group by a
new article)
When?
− Periodically Event
− Triggered by time/database/external events
− Query-driven
− Event-driven Immediately
− Data is transferred to the DWH directly after generation
− i.e., with near-real-time DWH
− Directly after creation in the source system
TRANSFORMATION
Filtering Harmonisation Aggregation Accumulation
Transformation components
− Work in the data cleansing area/work area
Transformation
− Read data, process it, write it back again
− Prepare data for loading Data
Homogenization Integration
Cleansing
Faulty Incomplete Redundant Old Schema Data
data data data Data Units of integration
Transformations data
types measurement Codings integration
− Homogenisation
− Unification of data types, encodings, etc.
− Integration of data from multiple sources
− Schema integration
− Data integration/instance integration
− Data cleaning/data cleansing
− Elimination of incorrect/missing/obsolete values, redundancies
− Extension cleansing
− Data scrubbing: applying domain knowledge to eliminate data errors
− Data auditing: Detecting rules or deviations with the help of data mining
Source of the text: Köppen et al., 2014, p. 28.
Source of the image: Vaas, 2023, based on Köppne et al., p. 28
TRANSFORMATION - CLEANSING
Polluted data
Dimensions of Data Quality
− Correctness − Missing data
− Syntactic and semantic correctness − Not missing but wrong data
− Completeness − Not missing, not wrong but unusable
− Value does not exist data
− Value exists but is unknown
− Unknown whether the value exists Data Cleaning Process
− Consistency
− Integrity conditions Data Analysis/Data Auditing
− Intra-relational conditions (cited scientific paper must have been
published before the citing paper)
Workflow Specification
− Timeliness
− Update rate:
How often must data be checked and changed if necessary? Workflow Execution
− Change periods:
Rate can change depending on different time periods(e.g., Post-processing/Control
seasonally dependent)
Source of the text: Kim et al., 2003, S. 81-99; Freytag & Muller, 2003.
Sourc Picture: compare Freytag & Müller, 2003, p. 11.
TRANSFORMATION - HOMOGENISATION
Character → Date
− Adaptation of data types „24. Dezember 21“ → 24.12.2021
Character → Number Plain Text → Coding
„ 815 “ → 815 „male“, „female“, „divers“
− Conversion of encodings → 1, 2, 3
Coding A → Coding B
Without domain knowledge
„m“, „f“, „d“→ 1, 2, 3
"Customer", "Client" → "Customer"
− Unification of character strings With domain knowledge (i.e. lists of
permissible first names)
"Henri Schmidt" → "Schmidt, Henri"
"Schmidt Henri" → "Schmidt, Henri" Formate A → Formate B
"Kurt Frank" → ? MM-TT-JJJJ → TT-MM-JJJJ
Timezone A → Timezone B
− Standardization of dates 30 mph → 48,279 km/h „11.11.2011 09:11:23“
100 inch → 2,54 m → „11.11.2011 11:11:23“
560 oz. → 15,8757 kg
− Conversion of units of measurement, currencies 87.029 € → 98307,96 $
− Separation/combination of strings Designation = „Smarties 250 g“
→ Weight in gramms= „250“, Product = „Oreos“
Price = Price_exkl_VAT * Value Added Tax Rate
− Calculation of derived values 329,00 = 276,47 *1,19
Source of the text: (Jordan, C., Schnider, D., Wehner, J. & Welker, P. (2011))
Source of examples: Vaas, S., 2022
TRANSFORMATION - DATA INTEGRATION
Merging data from multiple sources
Key treatments
Source Table Attribute Local Key Global artificial Key/
Surrogate Key
System 1 Product Product_ID 4711 50
System 1 Product Product_ID 3939 51
System 1 Product Product_ID 4567 52
System 2 Product Product Key A789 52
System 2 Product Product Key B234 50
System 2 Product Product Key C742 53
ource Image: Leitner, 2008, p. 15.
Source of the table: Vaas, 2023
TRANSFORMATION – SCHEMA INTEGRATION
Source of the image: Vaas, S., 2022
LOADING
Master Data
Loading Strategies for Master Data
− Only when loading for the first time (or after deleting
data)
Initial Loading − Mostly special case of incremental loading
ETL must ensure logic for delta determination in DWH
Full
Extraction Incremental Loading
− Delta is loaded completely
− Delta determination via snapshot date
Stage/ Snapshot Loading − Unchanged data is saved again
Working DWH − Leads to large amounts of data
− Only useful at long intervals (e.g. monthly)
Area
Delta − Even if the source system can version master data,
Extraction delta determination through ETL makes sense
Incremental Loading
− Even if the source system can version master data,
Snapshot delta determination through ETL makes sense
Extraction − Snapshot does not provide complete versioning
Source Text: Vaas, 2022, based on Jordan et al., 2011, p. 78.
LOADING
Loading Strategies for Motion Data Motion Data
− Only when loading for the first time (or after deleting
Full Initial Loading data)
Extraction − Mostly special case of incremental loading
− All supplied transaction data is inserted
Delta Stage/ − Typically for flows such as transactions, bookings,
Extraction Working DWH etc.
Area
Incremental Loading − All supplied transaction data is inserted
Snapshot − Typically for stock levels such as account balances,
Extraction stock levels, employee balances, etc.
Qsource Text: Vaas, 2022, Jordan et al., 2011, p. 78.
METADATA
Usage Categories in the DWH:
− Passive Data (Usage)
− Comprehensive and consistent documentation for the structure, development and use of the DWH system.
− Target group are all persons involved in data warehousing, from the developer to the end user
− Comparable with a help system
− Active Data (Usage)
− Semantic descriptions of components of the DWH system (i.e., transformation rules).
− Interpretation of data at runtime and for control
− Semi-active Data (Usage)
− Static data (i.e. structure definition, configurations)
− Reading of metadata at runtime
− No use for control
Source Text: Melchert et al., 2002.
SUMMARY
− The following components are involved in the ETL process: DWH
manager, metadata manager, monitors, extractors, transformation
components, and loading components.
− Monitoring strategies are based on the degree of cooperation between
the source systems.
− There are different extraction types and loading strategies.
REVIEW STUDYGOALS
− Understand how data from different operational systems are
integrated company-wide.
− Analyze the sub-steps that are necessary for this.
− Understand which components are involved in this.
SESSION 3
TRANSFER TASK
TRANSFER TASK
After your studies, you have taken a job as a data analyst at NASA.
Your first assignment is to analyze data in support of asteroid
research.
The division manager hands you a file that is to be examined,
cleaned, and transformed so that it can be easily read into the
DWH of the research division.
OPENREFINE
OpenRefine
− was originally developed by Google (Google Refine) in 2010
− As a pure web-application
− released under an Apache 2.0 license in 2012 and discontinued
− Since 2012 the project was maintained and developed by a
community of volunteers
− is available as a desktop application
− is an open-source tool
− benefits from contributions from a large community of
developers
− extensions and plugins can be used to extend the tool's
functionality
Source Software: Huynh et al., 2012.
Source of the Screenshot of the webpage and the image below: [Link]
TRANSFER TASK
1. Download the following files:
Software openrefine-3.7.2/3.5.2 (for Windows, Mac OS, Linux)
2. If you do not have Java installed on your computer, you can do
this by downloading and installing
for Windows: Java-Downloads for Windows or
for Mac: Java for Mac.
Please make a backup of your directory before installing the software
3. Download the marked files,
follow the quickstart file.
4. Explore the User Manual
[Link]
Source Software: Huynh et al., 2012.
Source of the Screenshot of the webpage and the image below: [Link]
TRANSFER TASKS
General Conditions:
− 45-60 minutes time
− Individual work
− After completion, take a screenshot of your results and upload it to the whiteboard of the event
− Afterwards, present the results in plenary
Have fun!
TRANSFER TASK
PRESENTATION OF THE RESULTS
Please present your
results.
The results will be
discussed in plenary.
LEARNING CONTROL QUESTIONS
True or False?
1. The two transformation steps aggregation and enrichment are responsible for the cleansing and
standardization of data within the ETL process (e.g. alignment of different codings, currencies).
2. The basic database is a data store that is located between the staging area and the evaluation
database. The C-DWH serves the purpose of making company-wide, integrated data available to
downstream systems.
3. In principle, transformation rules for ETL processes can be stored in a database or as metadata.
This type of metadata represents quasi-methods, which are executed accordingly and are
therefore also referred to as "active metadata".
LMS QUESTIONS
What what activities are addressed in data scrubbing?
A. Automatic format changing in large amounts
B. Mapping different formats to a consolidated format
C. Applying domain knowledge to eliminate data errors
D. Automatic filtering in large amounts
LIST OF SOURCES
Ahmed, I. (2022). ETL – was es bedeutet und warum es wichtig ist. [Link]
Bauer, A., & Günzel, H. (2009). Data Warehause Systeme. Architektur, Entwicklung, Anwendung [Data Warehouse System. Architecture, development, application]. dpunkt Verlag.
Freytag, J.-C. & Müller, H. (2003). Problems, methods, and challenges in comprehensive data cleansing. Humboldt-Universität. [Link]
Huynh, D., Google & Metaweb Technologies, Inc. (2012). [Link]
Jordan, C., Schnider, D., Wehner, J. & Welker, P. (2011). Data Warehousing mit Oracle: Business Intelligence in der Praxis [Data warehousing with Oracle: Business intelligence in the practice]. Hanser. ISBN: 978-
4446-42562-0
Kim, W. Y., Choi, B.-J., Hong, E. K., Kim, S.-K. & Lee, D. (2003). A taxonomy of dirty data. Data Mining and Knowledge Discovery, 7(1), 81–99. [Link]
Köppen, V., Saake, G. & Sattler, K.-U. (2014). Data Warehouse Technologien [Data warehouse technologies]. Mitp Verlag.
Leitner, P. (2008). Datenqualitätsanalyse beim Ladevorgang in Data Warehouses am Beispiel der ETL-Prozesse der OÖGKK [Data quality analysis during the loading process in data warehouses using the example of the
ETL processes of OÖGKK]. Johannes Kepler Universität Linz.
LoggaWiggler (w.d.). Druckwasserleitung, Rohr, Pipeline. In: Pixabay, open licence: [Link]
Melchert, F., Auth, G. & Herrmann, C. (2002). Integriertes Metadatenmanagement für das Data Warehousing. Grundlagen, Nutzenpotenziale, Architektur [Integrated metadata management for data warehousing:
Basics, potential benefits, architecture]. Universität St. Gallen.
Das Wirtschaftslexikon (o. D.). Multidimensionale Datenstruktur eines Data Warehouse. [Link] (zitiert nach: Bauer & Günzel, 2001).
Yadav, S. (2016). Extraction in SAP BI. [Link] 03/ 15/ extraction-in-sap-bi/
© 2022 IU Internationale Hochschule GmbH
This content is protected by copyright. All rights reserved.
This content may not be reproduced and/or electronically edited, duplicated, or distributed in any kind of
form without written permission by the IU Internationale Hochschule GmbH.