보미네

피벗 테이블 옵션 

표시 

클레식 피벗 uncheck

요약 필드 클릭후 

필드 설정 

요약 없음

레이아웃 클릭

항목 레이아웃 반복 check 

맨날 잊어버림...ㅠ.ㅠ ~ 늙으면 메모하자....

http://blogs.technet.com/b/the_microsoft_excel_support_team_blog/archive/2013/11/12/powerpivot-for-excel-2013-errors-after-october-update-kb-2825655.aspx

https://resnikb.wordpress.com/2009/06/17/c-lambda-and-foreach-variable/

catalog 이름을 입력해야 한다.

/****** Object:  LinkedServer [SSAS]    Script Date: 2015-07-05 오후 7:24:27 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'SSAS', @srvproduct=N'ssas 2014', @provider=N'MSOLAP', @datasrc=N'localhost', @catalog=N'study'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SSAS',@useself=N'False',@locallogin=NULL,@rmtuser=N'navercorp\Naver',@rmtpassword='########'

위와같이 쓸려고 했는데, olap 쿼리 몇방 날리지도 않는데 일일이 관리하기 귀찮기 때문에 사람들이 이렇게 쓰네...

CREATE PROCEDURE DBO.GETOLAPDATAMDX (
@OLAPDB NVARCHAR(MAX),
@OLAPMDX NVARCHAR(MAX)
) AS
-- EXTRACTS DATA FROM OLAP CUBE AND RETURNS IN RESULTSET FORMAT VIA SQL SERVER
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(MAX) =''
IF NOT EXISTS(SELECT * FROM SYS.SERVERS WHERE NAME = @OLAPDB)
BEGIN
EXEC MASTER.DBO.SP_ADDLINKEDSERVER @SERVER = @OLAPDB, @SRVPRODUCT=N'MSOLAP.4', @PROVIDER=N'MSOLAP', @DATASRC=N'LOCALHOST', @CATALOG=@OLAPDB
END
SET @SQL = 'SELECT * FROM OPENQUERY(['+@OLAPDB+'],'''+@OLAPMDX+''')'
EXEC SP_EXECUTESQL @SQL
--PRINT @SQL
GO

DECLARE
   @OLAPDB NVARCHAR(MAX) = N'STUDY'
 , @OLAPMDX NVARCHAR(MAX) = N'SELECT NON EMPTY { [MEASURES].[HDDCNT] } ON COLUMNS, NON EMPTY { ([DIM DATE 1].[DIMDATEHI].[IYEAR].ALLMEMBERS * [DIM HDD제조사].[HDD제조사].[HDD제조사].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( -{ [DIM HDD제조사].[HDD제조사].&[], [DIM HDD제조사].[HDD제조사].&[UNKNOWN] } ) ON COLUMNS FROM [EVENT DATA]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS'

EXEC MASTER.DBO.GETOLAPDATAMDX @OLAPDB, @OLAPMDX

위 결과가 몇개의 컬럼으로 구성되는지 미리 알고 있고 컬럼이 지저분하니 temp 에 정의해서 박어서 이쁘게 뽑아서 쿼리해서 쓰더라....이렇게 쓰려면 db 이름을 oltp 이름과 중복 안되게 잘 지어야 한다.

declare @tblx table
(c1 varchar(1000)
,c2 varchar(1000)
,c3 varchar(1000)
,c4 varchar(1000)
,c5 varchar(1000)
)

DECLARE
   @OLAPDB NVARCHAR(MAX) = N'STUDY'
 , @OLAPMDX NVARCHAR(MAX) = N'SELECT NON EMPTY { [MEASURES].[HDDCNT] } ON COLUMNS, NON EMPTY { ([DIM DATE 1].[DIMDATEHI].[IYEAR].ALLMEMBERS * [DIM HDD제조사].[HDD제조사].[HDD제조사].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( -{ [DIM HDD제조사].[HDD제조사].&[], [DIM HDD제조사].[HDD제조사].&[UNKNOWN] } ) ON COLUMNS FROM [EVENT DATA]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS'

insert into @tblx
EXEC MASTER.DBO.GETOLAPDATAMDX @OLAPDB, @OLAPMDX

select * from @tblx

이렇게 말이다.....여기서 이제 다른 쿼리 날리고 그런거 하네....

http://sqlblog.com/blogs/stacia_misner/archive/2010/11/30/31193.aspx

-- 이걸 이용해서 고급시계열 분석 예제를 실행해 본다면 다음과 같이 쿼리할 수 있다.

-- 예측 쿼리를 olap 서버에 날리는 방법

-- 01  no predicate
DECLARE
   @OLAPDB NVARCHAR(MAX) = N'tsbike'
 , @OLAPMDX NVARCHAR(MAX) =
 N'
SELECT
flattened
  [Forecasting].[Model Region],
  (PredictTimeSeries([Forecasting].[Amount],5)) as [PredictAmount],
  (PredictTimeSeries([Forecasting].[Quantity],5)) as [PredictQuantity]
From
  [Forecasting]
--where [Forecasting].[Model Region] =''''M200 Europe''''
 '

EXEC MASTER.DBO.GETOLAPDATAMDX @OLAPDB, @OLAPMDX
go

-- 02 with predicate
DECLARE
   @OLAPDB NVARCHAR(MAX) = N'tsbike'
 , @OLAPMDX NVARCHAR(MAX) =
 N'
SELECT
flattened
  [Forecasting].[Model Region],
  (PredictTimeSeries([Forecasting].[Amount],5)) as [PredictAmount],
  (PredictTimeSeries([Forecasting].[Quantity],5)) as [PredictQuantity]
From
  [Forecasting]
where [Forecasting].[Model Region] =''''M200 Europe''''
 '

EXEC MASTER.DBO.GETOLAPDATAMDX @OLAPDB, @OLAPMDX

-- 03 기존 model 에 실제 늘어난 데이터를 add해 모델을 정교화 시키는 쿼리
-- 필터도 있고

DECLARE
   @OLAPDB NVARCHAR(MAX) = N'tsbike'
 , @OLAPMDX NVARCHAR(MAX) =
 N'
SELECT
  flattened
  [Forecasting].[Model Region],
  (PredictTimeSeries([Forecasting].[Amount],5, EXTEND_MODEL_CASES)) as [PredictAmount],
  (PredictTimeSeries([Forecasting].[Quantity],5, EXTEND_MODEL_CASES)) as [PredictQuantity]
From
  [Forecasting]
PREDICTION JOIN
  OPENQUERY([Adventure Works DW Multidimensional 2012],
    ''''SELECT
      [NewDate],
      [NewAmount],
      [NewQty],
      [Series]
    FROM
      [dbo].[NewSalesData]
    '''') AS t
ON
  [Forecasting].[Reporting Date] = t.[NewDate] AND
  [Forecasting].[Amount] = t.[NewAmount] AND
  [Forecasting].[Quantity] = t.[NewQty] AND
  [Forecasting].[Model Region] = t.[Series]
where [Forecasting].[Model Region] =''''M200 Europe''''
 '
EXEC MASTER.DBO.GETOLAPDATAMDX @OLAPDB, @OLAPMDX

--04 대체 모델을 이용한 cross prediction example
DECLARE
   @OLAPDB NVARCHAR(MAX) = N'tsbike'
 , @OLAPMDX NVARCHAR(MAX) =
 N'
SELECT
  flattened
  ([All Regions].[Region]) as [Model Used],
  ( ''''T1000 Pacific'''') as [ModelRegion],
  PredictTimeSeries([All Regions].[Avg Qty], 5 ,REPLACE_MODEL_CASES)
From
  [All Regions]
PREDICTION JOIN
  OPENQUERY([Adventure Works DW Multidimensional 2012],
    ''''SELECT
      [ReportingDate],
      [Quantity]
    FROM
      (SELECT  ReportingDate, ModelRegion, Quantity, Amount
FROM     vTimeSeries
WHERE  (ModelRegion = N''''''''T1000 Pacific'''''''')) [T1000 Pacific Region]
    '''') AS t
ON
  [All Regions].[Reporting Date] = t.[ReportingDate] AND
  [All Regions].[Avg Qty] = t.[Quantity]
   '
EXEC MASTER.DBO.GETOLAPDATAMDX @OLAPDB, @OLAPMDX

결국

피벗이나 자유도 높은 분석 결과를 이용한 TimeSeries 예측을 SQL Server Analysis Service 를 이용해 하려면  DSO (Decision Support Objects)  COM (Component Object Model) 클래스 라이브러리를 이용해 자동화 시켜야 한다.

음 그렇군.....다 짜야하는군....하하하하하하

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. 이제 테스트로 접근해 보자

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