Business Intelligence Notes

Signposts Throughout the Course Book

The course book contains core content; additional materials are on the learning platform.
Content is divided into units and sections, each with one new key concept.
Self-check questions are provided at the end of each section.
Knowledge tests on the learning platform must be completed for modules with a final exam (80% correct).
Course is finished once knowledge tests are passed; evaluation must be completed before registering for final assessment.

Basic Reading

Grossmann, W., & Rinderle-Ma, S. (2015). Fundamentals of business intelligence. Springer.
Kolb, J. (2013). Business intelligence in plain language: A practical guide to data mining and business analytics. Createspace.
Sharda, R., Delen, D., & Turban, E. (2014). Business intelligence and analytics: Systems for decision support. Pearson.
Sherman, R. (2014). Business intelligence guidebook: From data integration to analytics. Morgan Kaufmann.
Vaisman, A., & Zimányi, E. (2016). Data warehouse systems: Design and implementation. Springer.

Required Reading

Unit 1: Simon, A. (2014). Modern enterprise business intelligence and data management. Elsevier MK. Chapter 5.
Unit 2: Clegg, D. (2015). Evolving data warehouse and BI architectures: The big data challenge. TDWI Business Intelligence Journal, 20(1), 19—24.
Unit 3: Ankorion, I. (2005). Change data capture: Efficient ETL for real-time BI. DM Review, 15(1), 36—43.
Unit 4: Kimball, R. (2008). Slowly changing dimensions, types 2 and 3. DM Review, 18(10), 19—38.
Unit 5: Abkay, S. (2015). How big data applications are revolutionizing decision making. Business Intelligence Journal, 20(1), 25—29.
Unit 6: Gangadharan, G. R., & Swami, S. N. (2004). Business intelligence systems: Design and implementation strategies. 26th international conference, information technology interfaces (ITI 2004) (pp. 139—144). The University of Zagreb Computing Centre (SRCE).

Learning Objectives

Business intelligence (BI) extracts information from company data for informed management and optimized business activities.
The course covers BI techniques, procedures, and models for data provision, information generation, analysis, and distribution.
At the end of the course, students will be able to:
- Explain data warehousing aspects.
- Independently select BI methods and techniques.
- Design and prototype business intelligence applications based on requirements.

Unit 1: Motivation and Introduction

Study Goals:
- Define business intelligence (BI).
- Understand the development of business intelligence over time.
- Identify data warehouse characteristics.
- Define business intelligence in practical terms.
Introduction
- Globalization and dynamization of markets drive companies to seek information advantages.
- Information is a managerial resource of strategic and tactical importance.
- Effective information supply enhances corporate decision-making.
- Business intelligence integrates strategies, processes, and technologies to generate knowledge about a company's fragmented divisions.
- Market and competitor data are combined with internal data in decision support systems.
- The data warehouse (DWH) is a key component within business intelligence.
1.1 Motivation and Historical Development
- Business intelligence evolved from the 1960s through various systems supporting managerial decision-making.
- Early systems: Management Information System (MIS) introduced in the 1960s.
  - Goal: Provide managers with necessary information for decision-making, optimizing time, content, and presentation.
  - Limitations: Technology limitations hindered meeting these goals fully.
- Decision Support System (DSS) replaced MIS in the mid-1970s.
  - Enabled individual analyses of information through interactive electronic data processing (EDP) systems.
    - Advances in hardware allowed more efficient information processing and laid the basis for data-based decision support.
  - Limitations: Did not fully meet expectations; analysis was limited to parts of the company and used only operational data.
  - Managers often distrusted computers for creative decision-making.
- Executive Information System (EIS) emerged in the mid-1980s with the rise of PCs.
  - Target users: Upper management and controlling functions.
    - Comprised individual systems presenting decision-relevant, multidimensional data in an improved way.
  - Easier to implement than MIS/DSS due to PC adoption; MIS/DSS required central computers.
  - Limitations: Individual systems limited use to single departments/sites; changes were expensive; lacked end-user acceptance.
