OLAP 엑셀 피벗 테이블을 이용한 온라인 분석 처리 구현

엑셀 피벗 테이블을 이용하여 온라인 분석 처리를 구현하는 과정을 보인다.

서버에 데이터베이스 생성

# 테이블 dept, student, lecture, class table 생성
CREATE TABLE dept(did CHAR(8),dname CHAR(16),
PRIMARY KEY(did));
CREATE TABLE student(sid INT, sname VARCHAR(16), 
dept CHAR(8), PRIMARY KEY(sid));
CREATE TABLE lecture(lid CHAR(16), title VARCHAR(16), 
lecturer INT,PRIMARY KEY(lid));
CREATE TABLE class(cno INT AUTO_INCREMENT,
lid VARCHAR(8), sid INT, semester VARCHAR(16), 
grade VARCHAR(8), PRIMARY KEY(cno));

# 행 입력
insert into dept values('IE', 'Industrial Eng'),
('ME', 'Mechanical Eng'),
('CE', 'Construct. Eng');

insert into student values(1, 'A', 'IE'),
(2, 'C', 'IE'), (3, 'C', 'IE'),
(4, 'D', 'ME'), (5, 'E', 'ME');

insert into lecture(lid, title) values('DB','Database'),
('OR','Ops Research');

insert into class(lid, sid, semester, grade) values
('DB',1, '2010S', 'A'), ('DB',2, '2010S', 'A'),
('DB',3, '2010S', 'B'), ('DB',4, '2010S', 'B'),
('OR',1, '2010S', 'A'), ('OR',2, '2010S', 'B'),
('OR',4, '2010S', 'B'), ('OR',5, '2010S', 'B');

# 데이터 큐브에 자료를 지원할 view 생성
CREATE view data_cube AS
SELECT c.cno, l.title, c.semester, s.sid, s.sname, c.grade, d.did
FROM lecture l, class c, student s, dept d
WHERE l.lid= c.lid and s.sid = c.sid and s.dept = d.did;

 

ODBC를 이용한 데이터베이스 연결 및 피벗 테이블 생성

MariaDB ODBC 드라이버 설치 및 연결(데이터원본) 만들기 읽어보기 >

ODBC를 이용한 MariaDB 엑셀에 연결 읽어보기 >

데이터가 있는 엑셀 시트로 부터 피벗 테이블을 생성한다.

피벗 테이블이 생성되면 자동으로 피벗 테이블 보고서(좌측)과 피벗 테이블 필드(우측) 창으로 나누어진 OLAP 환경으로 전환된다.

엑셀에서 MySQL, MariaDB 데이터베이스 검색 조건 연동하기

데이터베이스의 내용을 ODBC를 통하여 엑셀에 연결할 경우 다양한 장점이 있음:

• • 데이터베이스 응용 프로그램을 개발할 필요가 없음
  • Microsoft Query(MS Query)를 이용하여 엑셀에서 직접 SQL Query 문을 바꿀 수 있음
  • 엑셀의 차트 기능을 이용할 수 있음
  • 엑셀의 자료분석 기능(예 피벗테이블)을 이용할 수 있음

 

□ MS Query에서 SQL 문을 사용하여 질의를 구성할 수 있음

• • 엑셀의 데이터 연결에서 MS Query를 이용하여 SQL 지 의문을 이용한 데이터 연결 가능
  • 엑셀의 데이터 연결 속성 변경을 이용하여 SQL 질의 문 변경 가능

 

□ MS Query에서 매개 변수를 이용하여 검색 조건을 정의할 수 있음

• • MS Query의 매개 변수는 사용자가 입력하거나 엑셀의 셀러 값을 이용할 수 있음
  • MS Query에 SQL Query 문을 정의할 때 변수를 검색 조건에 사용
  • -> 자체 Editor에서 정의가 안될 경우가 있어 엑셀에 연결 후 데이터 연결 속성 변경을 통해 매개 변수 사용

 

