Business Intelligence Fundamentals

Business Intelligence (BI)

• BI encompasses data warehousing, business analytics and knowledge management.
• BI is a process used to convert data into information, information into knowledge, and knowledge into actionable plans that drive profitable business actions.

Challenges of Building BI Solutions

• Data is scattered across various locations.
• Data formatting does not support complex analysis.
• Different data needs for various workers.
• Determining what data to analyze and the level of detail required.
• Defining user interaction with data.

Data Consolidation

• The process of consolidating data involves moving, making consistent, and cleaning up as much data as possible.

Extraction, Transformation, and Loading (ETL)

• The data consolidation process is referred to as Extraction, Transformation, and Loading (ETL).
• ETL extracts data from multiple source systems.
• Data is then transformed to ensure consistency and improve data quality.
• Consolidated, consistent, and cleaned data is loaded into a data repository.
• ETL development typically consumes 80% of the development time.

ETL Tools

• Common ETL Tools:
  • Oracle Data Integrator (ODI)
  • Informatica
  • IBM Ascential
  • Abinitio

Business Considerations for Data Consolidation

• Business users should drive the content of the data warehouse.
• Business stakeholders are responsible for deciding how to consolidate inconsistent data.
• The business must determine how to handle additional requirements, such as currency conversions.

Users of Business Intelligence

• Executives and decision makers: Utilize high-level business views with limited analysis.
• Analysts: Perform complex, in-depth data analysis.
• Information workers: Require static reports or limited analytical capabilities.
• Line workers: No analytical capabilities, BI is integrated into their daily tasks.

Components of a Data Warehouse

• Cubes: Structures used for data storage. Users navigate through dimensions to access data in cubes.
• Measures: Represent the desired data. Typically numeric and often additive (e.g., sales, profit, expenses).
• Key Performance Indicators (KPIs): Quantifiable metrics used to track performance.
• Dimensions: Used to view data. Common dimensions include time, geography, product, account, employee, etc.
  • Attributes: Individual values within a dimension (e.g., Month, Year, Country, Region, City, Part Number, Size, Color, Manufacturer).
  • Hierarchies: Structures that organize attributes, enabling drill-down analysis (e.g., Year to Quarter to Month to Day).

Asking a BI Question

• BI facilitates multidimensional thinking.
• Users typically want to see a specific value in a particular context (e.g., "Show me sales by month by product for North America").
• Measures represent the desired data (sales in this example).
• Dimensions define how the data is viewed (month, product, North America in this example).

ETL Process Summary

• The ETL process is responsible for extracting data from source systems, transforming it, and loading it into a data warehouse or data mart.

Definition of Business Intelligence

• Business Intelligence (BI) involves processes, technologies, and tools used to transform data into information, information into knowledge, and knowledge into plans for profitable business actions.
• BI encompasses areas such as data warehousing, business analytics, and knowledge management.

Challenges in Building Business Intelligence

• Data is often fragmented across multiple sources
• Data may not be formatted to support complex analysis
• Different stakeholders have varying data needs
• Determining what data to examine and its level of detail is crucial
• Defining how users will interact with the data is essential.

Consolidating Data From Multiple Sources

• The process of data consolidation involves moving, making consistent, and cleaning up data as much as possible.

Extraction, Transformation, and Loading (ETL)

• The ETL process extracts data from various source systems.
• Data is transformed for consistency and improved data quality.
• The consolidated, clean data is loaded into a data repository.
• The ETL process often consumes the majority (80%) of development time.

ETL Tools

• Some popular ETL Tools include:
  • Oracle Data Integrator (ODI)
  • Informatica
  • IBM Ascential
  • Abinitio

Business Issues With Data Consolidation

• Business users should drive the content of the data warehouse.
• Business stakeholders must decide how to consolidate inconsistent data.
• The business needs to address other necessary elements, such as currency conversions.

Users of Business Intelligence