- Data Warehouse (DWH) gained acceptance with globalization in the 1990s.
  - Managers became dependent on available information due to global operations.
  - Decentralization required prompt, local decisions with up-to-date information.
  - Companies faced a flood of data from internationalization.
  - Previous systems (MIS, DSS, EIS) could not meet these new demands.
  - Need for a complete, uniform, consistent database to manage inconsistent data sources.
  - DWH emerged as a central database integrating data from different company systems.
- BI analysis tools influenced the development of data warehouses in the 1990s.
- BI is now mostly a generic term.
1.2 Business Intelligence as a Framework
- Companies face increasing data and insufficient useful information.
- Effective information supply to management is a key competitive factor.
- Business intelligence aims to ensure the right information is delivered in the right quantity, place, and time.
- Data warehouse improves operational information logistics and delivers timely information to management.
- Features of a DWH
  - W. H. Inmon: "A data warehouse is a subject-oriented, integrated, nonvolatile, time-variant collection of data in support of management’s decisions."
  - Four Basic Characteristics:
    - Subject-oriented (theme-focused): Data organized according to profession/business criteria.
    - Integrated (unified): Data integrated from heterogeneous sources, standardized in structure and format.
      - Nonvolatile (persistent): Permanent data storage; data are not changed or deleted.
    - Time-variant (historicization): Time series analyses possible; data stored as it existed at specific points in time.
- Definitions
  - Various DWH and BI definitions exist; interpretations summarized below.
  - DWH
    - Data warehouse (DWH) in the narrower sense: Purely data collection.
    - Data warehouse (DWH) broader sense: Includes connecting, extracting, and transforming external data; analysis and presentation with tools.
  - BI
    - Competitive advantages come from creative and intelligent use of data.
    - BI is the use of knowledge across the company.
      - Gartner Group: "Business intelligence is the process of transforming data into information and, through discovery, into knowledge."
    - BI involves techniques and applications to support decision-making.
  - Classification of BI:
    - BI in the narrower sense: Core applications supporting decision-making without advanced methods (OLAP, MIS, EIS).
    - Analysis-oriented BI: Applications for decision-makers to analyze data directly using a user interface (OLAP, MIS, EIS, text mining, data mining, ad hoc reporting).
    - Business intelligence (BI) in the broader sense: All applications used directly or indirectly for decision-making, including evaluation, presentation, data preparation, and storage.
Summary of Unit 1
- Companies use business intelligence to gain competitive advantages through information.
- Information is a strategic managerial resource.
- Business intelligence historical development goes back to the 1960s through various systems (BI, DWH, EIS, DSS, and MIS).
- DWH characteristics: Subject-oriented, integrated, nonvolatile, and time-variant data.
- Definitions characterize DWH/BI in narrower and broader senses.
- BI (broader sense) includes all applications supporting decision-making directly (OLAP) or indirectly (data extraction).

Unit 2: Data Provisioning

Study Goals
- Differentiate between operational and dispositive systems.
- Describe typical BI reference architecture.
- Identify basic BI components.
- Recognize possible architecture variants.
Introduction
- Business intelligence (BI) involves systematically analyzing electronic data for better decisions.
- Preparation and storage of consistent data are essential for using BI tools.
- Data warehouses (DWH) are realized via specialized database systems optimized for complex queries.
- Data is commonly stored in tables based on rational database systems.
2.1 Operational and Dispositive Systems
- Erich Gutenberg classified firm activities as operational and dispositive.
- Operational: Relates directly to the provision/utilization of goods/services and financial tasks.
- Dispositive: Relates to managing and controlling operational processes.
- Application systems and data are distinguished as either operational or dispositive.
- Operational Systems: Capture and record data.
- Dispositive Systems: Analyze data.
- OLTP and OLAP: Operational systems store information for daily operations (e.g., customer database, employee directory).
- Information is frequently changed and queried.
- Only current data records are relevant.
- Data models are optimized for high transaction numbers.
- Processing method: Online Transactional Processing (OLTP).
- Dispositive Systems
  - Used to extract information from operational data (e.g., customer relocation analysis).
  - Relieve operational systems of analytical queries.
  - Operational systems have many users with read requests for individual records.
  - Dispositive systems have fewer users, typically experts doing sophisticated queries on large datasets.
  - Processing method: Online Analytical Processing (OLAP).