준비사항:

• • MariaDB ODBC Driver 설치 관련 글 읽어보기 >
  • MariaDB ODBC 설정 "myfaasdb"

 

1) ODBC와 MS Query를 통한 데이터베이스 연결

데이터 > 데이터 가져오기 > 기타 원본에서 > MS Query에서

"쿼리를 만들거나 편집할 때 퀴리 마법사 사용" 해제 > 데이터 원본(ODBC) 선택 "myfaasdb" >확인

MS Query 환경 실행 - 테이블 추가에서 닫기

SQL 버튼을 눌러 SQL 작성 창 띄우기

SQL 작성 - "select a.proj_id prj, b.name, left(a.date_start, 10), a.date_end, a.cost from participant a, account b where a.acc_id = b.id;" > 확인

이 상태에서 변수를 포함한 SQL 명령 입력이 불가능함으로 엑셀에 연결 후 변수 포함 SQL 명령어 입력 예정

결과 확인 > "데이터 반환" 버튼 선택

엑셀의 원하는 위치에 질의 결과 가져오기

결과 확인

변수를 이용한 검색 조건 입력하기 위하여 검색 조건을 넣을 셀 준비(G1 셀)

데이터 > 쿼리 및 연결 선택

"쿼리 및 연결" 창에서 "MyFaaSDB_Query" 선택 - 가져온 데이터 활성화

변수를 설정하기 위하여 마우스 오른쪽 키로 "MyFaaSDB_Query" 선택 후 "속성" 선택

연결 창에서 "정의" 탭 선택 > 명령 텍스트에 매개 변수(?) 추가 질의로 변경 후 확인

"select a.proj_id , b.name, left(a.date_start, 10), a.date_end, a.cost from participant a, account b where a.acc_id = b.id and a.proj_id = ?;"

매개 변수 1 입력 창에 변숫값 입력

이후 다시 "퀴리 및 연결"의 "속성" > "정의" 탭을 선택하면 아래쪽 "매개 변수" 버튼이 활성화되어 있음

 

 

"매개 변수" 버튼을 선택

매개 변수 창에서 "다음 셀에서 가져오기" 선택

검색 조건 값을 입력할 셀 지정 > 자동 새로고침 설정 > 확인

지정 셀(G1)에 검색 조건 값을 입력하면 데이터베이스에서 해당 질의 결과를 가져옴

엑셀 퀴리 및 연결에서 SQL 변경하기

사용 버전은 마이크로소프트 오피스 Professonal Plus 2019.

 

엑셀에서 이미 연결되어 있는 데이터베이스에서 자료를 가져오는 SQL SELECT 문장을 바꾸기 위해서는 "퀴리 및 연결" 기능을 사용한다.

 

이 글에서는 쿼리를 사용하며 연결을 사용하는 경우, 연결의 "속성" 메뉴를 사용한다. 연결 사용 예는 아래 글에 설명되어 있다.

엑셀에서 MySQL, MariaDB 데이터베이스 검색 조건 연동하기

데이터베이스의 내용을 ODBC를 통하여 엑셀에 연결할 경우 다양한 장점이 있음: 데이터베이스 응용 프...

blog.naver.com

우선 엑셀 "데이터">"쿼리 및 연결"을 선택하여 우측에 퀴리 연결 탭 화면을 활성화시킨다.

SQL SELECT 문을 변경하기 원하는 쿼리 (예에서 "퀴리 1")를 선택하면 Power Query 편집기 창이 뜬다.

 

고급 편집기를 선택한다.

고급 편집기에서 SQL SELECT 문장을 변경한다.

"완료"를 선택하여 저장한다.

Query 편집기에서 "닫기 및 로드"를 선택하면 새로운 Query를 이용하여 데이터베이스에서 자료를 다시 불러온다.

 

 

2021 - 2020 NDo

11/8/2021 처음 2020/1/1