1. 서 론
업무의 다양함과 자료의 증가로 운영시스템의 유형이 증가하고 있고, 사용자는 여러 운영시스템에서 데이터를 집계 및 비교 분석을 해야 하는 복잡함과 통계 지표를 생성하기 위해서 많은 시간을 투자해야 하는 비효율적인 문제가 발생한다. 이러한 문제를 해결하기 위해 여러 운영시스템에서 정기적으로 데이터를 추출 및 정제(Cleansing)하여 데이터 마트(Data Mart)를 구축하고 이를 기반으로 BI(Business Intelligence) 시스템을 구축하여 기업의 의사결정을 돕는 방법을 사용하는데, 본 논문에서는 교육기관의 학사, 행정, 연구관리의 데이터들에 대해 BI 시스템을 설계하고 구축하였다. 구축한 BI 시스템은 교육기관에서 개별적이고 단편적으로 운영되는 데이터들로 부터 데이터 웨어하우스를 구축하고, 큐브(Cube)를 만들어, 주제별로 통합적이고 가치 있는 고급 정보로 가공해 다차원 관점에서 분석할 수 있게 돕는다.
본 논문의 구성은 다음과 같다. 2장에서는 본 논문에서 기술하고자 하는 BI 시스템의 개념과 이를 구현하기 위한 여러 기술 요소들을 설명한다. 3장에서는 BI 시스템 설계를 위한 여러 테이블 간의 매핑 설계와 요소 설계를 설명한다. 4장에서 BI 시스템을 학교 행정 시스템에 맞도록 구현한 결과를 기술하고, 5장에서 결론을 맺는다.
2. 이론적 배경 및 관련기술의 고찰
2.1 비즈니스 인텔리전스(BI) 개념 및 특징
BI는 기업(Business)의 수익창출과 경쟁력 향상을 위해 광범위한 데이터를 수집하여 의사결정을 도울 수 있게 하는 광범위한 기술들을 일컫는다[1]. 기업의 내⋅외부에서 발생하는 데이터는 Fig. 1과 같이 수집되고 가공되어 정보(Information)로 만들어지고, 생성된 정보를 분석하여 새로운 현상을 이해할 수 있는 지식(Knowledge)으로 만들어진 후, 이 지식이 의사결정자에게 활용되면 이를 지능(Intelligence)이라 하며, 이 지능이 의사결정 과정에 적용되어 기업의 활동(Action) 내용이 만들어진다. 이 기업 활동은 새로운 데이터를 만들게 되고, 이 데이터가 다시 수집되어 사용되는 순환 가치 사슬을 만들어낸다[2].
Fig. 1.Value Chain of Business Intelligence.
BI는 Fig. 2와 같이 데이터 웨어하우스(DW)와 데이터 마트(DM) 및 OLAP(OnLine Analytical Processing)으로 구성된다[3]. Fig. 2의 첫 단계로 나오는 OLTP(OnLine Transaction Processing)는 산업체에서 데이터 입력이나 거래조회 등을 위해 사용하는 트랜잭션 지향의 프로그램이다. 이 OLTP를 통해 DB가 형성된다. 데이터 웨어하우스는 DB에서 효율적으로 추출⋅정제된 데이터를 이용하여, 사용자의 관점에서, 의사결정을 위해 주제별로 요약, 통합, 관리하는 데이터들의 집합이다. 데이터 마트는 데이터 웨어하우스에서 유도된 데이터의 저장소로서, 단일 주제에 초점이 맞춰진 형태의 데이터 웨어하우스이다. 따라서 데이터 마트는 영업팀이나 재무팀 등 단일 부서의 지식을 위해 설계되고 만들어진다. OLAP은 데이터 웨어하우스 환경에서 데이터 접근 전략의 중요한 요소로 최종사용자가 다차원 정보에 직접 접근하여 대화식으로 정보를 분석하고 의사결정에 활용하는 과정이다[4]. OLAP은 다차원적으로 데이터를 분석할 수 있어야 하며, 이를 위해 OLAP 데이터는 다차원 DB에 저장된다.
Fig. 2.Components of Business Intelligence.
BI 분석 기법으로는 기술분석, 예측분석 및 최적화방법이 있다. 기술분석은 과거 데이터로부터 현재 상황의 패턴을 찾는 것이고, 또한 주어진 기간 동안에 어떤 사건이 얼마나 많이 혹은 얼마나 자주, 어디에서 발생했는지에 대한 정보를 찾아내는 것이다. 과거나 현재의 주어진 상황에서 발생한 이벤트에 대해 기술을 하거나, 그 이면에 존재 할 수 있는 요인 혹은 징후를 찾아 보여주는 것을 목적으로 한다. 예측분석은 과거 데이터나 사건으로부터 미래에 발생 가능한 상황이나 사건을 예측하여 선제적인 의사 결정을 지원하는 것이 주목적이다. 최적화방법은 주어진 가능한 결과들을 평가하여 최적의 결과를 도출하는 것을 목적으로 한다. 기업수준의 최적화 모형은 다양한 가정에 대한 시나리오 평가를 기반으로 기업이 취할 수 있는 최적의 의사 결정을 돕기 위해 확률적 기법과 통계적 기법 및 기술 모형, 예측 모형을 병합하여 수행한다.
2.2 ETL(Extract, Transform, Load)
기업의 의사결정을 돕기 위한 목적으로 데이터 웨어하우스를 정기적으로 로드(load)해 와야 하는데, 이를 위해 하나 이상의 소스 시스템(source system)으로부터 데이터를 추출하고(extract), 데이터 웨어 하우스로 복사해(copy) 가져온다. ETL은 Extract (추출), Transform(변환) 및 Load(저장)를 의미하지만, 사실상은 이 세 개의 단계만을 의미하는 것이 아니고, 조직 내외부의 복수의 소스 시스템들로부터 데이터 분석을 위해서 ODS (Operational Data Storage) 및 데이터 웨어하우스 내로 이동시키는 전체 처리 과정을 일컫는다.
2.3 데이터 분석을 위한 OLAP 유형
다차원 분석을 위한 OLAP은 데이터 웨어하우스 환경에서 데이터 접근 전략의 중요한 요소로서 최종 사용자가 다차원 정보에 직접 접근하여 대화식으로 정보를 분석하고 의사결정에 활용하는 과정이며[3,4], MOLAP(Multidimensional On-line Analytical Processing)과 ROLAP(Relational OLAP)으로 구분할 수 있다[5,6]. MOLAP은 다차원 DB를 기반으로 한 OLAP 아키텍처로서, 다차원 데이터의 저장과 처리를 위해 다차원 DBMS가 사용된다. 다차원 배열을 기반으로 하기 때문에 불필요한 항목의 발생에 따른 저장 구조의 낭비와 긴 데이터 로드 시간의 단점을 갖는다. 또한 다차원 DB에 저장된 데이터 조회 속도는 빠르나 원천 데이터에 대한 질의가 용이하지 않다. ROLAP은 관계형 DB에 기반한 OLAP 아키텍처로 팩트(fact) 테이블과 디멘젼(dimension) 테이블로 구성된다. 데이터의 차원 모델링을 활용함으로써 조인(join)의 횟수를 최소화시킬 수 있으나, 어플리케이션에 대한 확장성과 유연성에는 제한이 있다.
2.4 다차원 모델링
다차원 모델링은 비즈니스를 표현하는 가장 자연스러운 형태이며, 분석 목적에 맞도록 사용자의 관점에서 설계된다. 다차원 모델링의 기본적인 요소는 차원과 항목(member 또는 element)이고[5], 다차원 모델링으로는 스타 스키마(Star Schema)와 스노우플레이크 스키마(Snowflake Schema)가 사용된다[3]. 스타 스키마는 하나의 팩트 테이블과 다수의 디멘젼 테이블로 구성되는데 팩트 테이블의 각 튜플(tuple)은 각 디멘젼에 대한 포인터이고 디멘젼 테이블은 각 디멘젼의 속성(attribute)에 해당하는 열(column)들로 구성된다. 스타 스키마는 다차원 정보를 표현할 수는 있지만, 속성들의 계층 구조를 표현하지는 못한다. 스노우플레이크 스키마는 디멘젼 테이블들을 정규화 함으로써 스타 스키마를 더 상세히 표현할 수 있는 구조를 갖는다. 그러나 비정규화된 구조인 스타 스키마가 디멘젼들을 브라우징하는 데에는 더 적합하다.
스타 스키마에서 특정 쿼리에 대해 미리 처리한 결과를 데이터 큐브(OLAP 큐브라고도 불림)로 저장해 놓을 수 있는데 큐브의 각 세그먼트가 하나의 미리 처리된 쿼리 결과에 해당한다. 이 큐브를 통해 사용자들이 실시간으로 여러 쿼리들에 대한 결과를 받아 볼 수 있다. Fig. 3은 3차원 데이터 큐브를 그림으로 보인 한 예이다[7]. 그러나 차원이 높아지면 가시적인 그림으로 표현하기 어렵다.
Fig. 3.A Three-Dimensional Data Cube with Time, Product and Sales Unit Dimensions.
3. 학교행정을 위한 다차원 분석용 BI 시스템 설계
대학교의 관리시스템은 일반적으로 학사와 행정 관련 데이터를 주요 대상으로 하지만, 여기에 특허나 논문 등의 서로 다른 소스 시스템에서 검색해야 하는 여러 연구관리 데이터까지 통합하면, 자료를 찾거나 통계 분석 자료를 만들어 내는 데에 많은 시간과 인력이 드는 복잡한 일이 되며, 행정적 의사결정을 돕기 위한 분석 자료를 유효한 시간 내에 만들어 내는 일도 쉽지 않다. 따라서 본 논문에서는 다양한 소스 시스템으로부터 대학교의 행정시스템을 위한 BI 시스템을 구축하여 이런 문제점을 해결하고자 하였다. 본 3장에서는 2장에서 설명한 다차원 모델링을 이용하여 학사행정용 BI 시스템을 설계하기 위한 과정들을 기술한다.
3.1 업무 대상 한정 및 DB 구축 기본 방향 설계
본 논문에서 구축하는 대학 행정 BI 시스템에서 자료를 추출할 수 있는 업무 대상으로는 학사행정 시스템 정보, 연구⋅일반 행정 시스템 정보 그리고 특허 및 논문관리 시스템 정보로 한정하였다. 소스 시스템에서 데이터를 가져오지 못하는 경우에는 엑셀(Excel)로 업로드가 가능하게 설계하였다.
자료를 가져오는 방법은 소스 시스템 서버에 주제별로 뷰 테이블을 작성하여 BI 시스템의 대상 테이블과 1:1로 매핑하여 정제 없이 소스 시스템 데이터를 우선 그대로 ODS(Operational Data Storage)에 저장하도록 구성하였다. 엑셀 업로드 방식은 사전에 양식을 정하여 각 지표 별 화면에 게시하여 사용자가 해당 양식을 다운로드하여 필요한 자료를 작성한 후 업로드 하게 하여 정제를 한 후 시스템에서 자동적으로 가져온 데이터와 병합하도록 하였다.
3.2 데이터 프로세스 흐름도 설계
데이터 프로세스 흐름도는 Fig. 4에서 보는 바와 같이, 소스 시스템에서 데이터를 추출하여 ODS를 만들고, ODS로부터 데이터 웨어하우스를 생성하며, 그로부터 큐브를 구축한 후, 여러 다차원 분석을 위해 reporting 서비스와 같은 UI 까지 제공하도록 설계하였다. 소스 시스템에 있는 모든 의미 있는 데이터들에 대해 이 흐름에 맞춘 매핑과 테이블 관계 등을 정의하는 일이 시스템 설계에 있어서 주요 작업이다. Fig. 4는 많은 데이터 프로세스 흐름 중 학생정보와 학적변동정보 두 가지에 대한 흐름을 예로 보인 것이다.
Fig. 4.Design Example of Data Process Flows.
3.3 소스 시스템과 ODS간 매핑 정의
소스 시스템의 데이터를 정합성을 유지하면서 ODS로 이체하기 위해서 소스 시스템과 ODS 테이블 간의 매핑관계를 모두 1:1로 정의하였다. Fig. 5는 연구 수탁계약 현황 DB에 대한 ODS로의 매핑에 대한 설계 예이며, 소스 시스템인 연구_O_수탁연구계약 고현황 테이블이 ODS 팩트 테이블로 1:1매핑되도록 설계되었음을 보여준다.
Fig. 5.Mapping between Source Table and ODS Table.
3.4 ODS와 데이터 웨어하우스 관계 매핑 설계
ODS 테이블과 데이터 웨어하우스 팩트 테이블은 1:1 또는 n:1 관계이다. ODS 테이블에 저장된 데이터는 데이터 웨어하우스 팩트 테이블에 그대로 저장하거나 변환되어 저장되게 하였으며, ERD(Entity Relationship Diagram)는 스노우플레이크 스키마 기반 하에서 설계하였다. Fig. 6은 교육 부문 전체 ERD에서 일부를 발췌한 내용이다. 팩트 테이블인 교육_F_수강성적과 디멘젼 테이블 교육_D_수업유형, 공통_D_학기, 공통_D_학위과정이 서로 연관관계가 있음을 분석하여, 설계에 반영하였음을 볼 수 있다. 테이블 이름에 들어간 F는 팩트 테이블임을 나타내기 위한 것이고, D는 디멘젼 테이블임을 나타내기 위한 명명으로 사용하였다.
Fig. 6.ERD Design (Part).
3.5 디멘젼 기반의 큐브 설계
각 그룹 유형별로 ERD를 만든 후, 이를 기반으로 데이터 분석을 위한 큐브를 설계하였다. 큐브 대상업무는 차원별로 분석이 필요한 내용을 중심으로 하였으며, OLAP 다차원 분석을 위하여 강좌 큐브, 수강성적 큐브, 신분 변동 큐브, 휴직 연구 연가 큐브 등 19개의 큐브를 설계하였다.
예를 들어, 강좌 큐브에 사용된 팩트 테이블은 교육_F_강좌와 교육_F_강좌_교과목의 두 개 테이블이고, 연관되는 디멘젼 테이블은 공통_D_학기, 교육_D_교과목 등 12개에 달한다. 강좌 큐브는 갱신주기를 1일로 지정하여 업데이트되게 설계하였으나, 필요에 의해 수정 가능하다.
4. 시스템 구현
대학의 학사행정 시스템 정보, 연구와 일반 행정 시스템 정보, 그리고 특허 및 논문관리 시스템 정보를 통합하여 분석 자료를 만들어 주는 대학 행정 BI 시스템 구축의 설계 과정을 3장에서 기술하였다. 본 4장에서는 이 BI 시스템을 어떻게 구현하였는지 설명한다.
4.1 시스템 개발 환경
BI 시스템 구축을 위해 하드웨어적으로는 DB 서버 1 Set와 웹 서버 1 Set를 사용하였으며, 소프트웨어적으로 DBMS는 MS SQL Server를, 개발 툴은 MS SQL Server 2008 R2를 사용하였다. Table 1에 자세한 사양을 기술하였다.
Table 1.Implementation Specifications
4.2 시스템의 물리적⋅논리적 구성도
구현된 시스템의 물리적⋅논리적 구성도는 Fig. 7과 같다. 외부의 소스 시스템과 엑셀 파일들을 ETL와 직접 업로드를 통해 DB 서버에 데이터 웨어하우스로 구축하였다. 그리고 분석 데이터를 주제별로 구분하여 OLAP 큐브를 생성하여 역시 같은 물리적 위치에 적재시켰다. 사용자의 분석을 돕는 각종 기능은 웹 서버에 위치시켰으며, 이를 위해 분석 데이터를 지표별로 분석할 수 있는 리포팅 서비스와 분석 기능 등을 구현하였다. 리포팅 서비스 구현에는 SSRS(SQL Server Reporting Service)를 이용하였으며, 그래픽 기반의 분석 보고서 작성 구현에는 PPS(Performance Point Service)를 활용하였다.
Fig. 7.System Configuration.
4.3 ODS 저장 주기 지정 기능
일정 주기로 소스 시스템 DB로부터 자료를 가져와서 ODS에 저장하기 위해 주기를 지정할 수 있게 하였다. Fig. 8은 작업일정의 세부 사항을 정의하는 화면을 보인 것이다. 작업 빈도를 일별 수행으로 지정하고, 일별 수행 주기를 1번으로 지정한 예이고. 수행시간은 오전 4시, 시작일자는 2013년 12월 3일이다.
Fig. 8.Refresh Period Setting Capability.
4.4 큐브 구축
3장에서 설계한 바와 같이 각 그룹 유형별로 만들어진 ERD를 기반으로 다차원 분석을 위한 19개의 큐브를 구축하였다. 연관되는 팩트 테이블과 디멘젼 테이블들이 설계대로 모두 구현 시 반영되었으며, Fig. 9의 (a)는 한 예로 강좌수를 측정하기 위해 교육_F_강좌 팩트 테이블에서 필요로 하는 정보와 매핑되어 있는 예제이다. 강좌 수는 개설소속 최초코드의 합으로 구해진다. 교육강좌 큐브의 디멘젼 내역은 Fig. 9의 (b)에 보는 바와 같다.
Fig. 9.Implementation of a Cube (Example).
4.5 분석 보고서 작성 기능
분석 보고서 작성 기능은 엑셀 기반의 문서로 만들거나 그래픽 기반으로 보여주도록 구현하였다. 엑셀 기반의 경우 익숙한 형태의 테이블 형태로 보여준다. Fig. 10의 (a)는 엑셀 기반의 수강 성적 큐브 데이터 분석 보고서의 예이다. 2014년과 2015년의 강좌개설학점 전체와 강좌 주당 강의 시간, 주당 실험실습시간 등의 결과를 보기 쉽게 제공하는 것을 알 수 있다. Fig. 10의 (b)는 그래픽 기반의 보고서 생성 예이다. 20명 미만의 수강 인원을 갖는 824개의 교과목이 있고, 20∼50명 미만의 수강 인원을 갖는 567개의 교과목이 있음을 표와 함께 막대그래프로 보여주고 있다.
Fig. 10.Analytic Report Creation Capability.
4.6 구현된 BI 시스템의 효용성
구현된 BI 시스템의 효용성을 알기 위해, 각 소스 시스템으로 활용할 수 있는 분석에 익숙한 엔지니어를 기준으로 BI 시스템 없이 분석하는 것과 BI 시스템을 사용하여 분석하는 데 소요되는 시간을 측정하였다. 학사시스템강좌 DB 14만 건과 논문실적관리 시스템 DB 약 29만 건의 데이터에 대해 분석 자료를 만들어내는 데 드는 시간과 자료 집계시간, 그리고 분석보고서를 작성하는 데 드는 시간을 비교하였다. Table 2에서 “Without BI” 열은 BI 시스템 없이 처리하는 데 걸리는 시간이고, “With BI” 열은 구축한 BI 시스템을 활용하여 처리하는 데 걸리는 시간이다. Table 2의 “ratio” 열은 (With BI 시간) / (Without BI 시간)의 백분율을 보인 것이다. Table 2에서 보는바와 같이 구축한 BI 시스템을 사용한 경우 상대적으로 매우 적은 시간 안에 분석 리포트까지 만들어 낼 수 있다. 특히 일차원 분석에 비해 다차원 분석을 해야 하는 경우 매우 짧은 시간 안에 필요한 분석을 할 수 있음을 알 수 있다.
Table 2.Comparison of analysis time between with BI and without BI system
5. 결 론
본 논문에서는 대학의 학사행정, 연구 일반 및 논문⋅특허 등의 관리 데이터를 다차원으로 효율적으로 분석하기 위한 BI 시스템을 설계 개발하였다. 데이터 웨어하우스의 팩트 테이블과 디멘젼 테이블들을 설계하고, 각 그룹 유형별로 ERD를 만든 후, 이를 기반으로 데이터 분석을 위한 19개의 큐브를 설계 구현하였다. 행정적 의사결정을 돕기 위한 분석 자료를 빠른 시간 내에 만들어 낼 수 있는 시스템이 갖춰짐으로써, IT 개발부서는 보고서 구축에 들어가는 시간을 절약하고 큐브와 데이터 웨어하우스에 없는 항목을 추가하거나 DB 구성의 변경과 성능 개선에 집중할 수 있게 되었으며, 데이터 분석가들은 다양한 분석을 통해 업무 효율성을 높일 수 있게 되었다.
References
- Glossary of Terms. http://www.oranz.co.uk/glossary_text.htm (Accessed Apr., 8, 2016)
- S. Y. Huh, "Overview of Business Intelligence Concept," Journal of Information Science Society, Vol. 21, No. 10, pp. 5-11, 2003.
- S. Chaudhuri and U. Dayal, “An Overview of Data Warehousing and OLAP Technology,” ACM Sigmod Record, 1997.
- J. H. Cho and S. J. Park, Data Warehousing and OLAP, Dae Chung, 1998.
- C. M. Choi, Design and implementation of Data WareHouse-based for the Efficient Analysis of Data, Master's Thesis of Hanyang University, 2007.
- E. Y. Kim, and H. S. Yong, “Design and Implementation of Multidimensional Data Model for OLAP Based on Object-Relational DBMS,” The journal of Korea Information and Communications Society, 2000.
- L. Hamel, “A Brief Tutorial on Database Queries, Data Mining and OLAP,” The Encyclopedia of Data Warehousing and Mining, Idea Group Publishers, 2005.