• Executives and decision-makers: Focus on high-level business insights with limited analysis.
• Analysts: Conduct complex, detailed data analysis.
• Information workers: Require static reports or basic analytic capabilities.
• Line workers: Rely on BI insights presented as part of their job responsibilities; they typically do not need analytical skills.

Components of a Data Warehouse

• Data Warehouses are comprised of several elements:
  • Cubes: Structures used to store data.
  • Measures: The values you want to view, often numeric and additive (e.g., sales, profit, expenses).
  • Key Performance Indicators (KPIs): Measures that track critical aspects of performance (e.g., inventory accuracy, efficiency, costs).
  • Dimensions: Categorical attributes used to analyze data (e.g., time, geography, product, account).
    • Attributes: Individual values within dimensions (e.g., month, year, country, region).
    • Hierarchies: Structured arrangements of attributes to support drilling down (e.g., year -> quarter -> month).

Asking a BI Question

• Human thinking is inherently multidimensional.
• Users often want to view a specific value in a particular context (e.g., "Show me sales by month by product for North America").
• The desired value is called a measure.
• The context in which it is viewed is called a dimension.

Cubes

• Cubes serve as the storage structures for data within a data warehouse.
• Users explore data in cubes by navigating through different dimensions.

Dimensions

• Dimensions are how you choose to view the data.
• Common dimensions include time, geography, product, account, and employee.
• Each dimension comprises attributes and may include hierarchies.

Hierarchies

• Hierarchies organize attributes into a structured format to facilitate user analysis.
• A common BI functionality is "drilling down" to explore greater levels of detail.
• For example, a Time hierarchy might be year -> quarter -> month -> day.

Business Intelligence (BI)

• BI encompasses data warehousing, business analytics, and knowledge management
• BI transforms data into information, information into knowledge, and knowledge into actionable plans
• BI aims to drive profitable business action

Challenges of Building BI Solutions

• Data is often scattered across multiple locations
• Data may not be formatted for complex analysis, requiring transformation
• Different users have varying data needs, making it challenging to create a single solution
• Deciding which data to analyze and the necessary level of detail can be difficult
• Defining user interaction with the data and the optimal interface is essential

Consolidating Data

• Data consolidation involves moving, cleaning, and making data consistent for analysis

Extraction, Transformation, and Loading (ETL)

• ETL is the process of consolidating data
• ETL extracts data from multiple sources
• Data is transformed to ensure consistency and improve quality
• Transformed data is then loaded into a data repository
• ETL often consumes 80% of the development time

ETL Tools

• Popular ETL tools include Oracle Data Integrator (ODI), Informatica, IBM Ascential, and Abinitio

Business Issues with Data Consolidation

• Business users should actively drive the content of the data warehouse
• Someone in the business should determine how to handle inconsistent data
• The business needs to decide how to address other necessary items, such as currency conversions

Users of Business Intelligence

• Executives and decision-makers: Primarily focus on high-level analysis
• Analysts: Perform detailed and complex data analysis
• Information workers: Need static reports or limited analytical capabilities
• Line workers: Do not require analytical capabilities as BI is integrated into their jobs

Data Warehouse Components

• Cubes: Structures where data is stored and accessed through dimensions
• Measures: Quantitative values that users want to analyze (e.g., sales, profit)
• Key Performance Indicators (KPIs): Specific, measurable metrics that track progress toward goals (e.g., inventory accuracy, customer cycle time)
• Dimensions: Contextual frameworks for viewing data (e.g., time, geography, product)
  • Attributes: Individual values within a dimension (e.g., Month, Year, Country)
  • Hierarchies: Structured arrangements of attributes for easier analysis (e.g., Year > Quarter > Month)

Asking BI Questions

• Humans naturally think multidimensionally
• We often want to see a value within a specific context (e.g., sales by month by product for a specific region)
• Measure: What we want to see (e.g., sales)
• Dimension: How we want to view it (e.g., month, product, region)

Summary

• The ETL process extracts, transforms, and loads data into a data warehouse or data mart, providing a consolidated view of data for analysis.