Data Warehouse 구축 (설계 위주)

Presentation on theme: "Data Warehouse 구축 (설계 위주)"— Presentation transcript:

1 Data Warehouse 구축 (설계 위주)

2 ER model OLTP 설계 Normalization 적은 양의 record access 데이타 특성 중심으로 설계
중복 및 갱신 최소화 anomaly를 최소화 적은 양의 record access 데이타 특성 중심으로 설계 개체간의 관련성 중심

3 ER model(cont’d) Node - table Link - join path
여러 번의 조인 연산에 따른 성능 문제 다양한 join path 최적의 path를 찾기 어려움

4 ER model(cont’d) Ship type Shipper Ship District Credit Order item
Contact loc

5 Data Warehouse Data warehouse 새로운 데이타 모델의 필요성 차원 모델(Dimensional model)
시간 데이타 저장 시간에 따른 경향 분석 요약 데이타 요구 다양한 관점에 의한 데이타 관찰 Non-volatile 질의 위주 새로운 데이타 모델의 필요성 차원 모델(Dimensional model)

6 Dimensional model 사용자 중심의 데이타 모델 사실(fact) table 차원(dimension) table
중요 테이블을 중심으로 이를 분석하기 위한 관점들을 연결 사실(fact) table 분석 질의의 주요 대상이 되는 중심 테이블 차원(dimension) table 데이타 관점을 나타내는 주변 테이블 Star-join schema

7 Dimensional model 사용자 중심의 데이타 모델 사실(fact) table 차원(dimension) table
중요 테이블을 중심으로 이를 분석하기 위한 관점들을 연결 사실(fact) table 분석 질의의 주요 대상이 되는 중심 테이블 차원(dimension) table 데이타 관점을 나타내는 주변 테이블 Star-join schema

8

9 Star schema와 대응되는 다차원 모델

10 Star Schema (retail store)
지역별 분석 추세분석 비교분석 join 판매동향분석 고객성향분석 How many A-type video were sold in March by Boston stores to customers living in St.Louis ?

11 Star schema와 일반 ER schema 비교
Dimension Fact 小 大 Order Number Store Number Customer Transaction Date Grocery Order Store Number Store Name City State Country Telephone Store Customer Order Number Product Number Quantity Amount Order Item Customer First Name Last Name Address City State Country Postal Code 수치 데이터 => sum, average, min, max Product Description Category

12 Fact table Store all transactions for factual data about a business
Have a multi-part key(composite key) Each element of the key is a FK to a single dimension table The remaining fields in fact table are known as Facts Facts are almost numeric much larger than dimension tables

13 Dimension table Dimensions are attribute of facts
Store hierarchical relationships between different grouping or characteristics of those transactions dimension hierarchy time : Year, Quarter, Week, Day Product : Group, Subgroup, Department, Class, Item Territory : Division, Country, Region, Distinct Drill-down, Roll-up의 단위

14 Dimension table Have single key that is joined to a Fact table
The remaining fields are called attributes Dimension attributes Textual, Discrete Source of constraints and grouping columns

15 Data Warehouse 설계 단계 처리요구사항을 정의하고 처리의 중심 대상을 fact table로 정의함
단위 정보 정의, 하나의 거래 내용 정의 일별 snapshot , 또는 월별 snapshot Fact table에 저장할 중요 데이타를 결정함 판매액, 판매량 Fact table의 데이타를 분석할 주요 요인을 차원으로 결정함 mini-dimension 등을 정의 Fact table에서 derived (measured) data정의 according to business rule

16 Data Warehouse 설계 단계 Fact table에 대한 aggregated fact의 level결정
Dimension의 설명을 포함하여 dimension의 attribute를 정의함 DBA, SA, extract programmer, application developer 등이 함께 작업 Data filing Source 데이터와의 매핑관계 설정 Aggregated fact의 저장 방법을 결정함 dimension table 변동 추적 방법 refreshing 방법 결정

