큐브 함수의 기본 개념과 멤버/속성 조회, CUBEVALUE를 통한 측정값 추출과 CUBERANKEDMEMBER를 통한 순위화 및 드릴다운 분석을 통합하여 대화형 큐브 기반 보고서와 대시보드를 구축하는 고급 응용 방법을 알아볼 차례입니다.
왜 큐브 기반 보고서 및 대시보드 구축을 알아야 할까요?
OLAP 큐브는 방대한 다차원 데이터를 효율적으로 관리하지만, 그 자체로는 데이터가 단순히 저장되어 있을 뿐입니다.
이 데이터를 최종 사용자나 경영진이 쉽게 이해하고 탐색하며 의사결정에 활용할 수 있도록 시각화된 보고서나 대시보드 형태로 제공해야 합니다.
- 대화형 분석: 사용자가 직접 차원(지역, 시간, 제품 등)을 선택하고 필터링하여 원하는 관점에서 데이터를 탐색합니다.
- 실시간 업데이트: 큐브 데이터가 변경되면 보고서가 자동으로 새로 고쳐져 항상 최신 정보를 제공합니다.
- 유연한 레이아웃: 피벗 테이블의 고정된 틀을 넘어, 큐브 함수로 원하는 위치에 원하는 측정값과 멤버를 배치하여 맞춤형 대시보드를 만듭니다.
- 비즈니스 통찰력 시각화: 복잡한 OLAP 데이터를 직관적인 차트와 표로 시각화하여 핵심 성과 지표(KPI)와 트렌드를 한눈에 파악합니다.
이 고급 응용 기술들을 통해 여러분은 엑셀을 단순한 스프레드시트가 아닌, OLAP 시스템과 연동되는 강력한 비즈니스 인텔리전스 프론트엔드 도구로 활용할 수 있게 될 것입니다.
주요 응용 기술들
다음 핵심 응용 기술들을 실무 예제와 함께 자세히 살펴보겠습니다.
- 큐브 함수와 슬라이서 연동: 드롭다운 대신 슬라이서를 사용하여 차원 필터링
- 큐브 함수와 타임라인 연동: 날짜 차원을 이용한 동적 기간 필터링
- 큐브 함수를 이용한 KPI 대시보드 구축: 여러 KPI를 큐브에서 가져와 한 화면에 요약
- OLAP 피벗 테이블과 큐브 함수의 시너지: 피벗 테이블 기반의 큐브 함수 활용
실무 예제로 배우는 엑셀 큐브 함수 마스터하기
각 응용 기술을 실제 OLAP 데이터 분석 시나리오에 적용하여 대화형 큐브 기반 대시보드를 구축하는 방법을 보여드리겠습니다.
가정: 모든 예제는 'SalesAnalysis'라는 OLAP 데이터베이스의 'Sales' 큐브에 연결되어 있으며, 연결 이름은 "큐브연결1"입니다. 큐브에는 'Products' (제품), 'Geography' (지역), 'Time' (시간) 등의 차원과 'Sales Amount' (판매액), 'Quantity' (수량), 'Gross Profit' (총이익) 등의 측정값이 있다고 가정합니다.
1. 큐브 함수와 슬라이서 연동: 드롭다운 대신 슬라이서로 필터링
슬라이서(Slicer)는 피벗 테이블이나 테이블에 대한 직관적인 필터링 도구입니다.
큐브 함수 보고서에서는 슬라이서가 큐브 멤버를 선택하는 강력한 시각적 인터페이스 역할을 할 수 있습니다.
실무 예제:
'지역' 차원의 슬라이서를 사용하여, 사용자가 슬라이서에서 선택하는 지역에 따라 해당 지역의 총 판매액(Sales Amount)과 총이익(Gross Profit)을 큐브 함수로 동적으로 표시하세요.
해결 방법:
1. OLAP 피벗 테이블 생성 (슬라이서 연결용):
- 데이터 탭 -> 데이터 가져오기 그룹 -> Analysis Services에서 등 기존 큐브 연결 선택.
- 피벗 테이블 필드 창에서 지역 차원을 필터 영역에 놓습니다.
- 피벗 테이블 도구 분석 탭 -> 필터 그룹 -> 슬라이서 삽입 클릭 -> 지역 필드를 선택하고 확인.
- 슬라이서가 생성되면, 슬라이서에서 마우스 오른쪽 버튼 클릭 -> 보고서 연결 -> 해당 피벗 테이블 및 큐브 함수를 사용할 시트 (숨겨진 연결)를 모두 체크합니다. (여기서 큐브 함수도 슬라이서에 연결되도록 설정)
2. 큐브 함수 수식 작성:
- A2 셀에 선택 지역 총 판매액:
- B2 셀에 =CUBEVALUE("큐브연결1", CUBEMEMBER("큐브연결1", "[Geography].[City].CurrentMember"), "[Measures].[Sales Amount]")
- CUBEMEMBER("큐브연결1", "[Geography].[City].CurrentMember"): 가장 중요한 부분! 슬라이서가 연결된 차원(여기서는 [Geography].[City])의 현재 선택된 멤버를 동적으로 가져옵니다. - A3 셀에 선택 지역 총이익:
- B3 셀에 =CUBEVALUE("큐브연결1", CUBEMEMBER("큐브연결1", "[Geography].[City].CurrentMember"), "[Measures].[Gross Profit]")
결과: 사용자가 슬라이서에서 '서울'을 클릭하면 B2, B3 셀에 '서울' 지역의 총 판매액과 총이익이 자동으로 표시됩니다. '부산'을 클릭하면 '부산' 지역 데이터로 바뀝니다.
최상급 실무 활용:
대화형 대시보드 구축의 핵심입니다. 사용자가 드롭다운 메뉴 대신 슬라이서를 통해 원하는 차원(지역, 제품, 시간 등)의 멤버를 직관적으로 선택하며 데이터를 탐색할 수 있게 합니다.
경영 성과 모니터링, 캠페인 성과 분석, 예산 대 실적 비교 등 모든 동적인 OLAP 보고서에 필수적으로 활용됩니다.
2. 큐브 함수와 타임라인 연동: 날짜 차원 동적 필터링
타임라인(Timeline)은 피벗 테이블의 날짜 차원에 특화된 슬라이서입니다. 큐브 함수 보고서에서도 날짜 차원에 연결하여 동적인 기간 필터링을 구현할 수 있습니다.
실무 예제:
'시간' 차원의 타임라인을 사용하여, 사용자가 타임라인에서 선택하는 기간에 따라 해당 기간의 총 판매액(Sales Amount)을 큐브 함수로 동적으로 표시하세요.
해결 방법:
1. OLAP 피벗 테이블 생성 (타임라인 연결용):
- 데이터 탭 -> 데이터 가져오기 그룹 -> Analysis Services에서 등 기존 큐브 연결 선택.
- 피벗 테이블 필드 창에서 시간 차원의 Calendar 계층(또는 해당 큐브의 시간 계층)을 필터 영역에 놓습니다.
- 피벗 테이블 도구 분석 탭 -> 필터 그룹 -> 타임라인 삽입 클릭 -> Calendar 필드 선택하고 확인.
- 타임라인이 생성되면, 타임라인에서 마우스 오른쪽 버튼 클릭 -> 보고서 연결 -> 해당 피벗 테이블 및 큐브 함수를 사용할 시트 (숨겨진 연결)를 모두 체크합니다.
2. 큐브 함수 수식 작성:
- A5 셀에 선택 기간 총 판매액:
- B5 셀에 =CUBEVALUE("큐브연결1", CUBEMEMBER("큐브연결1", "[Time].[Calendar].CurrentMember"), "[Measures].[Sales Amount]")
- CUBEMEMBER("큐브연결1", "[Time].[Calendar].CurrentMember"): 타임라인이 연결된 날짜 차원([Time].[Calendar])의 현재 선택된 기간 멤버를 동적으로 가져옵니다.
결과: 사용자가 타임라인에서 2024년 1분기를 선택하면 B5 셀에 2024년 1분기의 총 판매액이, 2025년 2분기를 선택하면 해당 값으로 자동 업데이트됩니다.
최상급 실무 활용:
연간, 분기별, 월별, 일별 매출 추이 분석, 특정 기간 이벤트 효과 측정, 재무 보고서의 기간별 비교 등 날짜 기반의 동적인 분석이 필요한 모든 OLAP 보고서에 필수적입니다.
타임라인은 사용자가 기간을 직관적으로 선택하고 변경할 수 있게 하여 분석의 편의성을 극대화합니다.
3. 큐브 함수를 이용한 KPI 대시보드 구축
여러 개의 CUBEVALUE 함수를 조합하여 다양한 KPI를 한 화면에 표시하고, 슬라이서나 타임라인과 연동하여 동적으로 변화하는 KPI 대시보드를 구축합니다.
실무 예제:
사용자가 '지역' 슬라이서와 '시간' 타임라인을 조작할 때마다, 해당 조건에 맞는 총 판매액(Sales Amount), 총이익(Gross Profit), 판매 수량(Quantity)을 한 화면에서 실시간으로 보여주는 KPI 대시보드를 만들고 싶습니다.
해결 방법:
1. 슬라이서 및 타임라인 연결: 1번 및 2번 예시처럼 '지역' 슬라이서와 '시간' 타임라인을 생성하고 큐브 함수를 사용할 시트에 연결합니다.
2. KPI 수식 작성:
- A7 셀: 총 판매액:
- B7 셀: =CUBEVALUE("큐브연결1", CUBEMEMBER("큐브연결1", "[Geography].[City].CurrentMember"), CUBEMEMBER("큐브연결1", "[Time].[Calendar].CurrentMember"), "[Measures].[Sales Amount]")
- A8 셀: 총이익:
- B8 셀: =CUBEVALUE("큐브연결1", CUBEMEMBER("큐브연결1", "[Geography].[City].CurrentMember"), CUBEMEMBER("큐브연결1", "[Time].[Calendar].CurrentMember"), "[Measures].[Gross Profit]")
- A9 셀: 판매 수량:
- B9 셀: =CUBEVALUE("큐브연결1", CUBEMEMBER("큐베연결1", "[Geography].[City].CurrentMember"), CUBEMEMBER("큐브연결1", "[Time].[Calendar].CurrentMember"), "[Measures].[Quantity]")
결과: 슬라이서와 타임라인의 조합으로 선택된 지역 및 기간에 따라 모든 KPI 값이 실시간으로 업데이트됩니다. 이를 원형 차트, 막대 차트 등과 연동하여 시각적인 대시보드를 완성할 수 있습니다.
최상급 실무 활용:
경영진 보고용 대시보드, 부서별/개인별 성과 모니터링 대시보드, 실시간 재무 성과 지표 대시보드 등 비즈니스 인텔리전스의 핵심적인 결과물을 엑셀에서 구현할 수 있습니다.
큐브 함수를 활용하면 피벗 테이블의 한계를 넘어선 자유로운 레이아웃과 높은 수준의 자동화를 달성할 수 있습니다.
4. OLAP 피벗 테이블과 큐브 함수의 시너지
큐브 함수는 피벗 테이블에서 생성된 특정 셀을 참조하여 더욱 유연한 보고서를 만들 수 있습니다. 피벗 테이블은 데이터를 그룹화하고 요약하는 데 뛰어나고, 큐브 함수는 그 요약된 값이나 멤버를 독립적으로 가져와 다른 수식과 결합하는 데 뛰어납니다.
실무 예제:
피벗 테이블로 '지역'별 '판매액' 요약을 생성한 후, 큐브 함수를 사용하여 특정 지역(예: '서울')의 '판매액'을 피벗 테이블 외부에 가져오세요. 피벗 테이블의 필터가 변경되어도 큐브 함수는 정확한 값을 유지합니다.
해결 방법:
1. OLAP 피벗 테이블 생성: 삽입 탭 -> 피벗 테이블 -> 외부 데이터 원본 사용 -> 연결 선택. 지역을 행에, Sales Amount를 값에 놓습니다.
2. 큐브 함수로 피벗 테이블 값 참조:
- A11 셀에 서울 판매액 (큐브함수):
- B11 셀에 =CUBEVALUE("큐브연결1", "[Geography].[City].[Seoul]", "[Measures].[Sales Amount]")
결과: 피벗 테이블에 '서울' 지역의 판매액이 표시되고, B11 셀에도 동일한 '서울' 지역의 판매액이 큐브 함수로 추출됩니다.
만약 피벗 테이블의 지역 필터를 '부산'으로 변경하더라도, B11 셀의 값은 여전히 '서울'의 판매액을 정확하게 보여줍니다.
최상급 실무 활용:
CUBEVALUE 함수는 피벗 테이블의 필터링에 영향을 받지 않고 독립적으로 큐브의 값을 가져올 수 있다는 장점이 있습니다.
이는 피벗 테이블에서 특정 필터를 적용한 상태에서도 전체 데이터를 기준으로 한 비교 값(예: 전체 매출액 대비 특정 지역 매출액 비율)을 계산할 때 유용합니다. 또한, 피벗 테이블로 정의된 멤버(GETPIVOTDATA 대신 CUBEMEMBER 사용)를 기반으로 다른 계산을 수행하는 등 복합적인 OLAP 분석 시너지를 창출합니다.
큐브 함수와 엑셀의 슬라이서, 타임라인, 조건부 서식 등 다른 기능들을 결합하여 대화형 큐브 기반 보고서와 대시보드를 구축하는 고급 응용 방법들을 살펴보았습니다. 또한, OLAP 피벗 테이블과의 시너지를 통해 데이터 분석의 효율성을 극대화하는 전략도 알아보았습니다.