Part III Business Intelligence

1

Outline of Business Intelligence

1. Business Intelligence

2. Reports and Dashboards

3. Data Mining

4. Big Data

2

1. BUSINESS INTELLIGENCE

Business Intelligence

3

BI definitions

Gartner: Business Intelligence encompasses the

processes and tools required to transform enterprise

data into information, information into knowledge that

can be used to enhance decision-making and to create

actionable plans that drive effective business activity.

◦ BI is to take data that you already have and to transform it into

knowledge you can use for decisions or actions.

◦ Every organization has someone who is doing BI now. They just do not

recognize it.

BI can be used to acquire:

◦ Strategic insight to align business processes with business

objectives.

◦ Tactical insight to optimize business processes by identifying

trends, anomalies, and behaviors.

4

5

BI life-cycle

6

BI Key players and Values

BI area

Transaction Area (Data + OLTP)

Data

Ware

house

and

Data

Mart

• ETL

Meta Data

• ERP • Legacy

7

Business Intelligence Area (Information + OLAP/Data Mining)

BI Tools

DBMS

Data-integration tools

◦ ETL

◦ Data warehouse tools

Business Analytics Tools

◦ Ad-hoc queries

◦ Enterprise report management

◦ Messenger and notification services

◦ OLAP tools

◦ Data Mining tools

◦ Big Data Analysis

◦ Business Analytics + Hadoop based data management

◦ Visualization

◦ Dashboards, charts, graphics, etc.

8

BI Quality

The important four criteria for BI systems

Timely

◦ Ex: can manipulate data much faster using OLAP and data

warehouse than relational database

Accurate

◦ Ex: data warehouse should have accurate data

High-value

◦ Ex: can perform advanced data analysis to obtain high-value

information

Actionable

◦ Ex: analysis can recommend action to improve situation

9

Costs and Benefits of BI

BI is not a MUST. Needs to analyze costs and benefits

Costs ◦ Hardware costs (Actual or Opportunity)

◦ Software costs (ETL, Databases, Analytics, DM, System Integrations, etc.)

◦ Development costs

◦ Training costs

◦ Maintenance costs

Benefits ◦ Improved Decision Making

◦ Improved Operational Efficiencies

◦ Improved Knowledge Management

◦ Business agility (responsiveness)

10

2. REPORTS & DASHBOARDS

Business Intelligence

11

Reports and Dashboards

The most common and basic application of BI

Reports

◦ Does not lead readers to a predefined conclusion

Dashboards

◦ Has graphical interface, displays only key measures (KPIs),

contains predefined conclusion (no analysis required)

12

Dashboard Design

Keep it simple in both information and design

◦ Less colors, gradient, multimedia clips, emphasized borders, etc.

◦ Skip explicit trend lines, avoid data overload, skip unnecessary

legends, sort data before charting, etc.

Studies show that users pay more attentions to the

upper left part

Format number effectively and simply

◦ $23.45, $2.3M, 2010/03/15, etc.

Use descriptive titles and labels

1 1 2 3

1 1 2 2

2 2 2 3

3 3 3 3

13

Data Preparation

1. Define audience and purpose of the

dashboard

2. Delineate the key measures

◦ KPI (key performance indicator) - an indicator of

the performance of essential tasks

3. List the required data source

4. Define dimensions and filters

5. Determine the need for drill-down features

6. Establish the refresh schedule

14

Data Model and Layered Structure

Data Model

◦ Foundation on which

reporting is built

◦ Good model allows easy

reporting

Layered structure

◦ Separating data, analysis

and presentation layer

◦ Data, analysis methods and

design are independently

updated, changed or

revised.

15

Data Model Best Practice

Flat tabular data makes effective data model

◦ Spreadsheet data in report forms result in ineffective data

models

16

Charts

Charts offer immediate understanding of

relationships, differences, trends, exceptions, etc.

Chart Types and purposes

◦ Line chart (trend), pie chart (distribution), column

chart (comparison), stacked column chart(comparison

+ distribution among sub-items), bar chart, stacked

bar chart, XY scatter plot chart(correlation), area

chart(magnitude), etc.

