SQL Server 2017에 추가된 기능 중에 개인적으로 가장 흥미있는 기능이 Machine Learning Services - Python입니다. 개발자들의 사랑을 한 몸에 받고 있는 Python의 기능을 기본 제공에 더해서 1)Data를 Client 로 이동하지 않고 처리 2)빠르고 편한 배포 및 활용 3)기업용 리소스 및 보안 관리 4)풍부한 확장 패키지(Microsoft에서 제작한 RevoScalePy 패키지) 등의 이점이 추가되었습니다.  


In-Database R을 구현하면서 사용했던 기술을 많은 부분 그대로 사용하였으므로, 이미 In-DB R을 구성해본 분이라면 금방 익숙해질 것입니다. 이 글에서는, 독립 설치형 Python은 다루지 않고, In-DB Python 설치 및 SQL 구문내에서 Python script가 잘 동작하는지 확인하는 방법에 대해 정리하였습니다. 


설치 목록

1.       OS : Windows Server 2016

2.       SQL Server 2017 평가판 (120 무료 LINK)

3.       Anaconda (Open Source) : SQL Server 2017 설치 중에 인터넷에서 다운로드

4.       SQL Server Management Studio (무료)

 

설치 순서

1.       In-Database ML Services중에서 Python 설치


 

2.       참고) SQL Server와는 독립적으로 Python R 설치할 있음



3.       Python 배포판인 Anaconda package 인터넷을 통해 설치된 다는 메시지 확인


 

4.       설치될 파일 목록 확인(1) : Machine Learning Services (In-Database), Python


 

5.       설치될 파일 목록 확인 (2) : SQL Server LaunchPad. 아래 10번에 추가 설명 제공.


 

설치 완료 ,

6.       Computer Management(Compmgmt.msc) 수행 , 외부 스크립트를 호출하기위한 worker account (기본 20) 생성되었는지 확인.


계정들은 모두 SQLRUserGroup 구성원으로 포함되어 있음. (R용으로 만들었던 그룹을 그대로 Python 에서도 사용하고 있음)

 

7.       설치 폴더 확인


C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER 아래에 PYTHON_SERVICES 폴더가 생성됨


 

8.       SQL Server Management Studio(SSMS)  다운로드 설치

9.       SSMS에서 Python script 정상 동작하는지 확인하기

SSMS 로그인하여, Python script 수행되는지 Hello World! 테스트 진행

참고) 여기서는 system administrators 그룹의 사용자로 진행하였음. 사용자는 별도의 권한 부여 필요함. https://docs.microsoft.com/en-us/sql/advanced-analytics/python/setup-python-machine-learning-services

 

--Allow to execute external scripts such as R and Python

EXEC sp_configure 'external scripts enabled', 1

reconfigure with override

GO

 

--Restart SQL Server to apply changed value

 

--Check whether the Python script works well or not.

EXEC sp_execute_external_script

@language = N'Python',

@script = N'

import sys

import os

print("Hello World!")

'

 

 

정상 수행된 결과 확인.


참고) 오류가 발생하였다면 troubleshooting 과정이 필요 . ) import 구문 앞에 공백이 있으면 IndentationError 발생함.

 

참고) Python 완료 상태이므로 모든 SQL Server client tool에서 구문을 수행해 있음. : VS Code, SSDT, SSMS, SQLCMD .

 

10.   쿼리 수행시 호출되는 process 확인

Process explorer 열어 두고서 쿼리를 수행하면, 호출되는 process 상관 관계를 있음.

Launchpad.exe >  python.exe > conhost.exe, BxlServer.exe 호출. , 외부 스크리트는 sqlservr.exe 아닌 Launchpad.exe 실행의 주체임.


 

11.   실습

테이블에 요일(~) 표시된 값을 index(1~7) 바꾸어 출력해 주는 스크립트 수행

참고) Python Data Analysis Library(pandas) Series  이용하여 수행

 

 

--테스트용 DB 생성

CREATE DATABASE ML_Samples

GO

 

USE ML_Samples

GO

 

--테스트용 테이블 생성

CREATE TABLE PythonTest (

    [DayOfWeek] nvarchar(10) NOT NULL,

    [Amount] float NOT NULL

    )

GO

 

--데이터 입력 : 요일별 매출 현황

 

INSERT INTO PythonTest VALUES

(N'', 10.0),

(N'', 11.1),

(N'', 12.2),

(N'', 13.3),

(N'', 14.4),

(N'', 15.5),

(N'', 16.6),

(N'', 17.7),

(N'', 18.8),

(N'', 19.9)

GO

 

--변수 선언 초기화

DECLARE @ParamINT INT = 1234567

DECLARE @ParamCharN VARCHAR(6) = 'INPUT '

 

print '------------------------------------------------------------------------'

print 'Output parameters (before):'

print FORMATMESSAGE('ParamINT=%d', @ParamINT)

print FORMATMESSAGE('ParamCharN=%s', @ParamCharN)

 

print 'Dataset (before):'

SELECT * FROM PythonTest

 

print '------------------------------------------------------------------------'

print 'Dataset (after):'

--SQL Server에서 python으로 읽기 한번당 넘기는 행수 선언

DECLARE @RowsPerRead INT = 5

