보미네

https://channel9.msdn.com/Events/Ignite/2015/BRK3573

https://www.youtube.com/watch?v=tJGGyg1dDHY

IIS를 통해 SQL Server Analysis Service 에 접근하기 위해서 설명한 동영상이다. 위 환경도 도메인 기준으로 설명하고 있어 workgroup 서버에서 sql 에 억세스 하기 위해서는 조금 다른 설정이 필요하다.

1) iis 를 설정한다.

2. 웹 root 폴더에 ssas 의 접근하고자 하는 서버 인스턴스 이름이나 유사한 폴더를 하나 만든다.

   C:\inetpub\wwwroot\ssas2014 정도로 만든다.

3. ssas 인스턴스에서 다음과 유사한 폴더를 찾아들어가 파일을 찾는다.

   C:\Program Files\Microsoft SQL Server\MSAS12.SSAS2\OLAP\bin\isapi

   위 폴더에 들어가면 Resources 라는 폴더와 msmdpump.dll msmdpump.ini 파일이 있다 3개를 모두 복사해서

   C:\inetpub\wwwroot\ssas2014 폴더 아래에 넣는다.

4. 컴퓨터 관리\iis 관리자에 들어간다.

5. 응용 프로그램 풀 이라는 메뉴를 찾고 응용 프로그램 풀 추가를 클릭하고 아래와 같이 설정한다.

6. 디폴트 웹사이트에서 응용 프로그램 추가 메뉴를 클릭해 설정한다.

7. 다음과 같이 설정한다.

 8. 새로만든 응용 프로그램에서 처리기를 추가한다.

9. 오른쪽 메뉴에서 스크립트 매핑 추가를 클릭한다.

10. 내용을 아래와 같이 기입한다.

11. 확인을 누르면 경고가 뜨는데 Yes 한다.

12. ssas 에서 iusr 을 admin 으로 등록해야 억세스 할 수 있다.  위험하다. 그러니 ad 환경을 구축하는게 바르다고 볼 수 있다. 이 설정은 ad 구축이 싫고 로컬에 테스트만 할 때 쓰기 바란다. 혹시 회사에 이렇게 설정 되었다면 보안팀 가이드를 받아 올바른 설정을 하길 바란다. 정말 위험하다 뭐라 말도 못하고...^^;;;; 이것참.....그런데 해커들이 ssas 에 관심이 없는듯은 하다.

13. 이제 테스트로 접근해 보자

잘 된다. ~ 이제 로컬에서 붙은것과 완전히 동일하게 쓸 수 있다.

ssis를 이용해 오라클로부터 데이터를 받아 오고자 한다면, sql server ent. 버전이면 attunity 를 쓰면 가장 빠르다.

놀라운 속도가 나온다. 그러나, 그냥 몇건 안되는 데이터만 받아오면 된다면 꼭 ent. 를 쓸 필요없이 std. 로도 가능하다.

bol 에서 ent 만 attunity driver 를 지원한다고 되어 있지만 드라이버를 std 에 깔아도 정상 동작한다. -_-  (by 김상수 수석)

ssis 는 32bit / sql server agent 64bit 이니 2가지 oracle client 를 깔아야 한다.

win32_11gR2_client.zip

win64_11gR2_client.zip

두개를 깔때 admin 모드로 설치하면되고 설치가 완료되면 리부팅 한다.

tnsnames.ora 를 설정해야 한다.

깔고나면

D:\app\Naver\product\11.2.0\client_1\network 와 유사한 폴더가 생긴다. 이건 32bit 가 깔린 폴더

D:\app\Naver\product\11.2.0\client_2\network 와 유사한 폴더가 생긴다. 이건 64bit 가 깔린 폴더

두 폴더 아래에 admin 이라는 폴더를 만들고 tnsnames.ora 를 복사해 넣는다.

X =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 1.9.2.3)(PORT = 12345))
    )
    (CONNECT_DATA =
      (SID = X)
    )
  )

이럼 끝~

그냥 실행도 되고 agent 도 잘 돌아간다.

http://m.blog.daum.net/_blog/_m/articleView.do?blogid=0S8vK&articleno=110&maxNo=123&minNo=102&maxDt=20140711173404&minDt=20110830080000&maxListNo=0&minListNo=0&maxListDt=&minListDt=&currentPage=1&beforePage=1&categoryId=#

https://technet.microsoft.com/en-us/library/ee470675(v=sql.100).aspx

IF OBJECT_ID('DimDate') IS NOT NULL
DROP TABLE DimDate
GO

