Excel 및 Power Pivot 추가 기능을 사용 하 여 메모리 효율적인 데이터 모델 만들기

참고:  사용자 언어로 가능한 한 빨리 가장 최신의 도움말 콘텐츠를 제공하고자 합니다. 이 페이지는 자동화를 통해 번역되었으며 문법 오류나 부정확한 설명을 포함할 수 있습니다. 이 목적은 콘텐츠가 사용자에게 유용하다는 것입니다. 이 페이지 하단의 정보가 도움이 되었다면 알려주세요. 쉽게 참조할 수 있는 영어 문서 가 여기 있습니다.

Excel 2013 이상 버전에서는 수백만 개의 행을 포함 하는 데이터 모델 만들고 이러한 모델에 대해 강력한 데이터 분석을 수행할 수 있습니다. 있거나 없는 파워 피벗 추가 기능을 같은 통합 문서의 개수에 피벗 테이블, 차트 및 Power View 시각화를 지원 하기 위해 데이터 모델을 만들 수 있습니다.

참고: 이 문서에서는 Excel 2013의 데이터 모델에 설명 합니다. 그러나 동일한 데이터 모델링 및 Excel 2013의 도입 Powerpivot 기능에도 적용 Excel 2016. 효과적으로 작은 차이점 이러한 버전의 Excel 있습니다.

Excel에서 대규모 데이터 모델 쉽게 작성할 수는 없지만 다음과 같은 몇 가지 하지에 있습니다. 많은 테이블 및 열을 포함 하는 첫 번째, 큰 모델은 대부분 분석에 대 한 과도 한 번거로운 필드 목록에 대 한 합니다. 둘째, 큰 모델 다른 응용 프로그램과 동일한 시스템 리소스를 공유 하는 보고서 부정적인 영향을 주지 중요 한 메모리를 사용 합니다. 마지막으로, Office 365의 SharePoint Online 및 Excel Web App의 크기를 제한 10MB를 Excel 파일입니다. 수백만 개의 행을 포함 하는 통합 문서 데이터 모델에 대 한 됩니다 생겼을 10MB 제한을 매우 신속 하 게 됩니다. 데이터 모델 사양 및 제한을참고 합니다.

이 문서에서는 작업하기 더 쉽고 메모리 사용량이 적은, 긴밀한 구성의 모델을 구축하는 방법을 알아봅니다. 효율적인 모델 디자인의 모범 사례에 대해 충분히 이해한다면 Excel 2013, Office 365 SharePoint Online, Office Web Apps Server 또는 SharePoint 2013 등 어디에서 모델을 보든 효율적으로 모델을 만들고 사용할 수 있습니다.

또한 통합 문서 크기 최적화 프로그램을 실행 하는 것이 좋습니다. Excel 통합 문서를 분석 하 고 가능한 경우 추가로 압축 합니다. 통합 문서 크기 최적화 프로그램을 다운로드 합니다.

이 문서의 내용

압축 비율 및 메모리 내 분석 엔진

낮은 메모리 사용량을 위해서는 존재 하지 않는 열이 것이 효율적

항상 제외 해야 하는 열의 두 가지 예제

필요 없는 열을 제외 하는 방법

필요한 행만 필터링 하 시겠습니까?

열이 필요한 경우 여전히 공간 비용을 줄일 수 우리?

Datetime 열 수정

SQL 쿼리 수정

계산 된 측정값 열 대신 DAX 사용

유지할 두 개의 열 결정

결론

관련된 링크

압축 비율 및 메모리 내 분석 엔진

Excel의 데이터 모델에서는 메모리 내 분석 엔진을 사용하여 메모리에 데이터를 저장합니다. 이 엔진은 강력한 압축 기법을 구현하여 필요한 저장 공간을 줄임으로써 원래 크기의 극히 일부가 될 때까지 결과 집합을 축소합니다.

평균적으로 원래 지점의 동일한 데이터에 비해 7배에서 10배 정도까지 데이터 모델을 줄일 수 있습니다. 예를 들어 SQL Server 데이터베이스에서 7MB 데이터를 가져오는 경우 Excel에서 데이터 모델을 1MB 미만으로 쉽게 줄일 수 있습니다. 실제로 구현되는 압축 정도는 주로 각 열에 있는 고유 값의 수에 따라 다릅니다. 고유 값이 많을수록 이를 저장하기 위해 필요한 메모리 양이 더 많습니다.

압축과 고유 값에 대한 이야기하는 이유가 무엇일까요? 메모리 사용량을 최소화하는 효율적인 모델을 구축하는 일은 전적으로 압축 최대화와 관련이 있으며, 가장 쉬운 실행 방법은 꼭 필요하지 않은 열을 제거하는 것입니다(특히 열에 고유 값이 여러 개 들어 있는 경우).

