Power BI - 데이터 적재 , ETL

 

 

1. 원본 데이터 적재

실습용 데이터로 AdventureWorks 샘플 DB는 2019년 자료를 사용하였습니다.

https://learn.microsoft.com/ko-kr/sql/samples/adventureworks-install-configure?view=sql-server-ver16&tabs=ssms

 

다운로드한 데이터 파일을 SSMS를 통해 Desktop DB에 백업하고 Target DB가 될 데이터 베이스에 테이블을 생성하였습니다.

 

 

SSMS에서 원본 데이터의 테이블 우클릭 > 테이블 스크립팅 > CREATE > 새 쿼리 편집기 창을 통해 

해당 테이블의 CREATE 문을 확인할 수 있습니다. 

원본 데이터가 있는 DB를 편의상 Source DB라 칭하겠습니다.

create 구문을 사용하여 Target DB에 데이터가 없는 빈 테이블을 생성합니다. 

이때 Create 구문에 Identity (1,1) 과 같이 자동으로 데이터를 생성해주는 

IDENTITY [ (시작값, 증가값) ] 과 같은 구문이 있을때는 주의해야합니다. 원본 데이터와 그 값이 달라질 수 있습니다.

 

2. DB에 적재된 데이터 ETL

 

ETL은 크게 SSIS(Microsoft SQL Server Integration Services)와  ADE(Azure Data Factory ETL Tool)두 가지 방법이 있는데 SSIS는 On-premise  기반이고 ADF는 클라우드  기반이라고 한다.

 

 

Cloud

 

필요한 IT 자원만을 선택해 인터넷을 통해 서비스 방식으로 구입해 사용하는 방식

크게 세가지로 나뉨

SaaS( 서비스로서의 소프트웨어) : 소프트웨어를 웹에서 사용할 수 있는 서비스로 소프트웨어를 소유하지 않고 웹 브라우저를 통해 사용하는 서비스로 구글 드라이브 같은 서비스

IaaS( 서비스로서의 인프라) : 인터넷을 통해 가상 서버와 데이터 스토리지 같은 데이터 자원을 빌려 사용할 수 있는 서비스 ,AWS 자원 빌려 넷플릭스 운영

PaaS(서비스로서의 플랫폼) : 소프트웨어를 개발할 때 필요한 플랫폼을 제공하는 서비스 개발자가 개발환경을 위해 필요한 하드웨어나 소프트웨어의 구축없이 개발하고 구축하고 실행하는데 필요한 개발도구와 환경을 제공하는 서비스로 구글 앱 엔진이 해당됨

 

 

 

On-premise

 

기업의 서버를 클라우드 같은 원격 환경에서 운영하는 방식이 아닌, 자체적으로 보유한 전산실 서버에 직접 설치해 운영하는 방식을 의미함

장점은 기업의 비즈니스 정보를 보안성 높게 관리할 수 있다는 것

단점은 시스템을 구축하는데 있어서 많은 시간과 비용이 소요된다는 것

 

< 차이점>

 

	클라우드	온프레미스
1. 비용	초기 투자 비용 없음 구독형/충전형 요금제 데이터 증가 시 추가적인 비용 발생	높은 초기 구축 비용 데이터로 인한 추가 비용 적음 장기적으로 사용 시 비용 효율 높음
2. 보안	클라우드 서비스 제공 업체의 보안 시스템	기업 자체의 보안 시스템 적용
3. 구축 및 관리	신속한 도입 가능 관리를 위한 별도의 전문 인력 불필요	시스템 구축에 별도의 시간 필요 필요에 따라 맞춤형으로 구축 가능 인프라를 관리를 위한 전담 인력 및 조직 필요

 

○ SSIS 를 사용하여 ETL

 

SSIS 참고 사이트 

https://www.tutorialgateway.org/ssis/

 

SSIS :

다양한 소스에서 데이터를 추출하고 사용자 요구사항에 따라 해당 데이터를 변환한 다음 데이터를 다양한 대상으로 로드함

 

ETL : 추출(Extract), 변환(Transform), 로드(Load)를 나타내며 조직에서 여러 시스템의 데이터를 단일 데이터베이스, 데이터 저장소, 데이터 웨어하우스 또는 데이터 레이크에 결합하기 위해 일반적으로 허용되는 방법

 

1. Integration Service Project 선택

2. 연결관리자를 통해 데이터베이스(서버) 연결

 

연결관리자 :

데이터베이스에서 테이블을 추출하거나 데이터 웨어하우스에 행을 로드하거나 데이터베이스에서 텍스트나 excel 파일로 또는 그 반대로 레코드를 전송하는 등의 행위를 수행하기 위해 필요함

>> OLE DB, 플랫파일 , ADO.NET 등 해당하는 연결관리자 선택

• ADO.NET 연결 > Desktop 서버 연결 > Source DB로 이름 변경(선택)
• ADO.NET 연결 > 타겟서버 연결 > Target DB로 이름 변경(선택)
• 제어 흐름 창에 sql  실행 태스크 , 데이터 흐름 태스크 추가 >> 이것이 하나의 패키지가 됨

