엑셀 큐브 함수 OLAP 데이터 분석 CUBEMEMBER, CUBESET, CUBEMEMBERPROPERTY

엑셀은 단순한 로컬 스프레드시트 프로그램을 넘어, 대규모의 비즈니스 데이터를 분석하는 강력한 도구로 확장될 수 있습니다.

특히 기업의 데이터웨어하우스(Data Warehouse)나 비즈니스 인텔리전스(BI) 시스템에 저장된 OLAP(Online Analytical Processing) 큐브에 직접 연결하여 데이터를 추출하고 분석할 때, 엑셀의 큐브 함수는 그 진가를 발휘합니다.

이는 방대한 다차원 데이터를 엑셀에서 유연하게 탐색하고 보고서를 만들 수 있게 해줍니다.

큐브 함수의 기본 개념과 작동 방식을 소개하고, 큐브 내의 멤버(Member)와 세트(Set)를 조회하고 속성을 가져오는 기본적인 큐브 함수들을 집중적으로 다룰 예정입니다. 엑셀을 활용한 대규모 OLAP 데이터 분석의 첫걸음을 떼어봅시다!

왜 엑셀 큐브 함수를 알아야 할까요?

대기업이나 중견기업에서는 판매, 재무, 고객, 생산 등 다양한 비즈니스 데이터를 미리 정의된 '큐브' 형태로 관리하는 경우가 많습니다. 큐브는 데이터를 여러 차원(예: 시간, 제품, 지역, 고객)으로 구조화하여 빠른 분석을 가능하게 합니다. 이때 엑셀 큐브 함수를 사용하면:

다차원 데이터 탐색: 큐브 내의 방대한 데이터를 엑셀에서 원하는 차원과 멤버 기준으로 자유롭게 탐색할 수 있습니다.
유연한 보고서 생성: 피벗 테이블의 제약을 넘어, 큐브 함수를 사용하여 맞춤형 레이아웃의 보고서를 만들 수 있습니다.
실시간 데이터 연동: 큐브 데이터가 업데이트되면 엑셀 보고서도 자동으로 새로 고쳐집니다.
드릴다운/업 분석: 특정 멤버나 세트의 상세 내용을 탐색하거나 상위 레벨로 집계할 수 있습니다.

이 함수들은 엑셀을 단순한 데이터 시트가 아닌, 복잡한 OLAP 시스템과 연동되는 강력한 비즈니스 인텔리전스 프론트엔드 도구로 만들어줍니다.

큐브 함수의 핵심 개념: OLAP 큐브 연결 이해하기

큐브 함수를 사용하려면 먼저 엑셀이 분석할 OLAP 큐브에 연결되어 있어야 합니다.

데이터 원본 설정: 데이터 탭 → 데이터 가져오기 그룹 → 데이터베이스에서 또는 Analysis Services에서 등을 통해 OLAP 서버(예: SQL Server Analysis Services, Oracle OLAP)에 연결합니다.
연결 생성: 연결 마법사를 통해 서버 주소, 인증 정보 등을 입력하고, 분석할 데이터베이스와 큐브를 선택합니다.
피벗 테이블 생성 (선택 사항): 연결이 생성되면 일반적으로 빈 피벗 테이블이 생성됩니다. 이 피벗 테이블을 통해 큐브의 차원과 측정값을 탐색할 수 있으며, 큐브 함수의 인수로 사용될 '연결 이름'을 확인합니다.
- 연결 이름: 큐브 함수를 작성할 때 사용되는 연결의 이름입니다 (예: "연결1"). 이는 데이터 탭 → 쿼리 및 연결 창에서 확인할 수 있습니다.

주요 함수들

다음 큐브 함수들을 실무 예제와 함께 자세히 살펴보겠습니다.

CUBEMEMBER: 큐브 내의 멤버 또는 속성을 반환
CUBESET: 큐브 내의 세트(멤버 집합) 생성 또는 반환
CUBEMEMBERPROPERTY: 큐브 멤버의 속성 값 반환

