자동화된 엔터프라이즈 BI

이 예제에서는 ELT(추출, 로드 및 변환) 파이프라인에서 증분 로드를 수행하는 방법에 대해 설명합니다. Azure Data Factory를 사용하여 ELT 파이프라인을 자동화합니다. 파이프라인은 증분 방식으로 최신 OLTP 데이터를 온-프레미스 SQL Server 데이터베이스에서 Azure Synapse로 이동합니다. 트랜잭션 데이터는 분석을 위해 테이블 형식 모델로 변환됩니다.

아키텍처

이 아키텍처의 Visio 파일을 다운로드합니다.

이 아키텍처는 Azure Synapse를 사용한 Enterprise BI에 표시된 아키텍처 위에 빌드하지만 엔터프라이즈 데이터 웨어하우징 시나리오에 대해 중요한 일부 기능을 추가합니다.

• Data Factory를 사용하여 파이프라인의 자동화.
• 증분 로드.
• 다중 데이터 원본 통합.
• 지리 공간 데이터 및 이미지 같은 이진 데이터 로드.

워크플로

이 아키텍처는 다음 서비스 및 구성 요소로 구성됩니다.

데이터 원본

온-프레미스 SQL Server. 원본 데이터는 SQL Server 데이터베이스 온-프레미스에 위치합니다. 온-프레미스 환경을 시뮬레이션하려면 Wide World Importers OLTP 예제 데이터베이스는 원본 데이터베이스로 사용됩니다.

외부 데이터. 데이터 웨어하우스에 대한 일반적인 시나리오는 여러 데이터 원본을 통합하는 것입니다. 이 참조 아키텍처는 연도별 도시 인구를 포함하는 외부 데이터 집합을 로드하여 OLTP 데이터베이스의 데이터와 통합합니다. "각 지역의 매출 증가가 인구 증가와 일치하거나 초과합니까?" 같은 인사이트에 이 데이터를 사용할 수 있습니다.

수집 및 데이터 스토리지

Blob Storage Blob Storage는 Azure Synapse로 로딩하기 전에 원본 데이터에 대한 준비 영역으로 사용됩니다.

Azure Synapse. Azure Synapse는 대용량 데이터에 대한 분석을 수행하도록 설계된 분산 시스템입니다. 고성능 분석을 실행하는 데 적합하도록 하는 MPP(대규모 병렬 처리)를 지원합니다.

Azure Data Factory. Data Factory는 데이터 이동 및 데이터 변환을 오케스트레이션하고 자동화하는 관리되는 서비스입니다. 이 아키텍처에서 다양한 단계의 ELT 프로세스를 조정합니다.

분석 및 보고

Azure Analysis Services. Analysis Services는 데이터 모델링 기능을 제공하는 완전히 관리되는 서비스입니다. 의미 체계 모델은 Analysis Services에 로드됩니다.

Power BI. Power BI는 비즈니스 정보에 대한 데이터를 분석하는 비즈니스 분석 도구 제품군입니다. 이 아키텍처에서 Analysis Services에 저장된 의미 체계 모델을 쿼리합니다.

인증

Microsoft Entra ID (Microsoft Entra ID)는 Power BI를 통해 Analysis Services 서버에 연결하는 사용자를 인증합니다.

Data Factory는 Microsoft Entra ID를 사용하여 서비스 주체 또는 MSI(관리 서비스 ID)를 사용하여 Azure Synapse에 인증할 수도 있습니다.

구성 요소

• Azure Blob Storage
• Azure Synapse Analytics
• Azure Data Factory
• Azure Analysis Services
• Power BI
• Microsoft Entra ID

시나리오 정보

데이터 파이프라인

Azure Data Factory에서 파이프라인은 이 경우에 작업을 조정하는 데 사용된 활동의 논리적 그룹화로서 데이터를 Azure Synapse로 로드하고 변환합니다.

이 참조 아키텍처에서는 자식 파이프라인의 시퀀스를 실행하는 투명 파이프라인을 정의합니다. 각 자식 파이프라인은 하나 이상의 데이터 웨어하우스 테이블에 데이터를 로드합니다.

권장 사항

증분 로드

자동화된 ETL 또는 ELT 프로세스를 실행하는 경우 이전 실행 이후에 변경된 데이터만 로드하는 것이 가장 효율적입니다. 이는 모든 데이터를 로드하는 전체 로드와 달리 증분 로드라고 합니다. 증분 로드를 수행하려면 데이터가 변경되었음을 식별하는 방법이 필요합니다. 가장 일반적인 방법은 날짜/시간 열이든 고유 정수 열이든 원본 테이블에서 일부 열의 최신 값을 추적하는 것을 의미하는 상위 워터 마크 값을 사용하는 것입니다.

