Business Intelligence, Data Warehousing, and Data Mining Fundamentals

Module Overview and Learning Outcomes

  • Learning Outcomes:

    • Develop a systematic understanding of common machine learning and data mining techniques used to manipulate data.
    • Critically evaluate, select, and deploy appropriate techniques to develop Business Intelligence (BI) applications for a range of purposes.
    • Demonstrate an appreciation of current limitations of various tools used to manipulate data when developing data-intensive business applications.
    • Critically evaluate Big Data technologies and develop business solutions using appropriate Big Data tools.

  • Core Curriculum & Level 6 Requirements:

    • Module topics include:
    • Introduction to Data Mining
    • Cross Industry Standard Process for Data Mining (CRISP-DM)
    • Supervised and unsupervised learning
    • Data mining techniques: classification, clustering, association analysis
    • Practical applications of data mining
    • Big Data technologies
    • Level 6 study expectations require a high degree of independent learning.

  • Key Resources & Software:

    • RapidMiner Studio:
    • Accessible via AppsAnywhere.
    • Free educational license obtainable at https://rapidminer.com/educational-program/.
    • RapidMiner Academy tutorials and videos linked in the Blackboard 'Additional Resources' section.
    • Required & Recommended Reading:
    • Matthew North, Data Mining for the Masses (Online book available in Blackboard 'Additional Resources').
    • Sharda, Delen, & Turban, Business Intelligence, Analytics and Data Science: A Managerial Perspective (Available in Reading List).

  • Assessment Portfolio Structure:

    • Practical Exercise (40% of overall mark):
    • Due in-class during Week 6 and Week 10.
    • Group Project Presentation (60% of overall mark):
    • Presentations conducted in-class during Week 13.

  • Group Project Specifications (Portfolio Part 1):

    • Task: Investigation of BI/Data Mining for a tutor-assigned sector.
    • Deliverable: A 20-minute presentation backed by structured slides containing extensive research notes and background context, designed for a domain manager/executive with limited technical knowledge.
    • Content Requirements: Overview of the domain, rigorous academic evidence, case studies of data mining in action across multiple techniques covered in the module, current 'state of the art', and future directions of data mining in the given domain.

  • Project Management via Scrum Framework:

    • Managed using Scrum to provide hands-on experience with Agile methodologies.
    • Each group must maintain a dedicated Blackboard area with documentation for Sprints, Backlogs, and completed tasks.
    • Scrum ceremonies (Sprint Planning, Stand-ups, Reviews, Retrospectives) require documented meeting minutes on Blackboard.
    • The second half of the weekly 'Apply' session is dedicated to Scrum meetings and project progression.

  • Peer Assessment & Evaluation Rules:

    • Peer evaluation forms submitted alongside presentation slides.
    • Evaluation factors:
    • Group assessment criteria established in Week 1 and posted to Blackboard.
    • Evidence of contributions on Sprint Backlogs and task postings.
    • Compulsory attendance at all Sprint ceremonies (non-attendance results in mark deductions).
    • Absence or failure to make a meaningful contribution results in a grade of zero for the individual.
    • Peer assessment scores can moderate the final group mark upwards by one grade tier or downwards to zero (the tutor retains final discretion).

  • Assigned Domain Topics:

    • Education
    • Agriculture
    • Government
    • Travel and Tourism
    • Health/Medicine
    • Sports
    • Arts/Entertainment
    • Engineering/Manufacturing
    • The Environment

