SQL query and sub-query, Lecture notes of Data Warehousing

Download SQL query and sub-query and more Lecture notes Data Warehousing in PDF only on Docsity!

ISBS204 Database

Management Systems

Lecture 11

Database Warehouses and Business

Intelligence

Textbook Reading:

Chapter 13

Learning Objectives After completing this lecture, you will be able to: § Describe the role of business intelligence in providing comprehensive business decision support § Describe the architecture, reporting styles, evolution, and benefits of business intelligence § Differentiate between operational data and decision support data § Identify the purpose, characteristics, and components of a data warehouse § Develop star and snowflake schemas for decision-making purposes § Describe the characteristics and capabilities of online analytical processing (OLAP) § Describe the role and functions of data analytics and data mining § Explain how SQL analytic functions are used to support data analytics § Define data visualization and explain how it supports business intelligence

The Need for Data Analysis

• Companies and software vendors addressed these multilevel

decision support needs by creating autonomous applications

for particular groups of users

• This more comprehensive and integrated decision support

framework within organizations became known as business

intelligence

Business Intelligence (1 of 2)

• Comprehensive, cohesive, integrated set of tools and processes
  • Captures, collects, integrates, stores, and analyzes data
  • Generates and presents information to support business

decision making

• This intelligence is based on learning and understanding the

facts about the business environment.

• BI is a framework and allows business to transform
  • Data into information
  • Information into knowledge
  • Knowledge into wisdom

Business Intelligence Architecture (1/6)

• BI covers a range of technologies and applications to manage

the entire data life cycle from acquisition to storage,

transformation, integration, presentation, analysis, monitoring,

and archiving.

• BI functionality ranges from simple data gathering and

transformation

to very complex data analysis and presentation.

• BI architecture ranges from highly integrated single-vendor

systems to loosely integrated, multivendor environments.

• BI architecture is composed of many interconnected parts:

people, processes, data, and technology

• Figure 13.1 depicts how all these components fit together

within the BI framework.

Business Intelligence Architecture (2/6)

Business Intelligence Architecture (4/6)

Business Intelligence Architecture (5/6)

• BI practices to manage data
  • Master data management (MDM): collection of concepts, techniques, and processes for identification, definition, and management of data elements within an organization. - Main goal is to provide a comprehensive and consistent definition of all data within an organization.
  • Governance: BI provides a method of government for controlling business health and for consistent decision making
  • Special type of metrics--Key performance indicators (KPI): numeric or scale-based measurements that assess company’s effectiveness in reaching its strategic and operational goals.
  • KPI Examples:
    • General. Year-to-year measurements of profit by line of business, same-store sales, product turnovers, product recalls, sales by promotion, and sales by employee
    • Finance. Earnings per share, profit margin, revenue per employee, percentage of sales to account receivables, and assets to sales
    • Human resources. Applicants to job openings, employee turnover, and employee longevity
    • Education. Graduation rates, number of incoming freshmen, student retention rates, publication rates, and teaching evaluation scores

Business Intelligence Benefits

• Improved decision making is the main goal of BI, but BI provides other benefits - Integrating architecture—including different IT systems within an organization, diverse hardware such as mainframes, servers, desktops, laptops, and mobile devices. - Common user interface for data reporting and analysis--End users benefit from similar or common interfaces in different devices that use multiple clever and insightful presentation formats. - Common data repository fosters single version of company data-- Keeping the data synchronized and up to date - Improved organizational performance--provide competitive advantages in many different areas, from customer support to manufacturing processes
• Achieving all these benefits takes a lot of human, financial, technological resources, and time - BI benefits are not achieved overnight; are the result of a focused company-wide effort that could take a long time

Business Intelligence Evolution (1/3)

Business Intelligence Evolution (3/3)

Business Intelligence Technology Trends

• Several technological advances are driving the growth of business intelligence technologies - Data storage improvements—Newer data storage technologies, such as solid state drives (SSD) and Serial Advanced Technology Attachment (SATA) drives, offer increased performance and larger capacity that make data storage faster and more affordable - Business intelligence appliances--Vendors now offer plug-and-play appliances optimized for data warehouse and BI applications.These new appliances offer improved price-performance ratios, simplified administration, rapid installation, scalability, and fast integration. - Business intelligence as a service--Vendors now offer data warehouses and BI as a service. These cloud-based services allow any corporation to rapidly develop a data warehouse store without the need for hardware, software, or extra personnel. - Big Data analytics--Organizations are turning to social media as the new source for information and knowledge to gain competitive advantages. - Personal analytics--OLAP brought data analytics to the desktop of every end user in an organization. Mobile device BI is extending business decision making.

Operational Data versus Decision Support Data (1/3)

• Operational data and decision support data serve different purposes
  • Operational data is useful for capturing daily business transactions
    • Most operational data is stored in a relational database in which the structures (tables) tend to be highly normalized.
  • Decision support data gives tactical and strategic business meaning to the operational data
  • Their formats and structures differ
• Decision support data differs from operational data in three main areas
  • Time span
    • Operational data covers a short time frame.
    • In contrast, decision support data tends to cover a longer time frame.
    • Managers are seldom interested in a specific sales invoice to Customer X
    • rather, they tend to focus on sales generated during the last month, the last year, or the last five years.

Operational Data versus Decision Support Data (1/3)

• Granularity (level of aggregation)
  • Decision support data must be presented at different levels of aggregation, from highly summarized to nearly atomic.
  • Example: data sales by region, city, store
• Dimensionality
  • Operational data focuses on representing individual transactions rather than the effects of the transactions over time.
  • In contrast, data analysts tend to include many data dimensions and are interested in how the data relates over those dimensions. - Example: an analyst might want to know how Product X fared relative to Product Z during the past six months by region, state, city, store, and customer.
  • In that case, both place and time are part of the picture.