SQL Server 2016부터 임시 테이블을 사용할 수 있습니다. 이들은 데이터 변경 내용의 전체 기록을 유지하는 시스템 버전이 지정된 테이블이 있습니다. 데이터베이스 엔진은 별도 기록 테이블의 모든 변경 기록을 자동으로 레코드합니다. FOR SYSTEM_TIME 절을 쿼리에 추가하여 기록 데이터를 쿼리할 수 있습니다. 내부적으로 데이터베이스 엔진은 기록 테이블을 쿼리하지만 애플리케이션에 대해서는 투명합니다.

임시 테이블은 시간이 지남에 따라 변경될 수 있는 차원 데이터에 유용합니다. 팩트 테이블은 대개 시스템 버전 기록을 유지하는 것이 사리에 맞지 않은 경우에 판매 같은 변경이 불가능한 트랜잭션을 나타냅니다. 대신 트랜잭션에는 대개 워터 마크 값으로 사용될 수 있는 트랜잭션 날짜를 나타내는 열이 있습니다. 예를 들어 Wide World Importers OLTP 데이터베이스에서 Sales.Invoices 및 Sales.InvoiceLines 테이블에는 sysdatetime()을 기본값으로 하는 LastEditedWhen 필드가 있습니다.

ELT 파이프라인의 일반적인 흐름은 다음과 같습니다.

1. 원본 데이터베이스의 각 테이블의 경우 마지막 ELT 작업이 실행될 때 마감 시간을 추적하여, 데이터 웨어하우스에 이 정보를 저장합니다. (초기 설치 시 항상 시간은 '1900-1-1'로 설정돼 있습니다.)

2. 데이터 내보내기 단계 중 마감 시간은 원본 데이터베이스의 저장 프로시저 집합에 매개 변수로 전달됩니다. 이러한 저장 프로시저는 마감 시간 이후 변경되거나 생성된 모든 레코드에 대해 쿼리합니다. 판매 팩트 테이블에 대해 LastEditedWhen 열을 사용하고, 차원 데이터에 대해 시스템 버전이 있는 임시 테이블을 사용합니다.

3. 데이터 마이그레이션이 완료되면 마감 시간을 저장하는 테이블을 업데이트합니다.

또한 각 ELT 실행에 대해 계보를 레코드하는 것이 유용합니다. 지정된 레코드에 대해 계보는 데이터를 생성한 ELT 실행을 사용하여 해당 레코드와 연결합니다. 각 ETL 실행의 경우 새 계보 레코드가 모든 테이블에 대해 만들어져 시작 및 종료 로드 시간을 보여줍니다. 각 레코드에 대한 계보 키는 차원 및 팩트 테이블에 저장됩니다.

새 일괄 처리 데이터가 웨어하우스에 로드된 후 Analysis Services 테이블 형식 모델을 새로 고칩니다. REST API를 사용한 비동기 새로 고침을 참조합니다.

데이터 정리

데이터 정리는 ELT 프로세스의 일부여야 합니다. 이 참조 아키텍처에서 잘못된 데이터 원본 하나는 아마도 사용할 수 있는 데이터가 없기 때문에 일부 도시에 인구가 없는 도시 인구 테이블입니다. 처리 동안 ELT 파이프라인은 도시 인구 테이블에서 해당 도시를 제거합니다. 외부 테이블보다는 준비 테이블에서 데이터 정리를 수행하세요.

외부 데이터 원본

데이터 웨어하우스는 종종 여러 소스의 데이터를 통합합니다. 예를 들어 인구 통계 데이터가 포함된 외부 데이터 원본입니다. 이 데이터 세트는 WorldWideImportersDW 샘플의 일부로 Azure Blob Storage에서 사용할 수 있습니다.

Azure Data Factory는 Blob Storage 커넥터를 사용하여 Blob Storage에서 직접 복사할 수 있습니다. 그러나 커넥터는 연결 문자열 또는 공유 액세스 서명이 필요하므로 공용 읽기 액세스를 사용하여 BLOB을 복사하는 데 사용할 수 없습니다. 해결 방법으로 PolyBase를 사용하여 Blob Storage에서 외부 테이블을 만든 다음, 외부 테이블을 Azure Synapse에 복사할 수 있습니다.