- Operational and Dispositive Data
  - Data warehouse is a replication of data, but technical necessity occurs from different views of the data: operational and dispositive.
  - Operational data: Directly related to company's service provision.
  - Dispositive Data: Analytical nature used to manage and control the company.
  - Table 1: Characteristics of Operational and Dispositive Data. Comparision:
    - Objective:
      - Operational data handles business processes.
      - Dispositive data provides information for management and decision support.
    - Alignment:
      - Operational data is detailed and granular.
        Dispositive data is mostly condensed and transformed.
    - Time Frame:
      - Operational data is up-to-date and transaction-oriented.
      - Dispositive data is task-dependent.
    - Modeling:
      - Operational data often lacks modeling.
      - Dispositive data is topic-related, standardized, and suitable for end-users.
    - Status:
      - Operational data is often redundant and inconsistent.
        Dispositive data is consistently modeled and controlled for redundancy.
    - Update:
      - Operational data is continuously updated.
      - Dispositive data is complementary.
    - Queries:
      - Operational data has structured, static queries.
      - Dispositive data has ad-hoc queries for complex questions.
  - Examples:
    - Operational Data in Insurance Company: detailed insurance contracts, continuous data changes, storage of contracts in different systems.
    - Dispositive Data Examples: summaries of sales and profits, temporal changes, and comparison of product lines.
  - Direct evaluations based on operational data may not meet planning requirements, and heterogeneous systems make it hard to compare information.
  - Concurrency of queries and transactions on operational data can block the entire system.
2.2 The Data Warehouse Concept
- Data warehouse includes process phases, architectures, and BI components.
- Process Phases: Merge data and information, analyze data using OLAP and data mining, and communicate findings.
- Reference Architecture: Template for designing the collection and storage of data.
- Process Phases
  - Data Provision: Merge data from heterogeneous sources (SCM, ERP, CRM).
    - Information Generation, Storage, and Distribution: Analyze data using OLAP and data mining, generate warnings.
  - Information Access: Communicate findings to the company.
- BI Components
  - Source Systems: Heterogeneous sources with different structures and access interfaces (OLTP, websites, text files), internal and external data (ERP, market prices).
  - Staging Area: A work area in which data is temporarily stored to relieve downstream systems.
    - Operational Data Store (ODS): Doesn't have aggregated data or longer history and often a preliminary stage for supplying data.
  - Basic Database (Core Data Warehouse): Central database, data made available for various evaluation purposes.
  - Evaluation Database (Data Mart): For downstream analysis tools, stored with help of multidimensional model.
  - Extracting, Transforming, and Loading (ETL) Process: Transfer data from source systems to staging area via extraction step.
    - Transformation affects structure and content.
    - Plausibility checks improve data quality.
    - Data transferred to evaluation after cleaning.
  - Aggregation: Data are aggregated if they are required at a lower granularity.
    - Front End: Analysis tools for data mining and OLAP. Tools for data mining/OLAP and portal systems.
2.3 Architecture Variants
- Architecture Variants Include:
  - Independent data marts.
  - Data marts with coordinated data models.
  - Central core data warehouse (C-DWH).
  - Several C-DWHs.
  - C-DWH and dependent data marts.
  - DWH architecture mix.
- Independent Data Marts: Individual departments building DWH independently.
  - Reduces complexity but makes company-wide DWH difficult.
- Data Marts with Coordinated Data Models: Data marts coordinate with a common data model.
  - Ensures consistency and integrity.