--

execute sp_execute_external_script

@language = N'Python',

@script = N'

import sys

import os

print("*******************************")

print(sys.version)

print("Hello World")

print(os.getcwd())

print("*******************************")

if ParamINT == 1234567:

       ParamINT = 1

else:

       ParamINT += 1

 

ParamCharN="OUTPUT"

OutputDataSet = InputDataSet

 

global daysMap

 

daysMap = {

       "" : 1,

       "" : 2,

       "" : 3,

       "" : 4,

       "" : 5,

       "" : 6,

       "" : 7

       }

 

OutputDataSet["DayOfWeek"] = pandas.Series([daysMap[i] for i in OutputDataSet["DayOfWeek"]], index = OutputDataSet.index, dtype = "int32")

',

@input_data_1 = N'SELECT * FROM PythonTest',

@params = N'@r_rowsPerRead INT, @ParamINT INT OUTPUT, @ParamCharN CHAR(6) OUTPUT',

@r_rowsPerRead = @RowsPerRead,

@paramINT = @ParamINT OUTPUT,

@paramCharN = @ParamCharN OUTPUT

with result sets (("DayOfWeek" int null, "Amount" float null))

--

print 'Output parameters (after):'

print FORMATMESSAGE('ParamINT=%d', @ParamINT)

print FORMATMESSAGE('ParamCharN=%s', @ParamCharN)

GO

 

 

반환된 개의 결과

수행

수행

DayOfWeek       Amount

          10

          11.1

          12.2

          13.3

          14.4

          15.5

          16.6

          17.7

          18.8

          19.9

DayOfWeek       Amount

7            10

1            11.1

2            12.2

3            13.3

4            14.4

5            15.5

6            16.6

5            17.7

1            18.8

7            19.9

 

출력된 Output

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

Output parameters (before):

ParamINT=1234567

ParamCharN=INPUT

Dataset (before):

 

(10 rows affected)

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

Dataset (after):

STDOUT message(s) from external script:

*******************************

3.5.2 |Continuum Analytics, Inc.| (default, Jul  5 2016, 11:41:13) [MSC v.1900 64 bit (AMD64)]

Hello World

C:\PROGRA~1\MICROS~2\MSSQL1~1.MSS\MSSQL\EXTENS~1\MSSQLSERVER01\451A7F40-0448-42D2-AF50-AAAB1B9564EE

*******************************

*******************************

3.5.2 |Continuum Analytics, Inc.| (default, Jul  5 2016, 11:41:13) [MSC v.1900 64 bit (AMD64)]

Hello World

C:\PROGRA~1\MICROS~2\MSSQL1~1.MSS\MSSQL\EXTENS~1\MSSQLSERVER01\451A7F40-0448-42D2-AF50-AAAB1B9564EE

*******************************

 

 

(10 rows affected)

Output parameters (after):

ParamINT=2

ParamCharN=OUTPUT

 

 

 

 

참조

Python 컴퓨터 학습 Services (In-database) 설치

https://docs.microsoft.com/ko-kr/sql/advanced-analytics/python/setup-python-machine-learning-services

 

T-SQL 사용 Python 실행

https://docs.microsoft.com/ko-kr/sql/advanced-analytics/tutorials/run-python-using-t-sql


위에 입력했던 Screenshot이 포함된 Word 문서 : 

SQL Server 2017 ML Services Python 구성하기.docx

 

  1. Commented by Giwhan Han at 2017.10.09 19:52 신고

    흠, Flash Player 설치하기 싫어서, 파일 보관함을 통해 사진을 올렸더니 크기 조절이 안되네요. 원본 Word를 첨부파일로 추가하였습니다.

[질문]

Power BI Desktop에서 Microsoft Exchange 연결하여 가져올 있는 데이터의 종류

 

[답변]

Calendar, Mail, Meeting Requests, People, Tasks 5개의 테이블을 가져올 있습니다.

 

[추가 정보]

Power BI Desktop에서 Exchange 연결하여 데이터 가져오는 순서

--1.Power BI Desktop > 데이터 가져오기(Get Data) > Microsoft Exchnage













 

--2.Exchange 메일 주소 입력


 

--3.[인증 옵션1]Exchange account


 

--[인증 옵션1]Autodiscover 서비스로 접근 허용

참고) Exchange단에서 AutoDiscover Service 구성되어 있어야

 

--[인증 옵션 2] Microsoft account에서 암호 입력 (Office 365로 로그인)

 

 

--[인증 옵션 2] Sign in 후 Connect

 

 

--4.5개의 테이블 확인 후 적재(Load)

참고) 적재(Load) 수정(Edit) 원하는 테이블을 선택하면 우측에 내용이 표시됨 

 

--5.테이블 스키마 확인

 

--6.테이을 내용 확인 (스크린샷에는 내용이 없음)

참고) 데이터가 있는 경우, 테이블 형식으로 표시됨

 

--7.추가된 Fields 기반의 보고서 작성

 

[기타]

Microsoft Exchange Online에서 데이터 가져오기에서 “Connect”를 클릭한 후에는 Microsoft Exchange와 완전히 동일한 과정이 진행됨.

 

[참조]

Analyzing your Microsoft Exchange Account Data with Power Query

