Power Pivot으로 복잡한 데이터 모델 및 테이블 관계 마스터하기

Power Pivot은 사용자가 복잡한 데이터 모델과 테이블 간의 관계를 구축할 수 있는 강력한 Excel 추가 기능입니다. 고급 데이터 계산을 수행하기 위해 강력한 데이터 모델을 생성합니다. 외부 소프트웨어 없이도 동적 대규모 데이터 모델을 생성할 수 있어 Excel의 기능이 확장됩니다. 이 문서에서는 실제 사례를 통해 Power Pivot을 사용하여 복잡한 데이터 모델 및 관계를 구축하는 방법을 보여줍니다.

파워 피벗이란 무엇인가요?

Power Pivot은 사용자가 다음을 수행할 수 있게 해주는 Excel의 강력한 추가 기능입니다.

• 여러 소스에서 대규모 데이터 세트를 가져옵니다.
• 키/기본 필드를 사용하여 테이블 간의 관계를 만듭니다.
• DAX(Data Analysis Expressions)를 사용하여 고급 계산을 수행합니다.
• 효율적인 대화형 대시보드와 피벗 테이블을 만듭니다.

파워 피벗 탭 활성화:

• 파일로 이동 탭>> 옵션 선택>> 추가 기능을 선택합니다. Excel 옵션에서 .
• 관리에서 상자>> COM 추가 기능을 선택합니다.>> 이동을 클릭하세요. .
• COM 추가 기능에서 대화 상자>> Microsoft Power Pivot for Excel을 선택합니다.>> 확인을 클릭하세요. .

1. Power Pivot을 위한 데이터 준비

Power Pivot에서 복잡한 데이터 모델과 관계를 구축하기 전에 데이터 세트의 각 테이블에 고유 식별자가 있는지 확인하세요. 또는 기본 키 각 엔터티에 대해.

필요한 판매 데이터 세트를 고려해 보겠습니다:

• 판매 :SaleID (각 판매에 대한 고유 식별자)
• 제품 :ProductID(각 제품의 고유 식별자)
• 고객 :CustomerID (각 고객의 고유 식별자)
• 지역 :RegionID (각 지역의 고유 식별자)
• 날짜 :날짜(매일의 고유 식별자)

각 필드, 특히 ProductID, CustomerID, RegionID 등 관계 생성에 사용되는 필드가 테이블 전체에서 일관성을 유지하는지 확인하세요.

2. Power Pivot에 데이터 로드

데이터를 Power Pivot으로 가져오려면 데이터 유형에 따라 다른 옵션을 사용할 수 있습니다.

다른 소스에서 데이터를 가져오려면 다음 단계를 따르세요.

• 파워 피벗으로 이동 탭>> 관리를 클릭하세요. Power Pivot 창을 엽니다.
• Power Pivot 창에서>> 외부 데이터 가져오기를 클릭합니다.>> 다른 소스에서를 선택하세요. 데이터를 가져오세요.

기존 Excel 통합 문서에서 데이터를 가져오려면:

• 데이터 범위를 선택하세요.
• 삽입으로 이동 탭>> 표 선택 .

• 각 테이블의 이름을 Sales와 같이 지정하세요. , 제품 , 고객 , 지역 및 날짜 .

• 파워 피벗으로 이동 탭>> 데이터 모델에 추가 를 선택합니다. 파워 피벗 편집기를 열려면 .

다음 단계에 따라 데이터를 가져올 수 있습니다.

3. 관계 형성

이제 데이터가 로드되었으므로 이러한 테이블 간의 관계를 설정해야 합니다.

• Power Pivot 창에서 디자인으로 이동합니다. 탭>> 관계 만들기를 선택합니다. .
• 관계 만들기 대화 상자가 나타납니다.
• “서로 관련된 테이블과 열을 선택하세요.”
• 다음 매핑을 하나씩 사용하여 관계를 정의합니다.
  • 판매액[제품 ID] -> 제품[제품ID] :각 판매를 해당 제품에 연결합니다.
  • 판매[고객 ID] -> 고객[CustomerID] :각 판매를 해당 고객에게 연결합니다.
  • 판매[날짜] -> 날짜[날짜] :각 판매를 해당 날짜에 연결합니다.
  • 고객[지역 ID] -> 지역[지역ID] :각 고객을 해당 지역에 연결합니다.

관계:

대안:다이어그램 보기에서

• 다이어그램 보기로 전환 파워 피벗에서.
• 제품 ID 드래그 판매에서 제품 표 테이블.
• 고객 ID 를 드래그하세요. 판매에서 고객 에 대한 표 테이블.
• 날짜를 드래그하세요. 판매에서 표를 날짜 로 테이블.
• 지역 ID 드래그 고객 으로부터 테이블을 지역으로 테이블.

