이 글의 목적은 엑셀 Power Pivot에서 발생하는 데이터 원본 연결 오류를 체계적으로 진단하고 해결하여 현장에서 즉시 적용할 수 있도록 돕는 것이다.
Power Pivot 연결 구조를 먼저 이해한다
Power Pivot은 데이터 모델(VertiPaq 컬럼 저장 엔진) 위에 관계·측정값(DAX)·계산열을 구성하여 피벗테이블로 분석하는 도구이다. Power Pivot 연결은 다음 요소로 구성된다.
- 데이터 원본: SQL Server, Oracle, MySQL, PostgreSQL, Excel·CSV·TXT, OData, SharePoint, OneDrive, 폴더 등이다.
- 연결 계층: OLE DB 또는 ODBC Provider·Driver가 설치되어야 한다.
- 인증 방식: Windows 통합, 데이터베이스 로그인, 조직 계정(Azure AD), 익명·Basic, OAuth 등이다.
- 전송 계층: 네트워크, 프록시, 방화벽, TLS/SSL 설정이 관여한다.
- 엑셀 구조: 32/64비트 일치, Click-To-Run/MSI 차이, 추가기능 로드 상태, 신뢰 센터 설정이 영향을 준다.
증상→원인→해결의 매핑 표
| 대표 오류 메시지·증상 | 주요 원인 | 즉각 해결책 |
|---|---|---|
| “데이터 원본에 연결할 수 없음”, “연결 테스트 실패” | Provider 미설치 또는 비트수 불일치, 서버명·포트 오류, 방화벽 차단이다. | 필요 Provider 설치 후 엑셀 비트수와 일치시키고, 서버·포트·DNS 점검 및 방화벽 허용 규칙을 추가한다. |
| “시간 제한 초과” | 네트워크 지연, 대용량 쿼리, 인덱스 부재, Command Timeout 기본값 부족이다. | 연결 속성에서 Command Timeout을 상향하고, WHERE 절·인덱스·필요 컬럼만 로드한다. |
| “암호화 프로토콜 불일치/TLS 오류” | 서버·클라이언트 TLS 버전 미일치, 중간 인증서 누락이다. | TLS 1.2 활성화, 최신 인증서 체인 설치, 구형 프로토콜 사용 중지 후 재시도한다. |
| “로그인 실패” | 자격 증명 저장 불일치, 암호 변경, MFA 요구, 계정 잠김이다. | 데이터 원본 자격 증명 삭제 후 재입력, 조직 계정 로그인 갱신, 계정 잠금 해제 요청한다. |
| “클래스가 등록되지 않음” 또는 “Provider not found” | MSOLEDBSQL·ACE·Oracle 등 OLE DB Provider 미등록이다. | 해당 Provider 재설치 또는 재등록(regsvr32), 재부팅 후 확인한다. |
| “관계 만들기 실패/키가 고유하지 않음” | 차원 테이블 키 중복, 데이터 형식 불일치, 공백 값이다. | 키 고유성 확보, NULL 처리, 데이터 형식 일치(Int vs Text)로 정규화한다. |
| SharePoint/OneDrive 경로 연결 끊김 | 상대·절대 경로 변경, 동기화 위치 차이이다. | 웹 URL 또는 365 “라이브 연결” 경로로 재설정하고, 파일 이동 시 연결 정의 갱신한다. |
| Power Query는 새로고침되나 Power Pivot은 실패 | 프라이버시 레벨·자격 증명 스코프 불일치이다. | 쿼리 옵션에서 프라이버시 레벨 일치, 자격 증명을 동일 스코프에 재저장한다. |
사전 점검 체크리스트
- 엑셀 비트수와 Provider 비트수를 일치(32/64)한다.
- Power Pivot 추가기능이 활성 상태인지 확인한다(파일→옵션→추가 기능→COM 추가 기능→Microsoft Power Pivot for Excel 체크).
- 데이터 원본에 직접 접속이 가능한지 별도 도구(SSMS, sqlcmd, ODBC 테스트)로 확인한다.
- 방화벽·프록시·VPN 정책에서 대상 포트가 허용되는지 점검한다.
- 자격 증명(Windows/DB/조직 계정) 최신 여부를 확인한다.
- TLS 1.2 우선 사용 환경인지 서버·클라이언트 정책을 확인한다.
- 파일이 공유 네트워크·클라우드에서 이동되었는지 경로 변동을 확인한다.
주의 :
회사 PC에서 Provider 설치·레지스트리 수정이 제한되는 경우가 많다. IT 보안 정책과 표준 설치 패키지를 먼저 확인한다.
필수 Provider·Driver와 32/64비트 정렬
| 원본 | 권장 Provider/Driver | 비고 |
|---|---|---|
| SQL Server | MSOLEDBSQL(OLE DB) 또는 ODBC Driver for SQL Server | 구형 SQLNCLI는 신기능·TLS에서 제약이 있다. |
| Access/Excel 파일 | Microsoft Access Database Engine(ACE.OLEDB.12.0/16.0) | 엑셀 비트수 맞춰 설치한다. |
| Oracle | Oracle Provider for OLE DB 또는 ODAC ODP.NET·ODBC | 홈 경로·TNS 설정이 필요하다. |
| MySQL | MySQL ODBC Connector | ANSI/Unicode 버전 구분에 유의한다. |
| PostgreSQL | psqlODBC | SSL 모드·인증서 경로를 확인한다. |
| OData/SharePoint | 네이티브 커넥터 또는 OData Feed | 조직 계정 인증 갱신이 잦다. |
주의 :
엑셀 64비트에 32비트 Provider만 있으면 연결이 실패한다. 두 비트수 혼용은 피한다.
연결 만들기와 테스트 절차 표준
- 데이터→데이터 가져오기→해당 원본 선택→연결 문자열·자격 증명 입력한다.
- 프리뷰에서 필요한 열만 선택하고 필터를 적용하여 로드량을 줄인다.
- “데이터 모델에 로드”를 체크한다.
- Power Pivot 창에서 관계를 정의하고 데이터 형식을 지정한다.
- 데이터→연결→해당 연결 속성→정의에서 Command Timeout을 설정한다(예: 600초).
인증 관련 문제 해결
- Windows 통합 인증: 도메인·위임 정책, SPN 충돌 여부를 확인한다.
- 데이터베이스 로그인: 암호 변경 후 캐시 자격 증명을 삭제하고 재저장한다.
- 조직 계정(Azure AD): 토큰 만료 시 다시 로그인한다. MFA가 강제된 테넌트는 대화형 로그인 창을 통해 갱신한다.
- 프록시 환경: 시스템 프록시가 인증을 요구할 경우 Windows 자격 증명 관리자에 저장하거나 프록시 예외 목록에 대상 FQDN을 등록한다.
제어판 → 자격 증명 관리자 → Windows 자격 증명 → 일반 자격 증명 추가 대상: <서버 FQDN 또는 URL> 사용자: DOMAIN\id 또는 user@domain 암호: ********
TLS 1.2 및 암호화 오류 대처
최근 서버는 TLS 1.2 이상을 요구하는 경우가 많다. 클라이언트·서버 모두 TLS 1.2를 활성화한다.
레지스트리 편집기 [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client] "DisabledByDefault"=dword:00000000 "Enabled"=dword:00000001
주의 :
레지스트리 수정 전 시스템 복원 지점을 생성한다. 조직 정책(GPO)과 충돌하지 않도록 보안팀 승인 후 적용한다.
SQL Server 연결 전문 팁
- 서버명은 FQDN 또는 <서버명>,<포트> 형태를 사용한다. 인스턴스가 명시적 포트를 쓰면 브라우저 서비스 의존을 줄일 수 있다.
- 대규모 테이블은 뷰 또는 쿼리로 필요한 컬럼·행만 가져온다.
- 변경 데이터 캡처·증분 로드는 Power Pivot에 기본 제공되지 않는다. 소스에서 날짜 키로 필터링한다.
- 수백만 행 로드 시는 사전 형식 정규화(정수 키, 날짜 키, 범주 코드화)로 메모리 사용을 줄인다.
Oracle·MySQL·PostgreSQL 연결 체크포인트
- Oracle: ORACLE_HOME, TNS_ADMIN, tnsnames.ora 경로를 명시하고, EZCONNECT(<host>:<port>/<service>)를 고려한다. NLS 설정으로 숫자·날짜 파싱 오류를 예방한다.
- MySQL: SSL 모드(REQUIRED)와 서버 CA 경로를 지정한다. 타임존 변환이 필요한 DATETIME 컬럼은 쿼리에서 변환한다.
- PostgreSQL: Verify-Full 모드 사용 시 서버 CN/SAN과 호스트명이 일치해야 한다.
Power Query와 프라이버시 레벨 동기화
Power Query를 통해 모델로 로드한 경우 프라이버시 레벨이 다르면 조인 시 버퍼링·차단이 발생한다.
- 데이터→쿼리 및 연결→쿼리 우클릭→편집→파일→옵션 및 설정→쿼리 옵션→현재 통합 문서→프라이버시에서 모든 원본 레벨을 일치시킨다.
- 조직 정책이 엄격한 경우 원본을 동일 보안 경계로 묶는다.
연결 속성 고급 설정
- 데이터→연결→해당 연결 선택→속성→정의 탭에서 연결 문자열과 명령 텍스트를 검토한다.
- “배경 새로 고침 사용”은 대규모 로드 시 비활성화하여 충돌을 줄인다.
- “파일에 자격 증명 저장”은 외부 공유 파일에서 최소화한다.
관계·키·데이터 형식 오류 해결
- 관계는 기본적으로 다:1 단방향이다. 사실 테이블의 외래 키와 차원 테이블의 고유 키가 필요하다.
- 키 컬럼에서 공백·NULL을 제거하고 데이터 형식을 일치시킨다(Int vs Text).
- 중복 키는 DISTINCT로 정리하거나 소스 모델링을 개선한다.
- 날짜 테이블은 연속 날짜·고유 키를 보장한다.
대용량 성능 최적화
- 필요한 열만 로드한다. 텍스트·고유 값 종류가 많은 열은 메모리 사용량을 급증시킨다.
- 계산열 대신 가능한 한 측정값을 사용한다. 계산열은 저장을 증가시킨다.
- 카디널리티를 낮추기 위해 범주 코드를 사용한다.
- 초기 로드 후 관계 인덱스를 소스에서 최적화한다.
경로·배포 이슈 해결
- 네트워크 공유 경로는 UNC(\\서버\공유\폴더\파일.xlsx)로 통일한다.
- SharePoint/OneDrive는 웹 URL을 사용하고, 동기화 로컬 경로가 PC마다 다른 문제를 회피한다.
- 파일 이동 후 데이터→연결→속성→정의에서 경로를 재지정한다.
추가기능·신뢰 센터 점검
- 파일→옵션→추가 기능→관리: COM 추가 기능→이동→“Microsoft Power Pivot for Excel” 체크한다.
- 파일→옵션→보안 센터→보안 센터 설정→외부 콘텐츠에서 데이터 연결 보안 수준을 검토한다.
- 매크로가 필요한 경우 서명된 게시자를 신뢰 목록에 추가한다.
진단 로그·문제 재현 팁
- Power Query 추적: %LocalAppData%\Microsoft\Power Query\Trace
- Office Telemetry 로그: %LocalAppData%\Microsoft\Office\Telemetry
- 이벤트 뷰어: 응용 프로그램·시스템 로그에서 Schannel, OLE DB, MsOffice Alerts를 확인한다.
자동 복구 절차(현장용 플레이북)
- 엑셀 비트수와 Provider 일치 확인 후 누락분 설치한다.
- 연결 문자열을 서버 FQDN, 명시적 포트로 정정한다.
- 자격 증명 캐시를 삭제하고 재로그인한다.
- TLS 1.2 활성화 및 중간 인증서를 갱신한다.
- Command Timeout을 600~1200초로 상향한다.
- 필요 열·행만 로드하도록 쿼리를 조정한다.
- 관계 키 고유성·데이터 형식을 정규화한다.
- SharePoint·OneDrive 경로를 웹 URL로 재설정한다.
에러 코드·상황별 대응 표
| 코드·키워드 | 설명 | 핵심 조치 |
|---|---|---|
| 0x80004005 | 일반 OLE DB/COM 실패 | Provider 재설치, 권한·경로 점검 |
| Timeout | 쿼리 실행 시간 초과 | Command Timeout 상향, 쿼리 최적화 |
| TLS/Schannel | 암호화 핸드셰이크 실패 | TLS 1.2 강제, 인증서 체인 수정 |
| Login failed | 인증 오류 | 자격 증명 재등록, 계정·MFA 정책 확인 |
| Provider not registered | Provider 미설치 | MSOLEDBSQL/ACE/ODBC 설치 |
| Cannot create relationship | 키 중복·형식 불일치 | 키 정규화, 형식 일치 |
스크립트·설치 예시
관리자 권한 PowerShell에서 무인 설치 예시를 사용한다.
# ACE OLE DB 2016 x64 설치 예시 Start-Process msiexec.exe -ArgumentList '/i AccessDatabaseEngine_X64.exe /quiet /norestart' -Wait
SQL Server ODBC Driver 설치 예시
Start-Process msiexec.exe -ArgumentList '/i msodbcsql.msi IACCEPTMSODBCSQLLICENSETERMS=YES /quiet /norestart' -Wait
등록 실패 시 OLE DB DLL 재등록을 시도한다.
regsvr32 "C:\Program Files\Common Files\Microsoft Shared\OFFICE16\ACEOLEDB.DLL"
주의 :
regsvr32는 OLE DB 공급자가 COM 서버로 등록 가능한 경우에만 적용된다. 공식 설치 관리자를 우선한다.
운영 환경에서의 거버넌스
- 공유 파일은 연결 정의가 하드코딩된 로컬 경로를 사용하지 않도록 표준화한다.
- 데이터 원본 계정은 비대면 서비스 계정을 사용하고 암호 주기를 정책에 맞춘다.
- 버전 호환성 표를 유지하여 PC 교체 시 Provider 누락을 예방한다.
케이스 스터디: 대용량 판매 데이터 연결 실패 해결
- 증상: 1천만 행 판매 사실 테이블 로드시 5분 내 타임아웃이다.
- 원인: 인덱스 부재·과도한 열 로드·VPN 지연이다.
- 조치: 날짜·매장·상품 키만 로드, 필요 측정값은 DAX로, 소스 인덱스 생성, Command Timeout 1200초로 상향, VPN 우회 전용선 적용이다.
- 결과: 로드 시간 4분→1분 20초로 단축, 새로고침 성공률 100% 확보이다.
문제 재발 방지 체크리스트
- 새 원본 연결 전 Provider 설치 여부 점검 자동화 스크립트를 운영한다.
- 연결 정의 표준 템플릿과 명명 규칙을 사용한다.
- 모델에 불필요한 계산열·고카디널리티 열을 추가하지 않는다.
- 정기적으로 인증서·TLS 정책을 점검하고 만료 전 교체한다.
FAQ
Power Pivot과 Power Query 중 어디에서 필터링하는 것이 좋은가?
가능하면 Power Query 단계에서 행·열을 최소화하여 모델에 들어오는 데이터량을 줄이는 것이 좋다. 모델 크기가 줄면 새로고침 시간과 메모리 사용량이 모두 감소한다.
모델에 다대다 관계가 필요한 경우는 어떻게 처리하나?
Power Pivot은 기본적으로 다:1 관계를 권장한다. 다대다 시나리오는 브리지 테이블을 도입하여 양측 키의 고유 조합을 만든 뒤 사실 테이블과 각각 1:다 관계를 설정한다.
증분 새로고침을 할 수 있나?
Power Pivot 자체에는 증분 새로고침 기능이 없다. 소스 쿼리에 날짜·증분 키 조건을 두어 필요한 기간만 로드하는 방식으로 구현한다.
엑셀을 32비트에서 64비트로 바꾸면 모델이 깨지나?
통상 모델은 그대로 열리지만 Provider 비트수 재설치가 필요하다. 또한 매우 큰 모델은 64비트에서만 안정적으로 동작한다.
회사 보안 정책으로 외부 연결이 차단된 경우 대안은?
회사 보안 정책으로 외부 연결이 차단된 경우 대안은?
보안 존 내 중간 데이터베이스 또는 승인된 데이터 게이트웨이를 통해 간접 연결을 구성한다. 파일 반입 시에는 승인된 스테이징 영역을 사용한다.