Sql Server 数据更新之后， Analysis Services分析数据库同步更新办法

请支持开源软件：https://gitee.com/bing300/aspfm

在BI行业，使用Analysis Services做数据分析，数据挖掘的产品很多，那么避免不了要解决一个问题。

当生产数据库中新增了数据，Analysis Services数据库里的数据并不会自动更新。

那么就必须写一个JOB定时处理Analysis Services数据库。

1、在生产数据库中，新建一个链接数据

链接名称：BING_ANALYSISLINK

提供程序：Microsoft OLE DB Provider for Analysis Services

数据源：127.0.0.1

在安全性部分，使用此安全上下文建立连接：

远程登录：****

使用密码：******

在服务器选项部分，RPC Out  =  True

2、执行存储过程

EXEC bing_SyncAnalysis @DatabaseId = N'bing300',@AnalysisDBLinkName=N'BING_ANALYSISLINK',@ProcessingType = N'ProcessFull'

这个存储过程执行之后，Analysis Services数据库会得到最新数据。

这时，你就可以把这个执行语句，做成一个SQLServer Job（SqlServer Job不在这里详述）

3、最主要的存储过程

Create PROCEDURE bing_SyncAnalysisExec (
@AnalysisDBLink VARCHAR(256) = null,
@XMLA XML
)
--WITH ENCRYPTION
AS
BEGIN

	--Creates Linked Server if it does not exist.
	--exec [ASControl].[P_CreateASLinkedServer];

	DECLARE @XMLACommand VARCHAR(MAX) = CONVERT(VARCHAR(MAX), @XMLA);
	DECLARE @Command NVARCHAR(MAX);
	SET @Command = N'EXEC ('''+@XMLACommand+''') AT '+ @AnalysisDBLink;
	--print @Command;
	EXEC (@Command);

	/*	in case of the following error:
		
		OLE DB provider �MSOLAP� for linked server �OLAP_LINKED_SERVER� returned message �An error was encountered in the transport layer.�.
		OLE DB provider �MSOLAP� for linked server �OLAP_LINKED_SERVER� returned message �The peer prematurely closed the connection.�.

		Msg 7303, Level 16, State 1, Line 1

		Cannot initialize the data source object of OLE DB provider �MSOLAP� for linked server �OLAP_LINKED_SERVER�.

		This means you need to: Must be logged in with a Windows user or configure a windows user on MSOLAP provider properties.
	*/
END

Create PROCEDURE bing_SyncAnalysis (
	@DatabaseId VARCHAR(256) = null,
	@AnalysisDBLinkName VARCHAR(256) = null,
    @ProcessingType VARCHAR(256) = 'ProcessFull'
)
--WITH ENCRYPTION
AS
BEGIN
	--Error Levels
	DECLARE @DefaultErrorLevel int, @DefaultErrorState int;
	SET @DefaultErrorLevel = 16;
	SET @DefaultErrorState = 1;
	DECLARE @Msg nvarchar(max);

	DECLARE @SSASDatabaseName nvarchar(256) = isnull(@DatabaseId,@DatabaseId);

	if @SSASDatabaseName is null
	BEGIN
		SET @Msg = '@DatabaseId parameter was not provided, and routine was unable to find out the database name by itself.';
		RAISERROR (@Msg, @DefaultErrorLevel, @DefaultErrorState);
	END

	if @ProcessingType is null
	BEGIN
		SET @Msg = 'Parameter @ProcessingType cannot be NULL';
		RAISERROR (@Msg, @DefaultErrorLevel, @DefaultErrorState);
	END
	ELSE IF @ProcessingType not in ('ProcessDefault','ProcessFull','ProcessClear')
	BEGIN
		SET @Msg = 'Parameter @ProcessingType must be "ProcessUpdate" or "ProcessFull" or "ProcessClear" ';
		RAISERROR (@Msg, @DefaultErrorLevel, @DefaultErrorState);
	END

	DECLARE @XMLAStr XML = '
	  <Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
		<Process>
		  <Object>
			<DatabaseID>' + @SSASDatabaseName + '</DatabaseID>
		  </Object>
		  <Type>' + @ProcessingType + '</Type>
		  <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
		</Process>
	  </Batch>
	';
	
	EXEC bing_SyncAnalysisExec @AnalysisDBLink = @AnalysisDBLinkName,@XMLA = @XMLAStr;
END