CREATE TABLE DimDate
(--IDX INT
--,
DDATE date
,IFULLDATE int
,IYEAR INT
,IMONTH INT
,IDAY INT
,IQUARTER INT
,IWEEK INT
,IWEEKDAY INT
,VWEEKDAY VARCHAR(10)
,HOLIDAY_YN INT
,HOLIDAY_NAME VARCHAR(100)
)
GO

INSERT INTO DimDate (
-- IDX
--,
DDATE
,IFULLDATE
,IYEAR
,IMONTH
,IDAY
,IQUARTER
,IWEEK
,IWEEKDAY
,VWEEKDAY
)
SELECT top 2191 -- ROW_NUMBER() OVER (ORDER BY (SELECT 1)) IDX -- 2015년 말까지
 CAST('20100101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1
 , cast(CONVERT(VARCHAR(10), CAST('20100101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1, 112) as int)
 , DATEPART(YEAR, CAST('20100101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1)
 , DATEPART(MONTH, CAST('20100101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1)
 , DATEPART(DAY, CAST('20100101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1)
 , DATEPART(QUARTER, CAST('20100101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1)
 , DATEPART(WEEK, CAST('20100101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1)
 , DATEPART(WEEKDAY, CAST('20100101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1)
 , CASE DATEPART(WEEKDAY, CAST('20100101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1)
  WHEN 1 THEN '일'
  WHEN 2 THEN '월'
  WHEN 3 THEN '화'
  WHEN 4 THEN '수'
  WHEN 5 THEN '목'
  WHEN 6 THEN '금'
  WHEN 7 THEN '토'
  ELSE NULL END
FROM SYSOBJECTS A
, SYSOBJECTS B
--, SYSOBJECTS C

UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE IFULLDATE = '20090101'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20090125'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20090126'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20090127'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE IFULLDATE = '20090301'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE IFULLDATE = '20090502'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE IFULLDATE = '20090505'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE IFULLDATE = '20090606'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE IFULLDATE = '20090815'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20091002'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석,개천절' WHERE IFULLDATE = '20091003'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20091004'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE IFULLDATE = '20091225'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE IFULLDATE = '20100101'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20100213'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20100214'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20100215'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE IFULLDATE = '20100301'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE IFULLDATE = '20100505'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE IFULLDATE = '20100521'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE IFULLDATE = '20100606'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE IFULLDATE = '20100815'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20100921'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20100922'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20100923'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE IFULLDATE = '20101003'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE IFULLDATE = '20101225'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE IFULLDATE = '20110101'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20110202'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20110203'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20110204'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE IFULLDATE = '20110301'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE IFULLDATE = '20110505'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE IFULLDATE = '20110510'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE IFULLDATE = '20110606'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE IFULLDATE = '20110815'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20110911'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20110912'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20110913'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE IFULLDATE = '20111003'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE IFULLDATE = '20111225'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE IFULLDATE = '20120101'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20120122'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20120123'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20120124'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE IFULLDATE = '20120301'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE IFULLDATE = '20120505'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE IFULLDATE = '20120528'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE IFULLDATE = '20120606'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE IFULLDATE = '20120815'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20120929'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20120930'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20121001'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE IFULLDATE = '20121003'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE IFULLDATE = '20121225'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE IFULLDATE = '20130101'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20130209'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20130210'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20130211'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE IFULLDATE = '20130301'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE IFULLDATE = '20130505'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE IFULLDATE = '20130517'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE IFULLDATE = '20130606'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE IFULLDATE = '20130815'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20130918'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20130919'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20130920'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE IFULLDATE = '20131003'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE IFULLDATE = '20131225'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE IFULLDATE = '20140101'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20140130'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20140131'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20140201'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE IFULLDATE = '20140301'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE IFULLDATE = '20140505'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE IFULLDATE = '20140506'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE IFULLDATE = '20140606'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE IFULLDATE = '20140815'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20140907'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20140908'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20140909'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE IFULLDATE = '20141003'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE IFULLDATE = '20141225'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE IFULLDATE = '20150101'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20150218'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20150219'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20150220'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE IFULLDATE = '20150301'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE IFULLDATE = '20150505'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE IFULLDATE = '20150525'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE IFULLDATE = '20150606'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE IFULLDATE = '20150815'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20150926'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20150927'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20150928'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE IFULLDATE = '20151003'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE IFULLDATE = '20151225'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE IFULLDATE = '20160101'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20160207'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20160208'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20160209'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE IFULLDATE = '20160301'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE IFULLDATE = '20160505'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE IFULLDATE = '20160514'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE IFULLDATE = '20160606'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE IFULLDATE = '20160815'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20160914'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20160915'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20160916'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE IFULLDATE = '20161003'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE IFULLDATE = '20161225'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE IFULLDATE = '20170101'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20170127'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20170128'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20170129'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE IFULLDATE = '20170301'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE IFULLDATE = '20170503'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE IFULLDATE = '20170505'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE IFULLDATE = '20170606'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE IFULLDATE = '20170815'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절,추석' WHERE IFULLDATE = '20171003'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20171004'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20171005'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE IFULLDATE = '20171225'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE IFULLDATE = '20180101'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20180215'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20180216'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20180217'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE IFULLDATE = '20180301'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE IFULLDATE = '20180505'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE IFULLDATE = '20180522'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE IFULLDATE = '20180606'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE IFULLDATE = '20180815'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20180923'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20180924'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20180925'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE IFULLDATE = '20181003'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE IFULLDATE = '20181225'

UPDATE DimDate
SET HOLIDAY_YN = 1
WHERE VWEEKDAY IN ('토','일')

UPDATE DimDate
SET HOLIDAY_YN = 0
, HOLIDAY_NAME = ''
WHERE HOLIDAY_NAME IS NULL

DELETE DimDate WHERE IFULLDATE > '20181225'
GO

SELECT * FROM DimDate

신기술에 대한 데모와 스크립트를 제공한다.

http://social.technet.microsoft.com/wiki/contents/articles/6982.sql-server-2012-developer-training-kit-bom.aspx?Sort=MostRecent

https://en.wikipedia.org/wiki/Complex_event_processing

Notable vendors and products[edit]

• SQLstream SQLstream’s stream processing platform, s-Server, provides a relational stream computing platform for analyzing large volumes of service, sensor and machine and log file data in real-time.
• Microsoft StreamInsight Microsoft CEP Engine implementation ^[20]
• openPDC — A set of applications for processing streaming time-series data in real-time.
• Apama - A Complex Event Processing Platform that monitors rapidly moving event streams, detects and analyzes important patterns, and takes action according to rules.^[21]
• StreamBase Systems - A visual development platform and high performance event server for rapidly building and deploying real-time event-based applications. - Now Owned by TIBCO Software
• SAP ESP - A low-latency, rapid development and deployment platform that allows processing multiple streams of data in real time ^[22]
• TIBCO BusinessEvents & Streambase - CEP platform and High Performance Low Latency Event Stream Processing
• WebSphere Business Events
• Informatica RulePoint - High performant, scalable solution by Informatica.
• Drools Fusion
• GigaSpaces XAP
• Oracle Event Processing - A solution for building applications to filter, correlate, and process events in real time.
• Esper Complex event processing for Java and C#.
• Siddhi Complex event processing written in Java. Designed as part of a series of middleware components.

https://www.youtube.com/watch?v=UWq7uSXaoz8 내용중

제품

상용제품

TIBCO : TIBCO BusinessEvents : 업계 최고 제품

StreamBase Systems : StreamBase CEP : 정교한 CEP 제품

SAP : Sybase Event Stream Processor : Sysbase 인수

IBM : InfoSphere Streams : 테러 방지 목적으로 시작 멀티미디어 스트림 프로세싱 가능

Microsoft : StreamInsight : 

Informatica : RulePoint

오픈소스

Esper

quick start project https://github.com/tedwon/cep-esper-quick-start

입력 (이벤트를 수집) 어뎁터를 만들어야 함

처리 (실시간 처리) 

결과 (다양한 이벤트의 복합 영향도 패턴 인식 등을 구현할 수 있음)

EPL SQL 형식의 Event 처리 스크립트 언어

• Event filtering 
• Sliding data windows and aggregation
• grouped windows and output rate limiting
• joins and outer joins
• subquery
• historical or reference data

Event Streams 에서 EPL Statements 를 이용해 데이터를 검색할 수 있음

join 은 RDBMS 나 Apache HBase UDF 와 할 수 있다. 

Example

from pattern [a -> (b and c)]

b c 이벤트가 발생하고 a 가 발생하면 알람을 달라 라고 event 의 correlation 을 정할 수 있음 

개발전략

모니터링 이벤트 정의

이벤트 타입(포멧) 정의

EPL 정의

output adapter 개발 등록

input adapter 개발 등록

CEP 개발 주요 포인트

Runtime 환경 결정

Zero-downtime 지원 여부 결정

Runtime 시 EPL 업데이트 지원 여부 결정

Event 타입과 용량 결정 

솔루션 만들고 JBOSS RHQ 로 모니터링

첨부는 실습 메모이다. 

timeSeries.docx

틀린부분? 없다.....

https://technet.microsoft.com/ko-kr/library/cc879295(v=sql.110).aspx

https://technet.microsoft.com/en-us/library/cc879295(v=sql.110).aspx

msft 로지스틱 회귀분석은 msft 신경망 알고리즘의 한 형태이다. (신경망은 드릴쓰루가 안된다. )

나중에 좀 정리를 해야겠다. 저 표 읽을수 있다는게 참 다행이다. 하하하하하

모델을 만들고

분석하고 1

분석해서 2

예측한다.  

https://www.microsoft.com/en-us/server-cloud/products/sql-server/
http://www.gartner.com/technology/reprints.do?id=1-237UHKQ&ct=141016&st=sb

장점은 왠지 저런 기관에서 중요하게 생각하는 기능을 열심히 만들어 넣는듯?한 분위기이고 , 단점은 심각하다. 어플라이언스가 없고(DW제외), 분류는 엔터프라이즈 급 디비라고 하지만 사용자는 엔터프라이즈 어플리케이션에 잘 안쓴다. 2012부터 가격은 돌아버렸고, 아주 정말 아우~~~~ 장난치나...HA/DR 구현이 쉽지 않단다....음? 이건 먼소리지?

아래 표로는 좋아 보이지만 향 후 데이터베이스 시장은 엔터프라이즈 시장과 비 엔터프라이즈 시장으로 길을 나누어 갈 것이고 비 엔터프라이즈 시장은 mysql 이나 다른 무료 데이터베이스를 쓰면 충분하고 (all flash storage 쓰면 속도 아주 좋음 latch 는 설계 잘 하면 회피 할 수 있음) 캐시가 필요한 일반적인 곳은 윗단에 캐싱 레이어만 따로 구축하면 된다.

그나마, 데이터가 중요한 게임사는 가격 정책에 밀려 이미 다 떠나버렸고, mssql을 아직 게임사에 쓰면 개발사가 능력이 없는 것으로 보이고, 커뮤니티 같은 분야는 쓸 이유가 없고, 은행은 또 엔터프라이즈 느낌 안나는 mssql 잘 안쓴다 이말이군..

DB 배우는 사람은 그나마 미션 크리티컬한 곳에서 mssql 쓰는 곳에 취직을 해야 한다는 것인데, 이런곳에 고만 고만한 사람은 안 뽑는다. 과연 살아남을까? 이제 db를 처음 배운다면 잘 선택해야 할 듯 하다. 나는 끝까지 해서 메인프레임 관리자 처럼 되어봐야지 하하하하하하

Microsoft

Headquartered in Redmond, Washington, U.S., Microsoft markets its SQL Server DBMS for the operational DBMS market, as well as Microsoft Azure SQL Database (a database platform as a service) and Microsoft Azure Tables. Microsoft now has in-memory row-store technology for transactions in SQL Server 2014.

Strengths

• Market vision: Microsoft's market-leading vision consists of in-memory computing (SQL Server 2014 now has full transaction in-memory support), NoSQL (with a new document-store DBMS), cloud offerings (both cloud-only and hybrid cloud), use of analytics in transactions (HTAP) and support of mobility. Its vision for in-memory computing and putting the "cloud first" is ahead of its competitors.
• Strong execution: Microsoft SQL Server is an enterprisewide, mission-critical DBMS capable of competing with products from the other large DBMS vendors. Gartner's 2013 market share data shows Microsoft taking second place from IBM in terms of total DBMS revenue.
• Performance and support: Reference customers were very positive, with the performance of SQL Server, documentation, support, ease of installation and operation all rated highly. Only 7% reported problems with the DBMS overall.

Cautions

• Lack of an appliance: Microsoft still lacks an appliance for transactions (one comparable to its SQL Server Parallel Data Warehouse appliance), whereas its major competitors (IBM, Oracle and SAP) all offer one.
• Market image: Although SQL Server is an enterprise-class DBMS, Microsoft continues to struggle to dispel a perception of weakness in this area. Inquiries from Gartner clients demonstrate a continuing perception that SQL Server is not used for mission-critical enterprisewide applications — a view that inhibits wider use of SQL Server as a primary, enterprise-class DBMS.
• HA/DR and pricing issues: Reference customers again found the pricing model for SQL Server unacceptable (they gave it the lowest overall rating of any vendor in this Magic Quadrant) and blamed the price changes that came with SQL Server 2012. Microsoft also received one of the lowest overall scores for ease of implementing HA/DR.

연속된 값을 discretized 로 구간을 만들 수 있다.

아래와 같이 설정하면 되고, 연봉정보가 다 다를때, 나이대를 구분해 작업하고자 할 때 불연속 버전을 만들수 있다.

데이터 속성의 종류는 아래와 같고 그 특징을 이해해야 한다.

https://msdn.microsoft.com/ko-kr/library/ms174572.aspx