4. 계산된 열 및 측정값 생성

관계를 구축하면서 계산 및 분석 수행을 시작할 수 있습니다. Power Pivot을 사용하면 더 깊은 통찰력을 얻기 위해 계산된 열과 측정값을 만들 수 있습니다.

예:계산된 열

판매 섹션에 계산된 열을 만들어 보겠습니다. 이익을 계산하는 표 각 판매에서:

• 파워 피벗 창>> 판매를 선택합니다. 테이블.
• 열 추가를 클릭하세요. 수익을 계산하려면 다음 공식을 입력하세요.

이제 이 계산된 열이 판매 탭에 표시됩니다. 이익 값이 포함된 테이블입니다. 열 이름을 이익으로 바꿀 수 있습니다.

예:측정값 계산

측정 1:총 수익

모든 판매에 대한 총 수익을 계산하려면 Power Pivot에서 측정값을 만들 수 있습니다.

• 판매에서 표에서 계산 영역으로 이동하세요. .
• 다음 DAX 수식을 입력하여 총 수익을 만듭니다. 측정:

이 측정값은 데이터 모델에 적용된 필터 또는 슬라이서를 기반으로 조정하여 총 수익을 동적으로 계산합니다.

측정 2:총 이익

총 이익을 계산하려면 계산 영역에 다음 DAX 수식을 삽입하세요.

측정 3:평균 고객 소득

평균 고객 소득을 계산하려면 계산 영역에 다음 DAX 수식을 삽입하세요.

= AVERAGE(Customers[Income])

출력:

5. 고급 분석:시간 인텔리전스

날짜 포함 테이블에서는 시간에 따른 판매 추세 계산과 같은 시간 기반 분석을 수행할 수 있습니다. Power Pivot은 시간 인텔리전스를 지원합니다. TOTALYTD(연간 누계) 및 SAMEPERIODLASTYEAR와 같은 함수를 사용하면 여러 기간에 걸쳐 실적을 비교할 수 있습니다.

연간 누계 수익을 계산하려면 , 다음과 같은 측정값을 생성할 수 있습니다:

=TOTALYTD(SUM(Sales[Revenue]),Dates[Date])

이 측정값은 연초부터 선택한 날짜까지의 누적 수익을 계산합니다.

연도별 수익을 계산하려면 성장하려면 다음 DAX 수식을 삽입하세요.

=DIVIDE(
SUM(Sales[Revenue]) -
CALCULATE(SUM(Sales[Revenue]), SAMEPERIODLASTYEAR(Dates[Date])),
CALCULATE(SUM(Sales[Revenue]), SAMEPERIODLASTYEAR(Dates[Date])),
0)

이는 작년 같은 기간과 비교한 성장률을 계산합니다.

6. 데이터 분석을 위한 피벗 테이블 만들기

관계와 계산이 준비되면 피벗 테이블과 피벗 차트를 만들어 데이터를 분석할 수 있습니다.

• 삽입으로 이동 탭>> 피벗 테이블 선택 .
• 피벗 테이블 만들기에서 대화 상자>> 데이터 모델에서를 선택합니다. .
• 피벗 테이블 필드 목록에는 데이터 모델에 추가한 모든 테이블과 필드가 표시됩니다. 테이블의 필드를 행으로 드래그하세요. , 열 및 값 다양한 분석을 수행할 수 있는 영역입니다.

모델의 고급 통찰력:

• 제품별 총 수익 분석;
  • 제품 이름 드래그 제품에서 표를 행으로 지역 및 총 수익 가치에 맞춰 측정 지역.
• 지역별 수익 분석;
  • 지역 이름 드래그 지역에서 표를 행으로 지역 및 총 수익 가치에 맞춰 측정 지역.

더 나은 상호 작용을 위해 슬라이서를 추가할 수도 있습니다. 예를 들어 월에 대한 슬라이서를 추가할 수 있습니다. 월별로 데이터를 필터링합니다.

결론

실용적인 데이터 세트를 통해 Power Pivot에서 복잡한 데이터 모델과 관계를 구축하는 과정을 보여주었습니다. 이를 통해 기존 Excel 기능만으로는 달성하기 어려운 정교한 분석을 수행할 수 있습니다. 관련 테이블을 연결하고 계산된 열과 측정값을 사용하면 제품, 지역 또는 고객 인구통계별 판매 성과를 이해하는 등 데이터에 대한 더 깊은 통찰력을 얻을 수 있습니다.