Data Warehouse 모델링 및 구축방법

Contents 데이터 웨어하우스 구축 과정 데이터 웨어하우스 설계 방법 및 예 Aggregation 테이블 설계 방법 Data Warehouse 구축사례 국군정보사령부 사례 영등포 구청 수도 사업소

확장적인 D/W구축 1월 3월 5월 성공적인 DW구축은 작고, 빠르게, 반복적인 개발노력으로 가능 Big Bang 접근은 실패가능성이 큼 1월 국내원자재 외자제 3월 국내원자재, 외자재 국내제품, 대형고객 5월 국내원자재, 외자재, 국내제품, 대형고객 해외고객, 외국제품, 유망고객

Data Warehouse 구축 과정 문제 정의 데이터 모델링 구축의 필요성 인식 및 구축으로 인한 이득 분석 범위 결정 운용 시스템분석 및 데이타 종류 분석 요구되는 기능 조사 데이타 크기 및 증가치 분석 데이터 모델링 logical design 주제 설정 및 주제별 모델링 dimensional modeling

Data Warehouse 구축 과정(cont’d) 구조 설계 legacy system부터 최종 사용자 인터페이스 까지 Integration strategy loading management Repository관리 repository 및 필요한 도구 security, access control archive/restore strategy 요약 정보 구축 전략

Data Warehouse 구축 과정(cont’d) 구현 설계 H/W, S/W, networking setup 물리적 데이터 모델링 및 구축 데이터 로딩 data extraction from legacy system or external environment Front-end tool configuration OLAP and other tool configuration

Computing Platform for Data Warehousing

Money Matters

A Roadmap to Data Warehouse How do you justify the warehouse ? Data organization and Migration Database management, hardware and networking Information analysis and delivery

How do you justify the Warehouse To save money To speed information retrieval To become more competitive To improve productivity through improved access to information To improve decision making

Data Organization & Migration Where do the data originate? In transaction system A wide variety of other source market research company departmental PCs What extracts the data, transform it, and cleans it? Data copying and replication tool non-relational database에서는 사용할 수 없음 transforming과 cleaning도 제대로 지원하지 않음.

Data Organization & Migration Data transformation tool extract information from both relational database and non-relational database convert codes, aggregate, calculate derived values program generation Data cleaning tool 서로 다른 spelling이나 format으로 구성된 데이터를 하나의 통합된 형태로 변형. 예) 이름의 표현법

Data Organization & Migration What middleware links the sources with the warehouse 사용자에게는 알리지 않은 상태로 두개의 프로그램을 연결 사용자는 두개의 프로그램이나 데이타베이스가 하나의 통합된 형태로 보임 예) non-relational database에 대한 SQL

Database Management, Hardware, and Networking In what database are the warehouse data stored? RDB well-understood management, backup, and recovery 많은 양의 데이터 수용가능 병렬 처리의 가능성 대양한 인덱스 MDB specialized database Dual approach 대부분의 데이터는 RDB, 특화된 데이터는 MDB RDBMS vendor들이 MDB사를 인수

Database Management, Hardware, and Networking On what computer systems are the data stored? On Mainframe, On system UNIX/RISC, On Intel-based? Demand for data warehousing is unpredictable 사용하면서 요구 사항이 수시로 변함. 데이터의 양이 어느 정도 까지 늘어날지 예측못함.

Information Analysis and Delivery How do people find the information they need? 데이터 웨어하우스를 사용할 때 원하는 정보를 얻기 어렵거나 심지어 잘못된 결과가 나온다면? 사용자들이 점점 사용을 기피 사용하기 쉽게 고치기 위한 사용자나 구축자간의 노력 What special techniques speed the retrieval? 강력한 hardware 향상된 질의 처리기 다양한 index 적절한 요약 테이블

Information Analysis and Delivery How is the data delivery to end user? Querying and reporting system? Multidimensional database system? Relational OLAP system? What development tools create application for EIS and DSS? EIS tool Programming language Visual C++, Visual Basic S/W development too, PowerBuilder

Information Analysis and Delivery How is value is added through alerts Exception reporting or Alerts monitor specific indicator send out e-mail or other notices whenever values exceed pre-specified ranges

Management What management the process? What manages the systems? Matadata management facilities What manages the systems? Security network monitoring backup

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

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

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

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

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

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

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

다차원 모델링 Dimensional data cube Flattened view

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

Star schema와 정규 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

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

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의 단위

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

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

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

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

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

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

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

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

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

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

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

질의 결과 Drill-down Roll-up

Drill-down 결과

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

