Business Intelligence and Analytics

Question 1

Performance management

Answer 1

Processes, methodologies, and technologies used by enterprises to monitor, analyze, and plan business performance

Question 2

Business Intelligence

Answer 2

“A broad category of applications and technologies for gathering, storing, analyzing, sharing and providing access to data to help enterprise users make better business decisions.”

Question 3

Decision Support Systems (DSS)

Answer 3

• DSS are computer-based information systems (IS) that provide interactive information support to the decision-making process of managers and business users
• DSS are designed to give a quick and interactive response to the ad hoc queries and information exploration needs of business users
• Computerized DSS assist managers providing the right information, at the right time, with the right format (context)

Question 4

DSS Classification

Answer 4

Communication-driven - Communications (videoconferencing)

Data-driven - Database (capture, storage and retrieval of structured data)

Document-driven - Document storage and management (Search engines and HTML)

Knowledge-driven - Knowlegde base, AI - Expert systems

Model-driven - Quantitative models - Linear programming

Question 5

Data Warehouse & BI systems

Answer 5

Data Warehouse and BI systems are datadriven DSS

Have two main components
* An integration and data warehouse (DW) environment, managed by the technical team
* An analysis and reporting environment, used by the business users to visualize and explore information

“A DW is a platform for BI applications

Question 6

DW/BI systems: main components

Answer 6

Data Warehousing: Getting data in
* Involves moving data from a set of source systems into an integrated repository, the data warehouse

Business Intelligence: Getting data out
* Consists of business users and applications accessing data from the DW to perform enterprise reporting, OLAP, querying, and predictive analysis

Question 7

DW/BI systems: Kimball’s definition

Answer 7

“The shift to business intelligence puts initiative in the hands of business users, not IT.

The DW does the hard work of wrangling the data out of the source systems, cleaning it, and organizing it so that normal business users can understand it.”

Question 8

Kimball approach - technical architecture

Answer 8

Moving from back room to front room

• Source systems
  User desktops, External suppliers, Flat files and XML docs, Message queues
• ETL System
  Extract, Clean, Conform and Deliver
• Presentation Server
  Atomic business process dimensional models with aggregate navigation
  Conformed dimensions/facts
• BI Applications
  Queries, Standard reports, Analytic apps, Dashboards, Operational BI, Data Mining and Models

Question 9

Kimball approach - Technical Architecture - Source Systems

Answer 9

• Process real-time transactions
• Contain data structures optimized for modifications
  – Normalized schemas
• Usually provide limited decision support
• Are commonly referred to as:
  – Online transaction processing (OLTP) systems
  – Operational systems

Question 10

Kimball approach - Technical Architecture - ETL Process

Answer 10

• Extract data from the source systems
• Transform the data to convert it to a desired state
• Load the data into the data warehouse
• The most underestimated and time-consuming process in DW development
  – Often, 80% of development time is spent on ETL

Question 11

Kimball approach - Technical Architecture - Presentation Server (DW)

Answer 11

• Provides data for the analysis of business processes
  – Grouped in subject-specific stores called Data Marts
• Optimized for rapid ad hoc information retrieval
• Integrates data from heterogeneous source systems
• Provides a consistent historical data store

Question 12

Kimball approach - Technical Architecture - BI Applications

Answer 12

Data visualization techniques are pivotal for the design of
effective BI applications

• Reporting
• Dashboards
• OLAP
• Data Mining

Question 13

Star schema

Answer 13

• One Fact table
• Several Dimension tables

Question 14

Dimensional models - what is a Fact

Answer 14

A FACT is something that happenned; it’s a business event or transaction

• Example: sale, purchase, shipping…
• It’s a verb and essentially a measure

Question 15

Dimensional models - what is a Dimension

Answer 15

A DIMENSION describes or provides context to a fact

• Example: Customer, Product, Date, Account…
• It’s a noun and an object

Question 16

Dimension Tables

Answer 16

• Contain descriptive fields
• Dimension table attributes serve 2 critical purposes:
  1. Query constraining/filtering
  2. Query result set labeling
• The power of the DW is proportional to the quality and depth of the dimension attributes; robust dimensions translate into robust querying and analysis capabilities

Question 17

Dimension Tables: hierarchies

Answer 17

• A dimension table can have a series of parent-child relationships among groups of attributes.
• Days make up months, months fall into quarters, and quarters fall into years, for example.
• A hierarchy is used as a path for drilling up and down

Question 18

Drilling Up and Down

Answer 18

The attributes in the dimension tables are the source of application constraints and row headers in the final report.

DRILLING DOWN = add a row header
DRILLING UP = subtract a row header

Real drill down mix hierarchical and nonhierarchical attributes from all the available dimensions

Category attribute
|
Subcategory attribute
|
Brand attribute
|
Detailed product description attribute

Question 19

Dimension Date hierarchy

Answer 19

Each level represents an aggregation path, and may be represented by several columns or dimension attributes

Example:
* Level Month
Mês descrição - Outubro
Mês número - 10
Mês-Ano - Outubro-2015