Data Warehouses & Multi-dimensional Databases 2014 서울시립대학교 전자전기컴퓨터공학부 김한준 교수

Data Warehouse 기반 정보 아키텍쳐 DSS Legacy database Data Mining EIS 변환/추출 DW 기존 응용 시스템 분석 Reports Data Processing (Transaction) Information Processing OLTP성 DB와 OLAP성 DB(DW)의 분리

Data Warehouse 경영 및 정책결정에 필요한 정보 처리 기능을 효율적으로 지원하는 데이타베이스 시간 데이터 저장 44 Data Warehouse 경영 및 정책결정에 필요한 정보 처리 기능을 효율적으로 지원하는 데이타베이스 시간 데이터 저장 다양한 관점에 의한 데이터 관찰 시간에 따른 경향 분석 요약 데이터 요구 비휘발성 새로운 데이터 모델의 필요성 :다차원 모델(Dimensional model)

Data Warehouse subject (measure) -> ‘fact’ 테이블 Decision Making을 위해 특별히 설계된 ‘subject’ 중심적인 데이터 저장소로서 다양한 ‘view’에서 관찰 분석이 가능 subject (measure) -> ‘fact’ 테이블 view -> ‘dimension’ 테이블

Data Warehouse의 특성 주제중심적 (subject-oriented) 통합적(integrated) 시간성(time variant, historical) 비휘발성(non-volatile)

Data Warehouse의 특성 주제 중심 (Subject oriented) OLTP시스템의 데이타는 업무처리 중심 (process/function oriented) 으로 이루어짐 DSS의 관심은 정보 시스템의 대상이 되는 주제들임 DW의 Data Model은 주제들을 중심으로 이루어짐 다차원 모델 DSS에 쓰이지 않는 데이타는 DW에서 제외됨 Operational Data Warehouse 고객 대출처리 예금처리 판매 카드처리 재고

Data Warehouse의 특성 통합된 데이타베이스 (Integrated) 개체명 (table 이나 column) 단위 기호 일관된 데이타 원천 (source) 잔고 금액 현재잔고 잔고 응용 A 지름:cm 응용 B 지름:inch cm 변환 응용 A 남, 여 응용 B m, f 응용 C 0, 1 m , f

Data Warehouse의 특성 시간성 (Time variant) OLTP: 현재 순간의 현실세계 모습 DW: 특정주제가 시간에 따라 변화한 모습을 담고 있어야 함 고객 홍길동의 월별 전화 사용량 품목별 일별 판매량 키(key)에 시간 성분이 포함되어야 함 홍길동 from:94/1/1 to :현재 주소: 서울 홍길동 from:94/1/1 to :96/3/6 주소: 서울 홍길동 1996.3.7. 서울에서 부산으로 이사 홍길동 주소:서울 나이: 24세 update 홍길동 from:96/3/7 to :현재 주소: 부산 append 홍길동 주소:부산 나이: 24세 OLTP DW

Data Warehouse의 특성 갱신이 일어나지 않음 (nonvolatile) 레코드 단위의 갱신이 없음 cf) 실시간 데이터 웨어하우스 데이타의 추가 (load)만 있음 정규화가 크게 중요하지 않음 OLTP DW update insert load access delete

분석을 위한 DB의 구성 ? 해당 좌표에 분석대상을 저장 measures 분석의 관점 dimensions

Data Warehouse 구축 Multi-dimensional DB (n차원 배열형태) 로 구축 Product dimension measures dollars_sold units_sold dollars_cost Store dimension Date dimension

Data Warehouse 구축 관계형 데이터베이스에서 DW의 구축

Data Warehouse 스키마 관계형 데이터베이스에서 DW의 구축 : Star schema model measures