Business Intelligence and Analytics Foundations

  • Drivers in the Complex Business Environment:

    • Markets:
    • Strong competitive pressures.
    • Expanding global market reach.
    • Deployment of innovative marketing methods.
    • Demand for 24/7, real-time, on-demand transactions.
    • Consumer Demand:
    • High demand for product customisation.
    • Expectation for high quality and diverse offerings.
    • Requirement for instant product/service delivery.
    • Shift toward more empowered and less loyal customers.
    • Technology:
    • Rapid pace of innovation with new products and services.
    • Accelerating product and technology obsolescence rates.
    • Growing problem of information overload.
    • Emergence of social networking, Web 2.0, and advanced web technologies.
    • Societal Forces:
    • Expanding government regulation alongside strategic deregulation.
    • Heightened security concerns regarding global terrorism.
    • Stringent data security concerns and regulations (e.g., General Data Protection Regulation - GDPR).
    • Elevated corporate societal responsibilities.
    • Greater operational emphasis on sustainability and Green environmental issues.

  • Primary Business Drivers for Analytics:

  | Driver | Examples |   | :--- | :--- |   | Desire to optimise business operations | Sales, pricing, profitability, efficiency |   | Desire to identify business risk | Customer churn, fraud |   | Predict new business opportunities | Upsell, cross-sell, targeting new customers |   | Comply with laws or regulatory requirements | Anti-money laundering (AML), GDPR |

  • Evolution Timeline of Decision Support Systems (DSS), BI, and Analytics:
    • 1970s (Decision Support Systems):
    • Focus on Routine Reporting, AI / Expert Systems, and early Decision Support Systems.
    • 1980s (Enterprise / Executive IS):
    • Emergence of Enterprise Resource Planning (ERP), On-demand Static Reporting, and Relational Database Management Systems (RDBMS).
    • 1990s (Executive Information Systems & Data Warehousing):
    • Growth of Executive Information Systems (EIS), Dashboards & Scorecards, and Data Warehousing.
    • 2000s (Business Intelligence):
    • Expansion into Data Mining, Text Mining, Cloud Computing, Software as a Service (SaaS), and formalised Business Intelligence frameworks.
    • 2010s+ (Analytics and Big Data):
    • Introduction of Social network/media analytics, In-memory computing, In-database processing, and Big Data analytics.

Evolution of Decision Support Systems, Business Intelligence, and Analytics

  • Core Components of Business Intelligence Architecture:
    • Data Warehouse: Central repository housing normalized source data.
    • Business Analytics: Collection of data manipulation, mining, and statistical analysis tools used to query data within the data warehouse.
    • Business Performance Management (BPM / PBM): Systems focused on monitoring, measuring, and analyzing organizational KPIs and performance.
    • User Interface: End-user interactive presentation layer (e.g., executive dashboards).

Introduction to Data Warehousing

  • Data Warehouse Definition and Core Purpose:

    • A relational database explicitly structured for query, analytical, and reporting processes rather than transaction processing.
    • Houses deep historical data aggregated from transactional databases as well as external enterprise sources.
    • Architecturally isolates analytical workloads from transactional operational workloads to maintain system performance.

  • Key Features of a Data Warehouse:

    • Subject-Oriented: Data is organized and structured around specific subjects or business concepts (e.g., customer, product, sales) rather than business processes.
    • Integrated: Standardizes data across diverse source systems into a unified, consistent model.
    • Non-Volatile: Historical data records are static; once entered, data is not updated or deleted under standard operations.
    • Time-Variant: Maintains detailed historical timeline data to analyze trends and changes over time.
    • Massive Database Capacity: Designed to scale to store millions of data records.
    • Non-Transactional: Purpose-built to inform decision-making processes rather than process high-frequency operational transactions.

  • Operational Databases vs. Data Warehouses:

  | Parameter | Operational Database | Data Warehouse |   | :--- | :--- | :--- |   | Focus | High performance transaction execution | Flexibility, broad scope, business analysis |   | Operations | Routine operational read/write transactions | Complex, analytical read-only query processes |   | Latency Tolerance| Latency/delay in processing cannot be tolerated | Generous view of query processing execution time |   | Query Characteristics | Simple queries accessing hundreds of records | Complex queries accessing millions of records |   | Data Access Mode | Read and Write mode | Read-Only mode |   | Analysis Paths | Pre-defined, structured reporting paths | Ad-hoc, exploratory queries across undefined paths |

  • Data Warehouse Architectural Requirements:

    • Separation: Clear boundary maintained between analytical query workloads and transactional operational loads.
    • Scalability: Flexible framework capable of expanding computational capacity as data volumes scale.
    • Extensibility: System layout permits addition of new application modules without requiring full system redesign.
    • Security: Intensive oversight required due to the strategic, sensitive nature of aggregated organizational data.
    • Ease of Use: Highly accessible interface that abstracts underlying system complexity from business decision-makers.

  • The Extract, Transform, Load (ETL) Process:

    • Step 1: Data Selection and Extraction from source systems.
    • Step 2: Data Cleaning to eliminate errors and discrepancies.
    • Step 3: Data Transformation and Consolidation to standardize formats across disparate systems.
    • Step 4: Load into target Data Warehouse tables.
    • Step 5: Refresh updates scheduled periodically to ingest new transactional data.

  • Data Cleaning and Quality Control (Garbage In, Garbage Out - GIGO):

    • Format Violations: Resolving invalid character inputs (e.g., letters in numeric values or malformed phone numbers).
    • Missing Data: Handling incomplete database fields (e.g., null values in customer postal codes).
    • Internal Consistency Issues: Reconciling duplicate entity profiles caused by variations in spelling or layout.

  • Data Transformation Pipeline Mechanics:

    • Transformation normalizes, standardizes, and corrects raw source records into unified relational structures prior to data warehouse ingestion.

