보미네

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