Data Warehouse 구축 Relational DB로 구축 measures time_key (FK) product_key ‘Time’ Dimension 테이블 time_key day_of_week month quarter year holiday_flag ‘Product’ Dimension 테이블 ‘Store’ Dimension 테이블 measures time_key (FK) product_key store_key dollars_sold units_sold dollars_cost ‘Sales’ Fact 테이블

Data Warehouse 구축 Multi-dimensional DB로 구축 measures dollars_sold units_sold dollars_cost

Data Warehouse 구축 M e t a D Current Detail highly summarized 10년간의 품목군별 월별 판매량 M e t a D lightly summarized 5년간의 품목별 주간 판매량 Current Detail 올해 판매 구체적 데이타 (current detail) OLTP 로부터 load 지난해까지의 판매 구체적 데이타 (older detail)

Data Warehouse 활용 연산: Slicing 특정 차원을 고정시켜놓고 다른 차원들의 데이터를 관찰 예: ‘EverMore’ 지점을 고정하여, data, product 차원에서 판매 현황 관찰

Data Warehouse 활용 연산: Dicing 각 차원 마다 범위를 설정하여 보다 제한된 cube를 생성 예: Time 차원 범위: 208년 4월 Product 차원 범위: Food 카테고리 Store 차원 범위: Florida

Data Warehouse 활용 연산: Drill-down (“Show me more detail”) 연산: Roll-up 상위 수준의 요약정보로부터 시작하여 단계적으로 관련된 구체 데이터를 추적하는 과정 예: 2014년 분기별 판매량 정보 2014년 월별 판매량 정보 연산: Roll-up drill-down의 반대 연산

Data Warehouse 활용 연산: Pivoting (or Rotating) n개 차원의 방향을 전환 새로운 차원의 삽입하면서 수행할 수도 있음

Data Warehouse 스키마

Data Warehouse 구축: R의 활용 Dimension 테이블의 구축 product 차원 테이블: prod_table time 차원 테이블: month_table location 차원 테이블: state_table state_table <- data.frame(key=c("CA", "NY", "WA", "ON", "QU"), name=c("California", "new York", "Washington", "Ontario", "Quebec"), country=c("USA", "USA", "USA", "Canada", "Canada")) month_table <- data.frame(key=1:12, desc=c("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"), quarter=c("Q1","Q1","Q1","Q2","Q2","Q2","Q3","Q3","Q3","Q4","Q4","Q4")) prod_table <- data.frame(key=c("Printer", "Tablet", "Laptop"), price=c(225, 570, 1120))