실무 예제로 배우는 엑셀 큐브 함수 마스터하기

각 함수를 실제 OLAP 데이터 분석 시나리오에 적용하여 큐브 연결 및 멤버/세트 조회 방법을 보여드리겠습니다.

가정: 이 예제들에서는 'SalesAnalysis'라는 이름의 OLAP 데이터베이스에 'Sales'라는 큐브가 연결되어 있으며, 이 큐브에는 'Products' (제품), 'Geography' (지역), 'Time' (시간) 등의 차원과 'Sales Amount' (판매액), 'Quantity' (수량) 등의 측정값이 있다고 가정합니다. 연결 이름은 "큐브연결1"입니다.

1. CUBEMEMBER: 큐브 멤버 또는 속성 가져오기

CUBEMEMBER 함수는 큐브 내의 단일 멤버(예: 특정 제품, 특정 지역) 또는 멤버의 속성을 반환합니다.

이는 큐브 데이터 분석 보고서에서 특정 항목의 이름을 가져올 때 사용됩니다.

기본 형식: =CUBEMEMBER(연결, 멤버_식, [캡션])
- 연결: 큐브에 대한 연결 이름 (따옴표로 묶습니다). 예: "큐브연결1"
- 멤버_식: 큐브의 멤버를 고유하게 식별하는 문자열. 이는 MDX(Multidimensional Expressions) 형식의 경로를 따릅니다.
  - 예: "[Products].[Product Categories].[All Products].[Bikes]" (제품 차원의 Bikes 멤버)
  - 예: "[Time].[Calendar].[2025].[Q1].[January]" (2025년 1월 멤버)
- 캡션 (선택 사항): 셀에 표시될 텍스트. 생략 시 멤버의 기본 캡션이 표시됩니다.

실무 예제 1 (특정 멤버 가져오기): 'Sales' 큐브에서 'Products' 차원의 'Bikes' 카테고리 멤버를 가져와 엑셀 셀에 표시하세요.

해결 방법: A2 셀에 다음 수식을 입력합니다. =CUBEMEMBER("큐브연결1", "[Products].[Product Categories].[All Products].[Bikes]")

결과: Bikes

최상급 실무 활용: CUBEMEMBER 함수는 OLAP 기반 대시보드에서 특정 항목의 이름을 동적으로 표시하거나, 다른 큐브 함수의 인수로 사용될 멤버 식을 생성할 때 기본적으로 사용됩니다. 예를 들어, "[Time].[Calendar].["&A1&"]"처럼 A1 셀에 입력된 연도에 따라 동적으로 멤버를 참조할 수 있습니다.

실무 예제 2 (멤버 속성 가져오기): 'Sales' 큐브의 'Products' 차원에 'Color'라는 속성이 있다고 가정할 때, 'Bikes' 멤버의 'Color' 속성 값을 가져오세요.

해결 방법: A3 셀에 다음 수식을 입력합니다. =CUBEMEMBER("큐브연결1", "[Products].[Product Categories].[All Products].[Bikes]", "[Products].[Product Categories].[Color]")

세 번째 인수는 가져올 멤버의 속성 식입니다.

결과: (예시) Red (Bikes 제품의 색상이 Red라고 가정할 경우)

최상급 실무 활용: 멤버 속성을 가져오는 기능은 보고서에 상세 정보 추가, 특정 속성(예: SKU 번호, 제품 설명)을 기반으로 한 필터링 기준 생성, OLAP 데이터의 메타데이터 탐색 등에 유용합니다.

2. CUBESET: 큐브 내의 세트(멤버 집합) 생성 또는 반환

CUBESET 함수는 큐브 내에서 두 개 이상의 멤버로 구성된 세트(집합)를 정의하고 반환합니다.

이는 여러 멤버에 대한 집계 계산을 수행하거나, 피벗 테이블에서 사용자 정의 그룹을 만들 때 유용합니다.