3. ADO NET 원본 > 편집 > Source DB의 옮길 데이터 테이블을 선택 , 
ADO NET 대상 > 편집 > Target DB의 데이터를 적재할 테이블 선택 
>> 화살표로 연결

 

4. SQL 실행 태스크 
>> Connection Type : ADO.NET / Connection : TargetDB
>> 이때 외래키때문에 오류발생할 수 있음 외래키 없는 테이블 생성 필요
패키지 실행하면 데이터가 적재되는데 이때 중복 데이터 생성 방지를 위해
데이터를 삭제해주는 작업이 필요하다
이를 위해 TRUNCATE 구문을 사용한다
>> SQLStatement 에 Truncate구문 작성 : TRUNCATE TABLE [테이블명]

 

5. 위와 같이 하나의 테이블의 데이터를 이관하는 하나의 패키지에
하나의 (SQL + 데이터 태스크) 흐름을 두고
테이블개수만큼 패키지를 복사하여 생성 후
전체 패키지를 실행하는 하나의 패키지를 생성하여
패키지 실행 태스크를 통해 데이터를 적재한다

패키지들을 실행하기 위한 패키지

 

 

 

 

○ ADF(Azure Data Factory) 를 사용하여 ETL

 

Azure : 클라우딩 서비스 , 웹상에서 ETL 가능

https://portal.azure.com/

 

1. 리소스 만들기 > Data Factory
   리소스 항목에서 생성된 것을 확인할 수 있음
2. 생성된 Data Factory  > Studio 시작하기
3. 통합 런타임 >  새로만들기 > Self Hosted > 수동 설치 > 인증 키 (key1) 를 통해 인증하면 
   클라우드와 PC가 연결됨
   : Integration Runtime 필요 >> 클라우드 환경에서 On-Premise 환경과 연결하기 위해
4. 연결된 서비스(연결관리자) > 새로만들기 > sql  서버 > Source DB와 Target DB를 연결해준다.
   이때 DeskTop의 아이디와 비밀번호는 SSMS 참고 및 사용자 로그인 비밀번호
   Target DB는 동일하지만 IR을 생성한 IR이 아닌 AutoResolveIntegrationRuntime 로 설정해줌
   윈도우 인증 안되면 sql 인증으로하기 > 그러나 보통은 Sql 인증
5. 데이터 세트 (임시 저장) 
   매개변수는 스키마와 테이블 >> SCHEMA_NM , TABLE_NM
   
6. 파이프라인 생성 (SSIS의 패키지와 같은 역할)
   데이터 복사 > 원본에 source , 싱크에 target
   일부분만 가지고오고싶다면 쿼리 선택 후 쿼리문으로 select
   >> 또는 전체 파이프라인에 매개변수 두개 (스키마, 테이블) 이후에 동적 컨텐츠로 가져오기
   Select * from @{ 스키마}.@{테이블}
   사전복사 스크립트에 > truncate table @ 스키마. @ 테이블
   Ex) Truncate Table @{pipeline().parameters.SCHEMA_NM}.@{pipeline().parameters.TABLE_NM}
   Fact 데이터는 Delete > 중복 방지
   Dimension 데이터는 Truncate

 

 

++ 테이블 자동 만들기는특별한 경우 아니면 사용하지 않음

매핑으로 맞게 연결되었는지 확인

매개변수 사용시 편리함 >>  그 값만 바꿔주면됨

 

ETL 수정시각 포함해주라는 요청사항

: 원본 > 추가열 > 열 이름 만들고 사용자 지정 또는 동적 콘텐츠로 날짜 추가가능

 

트리거 : 자동으로 수행되게 하는것 >> 관리 영역

트리거 > 새로만들기 > 간격 지정(시간대)

 

 

 

 

3. Azure SQL Server로 이관한 데이터를 정제하여 Power BI에서 사용 가능한 Data Mart (SQL View) 구축

 

View 생성 : CREATE VIEW [뷰명] AS SELECT [컬럼명] FROM [테이블명] WHERE [조건절]

 

아래와 같이 Join 조건에 맞추어 주어진 View 정보에 따라 View를 생성했습니다.

CREATE VIEW [dbo].[DIM_Products] AS
SELECT ProductKey 
	 , ProductAlternateKey AS ProductItemCode 
	 , EnglishProductName AS ProductName 
	 , EnglishProductSubcategoryName AS SubCatagory 
	 , EnglishProductCategoryName AS ProductCategory 
	 , Color AS ProductColor 
	 , Size AS ProductSize 
	 , ProductLine 
	 , ModelName AS ProductModelName 
	 , EnglishDescription AS ProductDescription 
	 , ISNULL([Status] , 'Outdated' ) AS ProductStatus
FROM Dimproduct AS p 
LEFT JOIN DimproductSubCategory AS psc ON p.ProductSubcategoryKey = psc.ProductSubcategoryKey
LEFT JOIN DimproductCategory AS pc ON psc.ProductSubCategoryKey = pc.ProductCategoryKey

 

 

추후에 Power BI에서 데이터 가져오기를 통해 View 데이터를 토대로 보고서를 작성하면됩니다.