Excel에서 수백만 개의 행을 포함하는 데이터 모델을 만든 다음 이러한 모델에 대해 강력한 데이터 분석을 수행할 수 있습니다. 파워 피벗 추가 기능을 사용하거나 사용하지 않고 데이터 모델을 만들어 동일한 통합 문서에서 원하는 수의 피벗 테이블, 차트, 파워 뷰 시각화를 지원할 수 있습니다.
Excel에서 거대한 데이터 모델을 쉽게 빌드할 수 있지만 그렇게 하지 않는 몇 가지 이유가 있습니다. 첫째, 수많은 테이블과 열을 포함하는 대규모 모델은 대부분의 분석에서 과잉이며 필드 목록이 번거로워집니다. 둘째, 대형 모델은 귀중한 메모리를 사용하여 동일한 시스템 리소스를 공유하는 다른 응용 프로그램 및 보고서에 부정적인 영향을 미칩니다. 마지막으로 Microsoft 365에서는 SharePoint Online과 Excel Web App 모두 Excel 파일의 크기를 10MB로 제한합니다. 수백만 개의 행을 포함하는 통합 문서 데이터 모델의 경우 10MB 제한에 빠르게 도달하게 됩니다. 데이터 모델 사양 및 제한을 참조하세요.
이 문서에서는 작업하기 쉽고 메모리를 적게 사용하는 긴밀하게 구성된 모델을 빌드하는 방법을 알아봅니다. 시간을 들여 효율적인 모델 설계의 모범 사례를 배우면 Excel, Microsoft 365 SharePoint Online, Office Web Apps Server 또는 SharePoint에서 보고 있든 관계없이 만들고 사용하는 모든 모델에 대한 성과를 거둘 수 있습니다.
통합 문서 크기 최적화 프로그램을 실행하는 것도 좋은 방법입니다. 이 프로그램은 Excel 통합 문서를 분석하여 가능한 경우 추가로 압축합니다. 통합 문서 크기 최적화 프로그램을 다운로드합니다.
이 문서의 내용
압축률 및 메모리 내 분석 엔진
Excel의 데이터 모델은 메모리 내 분석 엔진을 사용하여 데이터를 메모리에 저장합니다. 엔진은 강력한 압축 기술을 구현하여 저장소 요구 사항을 줄이고 원래 크기보다 훨씬 작을 때까지 결과 집합을 축소합니다.
평균적으로 데이터 모델은 원본 시점의 동일한 데이터보다 7-10배 더 작을 것으로 예상할 수 있습니다. 예를 들어 SQL Server 데이터베이스에서 7MB의 데이터를 가져오는 경우 Excel의 데이터 모델은 쉽게 1MB 이하가 될 수 있습니다. 실제로 달성되는 압축 정도는 주로 각 열의 고유 값 수에 따라 달라집니다. 고유한 값이 많을수록 이를 저장하는 데 더 많은 메모리가 필요합니다.
압축과 고유 값에 대해 이야기하는 이유는 무엇인가요? 메모리 사용량을 최소화하는 효율적인 모델을 구축하는 것은 압축 최대화에 관한 것이기 때문이며, 가장 쉬운 방법은 특히 해당 열에 고유 값이 많이 포함된 경우 실제로 필요하지 않은 열을 제거하는 것입니다.
참고
개별 열에 대한 저장소 요구 사항의 차이가 클 수 있습니다. 경우에 따라 고유 값이 많은 열 하나보다 고유 값 수가 적은 열을 여러 개 사용하는 것이 좋습니다. 날짜/시간 최적화 섹션에서는 이 기술에 대해 자세히 설명합니다.
낮은 메모리 사용량에 대해 존재하지 않는 열을 이길 수 있는 것은 없습니다.
메모리 효율이 가장 높은 열은 처음부터 가져오지 않은 열입니다. 효율적인 모델을 작성하려면 각 열을 살펴보고 수행하려는 분석에 기여하는지 자문해보세요. 그렇지 않거나 확실하지 않은 경우 생략합니다. 필요한 경우 나중에 언제든지 새 열을 추가할 수 있습니다.
항상 제외해야 하는 열의 두 가지 예
첫 번째 예는 데이터 웨어하우스에서 생성된 데이터와 관련이 있습니다. 데이터 웨어하우스에서는 웨어하우스에서 데이터를 로드하고 새로 고치는 ETL 프로세스의 아티팩트를 찾는 것이 일반적입니다. 데이터가 로드될 때 "create date", "update date" 및 "ETL run"과 같은 열이 만들어집니다. 이러한 열은 모델에 필요하지 않으며 데이터를 가져올 때 선택을 취소해야 합니다.
두 번째 예는 팩트 테이블을 가져올 때 기본 키 열을 생략하는 것입니다.
팩트 테이블을 포함한 많은 테이블에는 기본 키가 있습니다. 고객, 직원 또는 판매 데이터가 포함된 테이블과 같은 대부분의 테이블에서는 모델에서 관계를 만드는 데 사용할 수 있도록 테이블의 기본 키가 필요합니다.
팩트 테이블은 다릅니다. 팩트 테이블에서 기본 키는 각 행을 고유하게 식별하는 데 사용됩니다. 정규화 목적에는 필요하지만 분석 또는 테이블 관계를 설정하는 데 사용되는 열만 원하는 데이터 모델에서는 유용하지 않습니다. 이러한 이유로 팩트 테이블에서 가져올 때는 기본 키를 포함하지 마세요. 팩트 테이블의 기본 키는 모델에서 엄청난 양의 공간을 사용하지만, 관계를 만드는 데 사용할 수 없기 때문에 아무런 이점도 제공하지 않습니다.
참고
데이터 웨어하우스와 다차원 데이터베이스에서 주로 숫자 데이터로 구성된 큰 테이블을 "팩트 테이블"이라고 부릅니다. 팩트 테이블에는 일반적으로 조직 구성 단위, 제품, 시장 부문, 지리적 지역 등에 맞게 집계되고 정렬된 판매 및 비용 데이터 요소와 같은 비즈니스 성과 또는 거래 데이터가 포함됩니다. 비즈니스 데이터를 포함하거나 다른 테이블에 저장된 데이터를 상호 참조하는 데 사용할 수 있는 팩트 테이블의 모든 열은 데이터 분석을 지원하기 위해 모델에 포함되어야 합니다. 제외하려는 열은 팩트 테이블에만 존재하고 다른 곳에는 존재하지 않는 고유한 값으로 구성된 팩트 테이블의 기본 키 열입니다. 팩트 테이블은 매우 방대하기 때문에 팩트 테이블에서 행 또는 열을 제외함으로써 모델 효율성의 가장 큰 이점 중 일부를 얻을 수 있습니다.
불필요한 열을 제외하는 방법
효율적인 모델에는 통합 문서에 실제로 필요한 열만 포함됩니다. 모델에 포함되는 열을 제어하려면 Excel의 "데이터 가져오기" 대화 상자가 아니라 파워 피벗 추가 기능의 테이블 가져오기 마법사를 사용하여 데이터를 가져와 야 합니다.
테이블 가져오기 마법사를 시작할 때 가져올 테이블을 선택합니다.
각 테이블에 대해 미리 보기 & 필터 단추를 클릭하고 테이블에서 실제로 필요한 부분을 선택할 수 있습니다. 먼저 모든 열의 선택을 취소한 다음, 분석에 필요한지 여부를 고려한 후 원하는 열을 계속 검사하는 것이 좋습니다.
필요한 행만 필터링하는 것은 어떻습니까?
기업 데이터베이스와 데이터 웨어하우스의 많은 테이블에는 오랜 기간 동안 누적된 기록 데이터가 포함되어 있습니다. 또한 관심 있는 테이블에 특정 분석에 필요하지 않은 비즈니스 영역에 대한 정보가 포함되어 있을 수 있습니다.
테이블 가져오기 마법사를 사용하면 기록 데이터 또는 관련 없는 데이터를 필터링하여 모델에서 많은 공간을 절약할 수 있습니다. 다음 이미지에서 날짜 필터는 필요하지 않은 기록 데이터를 제외하고 현재 연도의 데이터가 포함된 행만 검색하는 데 사용됩니다.
열이 필요하면 어떻게 해야 할까요? 여전히 공간 비용을 줄일 수 있습니까?
열을 압축에 더 적합한 후보로 만들기 위해 적용할 수 있는 몇 가지 추가 기술이 있습니다. 압축에 영향을 주는 열의 유일한 특성은 고유 값의 수입니다. 이 섹션에서는 일부 열을 수정하여 고유 값의 수를 줄이는 방법을 알아봅니다.
날짜/시간 열 수정
대부분의 경우 날짜/시간 열은 많은 공간을 차지합니다. 다행히 이 데이터 형식에 대한 저장소 요구 사항을 줄일 수 있는 여러 가지 방법이 있습니다. 기술은 열 사용 방법과 SQL 쿼리 작성에 대한 익숙한 수준에 따라 달라집니다.
날짜/시간 열에는 날짜 부분과 시간이 포함됩니다. 열이 필요한지 스스로에게 물어볼 때 날짜/시간 열에 대해 동일한 질문을 여러 번 합니다.
- 시간 부분이 필요한가요?
- 시간 수준의 시간 부분이 필요한가요? , 분? , 초? 밀리초인가요?
- 날짜/시간 열의 차이를 계산하거나 연도, 월, 분기 등을 기준으로 데이터를 집계하려고 하기 때문에 여러 날짜/시간 열이 있습니까?
이러한 각 질문에 어떻게 대답하느냐에 따라 날짜/시간 열을 다루는 옵션이 결정됩니다.
이러한 모든 솔루션에는 SQL 쿼리를 수정해야 합니다. 쿼리를 더 쉽게 수정하려면 모든 테이블에서 하나 이상의 열을 필터링해야 합니다. 열을 필터링하면 쿼리 구성을 축약 형식(SELECT *)에서 수정하기 훨씬 쉬운 정규화된 열 이름을 포함하는 SELECT 문으로 변경할 수 있습니다.
생성된 쿼리를 살펴보겠습니다. 테이블 속성 대화 상자에서 쿼리 편집기로 전환하고 각 테이블에 대한 현재 SQL 쿼리를 볼 수 있습니다.
테이블 속성에서 쿼리 편집기를 선택합니다.
쿼리 편집기에 테이블을 채우는 데 사용되는 SQL 쿼리가 표시됩니다. 가져오는 동안 열을 필터링한 경우 쿼리에 정규화된 열 이름이 포함됩니다.
반대로 열을 선택 취소하거나 필터를 적용하지 않고 테이블 전체를 가져온 경우 쿼리가 "선택 * 다음"으로 표시되어 수정하기가 더 어려워집니다.
|
|---|
SQL 쿼리 수정
이제 쿼리를 찾는 방법을 알았으므로 쿼리를 수정하여 모델 크기를 더 줄일 수 있습니다.
- 통화 또는 10진수 데이터를 포함하는 열에서 소수점이 필요하지 않은 경우 다음 구문을 사용하여 소수점을 제거합니다.
"SELECT ROUND([Decimal_column_name],0)... .”
센트가 필요하지만 센트의 분수가 필요한 경우 0을 2로 바꿉니다. 음수를 사용하는 경우 단위, 10, 100 등으로 반올림할 수 있습니다. - dbo라는 Datetime 열이 있는 경우 빅테이블. [Date Time] 시간 부분이 필요하지 않은 경우 구문을 사용하여 시간을 제거하세요.
"SELECT CAST (dbo. 빅테이블. [Date time] as date) AS [Date time]) " - dbo라는 Datetime 열이 있는 경우 빅테이블. [Date Time] 날짜와 시간 부분이 모두 필요한 경우 단일 날짜/시간 열 대신 SQL 쿼리에서 여러 열을 사용합니다.
"SELECT CAST (dbo. 빅테이블. [Date Time] as date ) AS [Date Time],
DatePart(hh, dbo. 빅테이블. [날짜 시간]) as [Date Time Hours],
DatePart(mi, dbo. 빅테이블. [날짜 시간]) as [Date Time Minutes],
DatePart(ss, dbo. 빅테이블. [날짜 시간]) as [Date Time Seconds],
DatePart(ms, dbo. 빅테이블. [날짜 시간]) as [Date Time milliseconds]"
각 부분을 별도의 열에 저장하는 데 필요한 만큼 열을 사용합니다. - 시간과 분이 필요하고 하나의 시간 열로 함께 사용하려는 경우 다음 구문을 사용할 수 있습니다.
Timefromparts(datepart(hh, dbo. 빅테이블. [Date Time]), datepart(mm, dbo. 빅테이블. [Date Time])) as [Date Time HourMinute] - [시작 시간] 및 [종료 시간]과 같은 두 개의 날짜/시간 열이 있고 실제로 필요한 것은 [기간]이라는 열로 두 열 사이의 시간차(초)인 경우 목록에서 두 열을 모두 제거하고 다음을 추가합니다.
"datediff(ss,[시작 날짜],[종료 날짜]) as [기간]"
ss 대신 키워드(keyword)ms를 사용하는 경우 기간을 밀리초 단위로 가져옵니다
열 대신 DAX 계산 측정값 사용
이전에 DAX 식 언어를 사용해 본 적이 있다면 계산된 열은 모델의 다른 열을 기반으로 새 열을 파생하는 데 사용되는 반면, 계산된 측정값은 모델에서 한 번 정의되지만 피벗 테이블이나 다른 보고서에서 사용할 때만 평가된다는 것을 이미 알고 있을 것입니다.
한 가지 메모리 절약 기술은 일반 또는 계산된 열을 계산된 측정값으로 바꾸는 것입니다. 전형적인 예로는 단가, 수량 및 합계가 있습니다. 세 개가 모두 있는 경우 두 개만 유지하고 DAX를 사용하여 세 번째를 계산하여 공간을 절약할 수 있습니다.
어떤 2개의 열을 유지해야 합니까?
위의 예제에서는 수량과 단가를 유지합니다. 이 두 값은 합계보다 적습니다. 합계를 계산하려면 다음과 같이 계산된 측정값을 추가합니다.
"TotalSales:=sumx('Sales Table','Sales Table'[Unit Price]*'Sales Table'[Quantity])"
계산된 열은 둘 다 모델의 공간을 차지한다는 점에서 일반 열과 같습니다. 반면, 계산된 측정값은 즉석에서 계산되며 공간을 차지하지 않습니다.
결론
이 문서에서는 보다 메모리 효율적인 모델을 빌드하는 데 도움이 되는 몇 가지 접근 방식에 대해 설명했습니다. 데이터 모델의 파일 크기와 메모리 요구 사항을 줄이는 방법은 전체 열과 행 수, 그리고 각 열에 나타나는 고유 값의 수를 줄이는 것입니다. 다음은 우리가 다룬 몇 가지 기술입니다.
- 물론 열을 제거하는 것이 공간을 절약하는 가장 좋은 방법입니다. 실제로 필요한 열을 결정합니다.
- 경우에 따라 열을 제거하고 테이블에서 계산된 측정값으로 바꿀 수 있습니다.
- 테이블에 모든 행이 필요하지 않을 수도 있습니다. 테이블 가져오기 마법사에서 행을 필터링할 수 있습니다.
- 일반적으로 단일 열을 여러 개의 개별 부분으로 나누는 것은 열의 고유 값 수를 줄이는 좋은 방법입니다. 각 부분은 적은 수의 고유 값을 가지며 결합된 합계는 원래 통합 열보다 작습니다.
- 대부분의 경우 보고서에서 슬라이서로 사용할 고유한 부분도 필요합니다. 적절한 경우 시, 분, 초와 같은 부분에서 계층 구조를 만들 수 있습니다.
- 열에 필요한 것보다 더 많은 정보가 들어 있는 경우가 많습니다. 예를 들어 열에 소수점이 저장되지만 모든 소수점을 숨기기 위해 서식을 적용했다고 가정해 보겠습니다. 반올림은 숫자 열의 크기를 줄이는 데 매우 효과적일 수 있습니다.
이제 통합 문서 크기를 줄이기 위해 할 수 있는 작업을 수행했으므로 통합 문서 크기 최적화 프로그램도 실행하는 것이 좋습니다. 이 프로그램은 Excel 통합 문서를 분석하여 가능한 경우 추가로 압축합니다. 통합 문서 크기 최적화 프로그램을 다운로드합니다.