- Central C-DWH: Only uses a core data warehouse, useful for smaller BI solutions.
- Multiple C-DWHs: Useful for large, division-oriented companies.
- C-DWH and Dependent Data Marts: Core data warehouse expanded with data marts, supplied through transformation.
  - Faster response times because data has fewer data extracts from C-DWH.
  - Structure is a hub-spoke architecture.
- DWH Architecture Mix: Uses C-DWHs, dependent and independent data marts, and direct data access.
Summary of Unit 2
- Application systems can be operational or dispositive per Gutenberg’s criteria.
- Operational systems store info; data warehouse extracts information from operational data.
- BI process phases: data provision, information generation, storage, distribution, and information access.
- BI architecture components: source systems, staging area, ODS, C-DWH, data mart, ETL, aggregation, and front-end.
- Vast amount of data warehouse or data mart architecture variants exist.

Unit 3: Data Warehouse

Study Goals
- Learn how data from different operational systems are integrated company-wide.
- Understand transformation steps necessary.
- Distinguish a C-DWH from data mart architecture.
- Identify functions offered by an operational data store.
- Explain how metadata can support business intelligence.
Introduction
- Before business intelligence (BI)-relevant data is available in the data warehouse, data from operational systems are transformed into a business management perspective.
- Data is consolidated and stored using the extracting, transforming, and loading (ETL) process, cleaning, transforming, and preparing it.
- After extraction, data is prepared via filtering, harmonization, aggregation, and enrichment, then loaded into the DWH evaluation level.
3.1 ETL Process
- The extracting, transforming, and loading (ETL) process converts operational data into management-relevant information.
- Three steps: Extract, Transform, and Load.
- Typically takes place at periodic intervals and consists of following steps:
  1. Extraction of relevant data from various sources.
  2. Transformation of data into a uniform multidimensional format.
  3. Loading of data into the data warehouse to be available for analysis.
- Establishing the ETL process is the most complex step in data warehouse development.
- The ETL process is of central importance as the creation of a solid DWH is only possible if it contains high-quality data.
- ETL processes can be individually programmed or developed with the help of various tools.
- Due to ETL processes’ complexity, use of such tools recommended in most cases.
- Transformation Process
  - In the Transformation components, consists of four sub-processes: filtering, harmonization, aggregation, and enrichment.
  - Individual components, filtering and harmonization, are responsible for cleansing and preparing data, e.g., aligning different codes and currencies.
  - Aggregation and enrichment steps summarize data according to topic, adding business key figures.
  - Transformation 1: Filtering: The data required for the DWH are selected, temporarily stored, and freed from defects. Extraction and cleansing are what filtering is divided into here.
    - Class 1: Adjustment- Automatic detection and correction ( Syntactic defects- Known format adjustment; semantic defects- Missing data values)
    - Class 2: Adjustment- Automatic detection and manual correction ( Syntactic defects- Recognizable format incompatibilities; semantic defects- Outlier values/inconsistent value constellaions)
    - Class 3: Adjustment- Manual detection and manual correction (Syntactic defects- None; semantic defects- Undetected sematic errors in source data)
  - Transformation 2: Harmonization:
    - Harmonization, is also know as normalization, refers to the process of reconciling filtered data. This is a must if the data is from different source systems, and different keys or characeristics are often used for the same facts or properties.
  - Transformation 3: Aggregation:
    - Filtered and harmonized data are condensed and converted to desired granularity.
  - Transformation 4: Enrichment: Creation and storage of key business figures from filtered and harmonized data.
3.2 DWH and Data-Mart Concepts
- The data storage includes:
  - Staging Area
    - Extracts are stored temporarily in order to relieve down-stream systems. Transformations are performed in the staging area and the data is then deleted. Purpose is to relieve down-stream systems
  - Basic database
    - Contains integrated data for downstream systems; provides detailed, historicized, consistent, and normalized data.
  - Evaluation database
    - Contains sections of the C-DWH. In order to simplify data handling, that’s why evaluation data-bases are introduced.
  - Update strategy
    - Core data warehouse is updated according using the BI system. 3 types of updates: changes based, periodic intervals, real-time.
