data CH13 summary

SQL

What is big data? Velocity, variety and volume

Business intelligence is a term used to describe a comprehensive, cohesive and integrated set of tools and processes used to support business decision making.

Data > information > knowledge > wisdom

Collect & store operational data > aggregate operational data into decision support data > analyze decision support data to generate information >…

Traditional info system focus on operational automation and reporting

BI focus on strategic and tactical use of information

Business intelligence architecture is composed of data, people, processes, technology and the management of such components. Main goal: improved decision making

Operational data: mostly stored in relational db, optimized to support transactions representing daily operations

Operational data vs analytical data
- Extract operational data for BI processing.
- BI effectiveness depends on quality of data gathered at operational level
- Operational data seldom well-suited for decision support tasks
- Need reformat data in order to be useful for business intelligence

Operational vs Decision support data:

• time span: operational data cover a short time frame, decision support data longer time
• granularity(level of aggregation): decision support data presented at different levels of aggregation, from highly summarized to near-atomic
• dimensionality: Opdata focus on individual transactions; Dsdata focus on the effects of the transactions over time.

Requirements for a decision support DBMS:

1. Db schema: aggregated and summarized data; queries extract multidimensional time slices
2. Data extraction and loading: supports different data sources; check for inconsistent data
3. Db size: dbms must support very large db
4. End user analytical interface

Data warehouse:

• Integrated, subject-oriented, time-variant and nonvolatile collection of data
• Provides support for decision making
• Usually a read-only database optimized for data analysis and query processing
• Requires time, money, and considerable managerial effort to create

Data marts

• Small, single-subject data warehouse subset
• More manageable data set than data warehouse
• Provides decision support to small group of people
• Typically lower cost and lower implementation time than data warehouse

Star Schemas

• Data-modeling technique, Maps multidimensional decision support data into relational database
• Creates near equivalent of multidimensional database schema from relational data
• Easily implemented model for multidimensional data analysis while preserving relational structures
  Four components: facts, dimensions, attributes, and attribute hierarchies
  Many fact rows related to each dimension row
• Primary key of fact table is a composite primary key
• Fact table primary key formed by combining foreign keys pointing to dimension tables
  Dimension tables are smaller than fact tables
  Each dimension record is related to thousands of fact records

Performance-Improving Techniques for the Star Schema

Four techniques to optimize data warehouse design:

1. Normalizing dimensional tables(Achieve semantic simplicity &Facilitate end-user navigation through the dimensions)
2. Maintaining multiple fact tables to represent different aggregation levels
3. Denormalizing fact tables (improves data access performance and saves data storage space)
4. Partitioning and replicating tables (Replication makes copy of table and places it in different location)
   Data Analytics(Subset of BI functionality)
   
   • Encompasses a wide range of mathematical, statistical, and modeling techniques
   • Purpose of extracting knowledge from data
   • Tools can be grouped into two separate areas: #

Explanatory analytics & Predictive analytics

Data Mining

• Analyze data
• Uncover problems or opportunities hidden in data relationships
• Form computer models based on their findings
• Use models to predict business behavior
  Runs in two modes: Guided & Automated
  4 phases: data preparation, data analysis and classification, knowledge acquisition and prognosis

Online analytical processing OLAP
Multidimensional analysis of business data, provides the ability for complex calculations, trend analysis, and sophisticated data modeling.
Three main characteristics:

1. Multidimensional data analysis techniques
   Data are processed and viewed as part of a multidimensional structure
   Augmented by the following functions:
   

   1) Advanced data presentation functions
   2) Advanced data aggregation, consolidation, and classification functions
   3) Advanced computational functions
   4) Advanced data modeling functions

2. Advanced database support
3. Easy-to-use end-user interfaces
   OLAP Architecture

Three main architectural components:
1. Graphical user interface (GUI)
2. Analytical processing logic
3. Data-processing logic
Designed to use both operational and data warehouse data
In most implementations, data warehouse and OLAP are interrelated and complementary
OLAP systems merge data warehouse and data mart approaches
Relational OLAP
- Multidimensional data schema support within the RDBMS
- Data access language and query performance optimized for multidimensional data
- Support for very large databases (VLDBs)
SQL Extensions for OLAP
ROLLUP
- Used with GROUP BY clause to generate aggregates by different dimensions
- GROUP BY generates only one aggregate for each new value combination of attributes
- ROLLUP extension enables subtotal for each column listed except for the last one
- Last column gets grand total
- Order of column list important

SELECT column1, colun2, …
FROM table1, table 2…
WHERE condition
GROUP BY ROLLUP(column1, column2…)
HAVING condition
ORDER BY column1, column2…

CUBE

• CUBE extension used with GROUP BY clause to generate aggregates by listed columns(Includes the last column)
• Enables subtotal for each column in addition to grand total for last column(Useful when you want to compute all possible subtotals within groupings)
• Cross-tabulations are good candidates for application of CUBE extension

SELECT column1, colun2, …
FROM table1, table 2…
WHERE condition
GROUP BY CUBE(column1, column2…)
HAVING condition
ORDER BY column1, column2…

GROUPING

RANKING (rank, percent_rank, row_number)
WINDOWING (cumulative aggregate functions, moving averages, lag, lead)

STATISTICAL

Materialized Views: A dynamic table that contains SQL query command to generate rows(Also contains the actual rows)
- Created the first time query is run and summary rows are stored in table
- Automatically updated when base tables are updated