참고: 개별 열에 필요한 저장 공간의 차이가 클 수 있습니다. 고유 값이 적은 여러 열이 있는 것이 고유 값이 많은 단일 열이 있는 것보다 나은 경우가 있습니다. Datetime 최적화에 대한 섹션에서 이 기법에 대해 자세히 다룹니다.

낮은 메모리 사용량을 위해서는 열이 존재하지 않는 것이 효율적

메모리 효율성을 가장 높이려면 애초에 열을 가져오지 않는 것이 좋습니다. 효율적인 모델을 구축하려는 경우에는 각 열을 살펴보고 해당 열이 수행할 분석에 필요한지 여부를 파악하여, 필요하지 않거나 확실하지 않은 경우에는 제외합니다. 필요한 경우 나중에 언제라도 새 열을 추가할 수 있습니다.

항상 제외해야 하는 열의 두 가지 예

첫 번째 예는 데이터 웨어하우스에서 가져온 데이터와 관련됩니다. 데이터 웨어하우스에서는 웨어하우스의 데이터를 로드하고 새로 고치는 ETL 프로세스의 아티팩트를 쉽게 볼 수 있습니다. 데이터가 로드되면 "만든 날짜", "업데이트 날짜" 및 "ETL 실행"과 같은 열이 만들어집니다. 이러한 열은 모델에 필요 없으므로 데이터를 가져올 때 선택을 취소해야 합니다.

두 번째 예는 팩트 테이블을 가져올 때 기본 키 열 생략과 관련이 있습니다.

팩트 테이블을 비롯한 다수의 테이블에는 기본 키가 있습니다. 고객, 직원 또는 판매 데이터가 포함된 대부분의 테이블의 경우 모델 내에서 관계를 만드는 데 사용할 수 있도록 테이블에 기본 키가 필요할 것입니다.

팩트 테이블은 다릅니다. 팩트 테이블의 경우 기본 키는 각 행을 고유하게 식별하는 데 사용됩니다. 정규화 목적으로 필요하기는 하지만, 분석에 사용할 열만 필요한 데이터 모델에서나 테이블 관계를 설정하려는 경우에는 유용성이 떨어집니다. 따라서, 팩트 테이블에서 데이터를 가져올 때는 해당 기본 키를 포함하지 않아야 합니다. 팩트 테이블의 기본 키는 모델의 공간을 대량으로 소비하면서도 관계를 만드는 데 사용할 수 없기 때문에 아무런 이점을 제공하지 않습니다.

참고: 데이터 웨어하우스 및 다차원 데이터베이스에서 대체로 숫자 데이터로 구성된 대규모 테이블을 종종 "팩트 테이블"이라고 합니다. 일반적으로 팩트 테이블에는 판매 및 비용 데이터 요소와 같은 비즈니스 실적 또는 거래 데이터가 포함되며, 이 데이터는 조직 구성 단위, 제품, 대상 시장, 지역 등에 맞춰 집계 및 조정됩니다. 팩트 테이블에서 비즈니스 데이터가 들어 있는 열이나 다른 테이블에 저장된 데이터를 상호 참조하는 데 사용할 수 있는 열은 모두 데이터 분석을 지원할 수 있도록 모델에 포함해야 합니다. 제외할 열은 팩트 테이블에만 존재하며 다른 위치에는 존재하지 않는 팩트 테이블의 기본 키 열입니다. 팩트 테이블은 매우 규모가 크기 때문에 모델 효율성 면에서 가장 큰 이득은 팩트 테이블에서 행이나 열을 제외하는 데서 옵니다.

필요 없는 열을 제외하는 방법

효율적인 모델 통합 문서에서 필요한 실제로 열만이 포함 되어 있습니다. 모델에 포함 되는 열을 제어 하려는 경우 Power Pivot 추가 기능에서 데이터를 가져올 테이블 가져오기 마법사를 사용 하지 않고 Excel에서 "데이터 가져오기" 대화 상자에서 해야 합니다.

테이블 가져오기 마법사를 시작하면 가져올 테이블을 선택합니다.

Power Pivot 추가 기능의 테이블 가져오기 마법사

각 테이블에 대해 미리 보기 및 필터 단추를 클릭하고 꼭 필요한 테이블의 부분을 선택합니다. 모든 열을 선택 취소한 후 분석에 필요한지 여부를 고려하여 원하는 열을 선택하는 것이 좋습니다.

테이블 가져오기 마법사의 미리 보기 창

필요한 행만 필터링하는 경우

회사 데이터베이스 및 데이터 웨어하우스에 있는 다수의 테이블에는 장기간에 걸쳐 축적된 기록 데이터가 있습니다. 또한 관심 분야에 속하는 테이블에 특정 분석에 필요 없는 비즈니스 영역에 대한 정보가 있을 수도 있습니다.