https://powerbi.microsoft.com/en-us/blog/analyzing-your-microsoft-exchange-account-data-with-power-query/

           Excel add-in인 파워쿼리에서 Exchange에 접속하는 방법.


감사합니다

한기환




질문

SQL Server 무료 버전 다운로드 방법


답변

SQL Server2016이 나오면서 Developer Edition은 무료 다운로드가 가능해졌습니다. 따라서, 무료로 사용 가능한 버전은 총 세가지 입니다.


1. Developer Edition

Enterpries Edition과 동일한 기능을 제공하며, Enterprise Edition으로 업그레이드도 가능합니다. 단, 개발용으로만 사용 가능하며, 운영에 쓰려면 Enterprise로 업그레이드가 필요합니다.


2. Evaluation Edition

Enterprise Edition 과 동일한 기능을 180일간 무료로 사용 가능하며, 기간이 만료되기 전에 Enterprise Edition으로 업그레이드 가능합니다.

다운로드 경로 https://www.microsoft.com/ko-kr/evalcenter/evaluate-sql-server-2016

(Microsoft Account 필요함)


2. Express Edition

소규모 제품에 무료로 활용 가능한 제품으로 성능 및 기능 제약이 있습니다.



Developer Edition 다운로드 절차는 다음과 같습니다.


1. Visual Studio 사이트에 Microsoft Account Sign in

https://www.visualstudio.com/ko/

 

만약 Microsoft Account 없다면,

  1. 현재 사용중인 조직 또는 개인 email Microsoft Account 등록할 있습니다.

 

  1. 또는 메뉴 중간에 있는 " 전자 메일 주소 받기" 선택하여 Microsoft Account(outlook.kr, outlook.com, hotmail.com ) 바로 만든 사용할 있습니다.

 

"전자 메일을 대신 사용" 선택하면 다시 이미 보유한 조직 개인 이메일을 넣는 화면으로 전환됩니다.

 

2. 다운로드 메뉴 - 제품 유형 : Servers - SQL Server 2016 Developer with Service Pack 1 선택

 

3. x64, Korean, DVD 선택 - 녹색 다운로드 버튼 선택

 

4. 2.77 GB iso 파일 다운로드


 

이제 핵심 업무영역에서 SQL Server 2016의 강력한 기능을 활용할 시간입니다. :)

 

감사합니다

한기환

 



Q. Powre BI를 다른 웹/앱에 통합하는 방법

A. Power BI Desktop에서 만든 보고서 파일(.pbix)을 Power BI Service 또는 Power BI Embedded내의 workspace에 게시할 수 있습니다. 이렇게 게시된 보고서는, 세 가지 방식으로, 다른 Web/App에 iFrame형태로 포함시킬 수 있습니다.

이미 기업용 앱/웹을 쓰고 있는 고객에게는 이 기능이 아주 중요합니다.

1. Pwer BI Service의 보고서를 Publish To Web으로 웹에 통합
<iframe..> 주소 자동 생성해 주며, 이것을 다른 웹 페이지에 HTML로 추가

*아무리 많은 사용자가 접속하더라도 추가금이 나오지 않음 (계정당 월 11,000원)
*이렇게 게시된 보고서는 누구나(anonymous) Power BI 계정이 없어도 볼 수 있음
*Blog나 대외용 웹에 보고서를 게시할 때 사용할 수 있음
*주소를 알면 URL으로도 직접 들어 올 수 있고, 누구나 다른 곳에 추가할 수 도 있음


참조) 데이터를 이용하여 스토리를 돋보이게 하기 [LINK]

2. Power BI Service의 보고서나 tile을 iFrame 으로 통합
*Azure AD에 client등록하는 과정이 필요하며, token을 받아서 Power BI 접근시 사용함
*Publish To Web과 다르게 등록된 client에만 보고서를 iframe으로 추가할 수 있음
*이 client 자체가 public하게 공개되어 있다면, 별도 인증없이 누구나 보고서를 볼 수 있음
즉, access control을 client에서 하게 됨
*API로 Dataset에 data를 밀어넣어, 대시보드에 실시간 반영되도록 할 수 있음



참조) 보고서를 앱에 통합 [LINK]


3. Power BI embedded용 workspace에 게시된 보고서를 iFrame으로 통합

*2번과 유사하지만, Power BI service처럼, GUI로 로그인해서 들어갈 수 없음

*예제 링크[LINK]에 포함된 Power BI 보고서는 등록된 Web인 azurewebsiteexperience.azurewebsites.net을 통해서 접속했을 때에만 보여지게 됨


*과금은 사용자가 보고서를 보기 위해 연결했던 세션의 수로 측정됨.

*즉, 미터링 가능하므로, 다른 솔루션에 넣어 end user에게 과금을 하려는 목적으로 적합함



참조) Power BI Embedded 시작 샘플 [LINK]



감사합니다

한기환

bluetooth mouse freezing 현상

그냥재미로 2016. 8. 18. 17:13 posted by Giwhan Han

문제

Windows 10에서 블루투스 마우스가 끊기거나 움직임이 멈추는 현상이 자주 발생함

장비

Microsoft Sculpt Comfort Mouse

 

증상

