Week 8-9: Business Intelligence & Data Warehouses

Data Analysis

It is the practice of working with data to glean useful information, which can then be used to make informed decisions. – Coursera

Business Intelligence (BI)

It is a term that describes a comprehensive, cohesive, and integrated set of tools and processes used to capture, collect, integrate, store, and analyse data with the purpose of generating and presenting information to support business decision making.

ETL - Extraction

Retrieves data from original data sources.

ETL - Transformation

Manipulates the data into an appropriate form.

ETL - Loading

Stores the data in the data warehouse.

BI Tools - Dashboard

A web-based system that presents key business performance indicators or information in a single, integrated view with clear and concise graphics.

BI Tools - Portal

A unified, single point of entry for information distribution.

Operational Data

This is stored in a relational database in which the structures (tables) tend to be highly normalized.

Decision Support System

This is an arrangement of computerized tools used to assist managerial decision making.

Decision Support Database Requirement

This is a specialized DBMS tailored to provide fast answers to complex queries.

Database Schema

One of the three main requirements for decision support database where it must support complex data representation.

Data Extraction and Filtering

One of the three main requirements for decision support database where the decision support database is created largely by extracting data from the operational database and by importing additional data from external sources

Database Size

One of the three main requirements for decision support database where the decision support databases tend to be very large; gigabyte and terabyte ranges are not unusual.

Data Warehouse

It can be defined as integrated, subject-oriented, time-variant, non-volatile collection of data that provides support for decision making. – Bill Inmon

Integrated

This means that the data are being stored in a globally accepted fashion with consistent naming conventions, measurements, encoding structures, and physical attributes, even when the underlying operational systems store the data differently.

Subject-Oriented

This means that all relevant data about a subject is gathered and stored as a single set in a useful format such as customers, products and sales.

Time-Variant

This provides a tracker to produce reports including the data changes done over time.

Non-Volatile

This means that the data in data warehouse is a read-only where it can be loaded and accessed in the data warehouse.

Star Schema

A data modelling technique used to map multidimensional decision support data into a relational database. It also represents data using a central table known as a fact table in a 1:M relationship with one or more dimension tables.

Components of Star Schema - Facts

In a data warehouse, the measurements (values) that measure a specific business aspect or activity.

Fact Table

This contains facts that are linked through their dimensions.

Components of Star Schema - Dimension

These are qualifying characteristics that provide additional perspectives to a given fact.

Dimension Table

The tables used to search, filter, or classify facts within the star schema.

Components of Star Schema - Attribute

The component often used to search, filter, or classify facts.

Components of Star Schema - Attribute Hierarchy

This component provides a top-down data organization that is used for two main purposes: aggregation and drill-down/roll-up data analysis.