BI 측면에서 본 SQL Server 2008 R2의 새로운 기능 - PowerPivot #1

안녕하세요 Kenial입니다.

지난 번 포스트 이후 새로운 글이 너무 늦었습니다. 개인적으로 바빴던 것이 가장 큰 이유였습니다만, PowerPivot 구성하는 것도 쉬운 일이 아니더군요. 두 개 이상의 머신에 구성하면 별 문제가 없는데, 하나의 머신에다가 구성하려고 시도하다가 계속 시행착오를 겪었더랬습니다. 어쨌든 시작해보죠.

 

 

-------------------------------------------------------------

 

 

최근 몇 년간 대형 벤더들의 BI 플랫폼은 이합집산을 거치며 극심한 변화를 겪고 있는 상황이다. 특정 벤더의 플랫폼에 대한 통합이 큰 이슈가 되어 왔으며, Microsoft 또한 자사의 플랫폼을 중심으로 BI 관련 제품 통합에 열을 올리고 있다.

출처 : “Tomorrow's Microsoft BI Platform”, Microsoft BI
(http://weblogs.sqlteam.com/derekc/archive/2009/02/02/60835.aspx)

다만 타 벤더와는 달리, Microsoft는 도저히 넘볼 수 없는 시장 지배력을 가진 Excel이라는 강력한 데스크탑 오피스 도구를 가지고 있다. 그간 ProClarity(제품명은 후에 PerformancePoint로 변경되었다)의 인수 등으로 타사와 비슷한 웹 기반 BI 플랫폼 솔루션을 갖추는데 집중하는 듯 했으나, 최근 PerformancePoint를 단종시키고(PerformancePoint의 모니터링 및 분석 기능은 SharePoint에 포함될 예정이라고 한다) Excel에서의 분석 기능을 강화하려는 모습을 보여주고 있다.

 

- 관리 가능한 셀프 서비스 BI

Microsoft는 SQL Server 2008 R2의 BI 영역에 추가된 기능을 “관리 가능한 셀프 서비스 BI(Managed Self-Service BI)”라는 말로 요약하고 있다. 셀프 서비스 BI는 사실 Microsoft에서 주창한 개념은 아니며, 확실히 정착된 용어라고 말하기도 어렵다. 어쨌든, 셀프 서비스 BI라는 용어는 말 그대로, 최종 사용자가 직접 데이터에 접근하여 자신이 필요로 하는 정보를 만들어내는 BI 활용 방식을 말한다.

(기업에서 일하는, IT와 직접적으로 연관이 없는 사용자 중에서 DB에 직접 접속하여 데이터를 가져오거나 하는 작업을 할 줄 아는 사람이 얼마나 될까 하는 문제는 일단 나중에 생각하도록 하자)

Microsoft에서 주창하는 셀프 서비스 BI를 기능 측면에서 좀 더 구체적으로 살펴보자면, 그 핵심에는 PowerPivot(예전에는 Gemini라는 코드명으로 불렸다)이 있다.

 

- PowerPivot for Excel

PowerPivot은 사실 SQL Server 2008 R2에 포함된 기능은 아니다. 좀 더 정확히 설명하자면 Analysis Services + PowerPivot for Excel + PowerPivot for SharePoint를 뜻하는 것이라고 할 수 있다. (이름을 보면 알 수 있겠지만, PowerPivot for Excel과 PowerPivot for SharePoint는 추가 기능 형태로 각각 Excel 2010과 SharePoint Server 2010에 설치되는 프로그램이다)

일단 PowerPivot for Excel에 대해 살펴보자면, PowerPivot 팀 블로그에서는 PowerPivot for Excel을 다음과 같이 설명하고 있다 (PowerPivot Component Architecture, http://blogs.msdn.com/powerpivot/archive/2010/03/22/powerpivot-component-architecture.aspx):

PowerPivot for Excel 2010은 어플리케이션 수준의 Excel 애드인 기능으로, 향상된 데이터 분석 기능과 리본 커스터마이징/스프레드시트 템플릿을 통한 사용자 경험을 제공하고, 기본 PivotTable 필드 리스트를 확장하여 Analysis Services 큐브 없이도 OLAP PivotTable과 PivotChart의 기능을 제공합니다.

PowerPivot for Excel 2010 is an application-level Excel add-in that implements advanced data analysis features, enhances the user experience through ribbon customizations and spreadsheet templates, and overrides the default PivotTable field list to implement its own task pane, thereby enabling the functionality of OLAP PivotTables and PivotCharts without requiring SQL Server Analysis Services cubes.

PowerPivot for Excel이 제공하는 분석 기능 자체는 기존의 PivotTable에서 조금 개선된 수준이지만, 핵심은 그게 아니다. 핵심은 “Excel에서 데이터 웨어하우스 혹은 OLAP 큐브와 같은 분석 가능한 데이터 소스를 생성할 수 있다”라는 부분이다.

데이터 웨어하우스나 다차원 데이터베이스가 구축되는 주된 이유 중의 하나는 '분석에 필요한 데이터를 통합하는 것'이다. 기업 내에서 발생하는 데이터는 흔히 '거미줄'이라고 표현할 만큼 여기저기에 널려 있으며, 기업의 의사 결정에 필요한 정보를 얻기 위해서는 이러한 데이터들이 어느 정도 정제되어 있어야만 한다. 하지만 요즘처럼 기업의 정보가 폭증하는 시대에는 기업 내에서 정보 분석에 활용하기 위해 구축되는 데이터 웨어하우스마저도 규모가 방대해지고 있으며, 제 아무리 파워유저라 한들 이러한 정보 분석용 데이터 웨어하우스의 데이터를 그때그때 수집하여 정보를 처리한다는 것은 어려운 일이다.

또 하나의 문제는, 엑셀이 대량의 데이터를 처리하기에 적합하지 않다는 것이다. 엑셀 2003에서는 고작 60,000행 가량의 데이터를 다룰 수 있었으며, 엑셀 2007에 와서는 처리할 수 있는 행 수가 1백만 행 가량으로 늘었다. 하지만 1백만 행이라고 해도 어차피 기업에서 발생하는 레코드 건 수에 비하면 한참 부족한 수준이다.

이와 같은 문제를 해결하기 위해, PowerPivot for Excel은 외부의 데이터 소스에 직접 접근하여 내부의 저장소에 데이터를 저장하는 동시에, 대량의 데이터를 저장할 수 있는 기능을 제공한다. PowerPivot for Excel을 사용하면 1억개 이상의 행을 외부 데이터에서 불러올 수 있으며, 외부에서 불러온 각 테이블 간에 관계를 설정하여 기존의 PivotTable 스타일로 정보 분석을 시행할 수 있다.

출처 : PowerPivot Component Architecture,
http://blogs.msdn.com/powerpivot/archive/2010/03/22/powerpivot-component-architecture.aspx

 

- PowerPivot 사용하기

먼저 알아두어야 할 것이 있는데, PowerPivot을 사용하기 위해서는 SQL Server 2008 R2, Sharepoint Server 2010, Excel 2010 등을 설치해야 한다. 개인 사용자에게는 설치하기가 까다로울 뿐더러, 단일 서버에 구성하려면 추가로 해줘야하는 작업도 있다. 가능하면 Microsoft에서 제공하는 데모(http://www.powerpivot.com/ 사이트의 hands on lab 메뉴)를 이용하도록 하자. 굳이 직접 설치를 하고 싶다면 다음 주소를 참고하자 : http://powerpivot-info.com/post/66-step-by-step-guide-on-installing-powerpivot-for-sharepoint

여기에서는 PowerPivot의 샘플 파일(http://powerpivotsampledata.codeplex.com/에서 다운로드받을 수 있다)을 사용해서 데이터를 가져오고 피벗 테이블을 만들어내는 단계를 보도록 하겠다.

위 사이트에서 다운로드 받은 samples.zip 파일을 열어보면 다음과 같은 파일들이 들어있다 :

ContosoStoreData.xlsx 파일을 더블클릭해 Excel 2010을 실행하고, PowerPivot 리본 메뉴에서 PowerPivot window 버튼을 클릭한다 :

그러면 PowerPivot 기능이 실행된다. (PowerPivot 은 엑셀 창 외부에서 별도로 실행되는 형태를 하고 있다) PowerPivot 창의 Get External Data 리본 메뉴에서 From Database 버튼을 클릭하고, From Access 를 이어서 클릭한다 :

그런 다음 파일 선택 창이 보이면, 아까 더블클릭한 ContosoStoreData.xlsx 파일이 있던 폴더에 있는 ContosoSales.accdb 파일을 선택한다 :

잠시 기다린 후 Next 를 한 번 더 클릭한다 (선택된 옵션은 데이터를 가져올 테이블을 직접 화면에서 보고 선택한다는 의미이다) :

일단 모든 테이블을 선택하고 finish 를 클릭하면 데이터를 가져오기 시작한다 :

데이터를 가져오는 작업이 끝나면 테이블이 나타난다. 오른쪽 위의 PivotTable 버튼을 클릭하고, Single PivotTable 버튼을 이어 클릭하자 :

피벗 테이블을 추가하고 나면 다음과 같은 화면이 나타난다 :

기존의 Pivot 기능과 마찬가지로, 원하는 항목을 체크해서 PivotTable에 추가할 수 있다 :

하지만 값이 이상하게 나오는 것을 볼 수 있는데, 이는 PowerPivot으로 임포트된 테이블간에 서로 관계가 설정되어 있지 않기 때문이다. (Gemini Task Pane 상단에 보면 ‘Relationship Needed’라는 메시지가 뜨는 것을 볼 수 있다) 여기서 말하는 관계는 물론 관계형 데이터베이스의 그 관계를 말하는 것이다. 데이터 웨어하우스처럼 차원(dimension)과 사실(fact) 테이블로 구성되어 있는 데이터를 PowerPivot으로 가져와 사용을 할 경우, 테이블간의 관계를 설정해주어야 한다.

오른쪽 위의 create 버튼을 클릭하면 자동으로 테이블간의 관계를 찾기 시작한다 :

Detail 버튼을 클릭하면 발견된 관계를 보여준다. 위에서 kenial이 임시로 만든 테이블에는 연도(CalendarYear)밖에 포함되어 있지 않기 때문에, 해당 필드에 대한 관계만이 생성된다. 예를 들어 방금처럼 관계를 생성했다면 :

연도별 비용과 매출액은 정상적으로 출력되지만, 여기에 다른 필드가 추가되면 아까와 마찬가지 형태로 잘못된 값이 출력된다 :

그러므로 PowerPivot 화면에서 미리 Relationship 메뉴를 통해 테이블간의 관계를 생성해두는 것이 좋을 것이다. 자동 관계 인식 기능은 편리하긴 하지만, 그리 성능이 좋은 것은 아니다. 다음은 관계 생성을 정상적으로 수행하고, 몇 가지 설정을 추가한 화면이다 :

기존의 Pivot 분석 화면과 별 차이는 없지만, 필터 항목과는 조금 다른 형태로 화면에 표시되어 있는 필드를 볼 수 있을 것이다. 이것들은 슬라이서(slicer)인데, 테이블에 나타난 수치가 어떤 필드 멤버들의 값을 합산한 것인지를 시각적으로 명확히 표현해주며, 사용법도 쉽다. (그냥 클릭하거나, 드래그하거나, shift/ctrl을 누른 상태로 계속 클릭하면 된다)

마지막으로, 이렇게 작성된 엑셀 파일의 데이터는 어디에 저장될까? 엑셀 파일에 그대로 저장된다. (데이터가 많다면 엑셀 파일의 크기가 급격히 증가하기는 하지만) PowerPivot 기능을 사용해 여러 데이터 원본에서 데이터를 가져온 다음, 저장된 엑셀 파일을 배포하면 다른 사용자들도 이 엑셀 파일을 가지고 정보 분석을 수행할 수 있게 되는 것이다. 데이터 원본(보통은 데이터 웨어하우스가 될 것이다)이 갱신되면, 해당 데이터 원본에 접근 가능한 네트워크 내에 있어야 한다는 조건이 붙긴 하지만 즉석에서 데이터를 갱신할 수 있다.