2010년 2월 18일 목요일

Excel Service를 이용한 Business Intelligence Solution구축 - Part 2

    지난번에 Excel 파일을 MOSS서버에 올려서 Excel 서비스를 사용해 Analysis Service 큐브의 데이터에 접근하는 방법을 살펴 보았습니다.

    이 포스트를 읽기 전에 혹시 이전 포스트를 보지 않으신 분들은 먼저 이전 Part1을 먼저 보셔야 합니다.

    이번에는 Excel Web Access 웹파트를 통해 Excel 피벗테이블과 차트를 내가 원하는 페이지에 올려 놓는 방법을 알려 드리도록 하겠습니다.

    Excel Web Access 웹파트는 MOSS에서 기본으로 제공되는 웹파트 중 하나입니다.

    이 웹파트는 서버에서 Excel 파일을 가져와 지정된 부분을 HTML UI로 렌더링 하는 역할을 합니다.

    앞선 포스트에 이어서 계속 진행 해 보도록 하겠습니다.

  1. 무엇을 Web으로 옮겨놓을 것인가?

    우선 Analysis Service와 Excel을 연결 했을 때 EWA 로 노출 할 수 있는 요소를 보도록 하겠습니다.

    위 그림에서 Analysis Service Filter라고 이름 붙어 있는 곳은 MOSS의 SQL Analysis Service Filter 웹파트로 대체 할 수 있습니다. 그리고 PivotTable 과 Pivot Table Chart는 각각 Excel Web Access 웹파트로 추출 할 수 있습니다.

  2. 웹파트 페이지 생성

    웹파트들이 올라갈 웹파트 페이지를 생성 합니다.

    보고서 사이트에서는 보고서 라이브러리에서 대시보드 페이지를 생성 할 수 있으므로 이 대시보드 페이지에 올려 놓도록 하겠습니다.

    1. 대시보드 페이지 생성

    보고서 라이브러리로 이동합니다.

    대시보드 페이지 생성 메뉴를 클릭합니다.

    파일 이름 페이지 제목을 지정하고 핵심 성과 지표는 사용하지 않습니다.

    확인을 누르고 생성된 대시보드 페이지를 확인 합니다.

  3. Excel 웹 액세스 웹파트 설정하기
    1. 피벗테이블 웹파트 설정

    Excel 웹 액세스의 도구창을 엽니다.

    통합 문서 표시 도구창에서 통합 문서 파일을 선택하기 위해 옆의 … 버튼을 누릅니다.

    이 웹파트가 참조할 Excel파일을 찾습니다.

    다음으로 명명된 항목을 써 넣습니다.

    명명된 항목은 피벗 테이블의 경우 피벗 테이블 이름 이고 피벗 차트의 경우 차트의 명명된 이름 항목을 지정 하면 됩니다.

    도구 모음 창을 확인을 누르고 빠져 나옵니다.

    1. 피벗 차트 웹파트

    위와 같은 방법으로 피벗 차트도 추가 합니다.

    1. SQL Analysis Service 필터 웹파트 설정하기

    EWA웹파트가 필터 적용을 받기 위해서는 원본 Excel파일을 게시할 때 필터가 적용할 파라미터를 설정 해야 합니다.

    1. 외부 파라미터 설정

    원본 Excel파일을 편집모드로 엽니다.

    필터가 있는 위치에 셀을 선택 한 후 셀의 이름을 지정 합니다.

    게시 메뉴의 Excel 서비스 메뉴를 선택 합니다.

    Excel 서비스 옵션 버튼을 클릭 하고 두번째 탭의 매개 변수를 선택 합니다.

    추가 버튼을 눌러 매개 변수를 추가 합니다.

    앞서 지정했던 필터 셀의 이름이 나오는 것을 확인 할 수 있습니다.

    확인을 누르고 기존 파일위에 덮어 씁니다.

    이제 다시 앞서 생성했던 대시 보드 페이지에서 페이지 편집을 통해 필터를 추가 합니다.

    필터 추가를 눌러 추가 할 수 있는 필터 목록을 확인 합니다.

    SQL Server 2005 Analysis Services 필터를 선택 한 후 추가 버튼을 누릅니다.

    도구 창을 열고 필터 이름과 필터가 참조하는 연결 파일 경로 그리고 필터의 차원과 레벨을 설정 합니다.

    ODC파일 설정

    차원과 계층구조 선택

    위와 같이 차원과 계층 구조를 선택 한 후 확인을 눌러 도구 모음창을 나옵니다.

    1. 웹파트 연결하기

    이제 만들어진 필터와 EWA 웹파트 들을 연결 할 차례입니다.

    필터의 편집 메뉴에서 연결 -> 필터 값 내보내기 -> EWA 웹파트를 차례로 선택합니다.

    연결선택 대화 상자에서 구성 버튼을 클릭합니다.

    필터링된 매개 변수항목에서 앞서 지정한 파라미터 이름을 확인하고 마침을 클릭합니다.

    아래와 같이 연결을 확인하고 피벗 차트용 EWA웹파트도 동일한 방법으로 연결합니다.

    페이지 편집이 완료 되었으면 게시 버튼을 눌러 다른 사용자들이 이 페이지를 볼 수 있게 공개 합니다.

    완료 후 필터 적용 테스트 화면입니다.

  4. 결론

    위와 같이 Excel의 Analysis Service 클라이언트 기능을 MOSS의 Excel서비스와 함께 연동하면 아주 쉽게 웹으로 노출 시킬 수 있습니다. 앞선 예제에서는 대시보드 페이지를 통해 웹파트를 노출 시켰지만 Master페이지나 웹파트 페이지의 개념을 알고 계신다면 SharePoint Designer 2007을 통해서 아주 유연하게 페이지 디자인을 바꿔서 사용자에게 서비스 할 수도 있을 것입니다.