3.3 ODS and Meta-Data
- Operational data store (ODS):
  - This is a preliminary stage of the DWH to a preliminary stage of a DWH, that contains current transaction data for evaluation purposes.
  - Features: Subject-oriented, integrated, time referenced, volatile, and high level of detail
  - Transformation usually involves filtering and harmonization
- Metadata:
  - Contains information about the data stored in the data warehouse including how they have been processed. Metadata supports the construction, administration, and operation of DWH systems.
    - Passive vs active: These different in a variety of ways. PAssive metadata they document data and their relationship to the enviroment. Active metadata represents methods that are executed on date.
  - Technical vs Business: Technical focuses on filtering; Business focuses on harmonization, aggregation, enrichment, and authorization management.
Summary of Unit 3
- ETL process cleanses and transforms operational data, then publishes them for analysis.
- DWH data storage components include staging area, basic database, data mart, ODS, and metadata.
- Staging area temporarily stores data, relieving downstream systems.
- C-DWH shares company-wide data.
- Data marts supply for user groups. Metadata describe the data for operating DWHs.

Unit 4: Modeling Multidimensional Dataspaces

Study Goals
- Learn what basic modeling techniques exist.
- Understand which analysis possibilities are offered by OLAP cubes.
- Explain how multidimensional models are physically stored.
- Recognize which options are available for historicizing dimensions.
Introduction
- Provides flexible views of data, depending on users. For this a multi-dimensional model is more flexible than a relational data model. In multi-dimensional models, enter-prise data is arranged in a multi-dimensional data space
4.1 Data Modeling
- Data Models can be semantically, logically, or physically oriented and the best-known semantic data models, is the entity relationship model (ERM).
- In practice relational databases are not always created using a model, but however, redundancy compromises the consistency of the database and can lead to anomalies.
  - To pre-vent anomalies, a number of rules and principles apply, know as Normalization of data/databases.
4.2 OLAP Cubes
- OLAP cubes is on business ratios as carriers with quantitative information, which are described by corresponding sets of dimensions.
  - Each dimension is explained by a set of attributes.
- Facts refer to figures, and Dimensions refer to the elements of a dimension grouped into hierarchies.
  - Various operation include: Roll-Up and Drill-Down, Slice and Dice, Drill-Across, Pivoting, and Rotation.
    - Slice: Allows you to view data across a single plane.
      - Dice: Allows you to view individual sub-cubes.
    - Pivoting:Involves rotating the cube to view data from diffrent perspectives.
4.3 Physical Storage Concepts
- Relational storage model (ROLAP):
  - Relational storage concepts, is where multi-dimensional data models are converted into relational storage concepts.
- Multidimensional storage model (MOLAP):
  - Physical storage of data takes place in a multidimensional database management system.
- Hybrid storage model (HOLAP):
  - Combines the strengths of the relational and multidimensional concepts.
4.4 Star Schema and Snowflake Schema
- Star schema:
  - Dimensions are arranged in a star shape around the fact table- relationships exist betweeen the fact table and the dimension table, not between the dimension tables.
- Snowflake schema:
  - Is normalized with respect to functional dependencies. IN this schema, a fact table is surrounded by a dimension tables.
4.5 Historicization
- Slowly changing dimensions (SCD):
- Concepts for the historicization of dimensions. 3 types according to Kimball: Type 1: overwrites the data record, Type 2- validity interval is added to each data record, Type 3- data record is extended by a attribute with the new name.
Summary of Unit 4
* Users usually have different interests in the data generated via business intelligence.
Flexible views are therefore necessary.
Multidimensional data models can be used to arrange data in a multidimensional data space.
The focus of the OLAP cube model is usually on business ratios as carriers of quantitative information, which are described by corresponding sets of dimensions.
There are a number of different concepts (ROLAP, MOLAP, and HOLAP) that exist for the storage of data in a DWH.
In the physical implementation of a multidimensional data model, a distinction can be made between two different database structures: the star schema and the snowflake schema.
The historicization of dimensions, also known as “slowly changing dimensions (SCD),” is a regularly occurring challenge in the DWH context.
Slowly changing dimensions can be processed via three different ways of recording changes in dimension tables.

