사용자 정의 집계

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 함수를 사용하는 것은 지원되지 않습니다. 또는 USERELATIONSHIP 대신 TREATAS 함수를 사용할 수 있습니다. TREATAS를 사용하는 경우 테이블 간에 활성 관계가 없는지 확인합니다. 이 구성에서 TREATAS를 사용할 때 집계가 여전히 영향을 받을 수 있습니다.
• 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 테이블의 스토리지 모드를 가져오기 로 변경하면 관련 차원 테이블을 스토리지 모드 이중으로 설정할 수 있다는 대화 상자가 열립니다.

관련 차원 테이블을 이중으로 설정하면 하위 쿼리에 따라 Import 또는 DirectQuery 역할을 할 수 있습니다. 예제에서는 다음을 수행합니다.

• 가져오기 모드 Sales Agg 테이블에서 메트릭을 집계하고 관련 이중 테이블의 특성별로 그룹화한 쿼리는 메모리 내 캐시의 결과를 반환합니다.
• DirectQuery Sales 테이블에서 메트릭을 집계하고 관련 이중 테이블의 특성별로 그룹화한 쿼리는 DirectQuery 모드에서 결과를 반환합니다. GroupBy 작업을 포함한 쿼리 논리는 원본 데이터베이스에 전달됩니다.

이중 스토리지 모드에 대한 자세한 내용은 Power BI Desktop의 스토리지 모드 관리를 참조하세요.

일반 관계와 제한된 관계

관계를 기반으로 하는 집계 결과에는 일반적인 관계가 필요합니다.

일반 관계에는 두 테이블이 단일 원본에서 온 다음 스토리지 모드 조합이 포함됩니다.

원본 간 관계가 일반인 유일한 경우는 두 테이블이 모두 가져오기로 설정된 경우입니다. 다 대 다 관계는 항상 제한적입니다.

관계에 의존하지 않는 원본 간 집계 적중은 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~500만 개 이상의 고유 값이 쿼리 성능에 영향을 줄 수 있는 성능 임계값에 도달할 수 있습니다. 그러나 세부 정보 테이블에는 수십억 개의 행이 있지만 열에는 2~500만 개의 고유 값이 있는 시나리오에서 유용할 수 있습니다. 이 경우 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 가져오기 테이블은 그룹별 특성이 적고 카디널리티가 낮기 때문에 세분성이 높습니다. 행 수는 수천 개까지 낮을 수 있으므로 메모리 내 캐시에 쉽게 맞을 수 있습니다. 이러한 특성은 유명 경영진 대시보드에서 사용되므로 참조하는 쿼리는 가능한 한 빨리 수행되어야 합니다.

이 모델의 메모리 공간은 비교적 작지만 거대한 모델의 잠금을 해제합니다. 아키텍처는 아키텍처의 구성 요소에 쿼리 부하를 분산하여 강점에 따라 활용하기 때문에 균형 잡힌 아키텍처를 나타냅니다.

드라이버 작업 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 전문가 및 피어가 토론 그룹, 비디오, 블로그 등에서 전문 지식을 공유하는 커뮤니티가 활성화되어 있습니다. 집계에 대해 알아볼 때는 다음 리소스를 확인해야 합니다.

• Power BI 커뮤니티
• Bing에서 "Power BI 집계" 검색

관련 콘텐츠

• 자동 집계
• 복합 모델