17 Data Warehouse 설계 예 처리사항과 fact table식별 fact table의 단위 정보
소매점을 포함하는 소매 연쇄점에 대한 설계 날짜별 상품별 매장별 판매 실적 분석 fact table : 판매 테이블 fact table의 단위 정보 매장별 날짜별 상품의 판매량, 금액, 비용 지역별, 월별, 품종별 판매 실적 집계가능

18 Data Warehouse 설계 예 dimension table결정 판매 사실의 관점 시간 (일, 월, 분기, 회기, 년도)
상품 (품종, 품목) 매장 (점포, 지역) 판촉 결과(할인판매, 할인권 발행, 진열대의 재배치) 고객의 구매 성향

19 Data Warehouse 설계 예 Dimension attributes 시간 상품 매장 판촉
key, 날짜, 월, 분기, 회기, 년도, 요일, 공휴일, 계절 계층관계 (일 - 월 - 분기 - 년도) 상품 key, 품목, 품종, 포장 유형, 크기, 무게, 색깔 계층관계 (품목 - 품종) 매장 key, 점포명, 주소, 도시, 판매지역, 점포 관리자, 전화번호, 점포 넓이, 시설 판촉 key, 판촉명, 할인 유형, 진열 상태, 판촉 비용

20 Data Warehouse 설계 예 Dimension내의 hierarchies 시간 상품 매장 판촉
일 - 월 - 분기 - 년도 상품 품목 - 품종 매장 점포명 - 도시 - 판매지역 판촉 판촉명 - 할인 유형

21 설계 결과(StarSchema) Fact table 상품키 시간키 품목명 날짜 품종 요일 크기 월 포장 유형 분기 부피
무게 색깔 시간키 날짜 요일 월 분기 회기년도 공휴일 계절 Fact table 시간키 상품키 매장키 판촉키 판매수량 금액 비용 판촉키 판촉명 할인 유형 할인권 유형 진열 상태 판촉비용 판촉기간 매장키 점포명 주소 도시 우편번호 판매지역 점포관리자

22 질의 예 1997년 월별 판매량은? 지난달 서울지역 각 대리점의 판매 비용은? 1997년 각 분기의 품종별 판매량은?
1997년 각 분기의 품종별 판매량은? 지난해 공휴일에 가장 잘 팔린 품목은?

23 질의 표현 일반적인 OLAP시스템 SQL OUTPUT = (월, SUM(판매수량))
Restriction = (회기년도 = 1997) OUTPUT format Cross tab 형태로 지정 SQL SELECT 월, SUM(판매 수량) FROM 판매, 날짜 WHERE 판매.시간키=날짜.시간키 and 날짜.회기년도 = 1997

24 일반적인 질의 인터페이스 월 SUM(판매수량) Drag & Drop Star schema구조 Measure list
각 attribute를 선택하여 selection 조건 명시 가로축에 들어갈 attributes list 월 SUM(판매수량) Drag & Drop Star schema구조 세로축에 들어갈 attributes list Measure list

25 질의 결과 Drill-down Roll-up

26 Drill-down 결과

27 Snow-flake schema Fact table 시간키 상품키 매장키 판촉키 판매수량 금액 비용 매장키 점포명 주소
월 분기 회기년도 공휴일 계절 상품키 품목명 품종 크기 포장 유형 부피 무게 색깔 시간키 날짜 요일 월 Fact table 시간키 상품키 매장키 판촉키 판매수량 금액 비용 매장키 점포명 주소 점포관리자 지역 판촉키 판촉명 할인 유형 할인권 유형 진열 상태 판촉비용 판촉기간 지역 우편번호 시 도

28 Star schema 월별, 지역별 매출실적을 구하는 분석요구 star schema로 작성 안 되는 경우
SELECT 월desc, 지역desc, sum(판매액) FROM 판매fact_table ft, 시간차원테이블 d1, 판매처차원테이블 d2 WHERE ft.월id=d1.월id AND ft.대리점id=d2.대리점id GROUP BY d1.월id, d2.지역id star schema로 작성 안 되는 경우 차원 요소간의 관계가 m:n(다대다 대응) multiple hierarchy

