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

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

첫 번째 예제는 데이터 웨어하우스에서 시작된 데이터와 관련이 있습니다. 데이터 웨어하우스에서는 웨어하우스에서 데이터를 로드하고 새로 고치는 ETL 프로세스의 아티팩트 를 찾는 것이 일반적입니다. 데이터가 로드될 때 "날짜 만들기", "업데이트 날짜" 및 "ETL 실행"과 같은 열이 만들어집니다. 이러한 열은 모델에 필요하지 않으며 데이터를 가져올 때 선택 취소되어야 합니다.

두 번째 예제에는 팩트 테이블을 가져올 때 기본 키 열을 생략하는 작업이 포함됩니다.

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

팩트 테이블은 다릅니다. 팩트 테이블에서 기본 키는 각 행을 고유하게 식별하는 데 사용됩니다. 정규화를 위해 필요하지만 분석 또는 테이블 관계를 설정하는 데 사용되는 열만 사용하려는 데이터 모델에서는 유용하지 않습니다. 이러한 이유로 팩트 테이블에서 가져올 때 기본 키는 포함하지 않습니다. 팩트 테이블의 기본 키는 모델에서 엄청난 양의 공간을 사용하면서도 관계를 만드는 데 사용할 수 없으므로 아무런 이점을 제공하지 않습니다.

불필요한 열을 제외하는 방법

효율적인 모델에는 통합 문서에 실제로 필요한 열만 포함됩니다. 모델에 포함된 열을 제어하려면 Power Pivot 추가 기능의 테이블 가져오기 마법사를 사용하여 Excel의 "데이터 가져오기" 대화 상자 대신 데이터를 가져와야 합니다.

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

각 테이블에 대해 미리 보기 & 필터 단추를 클릭하고 실제로 필요한 테이블 부분을 선택할 수 있습니다. 먼저 모든 열의 선택을 취소한 다음, 분석에 필요한지 여부를 고려한 후 원하는 열을 확인하는 것이 좋습니다.

Datetime 열 수정

대부분의 경우 Datetime 열에는 많은 공간이 소요됩니다. 다행히 이 데이터 형식에 대한 스토리지 요구 사항을 줄이는 방법에는 여러 가지가 있습니다. 이 기술은 열을 사용하는 방법과 SQL 쿼리를 빌드할 때의 편안함 수준에 따라 달라집니다.

날짜/시간 열에는 날짜 부분과 시간이 포함됩니다. 열이 필요한지 묻는 경우 Datetime 열에 대해 동일한 질문을 여러 번 합니다.

• 시간 부분이 필요한가요?

• 시간 수준에서 시간 파트가 필요한가요? 분? 초? 밀리초?

• 데이터 간 차이를 계산하거나 연도, 월, 분기 등을 기준으로 데이터를 집계하려고 하므로 Datetime 열이 여러 개 있나요?

이러한 각 질문에 답변하는 방법은 Datetime 열을 처리하기 위한 옵션을 결정합니다.

이러한 모든 솔루션에는 SQL 쿼리를 수정해야 합니다. 쿼리를 더 쉽게 수정하려면 모든 테이블에서 하나 이상의 열을 필터링해야 합니다. 열을 필터링하여 쿼리 생성을 약어 형식(SELECT *)에서 정규화된 열 이름을 포함하는 SELECT 문으로 변경하면 훨씬 쉽게 수정할 수 있습니다.

사용자를 위해 만들어진 쿼리를 살펴보겠습니다. 테이블 속성 대화 상자에서 쿼리 편집기로 전환하고 각 테이블에 대한 현재 SQL 쿼리를 볼 수 있습니다.

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

쿼리 편집기는 테이블을 채우는 데 사용되는 SQL 쿼리를 보여줍니다. 가져오는 동안 열을 필터링한 경우 쿼리에 정규화된 열 이름이 포함됩니다.

SQL 쿼리 수정

이제 쿼리를 찾는 방법을 알게 되었으므로 모델 크기를 더 줄이기 위해 쿼리를 수정할 수 있습니다.

1. 통화 또는 10진수 데이터가 포함된 열의 경우 소수 자릿수가 필요하지 않은 경우 이 구문을 사용하여 10진수를 제거합니다.

   "SELECT ROUND([Decimal_column_name],0)... .”

   센트가 필요하지만 센트의 일부가 아닌 경우 0을 2로 바꿉니다. 음수를 사용하는 경우 단위, 수십, 수백 등으로 반올림할 수 있습니다.

2. dbo라는 Datetime 열이 있는 경우 Bigtable. [날짜 시간] 시간 파트가 필요하지 않습니다. 구문을 사용하여 시간을 제거합니다.

   "SELECT CAST(dbo. Bigtable. [날짜 시간] 날짜) AS [날짜 시간]) "

3. dbo라는 Datetime 열이 있는 경우 Bigtable. [날짜 시간] 및 Date 및 Time 파트가 모두 필요합니다. 단일 Datetime 열 대신 SQL 쿼리에서 여러 열을 사용합니다.

   "SELECT CAST(dbo. Bigtable. [날짜 시간] as date ) AS [Date Time],

   datepart(hh, dbo. Bigtable. [날짜 시간]) [날짜 시간 시간]으로,

   datepart(mi, dbo. Bigtable. [날짜 시간]) [날짜 시간 분]으로,

   datepart(ss, dbo. Bigtable. [날짜 시간]) [날짜 시간 초]로,

   datepart(ms, dbo. Bigtable. [날짜 시간]) As [Date Time Milliseconds]"

   각 파트를 별도의 열에 저장하는 데 필요한 만큼 열을 사용합니다.

4. 시간 및 분이 필요하고 한 번 열로 함께 사용하는 것이 좋습니다. 구문을 사용할 수 있습니다.

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

5. [시작 시간] 및 [종료 시간]과 같은 두 개의 datetime 열이 있고 [Duration]이라는 열로 초 단위로 시간 차이가 필요한 경우 목록에서 두 열을 모두 제거하고 다음을 추가합니다.

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

   ss 대신 ms 키워드를 사용하는 경우 시간(밀리초)이 표시됩니다.

유지해야 하는 열은 2개인가요?

위의 예제에서 수량 및 단가를 유지합니다. 이 두 값은 Total보다 적습니다. 합계를 계산하려면 다음과 같은 계산 측정값을 추가합니다.

"TotalSales:=sumx('Sales Table','Sales Table'[Unit Price]*'Sales Table'[Quantity])"

계산 열은 둘 다 모델의 공간을 차지한다는 의 일반 열과 같습니다. 반면 계산된 측정값은 즉석에서 계산되며 공간을 차지하지 않습니다.