Power BI의 집계는 대규모 DirectQuery 의미 체계 모델에 비해 쿼리 성능을 향상시킬 수 있습니다. 집계를 사용하여 집계된 메모리 내 수준에서 데이터를 캐시합니다. 이 문서에 설명된 대로 Power BI의 집계는 데이터 모델에서 수동으로 구성할 수 있습니다. 프리미엄 구독의 경우 모델 설정에서 자동 집계 기능을 사용하도록 설정하여 자동으로 설정합니다.
집계 테이블 만들기
데이터 원본 형식에 따라 데이터 원본에서 집계 테이블을 테이블 또는 뷰, 네이티브 쿼리로 만들 수 있습니다. 성능 향상을 위해 파워 쿼리에서 만든 가져오기 테이블로 집계 테이블을 만듭니다. 그런 다음, Power BI Desktop의 집계 관리 대화 상자를 사용하여 요약, 세부 정보 테이블 및 세부 열 속성을 사용하여 집계 열에 대한 집계를 정의합니다.
데이터 웨어하우스 및 데이터 마트와 같은 차원 데이터 원본은 관계 기반 집계를 사용할 수 있습니다. Hadoop 기반 대규모 데이터 원본은 GroupBy 열에 대한 집계를 기반으로 하는 경우가 많습니다. 이 문서에서는 각 데이터 원본 유형에 대한 일반적인 Power BI 데이터 모델링 차이점을 설명합니다.
집계 관리
Power BI Desktop 보기의 데이터 창에서 집계 테이블을 마우스 오른쪽 단추로 클릭한 다음 집계 관리를 선택합니다.
집계 관리 대화 상자에는 집계 동작을 지정할 수 있는 테이블의 각 열에 대한 행이 표시됩니다. 다음 예제에서는 Sales 세부 정보 테이블에 대한 쿼리가 내부적으로 Sales Agg 집계 테이블로 리디렉션됩니다.
이 관계 기반 집계 예제에서 GroupBy 항목은 선택 사항입니다. DISTINCTCOUNT를 제외하고 집계 동작에는 영향을 주지 않으며 주로 가독성을 위한 것입니다. GroupBy 항목이 없으면 관계에 따라 집계가 여전히 영향을 받습니다. 이는 GroupBy 항목이 필요한 이 문서의 뒷부분에 있는 빅 데이터 예제 와 다릅니다.
유효성 검사
집계 관리 대화 상자는 유효성 검사를 적용합니다.
- Count 및 Count 테이블 행 요약 함수를 제외하고 세부 정보 열에는 집계 열과 동일한 데이터 형식이 있어야 합니다. 개수 및 개수 테이블 행은 정수 집계 열에만 사용할 수 있으며 일치하는 데이터 형식이 필요하지 않습니다.
- 세 개 이상의 테이블을 포함하는 연결된 집계는 허용되지 않습니다. 예를 들어 테이블 A의 집계는 테이블 C를 참조하는 집계가 있는 테이블 B를 참조할 수 없습니다.
- 두 항목이 동일한 요약 함수를 사용하고 동일한 세부 정보 테이블 및 세부 정보열을 참조하는 중복 집계는 허용되지 않습니다.
- 세부 정보 테이블은 가져오기가 아닌 DirectQuery 스토리지 모드를 사용해야 합니다.
- 비활성 관계에서 사용되는 외래 키 열을 기준으로 그룹화하고 집계 적중에 USERELATIONSHIP 함수를 사용하는 것은 지원되지 않습니다.
- GroupBy 열을 기반으로 하는 집계는 집계 테이블 간의 관계를 사용할 수 있지만 집계 테이블 간의 작성 관계는 Power BI Desktop에서 지원되지 않습니다. 필요한 경우 XMLA(XML for Analysis) 엔드포인트를 통해 타사 도구 또는 스크립팅 솔루션을 사용하여 집계 테이블 간의 관계를 만들 수 있습니다.
대부분의 유효성 검사는 드롭다운 값을 비활성화하고 도구 설명에 설명 텍스트를 표시하여 적용됩니다.
집계 테이블이 숨겨져 있습니다.
모델에 대한 읽기 전용 액세스 권한이 있는 사용자는 집계 테이블을 쿼리할 수 없습니다. 읽기 전용 액세스는 RLS(행 수준 보안)와 함께 사용할 때 보안 문제를 방지합니다. 소비자와 쿼리는 집계 테이블이 아닌 세부 정보 테이블을 참조하며 집계 테이블에 대해 알 필요가 없습니다.
따라서 집계 테이블은 보고서 보기에서 숨겨집니다. 테이블이 아직 숨겨지지 않은 경우 집계 관리 대화 상자는 모두 적용을 선택하면 숨김으로 설정됩니다.
스토리지 모드
집계 기능은 테이블 수준 스토리지 모드와 상호 작용합니다. Power BI 테이블 은 DirectQuery, 가져오기 또는 이중 스토리지 모드를 사용할 수 있습니다. DirectQuery는 백 엔드를 직접 쿼리하고 가져오기는 메모리에 데이터를 캐시하고 캐시된 데이터에 쿼리를 보냅니다. 모든 Power BI 가져오기 및 비다차원 DirectQuery 데이터 원본은 집계와 함께 사용할 수 있습니다.
쿼리 속도를 높이기 위해 집계 테이블의 스토리지 모드를 가져오기로 설정하려면 Power BI Desktop 모델 보기에서 집계된 테이블을 선택합니다. 속성 창에서 고급을 확장하고 스토리지 모드에서 선택 항목을 드롭다운한 다음 가져오기를 선택합니다. 가져오기 변경은 되돌릴 수 없습니다.
테이블 스토리지 모드에 대한 자세한 내용은 Power BI Desktop의 스토리지 모드 관리를 참조하세요.
집계에 대한 RLS
집계에 대해 올바르게 작동하려면 RLS 식이 집계 테이블과 세부 정보 테이블을 필터링해야 합니다.
다음 예제에서는 Geography 테이블의 RLS 식이 집계에 대해 작동합니다. Geography는 Sales 테이블 및 Sales Agg 테이블에 대한 관계의 필터링 쪽에 있기 때문입니다. 집계 테이블에 도달한 쿼리 및 RLS가 적용되지 않은 쿼리.
Product 테이블의 RLS 식은 집계된 Sales Agg 테이블이 아닌 세부 Sales 테이블만 필터링합니다. 집계 테이블은 세부 정보 테이블의 데이터의 또 다른 표현이므로 RLS 필터를 적용할 수 없는 경우 집계 테이블의 쿼리에 응답하는 것은 안전하지 않습니다. 이 역할의 사용자 쿼리는 집계 적중을 활용하지 않으므로 세부 정보 테이블만 필터링하는 것은 권장되지 않습니다.
Sales Agg 집계 테이블만 필터링하고 Sales 세부 정보 테이블은 필터링하지 않는 RLS 식은 허용되지 않습니다.
GroupBy 열을 기반으로 하는 집계의 경우 집계 테이블의 모든 GroupBy 열이 세부 정보 테이블에 포함되므로 세부 정보 테이블에 적용된 RLS 식을 사용하여 집계 테이블을 필터링할 수 있습니다. 반면에 집계 테이블의 RLS 필터는 세부 테이블에 적용할 수 없으므로 허용되지 않습니다.
관계 기반 집계
차원 모델은 일반적으로 관계에 따라 집계를 사용합니다. 데이터 웨어하우스 및 데이터 마트의 Power BI 모델은 차원 테이블과 팩트 테이블 간의 관계가 있는 별/눈송이 스키마와 유사합니다.
다음 예제에서 모델은 단일 데이터 원본에서 데이터를 가져옵니다. 테이블은 DirectQuery 스토리지 모드를 사용합니다. Sales 팩트 테이블에는 수십억 개의 행이 포함되어 있습니다. 캐싱을 위해 Sales 의 스토리지 모드를 가져오기로 설정하면 상당한 메모리 및 리소스 오버헤드가 사용됩니다.
대신 Sales Agg 집계 테이블을 만듭니다. Sales Agg 테이블에서 행 수는 CustomerKey, DateKey 및 ProductSubcategoryKey별로 그룹화된 SalesAmount의 합계와 같습니다. Sales Agg 테이블은 Sales보다 세분성이 높으므로 수십억 개 대신 관리하기 쉬운 수백만 개의 행이 포함될 수 있습니다.
다음 차원 테이블이 비즈니스 가치가 높은 쿼리에 가장 일반적으로 사용되는 경우, 일대다 또는 다대일 관계를 사용하여 Sales Agg를 필터링할 수 있습니다.
- 지리학
- 고객
- 날짜
- 제품 하위 범주
- 제품 범주
다음 이미지는 이 모델을 보여줍니다.
다음 표에서는 Sales Agg 테이블에 대한 집계를 보여 줍니다.
비고
Sales Agg 테이블은 다른 테이블과 마찬가지로 다양한 방법으로 로드될 수 있습니다. ETL/ELT 프로세스를 사용하거나 테이블에 대한 M 식을 사용하여 원본 데이터베이스에서 집계를 수행할 수 있습니다. 집계된 테이블은 의미 체계 모델에 대해 증분 새로 고침을 포함하거나 사용하지 않고 스토리지 가져오기 모드를 사용하거나 DirectQuery를 사용하고 columnstore 인덱스를 사용하여 빠른 쿼리에 최적화할 수 있습니다. 이러한 유연성을 통해 병목 현상을 방지하기 위해 쿼리 부하를 분산할 수 있는 균형 잡힌 아키텍처를 사용할 수 있습니다.
집계된 Sales Agg 테이블의 스토리지 모드를 가져오기 로 변경하면 관련 차원 테이블을 스토리지 모드 이중으로 설정할 수 있다는 대화 상자가 열립니다.
관련 차원 테이블을 이중으로 설정하면 하위 쿼리에 따라 Import 또는 DirectQuery 역할을 할 수 있습니다. 예제에서는 다음을 수행합니다.
- 가져오기 모드 Sales Agg 테이블에서 메트릭을 집계하고 관련 이중 테이블의 특성별로 그룹화한 쿼리는 메모리 내 캐시에서 반환될 수 있습니다.
- DirectQuery Sales 테이블에서 메트릭을 집계하고 관련 이중 테이블의 특성별로 그룹화한 쿼리는 DirectQuery 모드로 반환될 수 있습니다. GroupBy 작업을 포함한 쿼리 논리는 원본 데이터베이스에 전달됩니다.
이중 스토리지 모드에 대한 자세한 내용은 Power BI Desktop의 스토리지 모드 관리를 참조하세요.
일반 관계와 제한된 관계
관계를 기반으로 하는 집계 결과에는 일반적인 관계가 필요합니다.
일반 관계에는 두 테이블이 단일 원본에서 온 다음 스토리지 모드 조합이 포함됩니다.
| 여러 면의 테이블 | 1측의 표 |
|---|---|
| 이중의 | 이중의 |
| 수입 | 가져오기 또는 이중 |
| 다이렉트쿼리 | DirectQuery 또는 이중 |
원본 간 관계가 일반으로 간주되는 유일한 경우는 두 테이블이 모두 가져오기로 설정된 경우입니다. 다 대 다 관계는 항상 제한된 것으로 간주됩니다.
관계에 의존하지 않는 원본 간 집계 적중은 GroupBy 열을 기반으로 하는 집계를 참조하세요.
관계 기반 집계 쿼리 예제
Date 테이블의 열이 집계에 도달할 수 있는 세분성에 있기 때문에 다음 쿼리는 집계에 도달합니다. SalesAmount 열은 Sum 집계를 사용합니다.
다음 쿼리는 집계에 적중하지 않습니다. SalesAmount의 합계를 요청했음에도 불구하고 쿼리는 Product 테이블의 열에 대해 GroupBy 작업을 수행하며 집계에 도달할 수 있는 세분성이 아닙니다. 모델에서 관계를 관찰하는 경우 제품 하위 범주에는 여러 Product 행이 있을 수 있습니다. 쿼리에서 집계할 제품을 확인할 수 없습니다. 이 경우 쿼리는 DirectQuery로 되돌리고 SQL 쿼리를 데이터 원본에 제출합니다.
집계는 간단한 합계를 수행하는 간단한 계산을 위한 것이 아닙니다. 복잡한 계산도 도움이 될 수 있습니다. 개념적으로 복잡한 계산은 각 SUM, MIN, MAX 및 COUNT에 대한 하위 쿼리로 세분화됩니다. 각 하위 쿼리는 집계에 도달할 수 있는지 확인하기 위해 평가됩니다. 이 논리는 쿼리 계획 최적화로 인해 모든 경우에 성립하지 않지만, 일반적으로 적용됩니다. 다음 예제에서는 집계를 수행합니다.
COUNTROWS 함수는 집계를 활용할 수 있습니다. Sales 테이블에 대해 정의된 Count 테이블 행 집계가 있으므로 다음 쿼리는 집계에 도달합니다.
AVERAGE 함수는 집계를 활용할 수 있습니다. AVERAGE가 내부적으로 COUNT로 나눈 SUM으로 접히므로 다음 쿼리는 집계에 도달합니다. UnitPrice 열에 SUM 및 COUNT 모두에 대해 정의된 집계가 있으므로 집계가 적중됩니다.
경우에 따라 DISTINCTCOUNT 함수는 집계를 활용할 수 있습니다. 다음 쿼리는 집계 테이블에 CustomerKey의 고유성을 유지하는 CustomerKey에 대한 GroupBy 항목이 있기 때문에 집계에 도달합니다. 이 기술은 여전히 2~5백만 개 이상의 고유 값이 쿼리 성능에 영향을 줄 수 있는 성능 임계값에 도달할 수 있습니다. 그러나 세부 정보 테이블에는 수십억 개의 행이 있지만 열에는 2~5백만 개의 고유 값이 있는 시나리오에서 유용할 수 있습니다. 이 경우 DISTINCTCOUNT는 메모리에 캐시된 경우에도 수십억 개의 행이 있는 테이블을 검색하는 것보다 더 빠르게 수행할 수 있습니다.
DAX(데이터 분석 식) 시간 인텔리전스 함수는 집계를 인식합니다. DATESYTD 함수가 CalendarDay 값 테이블을 생성하고 집계 테이블이 Date 테이블의 그룹별 열에 대해 다루는 세분성에 있으므로 다음 쿼리는 집계에 도달합니다. 집계와 함께 사용할 수 있는 CALCULATE 함수의 테이블 값 필터 예시입니다.
GroupBy 열을 기준으로 한 집계
Hadoop 기반 빅 데이터 모델은 차원 모델과 다른 특성을 갖습니다. 큰 테이블 간의 조인을 방지하기 위해 빅 데이터 모델은 종종 관계를 사용하지 않고 차원 특성을 팩트 테이블에 비정규화합니다. GroupBy 열을 기반으로 하는 집계를 사용하여 대화형 분석을 위해 이러한 빅 데이터 모델의 잠금을 해제할 수 있습니다.
다음 표에는 집계할 이동 숫자 열이 포함되어 있습니다. 다른 모든 열은 그룹화할 속성입니다. 테이블에는 IoT 데이터와 많은 수의 행이 포함되어 있습니다. 스토리지 모드는 DirectQuery입니다. 전체 모델에 걸친 대규모 집계로 인해 데이터 소스에 대한 쿼리가 느립니다.
이 모델에서 대화형 분석을 사용하도록 설정하려면 대부분의 특성별로 그룹화하지만 경도 및 위도와 같은 카디널리티가 높은 특성을 제외하는 집계 테이블을 추가할 수 있습니다. 이렇게 하면 행 수가 크게 줄어들고 메모리 내 캐시에 편안하게 맞을 수 있을 만큼 작습니다.
집계 관리 대화 상자에서 드라이버 작업 Agg 테이블에 대한 집계 매핑을 정의합니다 .
GroupBy 열을 기반으로 하는 집계에서 GroupBy 항목은 선택 사항이 아닙니다. 그들이 없으면 집계가 제대로 작동하지 않습니다. 이는 GroupBy 항목이 선택 사항인 관계에 따라 집계를 사용하는 경우와 다릅니다.
다음 표에서는 드라이버 작업 Agg 테이블에 대한 집계를 보여 줍니다.
집계된 드라이버 활동 Agg 테이블의 스토리지 모드를 가져오기로 설정할 수 있습니다.
GroupBy 집계 쿼리 예제
다음 쿼리는 집계 테이블에 의해 작업 날짜 열이 포함되므로 집계에 도달합니다. COUNTROWS 함수는 Counted 테이블 행 집계를 사용합니다.
특히 팩트 테이블에 필터 특성이 포함된 모델의 경우 Count 테이블 행 집계를 사용하는 것이 좋습니다. Power BI는 사용자가 명시적으로 요청하지 않은 경우 COUNTROWS를 사용하여 모델에 쿼리를 제출할 수 있습니다. 예를 들어 필터 대화 상자에는 각 값에 대한 행 수가 표시됩니다.
결합된 집계 기술
집계를 위해 관계 및 GroupBy 열 기술을 결합할 수 있습니다. 관계를 기반으로 하는 집계에서는 비정규화된 차원 테이블을 여러 테이블로 분할해야 할 수 있습니다. 특정 차원 테이블에 비용이 많이 들거나 비실용적인 경우 해당 차원의 집계 테이블에서 필요한 특성을 복제하고 다른 차원에 대한 관계를 사용할 수 있습니다.
예를 들어 다음 모델은 Sales Agg 테이블에서 Month, Quarter, Semester 및 Year를 복제합니다. Sales Agg와 Date 테이블 사이에는 관계가 없지만 Customer 및 Product Subcategory와 관계가 있습니다. Sales Agg의 스토리지 모드는 Import입니다.
다음 표에서는 Sales Agg 테이블에 대한 집계 관리 대화 상자에 설정된 항목을 보여 줍니다. Date가 세부 정보 테이블인 GroupBy 항목은 Date 특성별로 그룹화되는 쿼리에 대한 집계를 적중하기 위해 필수입니다. 이전 예제와 같이 CustomerKey 및 ProductSubcategoryKey에 대한 GroupBy 항목은 관계가 있기 때문에 DISTINCTCOUNT를 제외하고 집계 적중에 영향을 주지 않습니다.
결합된 집계 쿼리 예제
집계 테이블은 CalendarMonth를 포함하고 CategoryName 은 일대다 관계를 통해 액세스할 수 있으므로 다음 쿼리는 집계에 도달합니다. SalesAmount 는 SUM 집계를 사용합니다.
집계 테이블이 CalendarDay를 다루지 않으므로 다음 쿼리는 집계에 적중하지 않습니다.
DATESYTD 함수는 CalendarDay 값 테이블을 생성하고 집계 테이블은 CalendarDay를 다루지 않으므로 다음 시간 인텔리전스 쿼리는 집계에 적중되지 않습니다.
집계 우선 순위
집계 우선 순위를 사용하면 단일 하위 쿼리에서 여러 집계 테이블을 고려할 수 있습니다.
다음 예제는 여러 원본을 포함하는 복합 모델 입니다.
- 드라이버 작업 DirectQuery 테이블에는 대용량 데이터 시스템에서 원본으로 사용하는 IoT 데이터 행이 1조 행 이상 포함되어 있습니다. 제어된 필터 컨텍스트에서 개별 IoT 판독값을 보기 위한 드릴스루 쿼리를 제공합니다.
- 드라이버 작업 Agg 테이블은 DirectQuery 모드의 중간 집계 테이블입니다. Azure Synapse Analytics(이전의 SQL Data Warehouse)에 10억 개 이상의 행이 포함되어 있으며 columnstore 인덱스를 사용하여 원본에서 최적화됩니다.
- 드라이버 작업 Agg2 가져오기 테이블은 그룹별 특성이 적고 카디널리티가 낮기 때문에 세분성이 높습니다. 행 수는 수천 개까지 낮을 수 있으므로 메모리 내 캐시에 쉽게 맞을 수 있습니다. 이러한 특성은 유명 경영진 대시보드에서 사용되므로 참조하는 쿼리는 가능한 한 빨리 수행되어야 합니다.
비고
세부 정보 테이블과 다른 데이터 원본을 사용하는 DirectQuery 집계 테이블은 집계 테이블이 SQL Server, Azure SQL 또는 Azure Synapse Analytics(이전의 SQL Data Warehouse) 원본에 있는 경우에만 지원됩니다.
이 모델의 메모리 공간은 비교적 작지만 거대한 모델의 잠금을 해제합니다. 아키텍처는 아키텍처의 구성 요소에 쿼리 부하를 분산하여 강점에 따라 활용하기 때문에 균형 잡힌 아키텍처를 나타냅니다.
드라이버 작업 Agg2에 대한 관리되는 집계 대화 상자는 우선 순위 필드를 10으로 설정하며 이는 드라이버 작업 Agg보다 높습니다. 우선 순위 설정이 높을수록 집계를 사용하는 쿼리에서 드라이버 작업 Agg2 를 먼저 고려합니다. 드라이버 작업 Agg2에서 응답할 수 있는 세분성이 아닌 하위 쿼리는 드라이버 작업 Agg를 대신 고려할 수 있습니다. 집계 테이블에서 답변할 수 없는 세부 쿼리는 드라이버 작업으로 직접 연결될 수 있습니다.
세부 정보 테이블 열에 지정된 테이블은 연결된 집계가 허용되지 않으므로 드라이버 작업 Agg가 아닌 드라이버 작업입니다.
다음 표에서는 드라이버 작업 Agg2 테이블에 대한 집계를 보여 줍니다.
쿼리가 집계에 도달했는지 또는 실패했는지 감지
SQL Profiler는 쿼리가 메모리 내 캐시 스토리지 엔진에서 반환되는지 또는 DirectQuery에 의해 데이터 원본으로 푸시되는지 여부를 검색할 수 있습니다. 동일한 프로세스를 사용하여 집계가 적중되는지 여부를 감지할 수 있습니다. 자세한 내용은 캐시를 적중하거나 누락한 쿼리를 참조하세요.
SQL Profiler는 확장 이벤트도 제공합니다 Query Processing\Aggregate Table Rewrite Query .
다음 JSON 코드 조각은 집계를 사용할 때 이벤트의 출력 예제를 보여 있습니다.
- matchingResult 는 하위 쿼리가 집계를 사용했음을 보여줍니다.
- dataRequest 는 사용된 하위 쿼리의 GroupBy 열과 집계된 열을 보여 줍니다.
- 매핑 은 매핑된 집계 테이블의 열을 보여 줍니다.
캐시를 동기화 상태로 유지
메모리 내 캐시가 원본 데이터와 동기화된 상태로 유지되지 않는 한 DirectQuery, 가져오기 및/또는 이중 스토리지 모드를 결합한 집계는 다른 데이터를 반환할 수 있습니다. 예를 들어 쿼리 실행은 캐시된 값과 일치하도록 DirectQuery 결과를 필터링하여 데이터 문제를 마스킹하지 않습니다. 필요한 경우 원본에서 이러한 문제를 처리하는 확립된 기술이 있습니다. 성능 최적화는 비즈니스 요구 사항을 충족하는 기능을 손상시키지 않는 방식으로만 사용해야 합니다. 데이터 흐름을 알고 그에 따라 디자인하는 것은 사용자의 책임입니다.
고려 사항 및 제한 사항
집계는 동적 M 쿼리 매개 변수를 지원하지 않습니다.
기능 변경으로 인해 2022년 8월부터 Power BI는 잠재적인 보안 위험으로 인해 SSO(Single Sign-On)가 설정된 데이터 원본으로 가져오기 모드 집계 테이블을 무시합니다. 집계를 사용하여 최적의 쿼리 성능을 보장하려면 이러한 데이터 원본에 대해 SSO를 사용하지 않도록 설정하는 것이 좋습니다.
커뮤니티
Power BI는 MVP, BI 전문가 및 피어가 토론 그룹, 비디오, 블로그 등에서 전문 지식을 공유하는 커뮤니티가 활성화되어 있습니다. 집계에 대해 알아볼 때는 다음과 같은 추가 리소스를 확인해야 합니다.