29 Snowflake schema (1)

30 Snowflake schema (2) dimension에서 multiple hierarchy를 모델링

31 월id 대리점id 판매액 판매수량 정규화 판매 fact 테이블 년id 년desc 관할id 관할desc 분기id 분기desc
년 차원 테이블. 년id 년desc 관할id 관할desc 관할 차원 테이블 분기 차원 테이블. 분기id 분기desc 년id 지역id 지역desc 관할구id 대리점 차원 테이블 월 차원 테이블. 월id 월desc 분기id 대리점id 대리점desc 지역id 지역 차원 테이블 월id 대리점id 판매액 판매수량 정규화 판매 fact 테이블

32 Snowflake Schema 월별, 지역별 매출실적을 구하는 분석요구 SELECT 월desc, 지역desc, sum(판매액)
FROM 판매fact_table ft, 월차원테이블 d1, 대리점차원테이블 d2, 지역차원테이블 d3 WHERE ft.월id=d1.월id AND ft.대리점id=d2.대리점id AND d2.지역id=d3.지역id GROUP BY d1.월id, d3.지역id

33 Galaxy-style schema - Sharing data
재고 수입 판매

34 Fact 테이블의 분리 Fact 테이블이 공통의 fact와 서로 이질적인 fact로 구성된 경우 Product 차원
Time key day of week holiday flag fiscal period quarter year Product key product description service category type Product key customer key time key primary balance (all) number of transaction (all) service charges (checking) times overdrawn (checking) interest paid (savings) chargeable debits (saving) Product 차원 Time 차원 Customer 차원 Customer key customer name address city, state

35 공통 fact Product 차원 Checking account time 차원 Customer 차원 Saving account
Time key day of week holiday flag fiscal period quarter year Product key product description service category type Product key customer key time key primary balance (all) number of transaction (all) Product 차원 Checking account time 차원 Product key customer key time key service charges (checking) times overdrawn (checking) Customer 차원 Customer key customer name address city, state Saving account Product key customer key time key interest paid (savings) chargeable debits (saving)

36 Galaxy-style schema Dim Dim Fact Dim Dim Dim Fact Fact Fact Dim Dim

37 Aggregated Fact 단위정보는 많은 양의 레코드를 포함 집계 사실(aggregated fact)을 미리 저장
요약 정보를 위해 많은 시간 소요 집계 사실(aggregated fact)을 미리 저장 질의 처리 시간 단축 수백 배 ~ 수천 배 저장 방법 별도의 fact table 생성 level field를 추가

38 새로운 fact table 생성 월단위 시간 차원 Aggregated fact table 상품키 품목명 품종 시간키 크기 월
포장 유형 부피 무게 색깔 시간키 월 분기 회기년도 공휴일 계절 시간키 상품키 매장키 판촉키 판매수량 금액 비용 판촉키 판촉명 할인 유형 할인권 유형 진열 상태 판촉비용 판촉기간 매장키 점포명 주소 도시 우편번호 판매지역 점포관리자 Aggregated fact table

39 새로운 fact table 생성 Aggregated fact이 따로 저장되므로 스키마 상에서 Aggregated fact record를 기초 fact table과 구분할 필요가 없음. 사용자가 Aggregated fact을 알고 있을 필요가 없음. aggregate navigator (집계 항해자) 데이타베이스의 점진적 관리가 가능 메타 데이타에 저장될 내용이 단순함

40 새로운 fact table 생성 월별 aggregation 일별 시간 Dimension 상품 Dimension Fact
월별 시간 Dimension Aggregated Fact 판촉 Dimension 매장 Dimension 월별 aggregation

41 새로운 fact table 생성 월별 aggregation 일별 시간 Dimension 상품 Dimension Fact
Aggregated Fact 매장 Dimension 판촉 Dimension 월별 aggregation

42 가능한 aggregation tables
각각의 Dimension에 hierarchy가 없고 판촉 테이블에 대해서는 aggregation을 안할 경우 시간 상품 매장 시간, 상품 시간, 매장 상품, 매장 시간, 상품, 매장