bluetooth mouse 사용 중 갑자기 마우스가 동작하지 않음.

잠시 후 다시 연결되거나, 다시 연결이 안 되기도 함

재부팀을 해도 계속 안될 때가 있고

마우스를 드라이버를 제거했다가 다시 추가하면 다시 동작하기도 함

마우스는 연결이 끊겼지만, 터치패드나 트랙포인트는 정상 동작함

 

해결

IPoint.exe의 호환 모드를 Windows 7으로 설정

사용 중인 마우스에 맞는 마우스 드라이버 (Microsoft Mouse and Keyboard Center)를 설치 -> 작업 관리자에 IPoint.exe 확인 -> 오른 마우스 클릭 후 [속성] -> 호환성 탭에서 "호환 모드"를 Windows 7으로 선택

image

 

참조

How to solve pointer lag & mouse freeze on windows 10
https://www.youtube.com/watch?v=fjAH7vRYu8k

HTH,
Gi HAN

Microsoft Analysis Services OLEDB Provider

공장이야기 2016. 7. 17. 01:20 posted by Giwhan Han

[질문]

SQL Server Analysis Services(SSAS) Tabular에 접속하기 위해 사용되는 Provider를 별도 설치할 수 있나요? 어떻게 설치해야 하며 어떻게 테스트 할 수 있나요?

[답변]

MSOLAP은 SQL Server feature pack 내에 포함되어 있습니다. feature pack은 각 버전 및 SP별로 다운로드 가능합니다. (무료)

연결 테스트는 client (엑셀, SSRS, SSMS, SSDT 등)에서 할 수 있고, 이런 도구가 없다면 UDL파일을 이용할 수 있습니다. (UDL 사용 방법은 아래에 설명함)

AS용 OLEDB Provider 설치 방법

1) Microsoft® SQL Server® 2016 Feature Pack 다운로드 페이지
https://www.microsoft.com/en-us/download/details.aspx?id=52676

2) client도구의 architecutre(32 bit/64 bit)에 맞게 다음 파일을 다운로드하여 설치

ENU\x64\SQL_AS_OLEDB.msi
ENU\x86\SQL_AS_OLEDB.msi

PC에 설치된 AS용 OLEDB Provider 파일 확인 방법

* feature pack 설치시, 다음 경로에서 msolap130.dll 파일 확인
C:\Program Files\Microsoft Analysis Services\AS OLEDB\130\
C:\Program Files (x86)\Microsoft Analysis Services\AS OLEDB\130\

image

* Power BI Desktop을 설치한 경우
자체적으로 다음 경로에 msolap130.dll 파일 설치
C:\Program Files\Microsoft Power BI Desktop\bin

image

* Office에서 msolap 파일의 위치
C:\Program Files\Microsoft Office\root\VFS\ProgramFilesX64\Microsoft Analysis Services\AS OLEDB\110

image

SSAS에 연결 테스트하는 방법

1) 탐색기에서 확장자 보이도록 설정 후, notepad를 열고 확장자를 UDL(Universal Data Link)로 바탕화면에 저장. (예: asdf.udl)

2) 파일을 더블 클릭하여 "공급자" 탭에서 Microsoft OLE DB Provider for Analysis Services 13.0 선택 (설치된 feature pack 버전에 따라 다른 숫자가 표시됨)

image

3-1)Windows NT의 통합 보안 사용(SSPI)하여 연결 테스트

[oledb]
; Everything after this line is an OLE DB initstring
Provider=MSOLAP;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TabularProject2;Data Source=myserver\tabular12;Update Isolation Level=2

image

3-2)특정 사용자 이름 및 암호 사용

[oledb]
; Everything after this line is an OLE DB initstring
Provider=MSOLAP;Password="";Persist Security Info=True;User ID=administrator;Initial Catalog=TabularProject2;Data Source=myserver\tabular12;Update Isolation Level=2

image

참고) Excel 2016에서 SSAS 2016 Tabular에 연결시 사용된 연결 문자열 :

Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=TabularProject2;Data Source=myserver\tabular12;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Update Isolation Level=2

- MSOLAP.5는 SSAS 2012용 provider이지만, SSAS 2016에도 문제없이 접속하여 MDX query를 수행하고, 결과 집합을 받아옴

- Excel에서 외부 데이터 연결 정보는 .odc (office data connectivity) 파일에 저장됨

FAQ

* SQL Server Analysis Services (SSAS) Tabular 모델의 버전 정보

(SQL Server 버전 – Provider Name – dll file - 지원되는 호환성 수준)

SSAS 2012 - MSOLAP.5 - Msolap110.dll - 1100
SSAS 2014 - MSOLAP.6 - Msolap120.dll - 1103
SSAS 2016 RTM - MSOLAP.7 - Msolap130.dll - 1200

* 꼭 최신 버전의 provider가 필요한가?

client(엑셀, SSRS, Tablear 등)에서 query를 생성 후 AS 모델에서 수행하는데는 최신 버전이 아니어도 됩니다. 하지만, modeling(SSDT)과 관리(SSMS) 도구에서는 최신 버전이 필요합니다.

* AMO, ADOMD.NET은 어디에 사용되나?