판매처 차원테이블 Time 차원테이블 월id 대리점id 판매액 판매수량 비정규화 판매 fact 테이블 대리점id 대리점desc

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

Snowflake schema (1)

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

월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 테이블

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

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

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

공통 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)

Galaxy-style schema

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

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

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

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

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

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

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

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

Level field의 추가 날짜 Dimension

Level field의 추가 Fact table

DW 시스템 성능 향상 Denormalization Summarization Partitioning Sampling Indexing Optimization of Join query

Denormalization 정규화의 불필요성 조인의 필요성을 없앰 갱신이 거의 이루어지지 않으므로 중복되어도 문제 없음 성능 향상 JOIN

요약(Summarization) 요약 요약된 table생성 계층적 요약 테이블 구성 상세 질의성능을 향상 중복 테이블의 효과 aggregation navigator disk낭비 base table변경시 이를 반영해주어야 (consistency 문제발생) refresh 주기, version관리 계층적 요약 테이블 구성 질의의 granularity에 따라 알맞은 요약 테이블을 탐색하여 질의 수행 상세

Summary Summary 방법 1 각 차원 항목과 fact table을 조인하여 요약 테이블 생성 품종ID 품종DESC 모델ID 구입일 … 품명ID 판매량 모델ID 품종ID 모델명 구입일 구입월 구입분기 구입년 일DESC 구입월 구입분기 구입년 월DESC 구입분기 구입년 분기DESC 구입년 년DESC Summary 방법 1 각 차원 항목과 fact table을 조인하여 요약 테이블 생성 예) 두개의 dimension에 대한 요약 테이블 => 14 요약 table 수 =  (각 차원 항목 수 + 1) -1

가능한 aggregation tables 격자 형태 ex) 차원이 시간, 상품, 매장 각각의 Dimension에 hierarchy가 없음 시간 상품 매장 시간, 상품 시간, 매장 상품, 매장 시간, 상품, 매장

Summary Summary 방법 2 여러 개의 차원들을 한 table에 같이 구성 특정부서나 개별 사용자의 요구 사항을 분석하여 자주 사용되는 몇 개의 차원 table을 비정규화하여 fact table을 재구성 summary 질의 예 CREATE summary-table SELECT AS 품종차원table.ID, 품종차원table.desc, 월차원table.ID, 월차원table.desc, count(fact_table. 판매량) FROM fact_table, 일차원 table WHERE 일차원table.월ID=월차원테이블.ID GROUP BY 품명차원table.품종ID, 월차원table.ID

Aggregate fact table - 2 dimension How many customers purchased products ? How many product units were sold for A store ? What was the total product sales for the day ?

Aggregate fact table - 3 dimension

분할(Partition) 큰 테이블을 여러 개의 조그만 테이블로 변환 Join이나 Union 작업 필요 연간 매출액 응답 속도 향상 부분적인 back-up, 복구가 용이 Join이나 Union 작업 필요 연간 매출액 월별 매출액 …...

Partitioning 데이터 분할 방법 (+) 작업 병렬화 => 조회 속도 향상 (+) 대용량 데이터 유지 용이 (+) 백업, 복구 용이 (-) 분할된 테이블간 조인 비용 방법 Round Robin 분할 범위 조건 분할 해싱 분할 랜덤 분할

Partitioning Round Robin 분할 범위 조건 분할 fact table의 각 레코드를 각 분할에 분배 . . . 1 2 N N+1 N+2 2N 범위 조건 분할 fact table의 특정 필드의 값에 따라 분배 ex) 전체 판매 테이블 => 년도별 판매 테이블 . . . 68 12 25 분할 조건 : Age < 20 20<=Age<30 Age >=60

Partitioning Equi-join FACT TABLE Round-Robin Partition Dimension Table Hashing Partition Dimension Table Hashing Partition Equi-join FACT TABLE Round-Robin Partition Dimension Table Hashing Partition Dimension Table Hashing Partition

Partitioning 질의시 View 작성 각 질의에 대해 지정된 선택조건에 따라 불필요한 분할 영역은 제거 예) 일년 판매 테이블 => 각 분기별로 분할 ALTER TABLE Q1_sale ADD CONSTRAINT C0 check (sales_date between jan-1-1997 and mar-31-1997) . . . CREATE VIEW sales AS SELECT * FROM Q1_sales UNION ALL SELECT * FROM Q2_sales UNION ALL SELECT * FROM Q3_sales UNION ALL SELECT * FROM Q4_sales

