엑셀에서 OLAP 큐브에 저장된 실제 측정값(Metrics)을 추출하고, 특정 기준에 따라 멤버를 순위화하여 데이터를 드릴다운(Drill-down) 분석하는 방법을 알아볼 차례입니다.
CUBEVALUE 함수를 중심으로 큐브 데이터의 핵심을 추출하고, CUBERANKEDMEMBER 함수를 통해 특정 세트 내에서 순위가 지정된 멤버를 가져와 상세 분석을 수행하는 방법을 집중적으로 다룰 예정입니다. 엑셀을 활용한 OLAP 데이터 분석 능력을 한 단계 더 끌어올려 봅시다!
왜 큐브 값 추출 및 드릴스루 함수를 알아야 할까요?
OLAP 큐브의 궁극적인 목적은 저장된 다차원 데이터를 기반으로 의미 있는 측정값을 빠르게 제공하는 것입니다.
"작년 3분기 서울 지역의 노트북 판매액은 얼마인가?", "가장 매출액이 높은 상위 5개 제품은 무엇인가?", "특정 지역의 특정 제품군에 대한 평균 이익률은?"과 같은 질문에 답하려면 큐브에 직접 쿼리하여 값을 가져와야 합니다.
- 정확한 측정값 추출: 여러 차원과 멤버의 조합에 해당하는 단일 측정값을 가져옵니다.
- 유연한 보고서 레이아웃: 피벗 테이블의 고정된 레이아웃에서 벗어나, 원하는 위치에 원하는 측정값을 배치하여 맞춤형 보고서를 만듭니다.
- 성과 모니터링: 특정 KPI(핵심 성과 지표)를 큐브에서 직접 가져와 대시보드를 구축합니다.
- 드릴다운 분석: 상위 레벨의 데이터(예: 총 매출액)에서 하위 레벨(예: 월별, 제품별 매출액)로 파고들어 상세 원인을 파악합니다.
CUBEVALUE와 CUBERANKEDMEMBER는 이러한 OLAP 데이터의 핵심을 엑셀로 가져와 분석하는 데 필수적인 도구입니다.
주요 함수들
다음 큐브 함수들을 실무 예제와 함께 자세히 살펴보겠습니다.
- CUBEVALUE: 큐브 내의 값을 추출
- CUBERANKEDMEMBER: 세트 내에서 순위가 지정된 멤버 반환 (드릴다운 응용)
- (응용) 데이터 탐색/드릴다운: 큐브 함수와 피벗 테이블의 연동
실무 예제로 배우는 엑셀 큐브 함수 마스터하기
각 함수를 실제 OLAP 데이터 분석 시나리오에 적용하여 큐브 데이터의 핵심을 추출하고 드릴다운 분석을 수행하는 방법을 보여드리겠습니다.
가정: 이 예제들에서는 'SalesAnalysis'라는 이름의 OLAP 데이터베이스에 'Sales'라는 큐브가 연결되어 있으며, 이 큐브에는 'Products' (제품), 'Geography' (지역), 'Time' (시간) 등의 차원과 'Sales Amount' (판매액), 'Quantity' (수량) 등의 측정값이 있다고 가정합니다. 연결 이름은 "큐브연결1"입니다.
1. CUBEVALUE: 큐브에서 핵심 측정값 추출
CUBEVALUE 함수는 큐브에 연결된 데이터에서 단일 집계된 값(측정값)을 가져옵니다.
이 함수는 여러 차원 및 멤버의 조합에 해당하는 특정 측정값을 조회할 때 사용됩니다.
- 기본 형식: =CUBEVALUE(연결, [멤버_식1], [멤버_식2], ...)
- 연결: 큐브에 대한 연결 이름 (따옴표로 묶습니다). 예: "큐브연결1"
- 멤버_식1, 멤버_식2, ...: 값을 가져올 차원 멤버 또는 측정값을 지정하는 문자열 (MDX 형식). 순서는 중요하지 않습니다. 측정값은 필수적으로 포함되어야 합니다.
실무 예제 1 (기본 측정값 추출):
'Sales' 큐브에서 전체 기간, 전체 지역에 대한 총 판매액(Sales Amount)을 가져오세요.
해결 방법:
A2 셀에 다음 수식을 입력합니다.
=CUBEVALUE("큐브연결1", "[Measures].[Sales Amount]")
- [Measures].[Sales Amount]: 'Sales Amount' 측정값을 지정하는 MDX 식.
결과: (예시) 12,345,678 (총 판매액)
최상급 실무 활용:
CUBEVALUE 함수는 OLAP 기반 보고서에서 주요 KPI를 한눈에 볼 수 있는 대시보드를 만들 때 필수적입니다. 이 함수는 특정 차원의 모든 멤버를 요약한 값을 가져오는 데 사용됩니다.
실무 예제 2 (여러 차원 및 멤버로 필터링하여 측정값 추출):
'Sales' 큐브에서 2024년 3분기(Q3)에 '서울' 지역에서 발생한 '노트북' 제품의 판매 수량(Quantity)을 가져오세요.
해결 방법:
A3 셀에 다음 수식을 입력합니다.
=CUBEVALUE("큐브연결1", "[Time].[Calendar].[2024].[Q3]", "[Geography].[City].[Seoul]", "[Products].[Product Name].[Laptop]", "[Measures].[Quantity]")
- 각 인수는 차원 멤버와 측정값을 나타내는 MDX 식입니다. 순서는 상관없습니다.
결과: (예시) 500 (2024년 3분기 서울 지역 노트북 판매 수량)
최상급 실무 활용:
이처럼 CUBEVALUE는 여러 차원과 멤버의 조합을 통해 특정 조건에 맞는 상세 측정값을 추출할 때 매우 강력합니다.
각 차원 멤버를 다른 셀에 입력하고 수식에서 해당 셀을 참조하면 사용자 입력에 따라 동적으로 값이 변화하는 대화형 보고서를 만들 수 있습니다.
예를 들어, "[Geography].[City].["&B1&"]"처럼 B1 셀에 '서울'을 입력하여 동적으로 지역을 변경할 수 있습니다.
2. CUBERANKEDMEMBER: 세트 내에서 순위가 지정된 멤버 가져오기 (드릴다운 응용)
CUBERANKEDMEMBER 함수는 CUBESET으로 정의된 세트(멤버 집합) 내에서 특정 순위(예: 가장 높은 값, 두 번째로 높은 값)에 해당하는 멤버를 반환합니다. 이는 상위 N개(Top N) 분석이나, 특정 순위의 상세 데이터를 드릴다운하여 탐색할 때 유용합니다.
- 기본 형식: =CUBERANKEDMEMBER(연결, 세트_식, 순위, [캡션])
- 연결: 큐브에 대한 연결 이름.
- 세트_식: 멤버 집합을 정의하는 MDX 세트 식 또는 CUBESET 함수의 결과.
- 순위: 세트 내에서 가져올 멤버의 순위 (1은 1위, 2는 2위 등).
- 캡션 (선택 사항): 셀에 표시될 텍스트.
실무 예제:
'Sales' 큐브에서 'Sales Amount'(판매액) 기준 상위 3개 제품의 이름을 가져와 순위표를 만들고 싶습니다.
해결 방법:
- 세트 정의: B2 셀에 '모든 제품' 세트를 정의합니다. =CUBESET("큐브연결1", "[Products].[Product Name].Members", "모든 제품")
- [Products].[Product Name].Members: 'Products' 차원의 'Product Name' 계층에 있는 모든 멤버를 의미합니다. - 상위 1위 제품 이름: C2 셀에 다음 수식을 입력합니다. =CUBERANKEDMEMBER("큐브연결1", B2, 1, "상위 1위 제품")
- B2: 위에서 정의한 '모든 제품' 세트.
- 1: 1위 멤버를 가져옵니다. - 상위 2위 제품 이름: C3 셀에 다음 수식을 입력합니다. =CUBERANKEDMEMBER("큐연결1", B2, 2, "상위 2위 제품")
- 상위 3위 제품 이름: C4 셀에 다음 수식을 입력합니다. =CUBERANKEDMEMBER("큐브연결1", B2, 3, "상위 3위 제품")
결과:
| 세트 | 멤버 |
| 모든 제품 (CUBESET 결과) |
상위 1위 제품 (노트북)
|
| (숨김, CUBESET 결과는 B2에만 있음) |
상위 2위 제품 (스마트폰)
|
| (숨김) |
상위 3위 제품 (태블릿)
|
최상급 실무 활용:
CUBERANKEDMEMBER는 '상위 N개 고객/제품/지역', '하위 N개 비효율 부서', '특정 기간의 최고/최저 실적 기록원' 등 랭킹 기반의 분석 보고서에 매우 유용합니다.
이 함수로 가져온 멤버 이름은 다시 CUBEVALUE의 인수로 사용하여 해당 멤버의 상세 측정값(예: 상위 1위 제품의 실제 판매액)을 추출하여 자동화된 순위 대시보드를 구축할 수 있습니다.
3. (응용) 큐브 함수와 피벗 테이블의 연동: 데이터 탐색/드릴다운
큐브 함수는 독립적인 보고서를 만들 때도 유용하지만, OLAP 피벗 테이블과 함께 사용하면 강력한 시너지를 발휘합니다. 피벗 테이블은 큐브에 대한 가장 기본적인 탐색 도구이며, 큐브 함수로 생성된 멤버나 세트를 피벗 테이블 필드에 활용할 수도 있습니다.
실무 예제:
피벗 테이블을 사용하여 '지역' 차원을 필터링하고 '제품 카테고리'를 행에 배치하여 '판매액'을 요약한 후, '서울' 지역 데이터에 대해 '드릴다운'하여 상세 주문 데이터를 확인하고 싶습니다.
해결 방법:
- 피벗 테이블 생성: OLAP 연결이 되어 있는 상태에서 삽입 탭 -> 피벗 테이블 -> 외부 데이터 원본 사용 -> 연결 선택 -> 확인.
- 필드 배치:
- 지역 필드를 필터 영역으로 드래그.
- 제품 카테고리 필드를 행 영역으로 드래그.
- Sales Amount 측정값을 값 영역으로 드래그. - 필터 적용: 피벗 테이블의 '지역' 필터에서 '서울'을 선택.
- 드릴다운: 피벗 테이블에서 '서울' 지역 '전자제품'에 해당하는 'Sales Amount' 값(셀)을 더블 클릭합니다.
결과: '서울' 지역 '전자제품'에 해당하는 원본 상세 데이터가 새로운 시트로 자동으로 생성됩니다.
최상급 실무 활용:
피벗 테이블의 드릴다운 기능은 큐브 함수로 생성된 요약 보고서에서 '숫자의 원천'을 탐색할 때 매우 유용합니다.
CUBEVALUE로 총 매출액을 확인한 후, 피벗 테이블로 전환하여 특정 지역 또는 제품의 상세 판매 내역을 더블 클릭하는 방식으로 종합적인 OLAP 분석 워크플로우를 구축할 수 있습니다.
큐브 함수로 원하는 멤버를 추출한 후, 이를 피벗 테이블 필터에 적용하는 것도 가능합니다.
CUBEVALUE 함수를 사용하여 큐브에서 실제 측정값(예: 판매액, 수량)을 추출하고, CUBERANKEDMEMBER 함수를 통해 특정 세트 내에서 순위가 지정된 멤버를 가져와 상위 N개 분석 및 드릴다운을 수행하는 방법을 살펴보았습니다.
또한, OLAP 피벗 테이블과의 연동을 통해 데이터 탐색 및 드릴다운을 수행하는 실무적인 접근 방식도 알아보았습니다.