Data Warehouse 구축: R의 활용 Fact 테이블의 구축: random하게 measure 데이터 생성 gen_sales <- function(rec_num) { # Generate ‘fact’ data randomly loc <- sample(state_table$key, rec_num, replace=T, prob=c(2,2,1,1,1)) time_month <- sample(month_table$key, rec_num, replace=T) time_year <- sample(c(2012, 2013), rec_num, replace=T) prod <- sample(prod_table$key, rec_num, replace=T, prob=c(1, 3, 2)) unit <- sample(c(1,2), rec_num, replace=T, prob=c(10, 3)) # 판매 개수 amount <- unit * prod_table[prod, ]$price # 판매금액 = 개수 * 단가 sales <- data.frame(month=time_month, year=time_year, loc=loc, prod=prod, unit=unit, amount=amount) sales <- sales[order(sales$year, sales$month),] # 시간에 따라 fact data의 sorting row.names(sales) <- NULL return(sales) } loc <- sample(state_table$key, rec_num, replace=T, prob=c(2,2,1,1,1)) 샘플링 대상 샘플링 횟수 중복허용 샘플링 확률

vector 또는 data.frame의 정렬 sales <- sales[order(sales$year, sales$month), ] 1차 정렬 2차 정렬 x <- c(10, 50, 40, 30, 5) order(x) # 5 1 4 3 2 를 출력 5번째 값 < 1번째 값 < 4번째 값 < 3번째 값 < 2번째 값 x <- x[c(5,1,4,3,2)] # => x <- x[order(x)]

Data Warehouse 구축: R의 활용 ‘Sales’ Fact 테이블의 생성 sales_fact <- gen_sales(500) head(sales_fact)

Data Warehouse 구축: R의 활용 library(sqldf) sqldf("select * from state_table") 표준 SQL 문장 처리 sqldf("select s.key, sum(amount) from sales_fact f, state_table s where f.loc=s.key group by s.key ")

Data Warehouse 구축: R의 활용

Data Warehouse 구축: R의 활용 다차원 Multi-dimensinal DB(cube)의 생성 revenue_cube <- tapply(sales_fact$amount, sales_fact[ , c("prod", "month", "year", "loc")], FUN=function(x){return(sum(x))})

tapply 연습 vector SQL의 “group by” 연산과 유사 첫 인자 vector와 동일한 길이의 index (factor) SQL의 “group by” 연산과 유사

Data Warehouse 구축: R의 활용 dimnames(revenue_cube)

DW Operations OLAP operations Slice Dice Rollup Drilldown Pivot

OLAP (On-Line Analytic Processing) 적극적인 정보사냥 Information Source Information Source Information Broker

OLAP 정의 FASMI Fast Analysis of Shared Multidimensional Information interactive system (5 ~ 20초 이내의 응답을 요구) keep a chain of thought Analysis Ad hoc. Analysis , Flexible end-user calculation Shared multiple access , one-write, multiple-read security 보장 Multidimensional MDB에서 처럼 데이터를 다차원적으로 관찰 eg) 판매정보의 차원 : 날짜, 품목, 대리점, 사원 등의 관점에서 관찰

DW Operations Slicing # cube data in Jan, 2012 revenue_cube[, "1", "2012",] revenue_cube["Tablet", "1", "2012",] cube 차원 순서 : "prod", "month", "year", "loc "

DW Operations Dicing scube <- revenue_cube[c("Tablet","Laptop"), c("CA","NY")] # dicing scube[ , "1", , "CA"] # 작은 size의 cube에서 질의 cube 차원 순서 : "prod", "month", "year", "loc "

DW Operations Roll-up apply(revenue_cube, c("year", "prod"), cube 차원 순서 : "prod", "month", "year", "loc " Roll-up 관찰하고 싶지 않은 차원을 없애기 위해 집계함수(aggregation function) sum 을 이용 예: year, prod, loc 차원에 대해 데이터 관찰 -> loc 차원 없애고, year, prod 차원에 대해 데이터 관찰  apply(revenue_cube, c("year", "prod"), FUN=function(x) {return(sum(x, na.rm=TRUE))}) (n차원) array FUN함수를 적용하게 되는 기준 컬럼

DW Operations Drill-down 차원의 추가 또는 차원 하향 레벨링을 통해 세밀하게 데이타 관찰 예: 각 product에 대한 매년 판매량 관찰 -> 매달 판매량 관찰 apply(revenue_cube, c("year", "month", "prod"), FUN=function(x) {return(sum(x, na.rm=TRUE))})

DW Operations Pivoting 예: year vs. month (또는 month vs. year) 차원에 대한 판매량 관찰 예: product vs. location (또는 location vs. product) 차원에 대한 판매량 관찰 apply(revenue_cube, c("year", "month"), FUN=function(x) {return(sum(x, na.rm=TRUE))}) apply(revenue_cube, c("prod", "loc"), FUN=function(x) {return(sum(x, na.rm=TRUE))})

2014 서울시립대학교 전자전기컴퓨터공학부 김한준 교수 빅데이터 분석 원리 및 분석 실습 교안 2014 서울시립대학교 전자전기컴퓨터공학부 김한준 교수 본 결과물은 교육부의 지원으로 수행한 공학교육혁신사업의 수행결과입니다. This work is financially supported by the Ministry of Education (MOE) through the fostering project of the innovation for Engineering Education.