43 Level field의 추가 Fact table 확장 상품차원 시간키 날짜 요일 월 분기 회기년도 공휴일 계절 상품키
품종명 품목명 부피 무게 색깔 시간키 상품키 매장키 판매수량 금액 비용 매장키 점포명 주소 도시 우편번호 판매지역 점포관리자 Fact table 확장 상품차원

44 Level field의 추가 Level field를 dimension table에 추가하고 기존의 fact table에 aggregated fact을 저장 Level 0 (일별 데이터) Level 1 (월별 데이터) Level 2 (분기별 데이터) 기초 fact와 aggregated fact의 혼돈

45 Level field의 추가 날짜 Dimension

46 Level field의 추가 Fact table

47 Bit Map Indexing 데이터의 cardinality가 크지 않은 경우 ( < 200개정도)
B-tree index보다 성능 우수 성별(남,여), 신분별(학생, 회사원, 무직, . . .), 고객별(우수, 보통, 약성, 이탈) Red : Blue : Yellow : SELECT COUNT(*) FROM table WHERE color=“Blue” or color=“Red” Bitwise-OR 연산

48 Issues in Dimensional Data Modeling
multiple fact tables drill across to combine data from separate fact tables 막대한 조인 비용=> huge immediate result set ex) 특정 product에 대해 ‘수입’fact 과 ‘판매’ fact 비교

49 Issues in Dimensional Data Modeling
dimension table의 크기 fact 테이블과 조인 비용 고려 aggregate data의 유지 Aggregate Table Explosion 각 차원의 hierarchy가 없을 때, n개 차원에서 가능한 aggregate table의 개수 => 2^n개 Row Count Explosion 100 stores, 25 product categories, 50 customer type 각 store당 매월 판매량 : 1,200 records 각 store당 매일 판매량 : 36,500 records 범주당, store당 판매량 : 912,500 records 범주당, 고객타입별, store 판매량 : 45,625,000

50 Aggregate Navigator Architecture
User Query Aggregation Navigator Aggregation metadata DBMS Data + aggregations

51 Aggregate Navigator 의미 질의 변환
a layer of software that chooses the appropriate aggregate table at query time 질의 변환 user’s base-level query => aggregate aware SQL Data + aggregations DBMS Aggregation Navigator metadata User Query

52 Aggregate Navigator Strategy
1 시간 2 상품 3 매장 4 시간, 상품 5 시간, 매장 6 상품, 매장 시간, 상품, 매장 7

53 Aggregate Navigator Strategy
SELECT 일, 매장, SUM(판매량) FROM 시간, 매장, 판매 WHERE 시간.시간키 = 판매.시간키 and 매장.매장키 = 판매.매장키 and 시간.월 = ‘Jan-98’ and 지역 = ‘서울’ GroupBy 일, 매장 SELECT 일, 매장, 판매량 FROM 시간, 매장, 시간매장 WHERE 시간.시간키 = 시간매장.시간키 and 매장.매장키 = 시간매장.매장키 and

54 Browse query Dimension Browsing SELECT DISTINCT fieldname
FROM dimension table WHERE constraint_1 AND constraint_2 ( AND constraint_N)

55 Join query SELECT FROM WHERE GROUP BY ORDER BY
NON-AGGREGATE FIELDNAME1, NON-AGGREGATE FIELDNAME2, SUM(AGGREAGTE FIELDNAME3), SUM(AGGREGATE FIELDNAME4), SUM(AGGREGATE FIELDNAME5) FROM DIMENSION TABLE1, DIMENSION TABL2, . . ., DIMENSION TABL N, FACT TABLE WHERE JOINCONDITION1 AND ... JOINCONDITION N DIMENSIONCONSTRAINT1 AND ...DIMENSIONCONSTRAINT N GROUP BY NON-AGGREGATE FIELDNAME1, NON-AGGREGATE FIELDNAME2 ORDER BY