큰 이진 데이터 처리

예를 들어 원본 데이터베이스에서 City 테이블에는 지리 공간 데이터 형식을 포함하는 Location 열이 있습니다. Azure Synapse는 기본적으로 geography 형식을 지원하지 않으므로 이 필드는 로딩 동안 varbinary 형식으로 변환됩니다. (지원되지 않는 데이터 형식에 대한 해결 방법을 참조합니다.)

하지만 PolyBase는 varbinary(8000)의 최대 열 크기를 지원하여 일부 데이터가 잘릴 수도 있습니다. 이 문제에 대한 해결 방법은 다음과 같이 내보내기 중에 데이터를 청크로 분할한 다음, 청크를 다시 어셈블하는 것입니다.

1. 위치 열에 대해 임시 준비 테이블을 만듭니다.

2. 각 도시의 경우 위치 데이터를 8000바이트 청크로 분할하여 그 결과 각 도시에 대해 1-N 행이 됩니다.

3. 청크를 다시 어셈블하려면 T-SQL PIVOT 연산자를 사용하여 행을 열로 변환한 다음, 각 도시에 대해 열 값을 연결합니다.

문제는 각 도시가 지리 데이터의 크기에 따라 다른 수의 행으로 분할된다는 것입니다. PIVOT 연산자가 작동하려면 모든 도시에 동일한 수의 행이 있어야 합니다. 이 작업을 수행하기 위해 T-SQL 쿼리는 빈 값으로 행을 채우기 위해 몇 가지 요령을 수행하므로 모든 도시에 피벗 뒤의 열 수가 동일합니다. 결과 쿼리는 한 번에 하나씩 행을 반복하는 것보다 훨씬 더 빠른 것으로 밝혀졌습니다.

동일한 방식이 이미지 데이터에 사용됩니다.

느린 변경 차원

차원 데이터는 상대적으로 정적이지만 변경될 수 있습니다. 예를 들어 제품이 다른 제품 범주에 다시 할당될 수 있습니다. 느린 변경 차원을 처리하는 방법은 여러 가지가 있습니다. 유형 2라는 일반 기술은 차원이 변경될 때마다 새 레코드를 추가하는 것입니다.

유형 2 방법을 구현하려면 차원 테이블은 지정된 레코드의 유효 날짜 범위를 지정하는 추가 열이 필요합니다. 또한 원본 데이터베이스의 기본 키가 중복되게 되므로 차원 테이블에 인공적인 기본 키가 있어야 합니다.

예를 들어 다음 이미지는 Dimension.City 테이블을 보여줍니다. WWI City ID 열은 원본 데이터베이스의 기본 키입니다. City Key 열은 ETL 파이프라인 도중 생성된 인공 키입니다. 또한 테이블에는 각 행이 유효한 경우 범위를 정의하는 Valid From 및 Valid To 열이 있습니다. 현재 값에는 '9999-12-31'과 동일한 Valid To가 있습니다.

이 방식의 장점은 분석에 중요할 수 있는 기록 데이터를 보존한다는 것입니다. 그러나 동일한 엔터티에 대해 여러 행이 있다는 의미이기도 합니다. 예를 들어 WWI City ID = 28561과 일치하는 레코드는 다음과 같습니다.

각 판매 팩트의 경우 송장 날짜에 해당하는 도시 차원 테이블의 단일 행과 해당 팩트를 연결하려 합니다.

고려 사항

이러한 고려 사항은 워크로드의 품질을 향상시키는 데 사용할 수 있는 일단의 지침 원칙인 Azure Well-Architected Framework의 핵심 요소를 구현합니다. 자세한 내용은 Microsoft Azure Well-Architected Framework를 참조하세요.

보안

우수한 보안은 중요한 데이터 및 시스템에 대한 고의적인 공격과 악용을 방어합니다. 자세한 내용은 보안 요소의 개요를 참조하세요.

추가 보안을 위해 Virtual Network 서비스 엔드포인트를 사용하여 가상 네트워크에 대해서만 Azure 서비스 리소스를 보호할 수 있습니다. 이렇게 함으로써 해당 리소스에 대한 공용 인터넷 액세스를 완전히 제거하여 가상 네트워크의 트래픽만 허용하게 됩니다.

이 방법을 사용하여 Azure에서 VNet을 만든 다음, Azure 서비스에 대한 프라이빗 서비스 엔드포인트를 만듭니다. 그러면 이러한 서비스는 해당 가상 네트워크의 트래픽으로 제한됩니다. 게이트웨이를 통해 온-프레미스 네트워크에서 이 서비스에 연결할 수도 있습니다.