Analysis Services Management Objects (AMO)는 사용자 프로그램이나 스크립트에서 AS의 기능을 프로그래밍하기 위해서 사용되는 API입니다. Microsoft.AnalysisServices.dll 어쎔블리의 일부인 Microsoft.AnalysisServices.Core 네임스페이스를 사용할 수 있습니다.

SSMS도 하나의 client program이며 AMO와 ADOMD.NET이 함께 설치 됩니다. 이 객체들은 SSMS에서 SSAS Tabular나 다차원 분석 서버에 연결하고 관리작업을 수행하는데 사용됩니다.

* SSAS에 ODBC로도 접속 가능한가요?

No. SSAS는 OLEDB provider만 제공합니다.

* SQL Server 엔진 처럼 별도의 인증체계를 사용하고 있나요?

No. 윈도우즈에 등록된 계정으로만 접속 가능합니다. 따라서, domain으로 묶이지 않은 머신에서 접속하려고 하면, 임시방편으로, 도일한 계정과 암호를 서버에 생성 후 SSAS내에 등록해 주어야 합니다.

* SSAS Tabular 모드는 별도의 provider를 사용하나요?

No. SSAS 다차원과 Tabular 모드에 접근시 동일한 provider를 사용합니다. Excel에서 Tabular에 접속해서 보고서를 작성시에도 MDX가 사용됩니다.

 

[참조]

How to obtain the latest versions of MSOLAP
https://support.microsoft.com/en-us/kb/2735567

Install Analysis Services data providers (AMO, ADOMD.NET, MSOLAP)
https://msdn.microsoft.com/en-us/library/mt592624.aspx

 

감사합니다
한기환

문제

증상1. 엑셀에서 "다른 이름으로 저장"시 SharePoint 서버를 지정할 수 없습니다.

*Excel 파일을 다른 이름으로 저장 - 위치에 local sharepoint명 입력 (예: http://myserver)

clip_image001

*오류 발생

clip_image002

증상2. SharePoint의 어떤 library에서건 Open in Explorer (탐색기에서 열기)를 수행하면, 신뢰할 수 있는 사이트에 추가하라는 메시지 발생하며 열리지 않음.

환경 : SharePoint 2013, Windows Server 2012, Excel 2013

원인

서버에서 "데크스톱 경험" 기능이 구성되지 않은 경우에 발생합니다.

해결

"데스트폽 경험" 기능 추가

1. "역할 및 기능 추가" 선택

clip_image003

2. 기능 - 사용자 인터페이스 및 인프라 - 데스크톱 경험 체크clip_image004

"잉크 및 필기 서비스"도 함께 설치하며, 서버 재시작 필요함.

3. 서버 재부팅 후, Excel을 "다른 이름으로 저장"-SharePoint-찾아보기- 선택clip_image005

4. 데스크톱 경험이 추가되면, WebClient 서비스가 설치됨.

서비스(services.msc)목록에 WebClient가 추가되고, 실행 중 상태인지 확인.clip_image006

5. SharePoint에서 탐색기에서 열기(Open in Explorer) 선택하여 잘 열리는지 확인.clip_image007

활용 시나리오

엑셀의 PowerPivot, PowerQuery를 통해 SharePoint에 저장된 파일을 소스로 참조할 수 있으므로 이를 활용하여 반복되는 수작업을 제거할 수 있습니다. 가령 주기적으로 동일한 포맷의 엑셀 데이터를 외부에서 메일로 전달받아 취합해야하는 곳이라면, SharePoint의 특정 폴더에 파일업로드 권한을 주고 주기적으로 올리게 한 후 PC의 엑셀에서 PowerQuery로 직접 불러와 취합할 수 있습니다. 이 때, 사용한 규칙은 그대로 저장되어 데이터 갱신(refresh)만 하면 그대로 적용됩니다.

파워쿼리로 SharePoint상의 엑셀 불러오기

1. 엑셀 - 파워 쿼리 - 파일에서 - 엑셀에서 - 위치에 http://myserver 입력 후 엑셀 파일 선택
clip_image008

2. 웹 켄텐츠 액세스를 위한 권한 설정 및 URL 선택
clip_image009

3. 도메인 내면, Windows 계정 선택 - xlsx 파일 선택 - 저장
clip_image010

4.  엑셀의 탐색 창에 엑셀 내의 "지역" 워크시트가 나타난 모습clip_image011

이제, 소스 데이터를 그냥 "로드"하지 않고 "편집"으로 들어가 원하는 대로 데이터를 가공합니다. 가공한 데이터는 PowerPivot에 적재하여 다른 테이블과 연동하여 사용할 수 있습니다. 같은 식으로 여러 개의 파일을 연결하여 PowerPivot으로 불러들여 처리하는 시나리오가 가능합니다. -> 이것을 SharePoint 에 올리면 자동 데이터 갱신 일정을 걸 수 있으므로, 수작업 해오던 많은 단계를 자동화 할 수 있습니다.

참고, PowerQuery로 연결한 소스는 SharePoint 2016부터 자동 스케쥴을 걸 수 있으며, PowerPivot에서 직접 가져온 소스는 SharePoint 2013에서도 자동 스케쥴 설정이 가능합니다.

HTH,
한기환

SQL Server Data Tools 2015 설치

공장이야기 2016. 6. 26. 17:26 posted by Giwhan Han

질문

SQL Server Analysis Services Tabular 모델을 만들려면, SQL Server Data Tools라는 도구를 사용해야 하는데 설치 방법이 너무 복잡합니다. 1)SQL Server 설치 미디어가 필요한 것인지, 2)아니면 다른 것을 다운로드 받아 설치해야 하는지, 3)Visual Studio(VS)가 꼭 필요한지가 궁금합니다.