테이블 가져오기 마법사를 사용하여 기록 데이터나 관련 없는 데이터를 필터링하면 모델의 공간을 상당히 절약할 수 있습니다. 다음 그림에서는 날짜 필터를 사용하여 필요 없는 기록 데이터를 제외하고 현재 연도에 해당하는 데이터가 있는 행만 검색합니다.

테이블 가져오기 마법사의 필터 창

열이 필요한 경우 공간 비용을 줄이는 방법

열을 압축하기 위해 적용할 수 있는 방법이 몇 가지 더 있습니다. 열의 특성 중 압축에 영향을 주는 요소는 고유 값의 수뿐입니다. 이 섹션에서는 일부 열을 수정하여 고유 값의 수를 줄이는 방법에 대해 알아봅니다.

Datetime 열 수정

Datetime 열은 공간을 많이 차지하는 경우가 많습니다. 다행히 이 데이터 형식에 필요한 저장 공간을 줄이는 여러 기법이 있습니다. 이 기법은 열을 사용하는 방법 및 SQL 쿼리 작성 시의 신뢰 수준에 따라 달라집니다.

Datetime 열에는 날짜 부분과 시간이 있습니다. 특정 열이 필요한지 여부를 고려할 때는 Datetime 열에 대해 다음과 같은 동일한 질문을 여러 번 해 봅니다.

  • 시간 부분을 필요한가?

  • 시간 부분에서 시간 수준, 분 수준, 초 수준, 밀리초 수준 중 어느 수준까지 필요한가?

  • Datetime 열이 여러 개인 이유가 열 사이의 차이를 계산해야 하기 때문인가 아니면 단순히 연도, 월, 분기 등을 기준으로 데이터를 집계하기 위해서인가?

위의 각 질문에 대한 대답에 따라 Datetime 열을 처리하는 옵션이 결정됩니다.

이러한 모든 솔루션을 위해서는 SQL 쿼리를 수정해야 합니다. 쿼리 수정을 쉽게 하려면 각 테이블에서 열을 하나 이상 필터링해야 합니다. 열을 필터링하면 축약 형식(SELECT *)에서 훨씬 더 수정하기 쉬운 정규화된 열 이름이 포함된 SELECT 문으로 쿼리 구성이 변경됩니다.

이미 만들어진 쿼리를 살펴보겠습니다. 표 속성 대화 상자에서 쿼리 편집기로 전환하여 각 테이블의 현재 SQL 쿼리를 확인할 수 있습니다.

표 속성 명령이 표시된 Power Pivot 창 리본 메뉴

표 속성에서 쿼리 편집기를 선택합니다.

표 속성 대화 상자에서 쿼리 편집기 열기

쿼리 편집기에 표를 채우는 데 사용된 SQL 쿼리가 표시됩니다. 가져오기 과정에서 열을 필터링한 경우에는 쿼리에 정규화된 열 이름이 포함됩니다.

데이터 검색에 사용된 SQL 쿼리

반면, 열을 선택 취소하거나 필터를 적용하지 않고 테이블을 통째로 가져온 경우에는 쿼리가 비교적 수정하기 어려운 "Select * from"으로 표시됩니다.

짧은 기본 구문이 사용된 SQL 쿼리

SQL 쿼리 수정

쿼리를 찾는 방법을 알았으니 이제 쿼리를 수정하여 모델 크기를 추가로 줄일 수 있습니다.

  1. 통화 또는 10진수 데이터가 포함된 열의 경우 10진수가 필요 없으면 다음 구문을 사용하여 10진수를 제거합니다.

    “SELECT ROUND([Decimal_column_name],0)… .”

    센트보다 작은 단위가 아니라 센트가 필요한 경우 0을 2로 대체합니다. 음수를 사용하는 경우 십, 백 등의 단위로 반올림할 수 있습니다.

  2. 이름이 dbo.Bigtable.[Date Time]인 Datetime 열이 있는 경우 시간 부분이 필요하지 않으면 다음 구문을 사용하여 시간을 제거합니다.

    “SELECT CAST (dbo.Bigtable.[Date time] as date) AS [Date time]) “

  3. 이름이 dbo.Bigtable.[Date Time]인 Datetime 열이 있는 날짜 부분과 시간 부분이 모두 필요하면 SQL 쿼리에서 단일 Datetime 열 대신 여러 열을 사용합니다.

    “SELECT CAST (dbo.Bigtable.[Date Time] as date ) AS [Date Time],

    datepart(hh, dbo.Bigtable.[Date Time]) as [Date Time Hours],

    datepart(mi, dbo.Bigtable.[Date Time]) as [Date Time Minutes],

    datepart(ss, dbo.Bigtable.[Date Time]) as [Date Time Seconds],

    datepart(ms, dbo.Bigtable.[Date Time]) as [Date Time Milliseconds]”

    개별 열에서 각 부분을 저장하는 데 필요한 수만큼 열을 사용합니다.

  4. 시간 및 분이 필요하며 시간과 분을 하나의 시간 열에 함께 두려면 다음 구문을 사용할 수 있습니다.

    Timefromparts(datepart(hh, dbo.Bigtable.[Date Time]), datepart(mm, dbo.Bigtable.[Date Time])) as [Date Time HourMinute]

  5. [Start Time] 및 [End Time]과 같이 두 개의 Datetime 열이 있으며 두 열 사이의 시간 차이(초 단위)를 [Duration]이라는 열로 도출해야 하는 경우 목록에서 두 열을 모두 제거하고 다음을 추가합니다.

    “datediff(ss,[Start Date],[End Date]) as [Duration]”

    키워드로 ss 대신 ms를 사용하면 밀리초 단위의 기간을 얻게 됩니다.