◦ Combined chart – combined purposes

17

Chart Examples

18

Information Visualization

Infographic:

◦ Visual representation technique of abstract data to

reinforce human cognition using signs, pictures, maps,

text, etc.

◦ Data: include both numeric (Excel charts) and non-

numeric data (text, geographic information)

◦ Objective: not just for delivering information but also

triggering insight and persuasion.

Many commercial infographic solutions are

available.

19

Infographic Example

Tag clouds (business key words)

20

Infographic Example

Geographic chart (world unemployment 2013)

21

Infographic Example

Social Network Analysis

22

Check List of Visualization

Everything on my dashboard has a purpose?

Correct information?

Clearly display its scope and itself?

◦ Ex: timestamp or descriptive titles

Prominently display key message?

Easily maintained?

Well documented?

Not overwhelmed with formatting and graphics?

User friendly?

23

Using Pivot Tables for Data Model

Pivot Table?

◦ Useful for analysis, reporting and dashboarding

◦ Most of BI applications provides pivot tables for reporting

Easily categorize data into groups, summarizes large

data into meaningful information, interactively performs

many analysis, and keeps refreshed

Four areas of pivot tables: values, rows, columns, filters

Various calculations on values of pivot tables:

◦ Sum, count, average, max, min, product, stddev, subtotals, sorting,

yearly/quarterly/monthly/weekly/daily views, etc.

24

Pivot Table Example

Pivot table

Excel charts

25

Lab 2 Pivot Table and Dashboards using Excel

Numbers.xlsx를 열고 다음을 실행해 보세요.

26

Lab 2 Pivot Table and Dashboards using Excel Numbers.xlsx

1. 삽입 ⇒ 피벗테이블

2. orderDate를 열레이블에 추가

3. category와 product를 행레이블에 추가

4. category와 product를 별도의 컬럼으로 구분하기

category ⇒ 필드설정 ⇒ 레이아웃 및 인쇄 ⇒ 테이블 형식으로 레이블 표시

5. 날자를 월별 분기별로 단순화

날 자에 마우스 놓고 우버튼 클릭 ⇒ 그룹 ⇒ 월, 분기, 연 선택

6. totalPrice를 ∑값에 추가

7. 분기별, std, sum, max 만들기

분기선택 마우스 우버튼 ⇒ 필드설정 ⇒ 부분합 및 필터 ⇒ std, sum, max 선택

8. 숫자 표기 정리

셀선택 마우스우버튼 ⇒ 셀서식 ⇒ 숫자, 100단위 구분기호 사용

9. 디자인 바꾸기: 디자인 선택

10. filtering: region, city를 filter로 drag&drop ⇒ 화면의 좌상에서 필터 선택

11. 행열에 대한 필터링: 연도선택 우버튼 ⇒ 피벗테이블옵션 ⇒ 표시 ⇒ 클래식... 선택

12. 행열 바꾸기: 항목 선택 ⇒ 우버튼 ⇒ 행으로 이동...

13. 차트만들기: 데이터 선택 ⇒ 삽입 ⇒ 차트 선택 27

Lab 2 Pivot Table and Dashboards using Excel 1. What are the sales totals for each category of product?

Hint: Drag the Category field into the Row area, and then drag the Sales into the Data area.

2. What are the sales totals for each product?

Hint: Drag the Product field into the Row area.

3. What are the three best-selling products in each category?

Hint: To view the top items in a field, click the Product field, click PivotTable on the PivotTable toolbar,

and then click Sort and Top 10. Under Top 10 AutoShow, click On. In the Show box , click Top and then

enter 3.

4. What are the quarterly sales by product?

Hint: Drag the Quarter field into the Column area.

5. How do the sales in the first quarter compare with those in the second?

Hint: To focus on two quarters only, click the dropdown arrow in the Quarter field. Select the check

boxes for just the first two quarters.

5. What are the average, largest, and smallest Beverage sales subtotals?

Hint: You can use more than one summary function for subtotals. Double-click the Category field, and

then click one or more options under Subtotals.

6. What is the average sale and minimum sale?

For the above questions, generate dashboards! 28