SSAS动态处理分区（二）

讲完了动态添加，那就看看如何动态处理，这个相对简单一些，直接上图了

一、大致流程如下图：

所需要用到的参数

二、每个控件说明

1、“获取需要处理的参数”

SQLStatement如下：

SELECT 'DynamicPartitionSSAS'                                                         AS DatabaseID,

       'RmyyMZ'                                                                       AS CubeID,

       'Fact Mz VisitTable'                                                          AS MeasureGroupID,

       'Fact Mz VisitTable' + CONVERT(VARCHAR(6), Dateadd(month, -1, Getdate()), 112) AS PartitionID

UNION

SELECT 'DynamicPartitionSSAS'                                      AS DatabaseID,

       'RmyyMZ'                                                   AS CubeID,

       'Fact Mz VisitTable'                                       AS MeasureGroupID,

      'Fact Mz Visit Table' + CONVERT(VARCHAR(6), Getdate(), 112) AS PartitionID

注：本文主要是根据当前的月份，动态处理本月和上一个月的数据

2、动态处理分区

3、返回需要处理的xmla语句

C#代码：

/*

   Microsoft SQL Server Integration ServicesScript Task

   Write scripts using Microsoft Visual C#2008.

   The ScriptMain is the entry point class ofthe script.

*/

usingSystem;

usingSystem.Data;

usingMicrosoft.SqlServer.Dts.Runtime;

usingSystem.Windows.Forms;

usingMicrosoft.AnalysisServices;

namespaceST_1f03ca252f334e528b0c03196047066b.csproj

{

   [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]

   public partialclass ScriptMain: Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

   {

        #regionVSTA generated code

       enum ScriptResults

       {

            Success =Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,

            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

       };

        #endregion

       /*

         The execution engine calls this method when the taskexecutes.

         To access the object model, use the Dts property.Connections, variables, events,

         and logging features are available as members of the Dtsproperty as shown in the following examples.

         To reference a variable, callDts.Variables["MyCaseSensitiveVariableName"].Value;

         To post a log entry, call Dts.Log("This is my logtext", 999, null);

         To fire an event, call Dts.Events.FireInformation(99,"test", "hit the help message", "", 0, true);

         To use the connections collection use something like thefollowing:

         ConnectionManager cm =Dts.Connections.Add("OLEDB");

         cm.ConnectionString = "Data Source=localhost;InitialCatalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;AutoTranslate=False;";

         Before returning from this method, set the value ofDts.TaskResult to indicate success or failure.

         To open Help, press F1.

     */

       public voidMain()

       {

            // TODO:Add your code here

            //将参数传给变量

            StringsDatabaseID = (String)Dts.Variables["DatabaseID"].Value;

            StringsCubeID = (String)Dts.Variables["CubeID"].Value;

            StringsMeasureGroupID = (String)Dts.Variables["MeasureGroupID"].Value;

            StringsPartitionID = (String)Dts.Variables["PartitionID"].Value;

            StringsServer = "localhost";

            ConnectionManagercm = Dts.Connections.Add("MSOLAP100");

            cm.ConnectionString = "Provider=MSOLAP.4;Data Source=localhost;IntegratedSecurity=SSPI;Initial Catalog=" + sDatabaseID;

            Microsoft.AnalysisServices.Server aServer = newServer();

            aServer.Connect(sServer);

            Microsoft.AnalysisServices.Database aDatabase =aServer.Databases.FindByName(sDatabaseID);

            Microsoft.AnalysisServices.Cube aCube = aDatabase.Cubes.FindByName(sCubeID);

            Microsoft.AnalysisServices.MeasureGroup aMeasureGroup = aCube.MeasureGroups.FindByName(sMeasureGroupID);

            //生成处理cube的xmla语句

            Dts.Variables["IsNetePresent"].Value = true;

            Dts.Variables["Xmla_Script"].Value = "<Batchxmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">"

                + "<Parallel>"

                + "<Processxmlns:xsd=\"http://www.w3.org/2001/XMLSchema\"xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"xmlns:ddl2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2\""

                + "xmlns:ddl2_2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2/2\"xmlns:ddl100_100=\"http://schemas.microsoft.com/analysisservices/2008/engine/100/100\"xmlns:ddl200=\"http://schemas.microsoft.com/analysisservices/2010/engine/200\"xmlns:ddl200_200=\"http://schemas.microsoft.com/analysisservices/2010/engine/200/200\">"

                + "<Object>"

                + "<DatabaseID>"+ sDatabaseID + "</DatabaseID>"

                + "<CubeID>"+ sCubeID + "</CubeID>"

                + "<MeasureGroupID>"+ sMeasureGroupID + "</MeasureGroupID>"

                + "<PartitionID>"+ sPartitionID + "</PartitionID>"

                + "</Object>  <Type>ProcessFull</Type> <WriteBackTableCreation>UseExisting</WriteBackTableCreation></Process> </Parallel> </Batch>";

            Dts.TaskResult = (int)ScriptResults.Success;

            Dts.TaskResult = (int)ScriptResults.Success;

       }

   }

}

4、处理cube

5、定时执行包任务。