Unit 5: Analytical Systems

Study Goals
- How analysis systems or front-ends can be classified
- What is meant by free data research and ad hoc analysis systems
- Which reporting systems are used in business intelligence
- Which model-based and concept-oriented analysis systems exist.
5.1 Free Data Research and OLAP
- Free data research:
  - Data is retrieved with using a data manipulation language, DM, (very often with structured query) but however, for technical adept as it may not be very popular with a wide audience.
- The term online analytical processing (OLAP) includes methods and technologies: allowing for ad-hoc analysis on the basis of multidimensional information.
  - British mathematician Edgar F. Codd came up with 12 rules to characterize the capability of IT system, categorized into 4 groups: general request, requirement for report generation, dimensional request, technological request.
5.2 Reporting Systems
- Reporting systems present users with a clear and simple evaluation of company data
  - Graphic-based user interfaces as well as drag-and-drop operations support the analyst to create reports.
Scorecards and Dashboards:
- KPI are business drivers, these typically include key performance indicator. Scorecards gives users a snapshot of decision-relevant data, while Dashboards: visualizes the key performance indicators, metrics, and other key data points, but commonly are used synonymously.
  - Management information systems (MIS):Are report-oriented analysis systems that focus on planning ,management, and control of the operational value chain. While MIS can provide a vast array of useful resources Executive Information Systems (EIS):are company specific.
5.3 Model-Based Analysis Systems
- Complex evaluations require model-based analysis system that have a strong algorithmic or rule based orientation. (Ex: decision support system, expert systems, and data mining).
Expert Systems (XPS): * Expert system is an information system that makes specialist knowledge available in a limited area of application and has three main-components: a knowledge-base, an interference engine, and an explanatory inference.
- Data mining:
  - Software-supported determination of previously unknown correlations, patterns and trends evident in large data-base: to help point to what to look for.
5.4 Concept-Oriented Systems
- Systems used when companies look at specific business concepts or procedures: to help give perspective of where the company values itself.
  - The balanced scorecard (BSC) is a specific form of business scorecard
Summary of Unit 5
* Analysis system differentiated into:
* Free data queires
* Ad-Hoc analyses
* Reporting system
* Model-based
* Concept oriented

Unit 6: Distribution and Access

Study Goals
- How knowledge is generated from business intelligence content
- Which support systems and formats are used for the distribution of information.
- How portals can be used to facilitate access to information
6.1 Distribution of Information
- Implementation of BI solutions gives knowledge, but the important role is where decisions are acted upon to help show the benefit to that knowledge. This ensures the long-term of adequate of information relating to everyone within the company.
Knowledge Management:
- Enables companies to document operational knowledge and make is available to relevant employees. It uses implicit knowledge, knowledge stored in minds of employees and what cannot be stored electronically due to its complexity.
  - The exchange of implicit knowledge is primarily possible through interpersonal communication. While Content management systems (CMS) and document management systems (DMS) are designed for handling unstructured data and are used to manage codified knowledge.
6.2 Access to Information
- It should have integration performance along with personalizatin to help give user oriented tools.
  *The implementation of solutions is simplified by open, internet-compatible formats for the transmission and conversion of data.
With portals these are central web application, where by companies can offer centralized structured information. With integrating web based analysis system, gives structured accessed for users. Technical, a Bl portal is com-posed of several other parts called portlets.
* Personalization, this allows content to be offered in a user oriented way

Business Intelligence Notes

Signposts Throughout the Course Book

Basic Reading

Required Reading

Further Reading

Learning Objectives

Unit 1: Motivation and Introduction

Unit 2: Data Provisioning

Unit 3: Data Warehouse

Unit 4: Modeling Multidimensional Dataspaces

Unit 5: Analytical Systems

Unit 6: Distribution and Access