SSAS dynamic processing partition (ii)

Source: Internet
Author: User

First, the general process such as:

The parameters that need to be used

Ii. description of each control

1. "Get parameters that need to be processed"

SQLStatement as follows:

SELECT 'Dynamicpartitionssas'                                                           asDatabaseID,'Rmyymz'                                                                         asCubeid,'Fact Mz Visit Table'                                                            asMeasuregroupid,'Fact Mz Visit Table' + CONVERT(VARCHAR(6),Dateadd(Month,-1,Getdate()), the) asPartitionIDUNIONSELECT 'Dynamicpartitionssas'                                       asDatabaseID,'Rmyymz'                                                     asCubeid,'Fact Mz Visit Table'                                        asMeasuregroupid,'Fact Mz Visit Table' + CONVERT(VARCHAR(6),Getdate(), the) asPartitionID

Note: This article is mainly based on the current month, the dynamic processing of this month and one months of data

2. Dynamic processing of partitions

3. Return the XMLA statement to be processed

C # code:

/*Microsoft SQL Server Integration Services Script Task Write scripts using Microsoft Visual C # 2008. The Scriptmain is the entry point class of the script.*/using system;using system.data;using microsoft.sqlserver.dts.runtime;using system.windows.forms;using Microsoft.analysisservices;namespace st_1f03ca252f334e528b0c03196047066b.csproj{[System.AddIn.AddIn ("Scriptmain", Version = "1.0", Publisher = "", Description = "")]     Publicpartial class ScriptMain:Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase {#region V STA 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 task executes. To access the object model with the Dts property. Connections, variables, events, and logging features is available as members of the Dts property as shown in the F        ollowing examples. To reference a variable, call dts.variables["Mycasesensitivevariablename"].        Value;        To post a log entry, call Dts.Log ("This is my log text", 999, NULL);        To fire a event, call Dts.Events.FireInformation ("Test", "hits the Help Message", "", 0, true); Use the Connections collection with something like the following:connectionmanager cm = Dts.Connections.Add ("OLE        DB "); Cm. ConnectionString = "Data source=localhost;initial catalog=adventureworks; Provider=sqlncli10;integrated Security=sspi;        Auto translate=false; ";                Before returning from this method, set the value of Dts.taskresult to indicate success or failure.     To open Help, press F1.*/         Publicvoid Main () {//Todo:ADDYour code here//?????????????? String Sdatabaseid=(String) Dts.variables["DatabaseID"].            Value; String Scubeid=(String) Dts.variables["Cubeid"].            Value; String Smeasuregroupid=(String) Dts.variables["Measuregroupid"].            Value; String Spartitionid=(String) Dts.variables["PartitionID"].            Value; String sserver="localhost"; ConnectionManager cm=Dts.connections.ADD("MSOLAP100"); Cm. ConnectionString="Provider=MSOLAP.4;D ATA Source=Localhost;integrated Security=Sspi;initial Catalog="+Sdatabaseid; Microsoft.AnalysisServices.Server Aserver=new Server ();            Aserver.connect (sserver); Microsoft.analysisservices.DatabaseAdatabase=AServer.Databases.FindByName (Sdatabaseid); Microsoft.AnalysisServices.Cube ACube=ADatabase.Cubes.FindByName (Scubeid); Microsoft.AnalysisServices.MeasureGroup Ameasuregroup=ACube.MeasureGroups.FindByName (smeasuregroupid); //generates an XMLA statement that processes a cube dts.variables["Isnetepresent"]. Value=true; Dts.variables["Xmla_script"]. Value="<Batch xmlns=\ "http://Schemas.microsoft.com/AnalysisServices/2003/Engine\ ">"                +"<Parallel>"                +"<Process xmlns: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/ -/Engine/ -/ -\ "xmlns:ddl200=\ "http://Schemas.microsoft.com/AnalysisServices/ ./Engine/ $\ "xmlns:ddl200_200=\ "http://Schemas.microsoft.com/AnalysisServices/ ./Engine/ $/ $\">"                +"<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. Processing cube

5, scheduled to execute the package task.

SSAS dynamic processing partition (ii)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.