2020. 11. 17. 21:54ㆍ카테고리 없음
책을 보면서 파워 BI(& 파워피벗)의 DAX를 공부하다 보니 여러 생각이 들더군요. 좀 개념을 정리 해볼 필요가 있다고 생각 되는 주제들이 떠올라서 남겨 보려 합니다.
먼저 오늘은 업무에 활용하기 위해 엑셀로 데이터 모델을 구성하는 방식에 대해서 적어 봅니다.
제목에는 '놓아주자' 는 과격한 표현을 썼지만 엑셀을 사용 하면서 VLOOKUP을 안 쓸수는 없습니다. 또한 VLOOKUP을 사용하는 것이 더 좋은 경우가 훨씬 많죠. 다만 과다 하게 사용되는 것이 문제 일 뿐입니다. 적정 수준에서는 여전히 엑셀 최강 함수라 생각합니다.
그럼 어떤 것이 과한 것일까요? 아래 내용은 판매실적과 각 마스터 데이터 간의 관계를 예시로 표현한 것 입니다.
일반적으로 우리가 사용하는 업무 시스템에 구성된 일별 판매실적 보고서를 예시로 만든건데요. (물론 코드 뿐만 아니라 각 코드의 명칭은 기본으로 들어가 있겠죠. ^^) 이 보고서를 개발할 당시에는 이 정도명 충분하다고 생각 했을 겁니다.
사실 원하는 형태의 데이터가 업무시스템(ERP 등)에서 멋지게 딱 하고 출력된다면 좋겠지만 현실적으로 모든 작업에 100% 만족할 수 있는 경우는 없기 때문에 (특히 분석, 예측 업무에는 더욱더.. ) 우리는 여러 데이터를 받아서 여기 저기에 붙이고 값을 가공하여 분석할 데이터 구조를 만들 수 밖에 없습니다.
보고서와 각 마스터 내역을 다운로드 받아 엑셀로 합치는 작업을 하죠. VLOOKUP을 사용해서요.
이런 작업은 간단합니다. 판매실적을 받은 시트에 고객코드, 제품코드, 매장코드를 참조하여 각 마스터를 받은 시트 또는 파일에 VLOOKUP을 걸어서 원하는 데이터를 붙이면 되죠~
그런데 이 방식은 간편한 이면에 몇가지 문제를 가지고 있습니다.
- 데이터를 매번 다운로드 받아 작업을 반복해야 한다.
- 참조하는 양이 많을 경우 엑셀이 현저히 느려진다. (보통 2~3만 줄 만 넘어도 무리가 되는 경우가 많다)
- 파일 용량이 커진다.
- 추가로 그룹을 구성해서 식을 추가할때마다 계산 속도가 느려진다.
* 어떻게 하면 이 짓을 그만할까?
이 소모적인 작업을 그만하려면 리포트를 다시 개발하면 됩니다. 현재 엑셀로 작업하는 구조대로 리포트를 수정 개발하면 되죠. 하지만 이것은 반복되는 수정 개발이 근본적인 해결 방법은 아니라고 생각합니다. 위의 예시 데이터는 업무의 요구 사항이 간단하니 문제되지 않을 수 있습니다만 대부분의 문제는 원본 데이터 구조에서 부터 발생하니까요.
우리는 현재를 기준으로 필요를 판단합니다. 그러나 내가 변하지 않으려 노력해도 환경이 변합니다. 팀이 변하고 회사가 변합니다. 고객도 공급사도 변합니다. 비지니스 환경은 가만히 있지 않습니다. 그러니 내가 하는 업무도 당연히 변합니다. 따라서 이런 작업이 완벽하게 없어지는 일은 없을 겁니다.
* 결국 불가능하다. 계속해야한다.
물론 상당히 예전부터 (적어도 내가 직장생활을 시작한 2000년대 부터는) 많은 업무에 OLAP 개념의 도구들이 많이 적용되고 있어서 약간의 노력으로 쉽게 원하는 결과에 근접할 수 있습니다. 이러한 도구, 시스템 들은 분석을 위한 Data Warehouse , Data mart 등의 구조화 된 환경 안에서 작동합니다. 그리고 이 환경을 구성하기 위해서 많은 시간? 공을 들여 설계 / 개발을 수행하죠. 하지만 이것도 시스템입니다. 이 구조를 사용자가 직접 수정 개발하면서 운영 할 수는 없죠.
시스템은 완벽하게 구성될 수 없으며 설령 완벽하다 하더라도 그것은 환경이 변하면 달라집니다.
* 하지만 편하게 할 수 있는 방법이 있다.
MS 파워 BI , 파워쿼리/파워피벗은 사용자가 가지고 있는 데이터, 즉 시스템에 구조화 되지 않은 엑셀 파일 등으로 구성된 데이터와 엮어서 사용하는 것이 수월합니다. 이것은 엄청난 강점입니다.
앞선 말이 길었네요.. 이제부터 VLOOKUP으로 데이터를 붙이지 않고, 시스템을 수정하지 않고 작업을 효율적으로 진행 할 수 있는 방법, 즉 엑셀에 데이터 모델을 구성하는 것을 소개합니다.
VLOOKUP으로 데이터를 이어 붙이는 방법과 비교를 통해 데이터 원본 구조와 자동화 이점에 대한 설명을 진행하겠습니다.
비교 1. 데이터 원본 구조
>> 피벗 테이블의 원본으로 한개 테이블만 참조할 수 있다.
>> 모든 정보를 피벗팅 할 테이블에 붙여야 한다.
분석이나 집계에 있어 엑셀 최고의 도구는 피벗테이블이죠. 보통 이번 예시처럼 데이터를 가공하는 이유도 다 피벗팅을 사용할 계획이 있기 때문입니다. 단순히 정형 보고서를 만들기 위해서 라면 (보다 효율적인) 여러 테이블을 만들고 SUMIFS 등을 사용해서 요약하는 방식으로 가능지만 정적인 형태의 보고서 만으로는 통찰을 발휘하기 어려운 한계가 있으니.. 피벗을 버릴 수 없죠.
데이터 양이 얼마 안될 경우에는 별문제가 되지 않습니다만 많은 행을 가진 데이터를 작업할 경우 문제가 됩니다. 만약 판매실적이 5만 행이라면 붙이는 컬럼만다 5만 행이 있어야 합니다. 이럴 경우 그냥 붙여서는 엑셀 파일이 돌아가지 않을 겁니다. 값복사 해야죠.
반면 파워피벗을 사용하면 이럴 필요가 없죠. 데이터 모델로 데이터를 넘겨 각 테이블간의 관계를 만들어 사용하면 됩니다.
필드를 추가 할 때도 파워피벗의 경우 각 마스터 데이터 테이블에 필드 하나만 추가 하면 되지만 Vlookup은 실적 데이터에 붙일 수 밖에 없습니다.
판매실적 데이터 테이블에 필요한 새로운 항목이 생길 때마다 계속 붙여 나가야 합니다. 분석을 위한 작업이라면 원하는 데이터의 모습을 갖출 때까지 여러 항목을 넣고 빼고 하는 일이 반복 됩니다. 이게 제가 손이 빠른 이유가 되었죠. 반복 작업을 빨리 해야 햤으니까요.
파워 피벗은 이미 관계로 데이터간에 연결이 되어있기 때문에 마스터데이터. 즉 제품의 속성을 추가하여 분석이 팔요하다면. 제품마스터만 수정하고 피벗테이블에서 항목만 추가 하면 됩니다.
파워 피벗이 훨씬 간편하고 효율적입니다.
비교 2. 자동화
1회 작업하는 속도는 VLOOKUP이 압도적으로 빠르죠. 하지만 매번 들어가는 노력과 시간을 고려한다면 달라집니다.
파워피벗이 자동화에 있어서는 압도적입니다. 내려받은 데이터만 변경하여 붙이고 새로고침 한번이면 끝나니까요. 만약DB에서 직접 내용을 쿼리 해온다면 파워피벗에서는 작업이라고 할 수 없죠. 파일 열고 '조회 월' 넣고 새로고침하면 끝이니 개발된 보고서와 다를 것이 없죠.
파워피벗이 아무리 좋아도 매번 해야 하는 작업량이 많다면 사용가치가 매우 떨어집니다.
음 일단 파일 크기 부터 차이가 나네요.
기능과 활용 팁 만 하다가 보다 기초적인 틀에 대한 이야기를 하니 재미있네요.
예시 파일을 2개 올립니다. 하나는 과거 제가 하던 작업방삭(VLOOKUP)과 파워피벗으로 데이터모델을 구성한 것 2개를 모두 올립니다.