oltp olap linked server query

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) 클래스 라이브러리를 이용해 자동화 시켜야 한다.

 

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