열 대신 DAX 계산된 측정값 사용

이전에 DAX 식 언어를 사용하여 작업한 적이 있다면 계산된 열은 모델의 일부 다른 열을 기반으로 새 열을 파생하는 데 사용되고 계산된 측정값은 모델에서 한 번 정의되지만 피벗 테이블 또는 기타 보고서에서 사용될 때만 평가된다는 것을 이미 알고 있을 것입니다.

메모리를 절약하는 한 가지 방법은 일반 열 또는 계산된 열을 계산된 측정값으로 대체하는 것입니다. 전형적인 예는 단가, 수량, 합계입니다. 이 세 가지가 모두 있는 경우에는 두 개를 유지하고 나머지를 DAX를 사용하여 계산하는 방법으로 공간을 절약할 수 있습니다.

유지할 두 개의 열 결정

위의 예에서 수량, 단가를 유지하겠습니다. 이 두 항목의 경우 값의 수가 합계에 비해 적기 때문입니다. 합계를 계산하려면 다음과 같이 계산된 측정값을 추가합니다.

“TotalSales:=sumx(‘판매 테이블’,’판매 테이블’[단가]*’판매 테이블’[수량])”

계산된 열은 모델의 공간을 차지한다는 점에서 일반 열과 비슷합니다. 반면, 계산된 측정값은 즉석에서 계산되므로 공간을 차지하지 않습니다.

결론

이 문서에서는 메모리 효율성이 더 높은 모델을 구축하는 데 도움이 되는 여러 접근 방식에 대해 설명했습니다. 데이터 모델의 파일 크기 및 필요한 메모리를 줄이는 방법은 열과 행의 전체 수 및 각 열에 표시되는 고유 값의 수를 줄이는 것입니다. 여기서 다룬 기법은 다음과 같습니다.

  • 열을 제거하는 것이 공간 절약에 가장 좋은 방법입니다. 꼭 필요한 열을 결정합니다.

  • 테이블에서 열을 제거하고 대신 계산된 측정값으로 바꿀 수 있는 경우도 있습니다.

  • 테이블의 일부 행이 필요하지 않을 수도 있습니다. 이 경우, 테이블 가져오기 마법사에서 행을 필터링할 수 있습니다.

  • 일반적으로 단일 열을 여러 개의 개별 부분으로 나누면 열의 고유 값 수를 효율적으로 줄일 수 있습니다. 각 부분의 고유 값 수가 줄어들고 원래 통합 열에 비해 총 합계가 적어집니다.

  • 개별 부분을 보고서의 슬라이서로 사용해야 하는 경우가 많습니다. 경우에 따라 부분에서 시, 분, 초와 같이 부분의 계층 구조를 만들 수 있습니다.

  • 열에 필요 이상으로 많은 정보가 들어 있는 경우가 많습니다. 예를 들어 열에 10진수가 저장되는 경우를 가정합니다. 이 열에서 10진수를 모두 숨기도록 서식을 지정한 경우 반올림을 사용하면 숫자 열의 크기를 줄이는 데 매우 효율적일 수 있습니다.

통합 문서 크기를 줄일 수 있는 완료 했으므로 통합 문서 크기 최적화 프로그램을 실행 하는 것이 좋습니다. Excel 통합 문서를 분석 하 고 가능한 경우 추가로 압축 합니다. 통합 문서 크기 최적화 프로그램을 다운로드 합니다.

관련 링크

데이터 모델 사양 및 제한

통합 문서 크기 최적화 프로그램 다운로드

Power Pivot: Excel의 강력한 데이터 분석 및 데이터 모델링

Office 기술 확장
교육 살펴보기
새로운 기능 우선 가져오기
Office Insider 참여

이 정보가 유용한가요?

의견 주셔서 감사합니다!

피드백을 주셔서 감사합니다. Office 지원 에이전트와 연락하는 것이 도움이 될 것 같습니다.

×