Data Transformation Pipeline Example

  • Step-by-Step Data Pipeline Example:

    1. Raw Input: John White Downing St. 10 TW1A 2AA London (UK)
    2. Normalization: Fields mapped to key-value pairs:
      • firstName: John
      • lastName: White
      • address: Downing St. 10
      • ZIPCode: TW1A 2AA
      • city: London
      • country: UK
    3. Standardization: Field entries reformatted to enterprise standard conventions:
      • address: 10, Downing Street
      • country: United Kingdom
    4. Correction: Data errors verified and corrected against official master data indexes:
      • ZIPCode: SW1A 2AA
    • Case Architecture: UWL Student at Risk System:

  • Ingests multiple operational data streams through independent ETL flows into an integrated central Data Warehouse powered by Oracle 11g.

UWL Student at Risk System ETL Pipeline

  • Ingestion Sources:
    • Student Enrolment (Oracle 10g): Ingests Student Background and Enrolment Status via ETL.
    • Student Result (Oracle 10g): Ingests Marks, Pass/Fail statuses, and Resit/Deferral records via ETL.
    • Course/Module (SQL Server, MS Word): Processes CRM Module Study Guides and Programme Specifications using Text Mining and ETL.
    • Progress File (Oracle 9i): Ingests Learning Skills metrics (Literary, Numeric, ICT) via ETL.
    • Student Activities (Oracle 9i, SQL Server, Text): Extracts Borrowing/Reading List activities, Online Group Activities, Homework Submissions, and E-Resource access logs via ETL from VLE, Library, and E-Resource platforms.
  • Storage & Query Layer:
    • Storage: Oracle 11g engine housing Integrated & Transformed Data with Materialized Views.
    • Output: Feeds user applications, reports, and dedicated Data Mining systems via structured database queries.

Data Lakes and Modern Big Data Architecture

  • Data Warehouse Limitations:

    • Schema rigidities require structured relational data formats, limiting ingestion scope.
    • Approximately 80% of data produced by modern organizations is unstructured and cannot be mapped into traditional data warehouse schemas due to schema constraints or extreme scale.
    • Data Warehouses are unable to natively store non-relational Big Data formats (such as video, raw text, and IoT streams).

  • Data Lake Concept & Architecture:

    • Developed to serve as an enterprise-wide central repository storing raw, unstructured data that cannot fit traditional data warehouse frameworks.
    • Alternately called Oracle's 'Data Reservoirs' or referred to critically as 'data swamps' if unmanaged.
    • Definition: A centralized repository that permits the storage of all structured and unstructured enterprise data at any scale in its native format ("as-is") without requiring front-end schema structuring. Supports immediate execution of diverse operational analytics, including interactive dashboards, data visualizations, big data processing, real-time streaming analytics, and machine learning models.

Data Lake Conceptual Layout

  • Data Lake Data Ingestion and Analytical Pipeline:
    • Inputs / Data Ingestion Streams:
    • On-premises Data Movement.
    • Real-time Data Movement.
    • Central Storage:
    • Data Lake storing raw structured and unstructured data at scale.
    • Downstream Output Processing:
    • Machine Learning workloads.
    • Advanced Analytics and Visualization applications.

Academic References

  • Golfarelli, M. and Rizzi, S., 2009. Data warehouse design: Modern principles and methodologies. McGraw-Hill, Inc.
  • Sharda, R., Delen, D. and Turban, E., 2016. Business intelligence, analytics, and data science: a managerial perspective. Pearson.