다음과 같은 제한 사항을 고려해야 합니다.

• Azure Storage에 대해 서비스 엔드포인트가 사용하도록 설정된 경우 PolyBase는 Storage의 데이터를 Azure Synapse에 복사할 수 없습니다. 이 문제에 대한 완화 방법이 있습니다. 자세한 내용은 Azure Storage에서 VNet 서비스 엔드포인트 사용의 영향을 참조합니다.

• 온-프레미스에서 Azure Storage로 데이터를 이동하려면 온-프레미스 또는 ExpressRoute에서 공용 IP 주소를 허용해야 합니다. 자세한 내용은 Virtual Network에 대한 Azure 서비스 보호를 참조합니다.

• Azure Synapse에서 데이터를 읽기 위해 Analysis Services를 사용하려면 Azure Synapse 서비스 엔드포인트를 포함하는 가상 네트워크에 Windows VM을 배포합니다. 이 VM에 Azure 온-프레미스 데이터 게이트웨이를 설치합니다. 그런 다음, 데이터 게이트웨이에 Azure Analysis 서비스를 연결합니다.

DevOps

• 프로덕션, 개발 및 테스트 환경에 대해 별도의 리소스 그룹을 만듭니다. 별도의 리소스 그룹을 만들면 배포 관리, 테스트 배포 삭제, 액세스 권한 할당 등이 더 간단해집니다.

• 각 워크로드를 별도의 배포 템플릿에 배치하고 리소스를 소스 제어 시스템에 저장합니다. 템플릿을 CI/CD 프로세스의 일부로 함께 또는 개별적으로 배포하여 자동화 프로세스를 더 쉽게 수행할 수 있습니다.

  이 아키텍처에는 세 가지 주요 워크로드가 있습니다.

  • 즉, 데이터 웨어하우스 서버, Analysis Services 및 관련 리소스
  • Azure Data Factory.
  • 온-프레미스에서 클라우드로 시뮬레이션된 시나리오

  각 워크로드에는 자체 배포 템플릿이 있습니다.

  데이터 웨어하우스 서버는 IaC 사례의 명령적 접근 방식을 따르는 Azure CLI 명령을 사용하여 설정 및 구성됩니다. 배포 스크립트를 사용하여 자동화 프로세스에 통합하는 것이 좋습니다.

• 워크로드를 준비하는 것이 좋습니다. 다음 단계로 이동하기 전에 다양한 단계에 배포하고 각 단계에서 유효성 검사를 실행합니다. 이렇게 하면 고도로 통제된 방식으로 프로덕션 환경에 업데이트를 푸시하고 예상치 못한 배포 문제를 최소화할 수 있습니다. 라이브 프로덕션 환경을 업데이트하기 위해 파란색-녹색 배포 및 카나리아 릴리즈 전략을 사용합니다.

  실패한 배포를 처리하기 위한 좋은 롤백 전략이 생깁니다. 예를 들어 배포 기록에서 이전에 성공한 배포를 자동으로 다시 배포할 수 있습니다. Azure CLI에서 오류 플래그 매개 변수에 대한 롤백을 참조하세요.

• Azure Monitor는 통합 모니터링 환경을 위해 데이터 웨어하우스 및 전체 Azure 분석 플랫폼의 성능을 분석하는 데 권장되는 옵션입니다. Azure Synapse Analytics는 Azure Portal 내에 모니터링 환경을 제공하여 데이터 웨어하우스 워크로드와 관련된 인사이트를 제공합니다. Azure Portal은 구성 가능한 보존 기간, 경고, 권장 사항, 메트릭과 로그용 사용자 지정 가능한 차트 및 대시보드를 제공하므로 데이터 웨어하우스를 모니터링할 때 권장되는 도구입니다.

자세한 내용은 Microsoft Azure Well-Architected Framework의 DevOps 섹션을 참조하세요.

비용 최적화

비용 최적화는 불필요한 비용을 줄이고 운영 효율성을 높이는 방법을 찾는 것입니다. 자세한 내용은 비용 최적화 핵심 요소 개요를 참조하세요.

Azure 가격 계산기를 사용하여 비용을 예측합니다. 다음은 이 참조 아키텍처에서 사용되는 서비스에 대한 몇 가지 고려 사항입니다.

Azure 데이터 팩터리