기본 형식: =CUBESET(연결, 세트_식, [캡션], [정렬_순서], [세트_유형])
- 연결: 큐브에 대한 연결 이름.
- 세트_식: 세트를 정의하는 MDX 표현식. (쉼표로 구분된 멤버 목록, 또는 MDX의 Set 함수 사용)
  - 예: "{[Products].[Product Categories].[All Products].[Bikes], [Products].[Product Categories].[All Products].[Clothing]}" (Bikes와 Clothing 멤버로 구성된 세트)
  - 예: "{[Time].[Calendar].[2025].[Q1], [Time].[Calendar].[2025].[Q2]}" (1분기와 2분기 멤버로 구성된 세트)
- 캡션 (선택 사항): 셀에 표시될 텍스트 (세트의 이름).
- 정렬_순서 (선택 사항): 세트 멤버의 정렬 순서.
- 세트_유형 (선택 사항): 세트 유형.

실무 예제: 'Sales' 큐브에서 'Products' 차원의 'Bikes'와 'Clothing' 카테고리를 포함하는 '주요 제품군' 세트를 생성하세요.

해결 방법: A4 셀에 다음 수식을 입력합니다. =CUBESET("큐브연결1", "{[Products].[Product Categories].[All Products].[Bikes], [Products].[Product Categories].[All Products].[Clothing]}", "주요 제품군")

결과: 주요 제품군

최상급 실무 활용: CUBESET 함수는 OLAP 기반 보고서에서 사용자 정의 그룹이나 집계 기준을 만들 때 필수적입니다.

예를 들어, 'APAC 지역' (한국, 일본, 중국 등 여러 국가의 세트), '상위 5개 제품', '이번 분기 신제품 라인업' 등 미리 정의되지 않은 특정 멤버 그룹에 대한 분석을 수행할 때 유용합니다. 이 함수로 정의된 세트는 다른 큐브 함수의 인수로 사용될 수 있습니다.

3. CUBEMEMBERPROPERTY: 큐브 멤버의 특정 속성 값 가져오기

CUBEMEMBERPROPERTY 함수는 큐브 멤버의 특정 속성 값을 반환합니다.

CUBEMEMBER의 세 번째 인수(속성)와 유사하지만, 이 함수는 속성 자체를 명시적으로 가져올 때 사용됩니다.

기본 형식: =CUBEMEMBERPROPERTY(연결, 멤버_식, 속성_이름)
- 연결: 큐브에 대한 연결 이름.
- 멤버_식: 속성 값을 가져올 멤버의 MDX 식.
- 속성_이름: 가져올 속성의 이름(텍스트) 또는 속성 MDX 식.

실무 예제: 'Sales' 큐브의 'Products' 차원에 'SKU'(재고 관리 코드)라는 속성이 있다고 가정할 때, 'Bikes' 멤버의 'SKU' 속성 값을 가져오세요.

해결 방법: A5 셀에 다음 수식을 입력합니다. =CUBEMEMBERPROPERTY("큐브연결1", "[Products].[Product Categories].[All Products].[Bikes]", "SKU")

결과: (예시) BIKE001 (Bikes 제품의 SKU가 BIKE001이라고 가정할 경우)

최상급 실무 활용: CUBEMEMBERPROPERTY는 큐브 멤버의 상세 속성 정보를 보고서에 포함하거나, 특정 속성 값을 기반으로 데이터를 필터링하거나, 다른 시스템과 연동할 때 사용됩니다.

예를 들어, 제품의 설명, 출시일, 담당자 등 큐브에 저장된 다양한 메타데이터를 엑셀로 가져와 활용할 수 있습니다.

OLAP 큐브의 기본 개념과 엑셀 연결 방식을 이해하고, CUBEMEMBER로 멤버 및 속성을, CUBESET으로 멤버 집합을 생성하며, CUBEMEMBERPROPERTY로 특정 속성 값을 가져오는 방법을 살펴보았습니다.

이 함수들은 엑셀을 활용하여 대규모 OLAP 데이터의 구조를 탐색하고, 맞춤형 보고서를 구축하기 위한 필수적인 시작점입니다.

저작자표시 비영리 변경금지 (새창열림)

IT Office