Sampling DW시스템의 비용감축, 성능향상을 위해 source시스템으로부터 데이터를 표본조사방법을 이용하여 추출 데이터 용량 크게 감소 => 질의 성능 대폭 향상 전체 데이터 량이 적을 수록 표본 비율을 높여야 육안 분석에 중점을 둔 분석질의에 적합 예) 추세 분석, 예측분석(주가, 환율등) 관건은 분석결과의 정확도, 신뢰도 적정 유지 실제 추세 표본 추세

Index B tree index well suited to finding and retrieving a small number of rows DW환경에는 부적합 cardinality가 적은 attribute에는 무의미 예)남/여 DW환경에서의 B-tree 구축시 막대한 유지비용 sensitive to bulk inserts B-tree는 비교적 간단한 질의이거나 이미 access path가 알려진 환경에 적합

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

Bitmap Indexing select k5, k10, count(*) from bench group by k5, k10 0 0 # 1 2 . . . 9 3 4 10000 00001 00010 k5 0100000000 0000100000 1000000000 0001000000 k10

Issues in Dimensional Data Modeling Denormalization dimension의 계층구조 변화 => 비정규화된 schema상에서 수정되어야 할 record수가 아주 많을 수 있다.

Time dimension 설계 - 정규화 fiscal year/month/week calendar year/month/week holiday

Time dimension 설계 - 비정규화

Issues in Dimensional Data Modeling Region | Territory Metro Area Community Store 정규화

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 비교

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

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

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

Aggregate Navigator Strategy Rank all the aggregate fact tables from the smallest to the largest For small aggregate fact table, look in its associated dimension tables to verify that all the dimensional attributes in the current query can be found, If found, rewrite query. If Step 2 fails, find the next smallest aggregate fact table.

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

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

Data Warehouse 구축 전체 시스템 구성을 위한 프로세스적 구축 DW Database 자체의 설계 H/W, S/W 선택 benchmarking fact, dimension table loading browse querying, join querying indexing , multi-user support ex) 10억 레코드 fact table, 10만 레코드 dim. table DW Database 자체의 설계 ex) star (snow-flake) schema

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

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

Compression 디스크 공간 절약 압축된 데이터 전체를 메모리에서 조작 디스크 접근 속도 향상 제한된 메모리를 가지고 “hit ratio”를 높임 디스크 접근 횟수 줄임

Data Warehouse 구축 비용 Internal Labor Consulting Software Hardware Computer Storage Device Network DBMS ETT OLTP tool

구축사례 국군정보 사령부 사례 영등포 구청 수도 사업소 서울대 통신망 유통량 분석

국군 정보 사령부 사례 전선관측 자료 과거의 관측 자료를 분석하여 현재와의 변동사항 도출 155마일 전선에서 매일 수집 종합 수집(수집 관리반) 기록/평가/해석(전보 생산조) 과거의 관측 자료를 분석하여 현재와의 변동사항 도출 적의 위협 정도 파악, 조기 경보에 기여 신속하고 정확한 첩보 처리와 정보 제공 지휘관 및 참모의 의사결정에 기여 문제점 방대한 양의 자료, 단순 통계분석

전선 관측 모델 차원 기간 적의 활동 종류 동거 좌표 북거 좌표 78년계: 1월 초순부터 12월 하순까지 36개 완전 무장, 단독 무장, 차량 이동, 교육, 훈련, 포성/폭음/총성, 아방 관측, 연기, 불빛, 특이활동, 포/장비 이동 동거 좌표 E12 ~E32 북거 좌표 N42 ~ N53

테이블 뷰

좌표별 분석

기간별 분석

영등포 구청 수도 사업소 상수도 행정의 체계화 누수량, 시설비, 노후화에 따른 배관 교체의 효율적 관리 누수관 교체 계획 수립, 복구 비용 절감 1996년 1월 ~1996년 6월 인원 처리 일지 누수 현황 일지

데이터 구조 Dimensions Measures 기간 조치사항 지역 (각 동별) 수도관 종류 수도관 구경 1월 2월 3월 4월 5월 6월 조치사항 밸브 합점/폐쇄, 박킹 교체, 엘보 접합, 맹세틀 조임, 누수 방지 대접합,... 지역 (각 동별) 수도관 종류 아연도광관, PVC관, PE관, 스테인레스관, 동관,.. 수도관 구경 급수관(13M/M,…50M/M), 배수관(75M/M ~ 700이상) Measures 복구 인원, 복구 기간, 지급액, 누수량

월별 지역별 누수량

지역별 월별 누수량

지역별 공사 내역

서울대 통신망 유통량 분석 장애관리, 구성관리, 성능 관리 이용 통계 관리 일별, 주별, 월별 요약된 유통량 정보 장단기 통신망 용량 설계 시 이용 주기적 리포트 작성

