Dynamically Processing partition of a Cube with SSIS package 2008

Question

Hi,

Can anybody help me in How to process partitions dynamically in Cube through SSIS packages(2008 R2)?

For Example: A cube has 2 MeasureGroup

• C202_SalesSalesCycle_2010
• C202_SalesSalesCycle_2011

 

• B012 Olap Cust Item Status_2010
• B012 Olap Cust Item Status_2011

The suggession required are:

Suppose we want only to process Partition ending with 2011 and regulate the partition to process with parameter.

How could I achieve this?

Please help ..any form of help will be thankfull.

Thx.

---

Thanks, Tanmoy Santra

Answer 1

http://www.ssas-info.com/VidasMatelisBlog/11_ssis-package-that-process-all-partitionsmeasure-groupscubes-in-one-database

Not sure whether this will be useful...but you can take a look.(the above link is sufficient)

http://www.ssas-info.com/analysis-services-scripts/1622-script-to-automate-ssas-partition-management-sql-ssis

For selective partition process you might have to form the XMLA dynamically and then use that XMLA for processing

http://sql-bi-dev.blogspot.com/

---

vinu

Answer 2

Hi Vinu,

Thanks for the links.

I am using the XMLA to dynamically processing the cube.

I am facing the error that the " Processing Command contains the DDL that is not valid"

I think if I dont initialize the variable which contain the XMLA script it will not validate.So it is giving the error.But the initilaization space for a string is not enogh to accomodate a xmla script for processing.

Is there anything needed regarding this.

Thanks,

Tanmoy

---

Thanks, Tanmoy Santra

Answer 3

Hi,

If you are using SSIS for processing partitions then you can simply use "Script Task" in your SSIS package and then write AMO code for processing partitions.

Check following sample code which I have build on adventureworks sample and it can be used for processing partitions with ID ending with 2001. you can modify sample AMO code as per your requirement.

Dim objServer As Server
    Dim objDatabase As Database
    Dim objCube As Cube
    Dim objMeasureGroup As MeasureGroup
    Dim objPartition As Partition

    objServer = New Server

    objServer.Connect("localhost")
    objDatabase = objServer.Databases("Adventure Works DW 2008")

    If objDatabase.Cubes.Count > 0 Then
      objCube = objDatabase.Cubes("Adventure Works")

      For Each objMeasureGroup In objCube.MeasureGroups
        For Each objPartition In objMeasureGroup.Partitions
          If objPartition.ID.Trim.Substring(objPartition.ID.Trim.Length - 4, 4) = "2001" Then
            objPartition.Process(ProcessType.ProcessFull)
          End If
        Next
      Next
    End If

    objServer.Disconnect()

 

 

---

Aniruddha http://aniruddhathengadi.blogspot.com/