답변

  1. SQL Server 설치 미디어는 더 이상 필요 없습니다.
  2. (SQL Server 2016부터) 개발 도구는 SQL Server와 분리 설치가 가능하며, 별도로 다운로드 받을 수 있습니다.
  3. VS가 이미 설치되어 있으면, BI프로젝트 템플릿을 추가할 수 있습니다. 아니라면, SSDT만 독립 설치할 수 있습니다.

추가 정보

SQL Server에서 제공하는 BI 서비스는 총 세 개의 유형으로 제공됩니다. SQL Server Analysis Services(SSAS), Reporting Services(SSRS), Integration Services(SSIS)가 그것이며, 이 서비스들은 모두 SQL Server Data Tools(SSDT)에서 프로젝트를 만들어 관리할 수 있습니다. SSDT는 Visual Studio 2013 / 2015기반으로 동작하며, SSDT와 SQL Server의 호환성이 맞아야만 문제없이 프로젝트를 생성할 수 있습니다.

SQL Server 2016 호환성 모드 : 1200

SQL Server 2014 호환성 모드 : 1103

툴을 SQL Server 설치미디어에서 분리함으로써, 월 단위로 빠른 업데이트를 제공할 예정입니다.

SSDT 설치 옵션

* 옵션 1) 기존에 VS2013, 2015가 설치되어 있는 경우, SSDT in VS2015방식으로 설치.

Tools > Extensions and Updates 메뉴에서 SQL Server tooling update를 수행

* 옵션 2) VS가 설치되지 않은 경우, SSDT만 독립 설치.

내부적으로는 .net framework과 vs2015 shell (통합)을 함께 설치함

옵션1) SSDT in Visual Studio 2015

Microsoft Account만 있으면, VS 2015 Community Edition을 무료로 사용할 수 있으며, SSDT도 추가할 수 있습니다.

다운로드 : https://www.visualstudio.com/ko-kr/products/visual-studio-community-vs.aspx

clip_image013

옵션2) SSDT 독립 설치

다음 문서에서 3) Set up an Administrative Install Point (optional)에 있는 link를 통해 컴퓨터에 저장 후 "관리 권한으로 실행".

SQL Server Data Tools in Visual Studio 2015
https://msdn.microsoft.com/ko-kr/mt186501

참조) offline 설치를 하려면, 4) Download SSDT as an ISO image 파일 다운로드 후 관리자 권한으로 실행. SSDT_14.0.60525.0_KO.iso(873MB) - (설치에 13분 소요)

SSDT 독립 설치 과정

1. 파일 저장 후 관리자 권한으로 수행

clip_image001

2. BI관련 도구 설치

clip_image002

3. 다운로드 후 설치 과정

clip_image004

4. 설치 성공

clip_image005

5. SQL Server Data Tools 2015 수행 - 도움말 - Microsoft Visual Studio 정보에서 설치된 제품 목록 확인

clip_image006

6. 도구 - 확장 및 업데이트 - 업데이트 - Visual Studio 2015 Update 2 업데이트

clip_image007

7. 파일 - 새로 만들기 - 프로젝트 - Analysis Services 테이블 형식 프로젝트 선택 후 "확인" 클릭

clip_image009

8. 테이블 형식 모델 디자이너에서 개발용 tabular 서버 연결 및 호환성 수준 선택

clip_image010

참조) host 컴퓨터에 로그인한 id/pwd를 별도 AD를 갖는 가상머신에 동일하게 생성해 준 후 접속시도 : 1) SSMS에서는 myserver\tabular에 접속 성공 2) Excel2016에서도 연결하여 피벗 테이블 필드 가져옴 3) SSDT에서는 연결은 되는데 인증을 받지 못함. 도메인명까지 확인하는 것으로 예상됨.

clip_image011

호환성 수준은, SQL Server 버전에 맞게 선택합니다. 2016에서 성능/기능 향상이 많이 있었으므로 가능한 최신 버전을 권장합니다.

clip_image012

9. 이제 모델링을 위한 준비가 완료되었습니다.

Tabular 자습서

설치가 완료되고 서버에 연결까지 성공하였다면, Adventure Works 자습서를 통해 모델링 과정을 체험해 보세요. (2014용 한글 자습서 및 샘플 디비 활용)

테이블 형식 모델링(Adventure Works 자습서)https://msdn.microsoft.com/ko-kr/library/hh231691.aspx

참조

SQL Server Data Tools Team Blog
https://blogs.msdn.microsoft.com/ssdt/

HTH,
한기환

질문

월이 지났지만 아직 지난 달의 월 마감이 끝나지 않은 상황이라면, 관리자가 Datazen 대시보드의 데이터를 보고 혼란스러울 수 있습니다. 관리자가 알아 볼 수 있도록 최종마감일 같은 날짜나 안내문을 넣을 수 있나요?

