Power Pivot은 Excel에서 복잡한 데이터 모델과 테이블 간 관계를 구축할 수 있도록 지원하는 강력한 추가 기능입니다. 견고한 데이터 모델을 만들어 고급 데이터 계산을 수행할 수 있으며, 외부 소프트웨어 없이도 동적이고 대규모의 데이터 모델을 생성함으로써 Excel의 기능을 한층 확장해 줍니다. 이 글에서는 실전 예제를 통해 Power Pivot으로 복잡한 데이터 모델과 관계를 구축하는 방법을 단계별로 살펴봅니다.
Power Pivot이란?
Power Pivot은 Excel에서 다음과 같은 작업을 가능하게 해주는 강력한 추가 기능입니다.
- 여러 데이터 원본에서 대용량 데이터를 가져올 수 있습니다.
- 키(기본 키) 필드를 사용해 테이블 간 관계를 생성합니다.
- DAX(Data Analysis Expressions)로 고급 계산을 수행합니다.
- 효율적인 인터랙티브 대시보드와 피벗 테이블을 만들 수 있습니다.
Power Pivot 탭 활성화 방법:
- 파일 탭 → 옵션 선택 → Excel 옵션에서 추가 기능을 클릭합니다.
- 하단의 관리 드롭다운에서 COM 추가 기능을 선택한 뒤 이동을 클릭합니다.
- COM 추가 기능 대화 상자에서 Microsoft Power Pivot for Excel에 체크하고 확인을 누릅니다.
1. Power Pivot을 위한 데이터 준비하기
Power Pivot에서 복잡한 데이터 모델과 관계를 구축하기 전에, 데이터셋의 각 테이블이 고유 식별자 또는 기본 키를 가지고 있는지 반드시 확인해야 합니다.
예를 들어 판매 데이터셋이라면 다음과 같은 필드가 필요합니다.
- Sales(판매): SaleID — 각 판매 건의 고유 식별자
- Products(제품): ProductID — 각 제품의 고유 식별자
- Customers(고객): CustomerID — 각 고객의 고유 식별자
- Regions(지역): RegionID — 각 지역의 고유 식별자
- Dates(날짜): Date — 각 날짜의 고유 식별자
특히 ProductID, CustomerID, RegionID처럼 관계를 맺는 데 사용되는 필드는 테이블 전반에서 일관성을 유지해야 합니다.
2. Power Pivot에 데이터 불러오기
데이터 유형에 따라 다양한 방법으로 Power Pivot에 데이터를 가져올 수 있습니다.
외부 원본에서 데이터 가져오기:
- Power Pivot 탭 → 관리를 클릭해 Power Pivot 창을 엽니다.
- Power Pivot 창에서 외부 데이터 가져오기 → 기타 원본을 선택해 데이터를 불러옵니다.
기존 Excel 통합 문서에서 데이터 가져오기:
- 데이터 범위를 선택합니다.
- 삽입 탭 → 표를 선택합니다.
- 각 표에 Sales, Products, Customers, Regions, Dates처럼 이름을 지정합니다.
- Power Pivot 탭 → 데이터 모델에 추가를 선택하면 Power Pivot 편집기가 열립니다.
이 과정을 거치면 데이터가 성공적으로 모델에 추가됩니다.
3. 테이블 관계 만들기
데이터를 불러왔다면 이제 테이블 간 관계를 설정해야 합니다.
- Power Pivot 창에서 디자인 탭 → 관계 만들기를 선택합니다.
- 관계 만들기 대화 상자가 열리면 서로 연결할 테이블과 열을 선택합니다.
- 아래 매핑을 하나씩 정의하여 관계를 만듭니다.
- Sales[ProductID] → Products[ProductID]: 각 판매 건을 해당 제품과 연결
- Sales[CustomerID] → Customers[CustomerID]: 각 판매 건을 해당 고객과 연결
- Sales[Date] → Dates[Date]: 각 판매 건을 해당 날짜와 연결
- Customers[RegionID] → Regions[RegionID]: 각 고객을 해당 지역과 연결
생성된 관계:
대체 방법: 다이어그램 보기 활용
- Power Pivot에서 다이어그램 보기로 전환합니다.
- Sales 테이블의 ProductID를 Products 테이블로 끌어다 놓습니다.
- Sales 테이블의 CustomerID를 Customers 테이블로 드래그합니다.
- Sales 테이블의 Date를 Dates 테이블로 드래그합니다.
- Customers 테이블의 RegionID를 Regions 테이블로 드래그합니다.
4. 계산 열 및 측정값 만들기
관계를 설정했다면 이제 본격적인 계산과 분석을 시작할 수 있습니다. Power Pivot에서는 계산 열과 측정값을 만들어 더 깊이 있는 인사이트를 얻을 수 있습니다.
예제: 계산 열
Sales 테이블에 각 판매 건의 수익(Profit)을 계산하는 계산 열을 만들어 보겠습니다.
- Power Pivot 창에서 Sales 테이블을 선택합니다.
- 열 추가를 클릭하고 수익 계산을 위한 아래 수식을 입력합니다.
계산 열이 Sales 테이블에 수익 값과 함께 나타나며, 열 이름을 'Profit'으로 변경할 수 있습니다.
예제: 측정값 계산
측정값 1: 총 매출(Total Revenue)
전체 판매의 총 매출을 계산하려면 Power Pivot에서 측정값을 생성합니다.
- Sales 테이블의 계산 영역으로 이동합니다.
- 아래 DAX 수식을 입력해 Total Revenue 측정값을 만듭니다.
이 측정값은 데이터 모델에 적용된 필터나 슬라이서에 따라 동적으로 총 매출을 계산합니다.
측정값 2: 총 수익(Total Profit)
총 수익을 계산하려면 계산 영역에 아래 DAX 수식을 입력합니다.
측정값 3: 평균 고객 소득(Average Customer Income)
평균 고객 소득을 계산하려면 계산 영역에 다음 DAX 수식을 입력합니다.
= AVERAGE(Customers[Income])
실행 결과:
5. 고급 분석: 시간 인텔리전스(Time Intelligence)
Date 테이블이 있으면 시간 흐름에 따른 판매 추세 분석 같은 시간 기반 분석을 수행할 수 있습니다. Power Pivot은 TOTALYTD(연초 대비 누계), SAMEPERIODLASTYEAR(작년 동일 기간) 같은 시간 인텔리전스 함수를 지원하여 서로 다른 기간 간 성과 비교가 가능합니다.
연간 누적 매출(YTD Revenue)을 계산하려면 다음과 같은 측정값을 만듭니다.
=TOTALYTD(SUM(Sales[Revenue]),Dates[Date])
이 측정값은 연초부터 선택한 날짜까지의 누적 매출을 계산합니다.
전년 대비 매출 증가율(YoY)을 계산하려면 아래 DAX 수식을 입력합니다.
=DIVIDE( SUM(Sales[Revenue]) - CALCULATE(SUM(Sales[Revenue]), SAMEPERIODLASTYEAR(Dates[Date])), CALCULATE(SUM(Sales[Revenue]), SAMEPERIODLASTYEAR(Dates[Date])), 0)
이 수식은 작년 동일 기간과 비교한 백분율 증가율을 계산합니다.
6. 피벗 테이블로 데이터 분석하기
관계와 계산이 완료되면 피벗 테이블과 피벗 차트를 만들어 데이터를 분석할 수 있습니다.
- 삽입 탭 → 피벗 테이블을 선택합니다.
- 피벗 테이블 만들기 대화 상자에서 데이터 모델에서를 선택합니다.
- 피벗 테이블 필드 목록에 데이터 모델에 추가한 모든 테이블과 필드가 표시됩니다. 테이블의 필드를 행, 열, 값 영역으로 드래그하여 다양한 분석을 수행하세요.
데이터 모델에서 도출하는 고급 인사이트:
- 제품별 총 매출 분석:
- Products 테이블의 ProductName을 행 영역으로, Total Revenue 측정값을 값 영역으로 드래그합니다.
- 지역별 매출 분석:
- Regions 테이블의 RegionName을 행 영역으로, Total Revenue 측정값을 값 영역으로 드래그합니다.
슬라이서를 추가하면 상호작용성을 더욱 높일 수 있습니다. 예를 들어 월(Month) 슬라이서를 추가하면 월별로 데이터를 손쉽게 필터링할 수 있습니다.
마무리
실전 데이터셋을 활용해 Power Pivot에서 복잡한 데이터 모델과 테이블 관계를 구축하는 전 과정을 살펴봤습니다. 이를 통해 기존 Excel 함수만으로는 달성하기 어려운 정교한 분석이 가능해집니다. 관련 테이블을 연결하고 계산 열과 측정값을 활용하면 제품별·지역별·고객 특성별 판매 성과처럼 데이터를 더 깊이 있게 이해할 수 있습니다.