Azure Data Factory는 ELT 파이프라인을 자동화합니다. 파이프라인은 데이터를 온-프레미스 SQL Server 데이터베이스에서 Azure Synapse로 이동합니다. 그런 다음 데이터는 분석을 위해 테이블 형식 모델로 변환됩니다. 이 시나리오의 경우 가격 책정은 활동, 트리거 및 디버그 실행을 포함한 활동 실행에 대해 $0.001/월부터 시작합니다. 해당 가격은 오케스트레이션에 대한 기본 요금입니다. 데이터 복사, 조회 및 외부 작업과 같은 실행 작업에 대해서도 요금이 청구됩니다. 각 작업은 개별적으로 가격이 책정됩니다. 해당 월에 연결된 트리거 또는 실행이 없는 파이프라인에 대해서도 요금이 청구됩니다. 모든 작업은 분 단위로 일할 계산되고 반올림됩니다.

비용 분석 예제

서로 다른 두 소스에서 두 개의 조회 작업이 있는 사용 사례를 생각해보겠습니다. 하나는 1분 2초(반올림하여 최대 2분)가 걸리고, 다른 하나는 1분이 걸려서 총 시간은 3분입니다. 하나의 데이터 복사 작업은 10분이 걸립니다. 하나의 저장 프로시저 작업은 2분이 걸립니다. 총 활동 실행은 4분입니다. 비용은 다음과 같이 계산됩니다.

활동 실행: 4 * $ 0.001 = $0.004

조회: 3 * ($0.005 / 60) = $0.00025

저장 프로시저: 2 * ($0.00025 /60) = $0.000008

데이터 복사: 10 * ($0.25 /60) * 4 DIU(데이터 통합 단위) = $0.167

• 파이프라인 실행당 총 비용: $0.17.
• 30일 동안 하루에 한 번 실행: $5.1/월.
• 30일 동안 100개 테이블당 하루에 한 번 실행: $510

모든 활동에는 관련 비용이 있습니다. 가격 책정 모델을 이해하고 ADF 가격 계산기를 사용하면 성능뿐만 아니라 비용에 최적화된 솔루션을 얻을 수 있습니다. 서비스의 시작, 중지, 일시 중지 및 스케일링별로 비용을 관리합니다.

Azure Synapse

Azure Synapse는 쿼리 성능과 컴퓨팅 스케일링 성능 요구 사항이 높은 집약적 워크로드를 위한 것입니다. 종량제 모델을 선택하거나 1년(37% 할인) 또는 3년(65% 할인)의 예약 플랜을 사용할 수 있습니다.

데이터 스토리지는 별도로 청구됩니다. 재해 복구 및 위협 탐지와 같은 다른 서비스도 별도로 청구됩니다.

자세한 내용은 Azure Storage 가격 책정을 참조하세요.

Analysis Services

Azure Analysis Services의 가격 책정은 계층에 따라 다릅니다. 이 아키텍처의 참조 구현은 평가, 개발 및 테스트 시나리오에 권장되는 개발자 계층을 사용합니다. 다른 계층에는 소규모 프로덕션 환경에 권장되는 기본 계층, 중요 업무용 프로덕션 애플리케이션을 위한 표준 계층 등이 있습니다. 자세한 내용은 필요에 따른 적절한 계층을 참조하세요.

인스턴스를 일시 중지하면 요금이 청구되지 않습니다.

자세한 내용은 Azure Analysis Services 가격을 참조하세요.

Blob Storage

Azure Storage 예약된 용량 기능을 사용하여 스토리지 비용을 절감하는 것이 좋습니다. 이 모델을 사용하면 1년 또는 3년 동안 고정 스토리지 용량에 대한 예약을 약정할 수 있는 경우 할인을 받을 수 있습니다. 자세한 내용은 예약 용량을 통한 Blob Storage에 대한 비용 최적화를 참조하세요.

자세한 내용은 Microsoft Azure Well-Architected Framework의 비용 섹션을 참조하세요.

다음 단계

• Azure Synapse Analytics 소개
• Azure Synapse Analytics 시작
• Azure Data Factory 소개
• Azure Data Factory란?
• Azure Data Factory 자습서

관련 리소스

동일한 기술 중 일부를 사용하여 특정 솔루션을 보여주는 다음 Azure 예제 시나리오를 검토해 보세요.

• 영업 및 마케팅에 대한 데이터 웨어하우징 및 분석
• Azure Synapse를 사용한 Azure의 엔터프라이즈 BI