답변

대시보드는 숫자 필드에 문자를 넣는 것이 불가하고, 별도로 안내사항을 출력할 차트를 제공하지는 않습니다. 하지만, Workaroud로 DataGrid를 이용하여 최종마감일이나 공지사항을 출력할 수 있습니다.

--마감일과 안내문을 관리하는 소스 테이블 생성

clip_image001

--데이터 소스와 뷰 설정

clip_image002

--Datazen Publisher에서 가져오기

clip_image003

--원하는 메시지가 담긴 소스 가져와서 DataGrid에 연결

clip_image004

--우측 상단에 "안내사항"과 "최종마감일"을 포함하는 대시보드

clip_image005

참고로, KPI의 Value에는 숫자나 문자를 모두 출력할 수 있습니다.

방법1. 최근 마감일 KPI를 하나 만들고, cutoffdate를 수작업으로 입력하거나 cutoffdate 테이블에서 select

방법2. 숫자를 가져오던 KPI의 query를 변경하여, if (마감) 수치 else "안내문" 반환.

--최근 마감일 쿼리 수행. (테이블은 만들어서 마감일을 넣어 두었음)clip_image006

--KPI에 최근 마감일이 출력되는 모습 미리보기clip_image007

--KPI에 최근 마감일이 출력된 모습

clip_image008

HTH,
한기환

SQL Server R Services 실습

공장이야기 2016. 3. 6. 23:27 posted by Giwhan Han

SQL Server R Services가 정상적으로 구성되었다면, 이제 Sample로 제공되는 script를 통해서 고급 분석 방법에 대해 알아볼 차례입니다.

참고로, 이 글은 Advanced Analytics 샘플 스크립트 폴더에 있는 Getting Started.docx 문서를 기반으로 작성되었습니다.

 

사전 준비 작업

1.SQL Server R Services 구성이 완료된 상태여야 함
참조) http://optimizer.tistory.com/entry/SQL-Server-R-Services-설치-및-구성

사용할 도구 : SSMS(SQL Server Management Studio), RGUI (또는 Rstudio)

2.샘플 스크립트 다운로드
AdventureWorks Sample Databases and Scripts for SQL Server 2016 CTP3
https://www.microsoft.com/en-us/download/details.aspx?id=49502

다운로드 클릭 후, SQLServer2016CTP3Samples.zip 파일만 선택
Advanced Analytics 폴더에서 실습에 사용할 파일 목록 확인
clip_image001

3.SQL Server 2016 Report Builder 설치
https://www.microsoft.com/en-us/download/details.aspx?id=49162

rdl파일이 있는 예제에서만 사용

 

항공기의 요일별 도착 지연 현황 분석

--아키텍처 : Data Scientist용 도구(RGui)에서 Data 탐색 및 예측 모델 생성
clip_image002

1)client의 R IDE에서 SQL Server에 연결 후, Script 전송
2)SQL Server에서 R code 수행
3)결과값을 client로 반환

--SQL Server에서 ImportAirlineDB.sql 수행

1)RevoTestDB 및 관련 테이블(AirlineDemoSmall, AirlineDemoSmallImport) 생성
2)AirlineDemoSmall.csv파일을 AirlineDemoSmallImport 테이블로 bulk insert
C:\Program Files\RRO\RRO-3.2.2-for-RRE-7.5.0\R-3.2.2\library\RevoScaleR\SampleData\AirlineDemoSmall.csv'
3)AirlineDemoSmallImport에서 가공된 ArrDelay, CRSDepTime, DayOfWeek 컬럼을 AirlineDemoSmall 테이블에 입력

 

