Part III Business Intelligence
Outline of Business Intelligence
1.
Business Intelligence
2.
Reports and Dashboards
3.
Data Mining
4.
Big Data
1. BUSINESS
INTELLIGENCE
Business Intelligence
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
BI life-cycle
BI Key players and Values
BI area
Transaction Area (Data + OLTP)
Data Ware house
and Data Mart
• ETL
Meta Data
• ERP
• Legacy
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.
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
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)
2. REPORTS &
DASHBOARDS
Business Intelligence
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)
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
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
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.
Data Model Best Practice
Flat tabular data makes effective data model
◦ Spreadsheet data in report forms result in ineffective data models
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
Chart Examples
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.
Infographic Example
Tag clouds (business key words)
Infographic Example
Geographic chart (world unemployment 2013)
Infographic Example
Social Network Analysis
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?
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.
Pivot Table Example
Pivot table
Excel charts
Lab 2 Pivot Table and Dashboards using Excel
Numbers.xlsx를 열고 다음을 실행해 보세요.
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. 행열에 대한 필터링: 연도선택 우버튼 ⇒ 피벗테이블옵션 ⇒ 표시 ⇒ 클래식... 선택
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?