서울대 LAN 구성도

MetaCube의 구성

서울대 통신망 유통량 분석 DW (Informix) Packet header 가공정보 통신망 SNOOP 추출/변환/전송 모듈 Metacube Explorer Metacube Engine Informix-Net Warehouse Manager

데이터 모델 패킷 정보 Fact table 출발지, 목적지의 도메인 이름 IP 주소 패킷 크기 프로토콜 이름 패킷단위 정보 시간, 프로토콜, 출발지, 목적지, 패킷 크기

데이터 모델 Dimension tables 시간 프로토콜 출발지 목적지 시, 일, 주, 월, 년,... 트랜스포트 계층, 응용 계층 출발지 목적지 호스트 이름, 호스트 IP, 부기관, 기관, 범주

Star Schema packet_source source_code hostname_code packet_time hostname_desc s_address suborg_code suborg_desc organization_code organization_desc packet_time time_code hour_code hour_desc day_code day_desc date_code date_desc month_code month_desc quarter_code quarter_desc packet_info packet_code time_code protocol_code source_code destination_code packet_size packet_destination destination_code hostname_code hostname_desc d_address suborg_code suborg_desc organization_code organization_desc packet_protocol protocol_code transport_code transport_desc application_code application_desc agg_level

Snowflake Schema

주요 서버 이용률

처리 속도 비교

Data Warehouse, OLAP의 미래 표준화 Meta data 표준화 제품간 meta data의 호환 => MIF (Metadata Interchange Format) OLAP 표준화 공통 API, 용어, 기술 Codd’s Rule Intranet과 OLAP 지역적 제한 극복, 정보 접근 분석 Agent와 OLAP 반복적인 분석질의 => time-consuming 정보 과부하 => not enough information 분석 대리인 기능 요구

Data Warehouse, OLAP의 미래 DB 기술적 측면 optimization of star join queries new indexing method application of geographic indexes data compression parallel processing caching scheme suitable to OLAP queries syntax extensions for SQL rank, N-tile, MovingAvg, MovingSum, DimensionCount ex) WHERE . . . RANK(f.sales) <= 10

Successful Data Warehouse 구축의 목적을 분명히 프로젝트의 범위 및 정확한 요구사항의 정확한 이해 비즈니스 추세, 잠재적 사용가치 등에 의거 미래의 요구 예측 최적의 비용 정보제공의 전과정에서 비용감소 노력 formal한 구축 방법론에 입각 적당한 크기 최고 간부의 의지 전사적인 지원 및 경영층의 뒷받침 비즈니스 사용자 참여 계속적인 유지 “Data Warehouse is not just a technology, it’s a process.” CASE와 같은 DW설계, 구현, 유지보수를 위한 모델링도구

OLAP Tool, Data Mining Tool

select 년도, 지역, 도시, sum(판매량) 다차원 분석질의 변환 예제) ‘96년도 판매량이 가장 많은 2곳의 판매소와 판매량을 출력하라. 사용자 인터페이스 질의 변환기 select 년도, 지역, 도시, sum(판매량) from 판매소, 시간 where 년도=‘96’ group by 지역, 도시

다차원 분석질의 변환 예제)최고판매량을 보인 대전의 월별 판매량을 출력하라. (drill-down) pivoting, roll-up, slicing등은 새로운 SQL을 만들 필요 없음. select 년도, 지역, 도시, sum(판매량), 월 from 판매소, 시간 where 년도=‘96’ and 도시=‘대전’ group by 지역, 도시, 월

메타메이타 실제예 star schema자체 정보 fact table dimension table dimension 계층구조 database 정보

ROLAP (Star Tracker) 질의방식 => dragging 질의결과

ROLAP (Star Tracker) Star schema 형태에 의한 사용자 인터페이스 기본 분석질의 방법 ranking aggregation Top N Drill-down

MOLAP(PowerPlay)

MOLAP(PowerPlay) 예1) What are the top 10 computer index companies, with annual revenues greater than 100 million dollars and year-to-year revenue growth greater than 20%, in terms of 1997 profit margin ?

MOLAP(PowerPlay) 예2) What were the 1996 year-to-year growth rates for revenue, net income, and market value for U.K. companies, broken down by company size? Drill down/up analysis cross tab table, clustered bar display multiple measure

MOLAP(PowerPlay) 예3) What is the relationship bet’n earnings/share growth and beta value for companies in each of the Nasdaq indices with less than $100 million market value? correlation

Data Mining Classification decision tree attribute selection &pruning training & prediction