USE master
DROP DATABASE IF EXISTS RevoTestDB;
--DB 생성
CREATE DATABASE RevoTestDB
GO
--복구 모드 단순으로 변경
ALTER DATABASE [RevoTestDB] SET RECOVERY SIMPLE WITH NO_WAIT
GO
USE RevoTestDB;
GO
DROP TABLE IF EXISTS AirlineDemoSmallImport, AirlineDemoSmall;
GO
--Sample Data를 넣을 테이블 생성
CREATE TABLE AirlineDemoSmallImport(
ArrDelay varchar(100) NOT NULL, -- we start with varchar because missing values are coded as 'M'
CRSDepTime float NOT NULL,
DayOfWeek varchar(12) NOT NULL -- +2 for the quotes;
)
GO
--가공된 데이터를 넣을 테이블 생성
CREATE TABLE AirlineDemoSmall(
ArrDelay int NULL,
CRSDepTime float NOT NULL,
DayOfWeek varchar(10) NOT NULL
CONSTRAINT CHK_DOW CHECK(DayOfWeek IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday')))
GO
--Sample Data를 Bulk Insert로 Import 테이블에 입력
BULK INSERT AirlineDemoSmallImport
FROM 'C:\Program Files\RRO\RRO-3.2.2-for-RRE-7.5.0\R-3.2.2\library\RevoScaleR\SampleData\AirlineDemoSmall.csv'
WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FIRSTROW = 2 -- Skip header
)
GO
--import 테이블의 행이 60만이 아니면 오류 발생
IF (SELECT COUNT(*) FROM dbo.AirlineDemoSmallImport) <> 600000
BEGIN
RAISERROR('Not all rows were imported.', 20, -1) WITH LOG
END
GO
--Import테이블의 값을 가공하여 Small테이블에 입력
INSERT INTO dbo.AirlineDemoSmall(ArrDelay, CRSDepTime, DayOfWeek)
SELECT
CASE ArrDelay
WHEN 'M' THEN NULL
ELSE CONVERT(int, ArrDelay)
END,
CRSDepTime,
REPLACE(DayOfWeek, '"', '')
FROM dbo.AirlineDemoSmallImport
GO
--SQL 로그인 생성
USE [RevoTestDB]
GO
CREATE LOGIN [MyUser] WITH PASSWORD= ‘MyPassword’, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
CREATE USER [MyUser] FOR LOGIN [MyUser] WITH DEFAULT_SCHEMA=[db_datareader]
ALTER ROLE [db_datareader] ADD MEMBER [MyUser]

USE [master]
GO
CREATE USER [MyUser] FOR LOGIN [MyUser] WITH DEFAULT_SCHEMA=[db_rrerole]
ALTER ROLE [db_rrerole] ADD MEMBER [MyUser]

 

--Connector Rgui 를 관리자 권한으로 수행 (Run as administrator)

clip_image003

"C:\Program Files\RRO\RRO-3.2.2-for-RRE-7.5.0\R-3.2.2\bin\x64\Rgui.exe"

--테스트용 R 스크립트 수행(R memory로 처리할 data를 가져오지 않고, SQL Server에서 직접 수행)

#SQL Server 연결 정보 (실 정보 입력해야 함)
connectionString <- "Driver=SQL Server; Server=vmhostname; Database=RevoTestDB; Uid=MyUser; Pwd=MyPassword";

#테이블과 컬럼 스키마 정보를 airData에 저장
airData <- RxSqlServerData(
connectionString = connectionString,
|table = "AirlineDemoSmall",
colInfo = list( ArrDelay = list(type = "integer"), DayOfWeek = list(type = "factor", levels = c("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday")))
);

#ComputeContext용 환경 설정 구문
cc <- RxInSqlServer(connectionString = connectionString, autoCleanup = FALSE, consoleOutput = TRUE);

#cc 설정
rxSetComputeContext(cc);

#airData에서 ArrDelay변수를 DayOfWeek의 카테고리마다 요약 통계 계산
rxSummary(~ArrDelay + DayOfWeek, data = airData);

--결과
> stopifnot(require(RevoScaleR) || stop(.libPaths())); rxRemoteCall()
====== GISQL2016CTP33 ( process 1 ) has started run at Sun Mar 06 11:53:49 2016 ======

GISQL2016CTP33 [8328]: Rows Processed: 500000
GISQL2016CTP33 [8328]: Rows Processed: 600000

Computation time: 2.063 seconds.

====== GISQL2016CTP33 ( process 1 ) has completed run at Sun Mar 06 11:53:52 2016 ======

Call:
rxSummary(formula = ~ArrDelay + DayOfWeek, data = airData)

Summary Statistics Results for: ~ArrDelay + DayOfWeek
Data: airData (RxSqlServerData Data Source)
Number of valid observations: 6e+05

Name Mean StdDev Min Max ValidObs MissingObs
ArrDelay 11.31794 40.68854 -86 1490 582628 17372

Category Counts for DayOfWeek
Number of categories: 7
Number of valid observations: 6e+05
Number of missing observations: 0

DayOfWeek Counts
Monday 97975
Tuesday 77725
Wednesday 78875
Thursday 81304
Friday 82987
Saturday 86159
Sunday 94975

>

참조
rxSummary() #RevoScaleR 패키지의 함수

rxSummary(formula, data, byGroupOutFile = NULL,
summaryStats = c("Mean", "StdDev", "Min", "Max", "ValidObs", "MissingObs"),
byTerm = TRUE, pweights = NULL, fweights = NULL, rowSelection = NULL,
transforms = NULL, transformObjects = NULL,
transformFunc = NULL, transformVars = NULL,
transformPackages = NULL, transformEnvir = NULL,
overwrite = FALSE,
useSparseCube = rxGetOption("useSparseCube"),
removeZeroCounts = useSparseCube,                 
blocksPerRead = rxGetOption("blocksPerRead"),
rowsPerBlock = 100000,
reportProgress = rxGetOption("reportProgress"), verbose = 0,
computeContext = rxGetOption("computeContext"), ...)

formula
Formula는 전형적으로 응답 변수를 포함하지 않는다. 예를 들면, ~ 구문이 쓰여야 한다. 만약 ~가 쓰이면 모든 non-character 변수에 대해서 요약 통계가 계산된다. 만약 숫자 변수가 factor 변수와 상호 동작하는 관계에 있다면, 요약 통계는 factor의 각 카테고리에 대해서 계산될 것이다.

출처: http://www.rdocumentation.org/packages/RevoScaleR/functions/rxSummary

Preview of using Revolution R Enterprise inside SQL Server 2016
출처: <https://gist.github.com/andrie/1d69d09c5eb2b1bc0033>

HTH
한기환