Cube Operators
Dice
Roll-up
Slice
Drill-down
Pivot Combination of cube operators
=> 복잡한 분석
CUBE Operators
• Pivot
– 행과 열의 위치를 바꾸는 연산자
• Roll-up
– 차원 계층을 따라서 상위 레벨에 대한 집계치를 계산하는 연산
– 예를들어, 날짜별 판매량에서 주별, 혹은 월별 판매량을 구할 때 사용됨
• Drill-down
– 차원 계층을 따라서 하위 레벨에 대한 집계치를 계산하는 연산
– 예를들어, 월별 판매량에서 주별, 혹은 날짜별 판매량을 구하는데 사용됨
• Slice
– 한 차원에 대하여 주어진 값으로 selection한 결과 (subcube)를 산출하는 연산
– 예를들어 slice for time = "Q1"은 time 차원에서 하나의 값 (Q1)으로 셀렉션하여 생성됨
• Dice
– 두 차원 이상에 대하여 주어진 값들로 selection한 결과 (subcube)
– location, time, item 세 차원에서 selection 된 결과 subcube를 만들 때 사용됨
• Drill-across
– 두 개 이상의 사실 테이블을 포함한 질의를 수행할 때 사용되는 연산자
• Drill-through
– 큐브에서 해당 소스 데이터베이스 (관계형 데이터베이스 테이블)까지 접근하여 관련 raw data를 검색하는 연산
2012-11-00 44
CUBE Operators
• Ranking (Sorting) the top/bottom N items
– 최고치 (혹은 최소치)로부터 차례로 N개를 선택하는 연산
– 매출량이 가장 높은 (낮은) 10개 상점은 ?
• 이 밖에도 큐브에 대한 다양한 연산자들이 제안되고 있음
– Moving average
– Growth rate
– Interests
– Internal rate of return
– Depreciation
– Currency conversion
– Statistical functions
2012-11-00 45
CUBE Operators - 예제
분석의 예제
• 분기별 sales amount ? (slice)
• 1분기 도시별, 제품별 sales amount ? (dice)
• 월별 (혹은 날짜별) sales amount ? (drill down)
• 도시별 sales amount ? (slice)
• 제품별 sales amount ? (slice)
• 분기별, 도시별 sales amount ?
• 분기별, 도시별, 제품별 sales amount ?
• 월별, 도시별 sales amount ?
• 월별, 도시별, 제품별 sales amount ?
• 1분기 New York에서 제품별 sales amount ?
• Computer 제품의 도시별 sales amount ?
• Computer 제품의 도시별, 분기별 sales amount ?
• 휴일 동안에 Vancouver 에서 판매된 자동차의 sales amount ?
• (조건에 따라서 무수히 많은 질문이 가능하며, OLAP은 이러한 질의를 효과적으로 작성하고, 빠르게 분석할 수 있어야 함)
2012-11-00 46
Time(quarter)
Item (types)
TV …..
Computer Phone Car
Vancouver Q1 Q2 Q3 Q4
Chicago New York Toronto
CUBE Operators vs. SQL
2012-11-00 47
Multi-dimensional cube Relational Table
day 2 p1 c144 c24 c3
p2 c1 c2 c3
p1 12 50
p2 11 8
day 1
c1 c2 c3
p1 56 4 50
p2 11 8
p1 p2 c1 56 11 c2 4 8 c3 50
Select ProdId, StoreId, sum(amount) From Sales
Group by ProdId, StoreId
c1 c2 c3 sum 67 12 50
sum p1 110 p2 19
subcube
Dice
Pivot
summary
sale prodId storeId date amt
p1 c1 1 12
p2 c1 1 11
p1 c3 1 50
p2 c2 1 8
p1 c1 2 44
p1 c2 2 4
p1 c1 56 p1 c2 4 p1 c3 50 P2 c1 11 P2 c2 8
Star Schema
• Relational Implementation of Cubes
2012-11-00 48
Time(quarter)
Item (types)
TV …..
Computer Phone Car
Vancouver Q1 Q2 Q3 Q4
Chicago New York Toronto
TimeTid DayWeek Month Quarter Year
Location SidCity
Province Country
ItemPid Model Pname Category SaleTid
PidSid
QtyUnit_Price
Star Schema
• Q1 : 년도별, 월별 판매금액 합계
2012-11-00 49
SELECT year, month, SUM(Qty*Unit_Price) FROM Sales S, Time T
WHERE S.time_key = T.time_key GROUP BY year, month;
year month SUM
2005 1 200
2005 2 250
2005 3 530
... ... ...
2006 1 330
2006 2 420
... ... ...
TimeTid DayWeek Month Quarter Year Location SidCity Province Country
ItemPid Model Pname Category SaleTid
PidSid
QtyUnit_Price
Star Schema
• Q2 : Q1에 대한 roll-up / drill-down 연산
2012-11-00 50
SELECT year, SUM(dollars_sold) => year, month, SUM(dollars_sold) FROM Sales S, time T
WHERE S.time_key = T.time_key
GROUP BY year => year, month
year SUM 2005 2200 2006 3250 2007 6530 ... ...
year month SUM 2005 1 200 2005 2 250 2005 3 530 ... ... ...
2006 1 330 2006 2 420 ... ... ...
Rollup Drill down
TimeTid DayWeek Month Quarter Year Location SidCity Province Country
ItemPid Model Pname Category SaleTid
PidSid
QtyUnit_Price
Star Schema
• Q3) Slice for time = "Q1"
SELECT S.*
FROM sales S, time T
WHERE S.time_key = T.time_key AND T.quarter = "Q1";
• Q4) Dice for
(location.city = "Toronto" or "Vancouver") AND (time.quarter = "Q1" or "Q2") AND
(item.item_name = "entertainment" or "PC")
SELECT S.*
FROM sales S, time T, item I, location L
WHERE S.time_key = T.time_key AND
S.lication_key = L.location_key AND S.item_key = I.item_key AND
(T.quarter = "Q1" or T.quarter = "Q2") AND (L.city = "Toronto" or L.city = "Vancouver") AND
(I.item_name = "entertainment" or I.item_name = "PC");
2012-11-00 51
실무사례
실무사례 구축
2016-09-30 CBU / MIS 52
1. DW 요구사항 분석
Sales 전략을 수립하고 실행을 담당하는 Sales Manager는 다양한 분석을 기반으로 의사 결정을
수행하기 위하여 회사의 자재 (materials), 고객(customers), 판매 조직(sales organizations)에 대한 정보와 판매기록에 관한 정보를 (1)~(4)의 sample data와 같이 관리한다.
(1) Material Tables
실무사례
2016-09-30 CBU / MIS 53
(2) Sale Organization data
실무사례
2016-09-30 CBU / MIS 54
(3) Customer Tables
실무사례
2016-09-30 CBU / MIS 55
(4) Sales Data
- 2010년 1월부터 2012년 12월까지 월별 5개씩의 레코드를 추가하여 총 36 * 5 = 180개 정도의 레코드를 입력하시오 (각 필드의 값은 랜덤하게).
- 각 material 별로 unit of measure 값이 선택될 수 있도록 Sales data에 대한 입력폼을 만들어 보시오 (form 만드는 방법 숙지).
실무사례
2016-09-30 CBU / MIS 56
실무사례
2016-09-30 CBU / MIS 57
이상의 데이터를 사용하여 다음과 같은 스타 스키마로
구축한다.
실무사례
2016-09-30 CBU / MIS 58
그림 1-1의 스타 스키마에 대하여 다음과 같은 분석을 실행한다 : SQL로 작성
• Customer Name별로 판매금액의 합계는 ?
• 고객이 거주하는 도시별로 판매금액의 합계는 ?
• 제품 카테고리별로 판매금액의 합계는 ?
• 제품 이름별로 판매금액의 합계는 ? \
• 매장이 위치한 도시별로 판매금액의 합계는 ?
• 매장별로 판매금액의 합계는 ?
• 년도별 Sales Revenue의 합계는 ?
• 년도별, 월별 Sales Revenue의 합계는 ?
• 년도별, 월별, 주별 Sales Revenue의 합계는 ? (주 : 1주 ~ 53주)
• 제품별, 년도별 Sales Revenue의 합계는 ?
• 비오는 날에 Sales Revenue의 합계는 ?
• 환율이 높은 시기/중간/낮은 시기에 제품별로 Sales Revenue의 합계는 ?
실무사례
2016-09-30 CBU / MIS 59
그림 1-1에서 다음과 같은 분석이 불가능하다. 이러한 분석이 가능하도록 1-1의 스타 스키마를 확 장하고, 적절한 데이터베이스를 로딩한 다음에 분석을 하시오.
• 고객이 거주하는 도시별로 Sales Revenue의 합계는 ?
• 고객이 거주하는 도시를 EAST, MIDWEST, WEST로 구분한 지역으로 묶는다고 가정할 때 각 지역별로 Sales Revenue의 합계는 ?
• 고객의 성별로 Sales Revenue의 합계는 ?
• 고객의 연령대별 Sales Revenue의 합계는 ? (10대, 20대, ..., 60대)
• Material 테이블에서 각 항목을 4개의 categories인 Food, Furniture, Electronics, Others 으로 묶은 다음에 각 category별로 Sales Revenue의 합계는 ?
• 앞에서 각 category별로 년도별 Sales Revenue의 합계는 ?
Access DB
• Database – star schema
2016-09-30 CBU / MIS 60
분석 예제
• Customer Name별로 Sales Revenue의 합계는 ?
2016-09-30 CBU / MIS 61
SELECT cust.이름, count(*) as 구매회수, sum(단가*판매량) as 구매금액 FROM cust INNER JOIN sales ON cust.고객번호 = sales.Cust
Group By cust.이름;
분석 예제
• Customer의 고객거주 도시별로 Sales Revenue의 합계는 ?
2016-09-30 CBU / MIS 62
SELECT cust.주소시, count(*) as 구매회수, sum(단가*판매량) as 구매금액 FROM cust INNER JOIN sales ON cust.고객번호 = sales.Cust
Group By cust.주소시;