Excel Service를 이용한 Business Intelligence Solution구축 - Part 1

    오늘은 MOSS기반의 BI솔루션 구축에 있어서 핵심적인 역할을 하는 Excel Service를 사용하는 방법과 실제 데모를 만들어 보도록 하겠습니다.

    MOSS가 내세우는 강력한 기능 중 하나가 BI 플랫폼으로서의 기능입니다. 뒷단에 SQL Server 2005 Analysis Service(이하 SSAS)를 Data Ware House로 두고 큐브 안의 데이터를 손쉽게 Dashboard로 만들어 사용자 UI로 노출 시켜 솔루션 구축에 있어서 빠르고 간편하면서도 강력한 기능을 제공 하고 있습니다.

    clip_image001

    Excel Service를 이용하면 Excel이 가지고 있는 OLAP Client로서의 기능을 별도의 코드 구현 없이 바로 서버에서 구현 가능 하다는 장점이 있습니다.

  1. Prerequisite
    1. SQL Server 2005 Analysis Service
    2. Adventureworks Data Warehouse Sample
    3. Adventureworks Analysis Service Business Intelligence Project Sample

    b, c 항목은 http://technet.microsoft.com/ko-kr/library/ms143804.aspx 을 참조해 설치 하면 됩니다.

    clip_image002

    clip_image003

    Analysis Service가 접근하고자 하는 Windows계정으로 접근 가능한가 확인이 필요 합니다.

  2. Excel Service 구성하기
    1. Excel Service 시작 확인

    중앙관리 -> 작업 -> 서버 제공 서비스로 들어 갑니다.

    clip_image004

    아래와 같이 Excel 계산 서비스가 시작 되어 있는지 확인 합니다.

    clip_image005

    만약 시작 되어 있지 않다면 시작을 눌러 서비스를 시작 합니다.

    1. 신뢰할 수 있는 파일 위치 지정

    Excel Service를 사용하기 위해서는 서버의 Excel Service가 참조하는 Excel파일의 위치를 신뢰하는 위치로 지정 해야 합니다.

    중앙관리-> 공유서비스 관리 ->[공유서비스이름]->신뢰할 수 있는 파일 위치

    clip_image006

    clip_image007

    위치 : 주소 항목에 Excel 파일이 저장될 위치를 지정 합니다.

    하위 항목 신뢰 체크박스를 활성화 합니다.

    clip_image008

    외부 데이터 : 외부 데이터 허용 항목에서 신뢰할 수 있는 데이터 연결 라이브러리 및 포함 라이브러리 옵션을 선택합니다.

    Excel 파일에서 Analysis Service와 동적인 연결을 할 때 Office Connection File을 사용하게 되는데 이 옵션을 선택 해야 외부 연결이 가능 합니다.

    확인을 누르고 신뢰할 수 있는 파일 위치 목록으로 돌아 오면 아래와 같이 등록 되어있는 것을 확인 할 수 있습니다.

    clip_image009

    이제 Excel Service가 시작 되어 있고 신뢰 할 수 있는 파일 위치가 제대로 지정되어 있으면 보고서 사이트의 예제 Excel Service가 동작하는 것을 확인 할 수 있습니다.

    clip_image010

    1. Excel Service 무인 서비스 계성 설정하기

    Excel Service가 외부의 데이터에 접근 할 때 사용하는 무인 서비스 계정을 설정하고 인증 모드를 수정 해야 합니다.

    clip_image011

    clip_image012

    외부 데이터 연결 의 무인 서비스 계정에 Excel Service가 사용할 계정을 입력 합니다. 주로 SSO와 연동해 사용하므로 SSO서비스 계정을 함께 쓰는 것이 좋습니다.

    1. Excel Service 인증 모드 수정하기

    Excel Service는 Farm 배포와 Stand Alone 배포시에 각기 다른 인증 모드를 가지고 있습니다. (자세한 내용은 여기를 참조 하세요.) Delegation 모드로 변경하기 위해 Command Prompt를 열고 "C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>" 으로 이동합니다.

    다음 명령어를 실행 합니다.

    • stsadm -o set-ecssecurity -accessmodel delegation -ssp sharedservices1
    • Iisreset
    • Stsadm -o execadmsvcjobs

    clip_image013

  3. Office Data Connection File구성하기
    1. 신뢰할 수 있는 데이터 연결 라이브러리 지정

    Office Data Connection 파일을 올릴 라이브러리를 신뢰할 수 있는 데이터 연결 라이브러리로 지정 해야 합니다.

    중앙 관리 -> 공유 서비스관리->[공유서비스이름]-> 신뢰할 수 있는 데이터 연결 라이브러리

    clip_image014

    clip_image015

    주소 항목에 데이터 연결 라이브리 주소(http://moss.fabrikam.com/Reports/Data Connections/)를 입력하고 확인을 클릭합니다.

    clip_image016

    1. Excel 2007을 통해 ODC(Office Data Connection) 파일 생성

    Excel 을 열고 데이터->기타원본->Analysis Services를 선택합니다.

    clip_image017

    clip_image018

    현재 Analysis Service는 Windows 통합 인증만을 지원 하고 있습니다. Windows 인증 사용을 선택하고 다음으로 넘어 갑니다.

    clip_image019

    데이터베이스를 선택하고 큐브를 선택한 다음

    clip_image020

    파일 이름과 이름을 지정하고 Excel 서비스 인증 설정 버튼을 누릅니다.

    Excel 서비스가 Analysis Service에 접근할 때 사용하는 계정을 가져오기 위해 아래와 같이 MOSS에서 지정한 SSO Application이름을 지정합니다.

    clip_image021

    MOSS의 SSO설정 방법은 지난 포스트에 설명되어 있습니다.

    주)만약 Analysis Service에 접근할 대 현재 로그온 한 Windows 사용자 계정의 Security Context를 그대로 가지고 접근을 하려면 위에서 Windows 인증을 선택하고 Kerberos 구성을 추가로 해야 합니다.

    마침 버튼을 누르고 데이터 가져오기 창에서 연결만 만들기를 선택합니다.

    clip_image022

    속성 버튼을 클릭

    clip_image023

    파일을 열 때 데이터 새로 고침 체크

    정의 탭에서 연결파일 내보내기를 선택

    clip_image024

    내보낼 위치에서 우리는 신뢰할 수 있는 데이터 연결 라이브러리를 이미 지정 해 놓았기 때문에 해당 라이브러리 주소를 지정해 내보냅니다.

    clip_image025

    clip_image026

    연결 형식 : Database

    UDC 목적 : ReadOnly

    선택 후 확인을 누릅니다.

    clip_image027

    연결파일 경로가 변경 된 것을 확인 할 수 있습니다.

    아래와 같이 데이터 연결 라이브러리에서도 확인이 가능합니다.

    clip_image028

    ODC파일을 업로드 한 이후에는 데이터 연결 라이브러리에서 해당 파일을 승인 해 줘야 다른 사용자들이 연결 파일을 사용할 수 있습니다.

    clip_image029

    clip_image030

    승인됨을 선택 후 확인 버튼을 눌러 ODC 파일을 승인합니다.

    다시 데이터 가져오기 화면에서 취소 버튼을 눌러 창을 닫습니다.

    clip_image031

  4. Excel에서 Analysis Service 사용하기
    1. ODC파일을 이용한 외부 연결

    Excel 2007 에서 데이터 -> 기존연결을 선택합니다.

    clip_image032

    clip_image033

    기존 연결화면에서 더 찾아보기를 누릅니다.

    clip_image034

    파일 이름부분에서 데이터 연결 라이브러리의 주소를 입력하고 엔터를 칩니다.

    clip_image035

    앞서 올렸던 AdventureworksConnections.odc 파일을 선택 하고 열기를 클릭합니다.

    clip_image036

    피벗 테이블 보고서나 피벗 차트 및 피벗 테이블 보고서를 클릭합니다.

    clip_image037

    외와 같이 Excel을 이용해 Analysis Service의 데이터를 dashboard 형태로 표현합니다.

    완성된 Excel파일을 로컬에 저장하고 MOSS서버의 Excel Service 신뢰하는 파일 위치 라이브러리에 올립니다.

    clip_image038

    여기서는 http://moss.fabrikam.com/Reports/ReportsLibrary/ 에다 올리도록 하겠습니다.

    clip_image039

    업로드 이후에는 자동으로 브라우저가 실행되며 웹으로 Excel 파일이 렌더링 되는 것을 확인 할 수 있습니다.

    clip_image040

  5. 결론

    Excel 자체가 가지고 있는 OLAP Client로서의 기능을 앞서 설명한바 와 같이 Web UI로 쉽게 가져올 수 있음을 알 수 있었습니다.

    다음에는 위에서 만든 Excel파일의 특정 피벗 테이블을 웹파트 페이지에 떼어서 넣는 방법과 함께 MOSS가 기본적으로 가지고 있는 Filter에 대해서 살펴보도록 하겠습니다.