Create SQL Server Analysis Services Partitions using AMO

By:   |   Comments (9)   |   Related: > Analysis Services Administration


Problem

When you have SSAS cubes with millions of rows of data, it is very helpful to create partitions. If you have a few cubes you could probably do this manually, but if there are many or if you want to automate this process you should look for smarter solutions such as programming the creation of partitions dynamically.

Solution

This tutorial will show how to create partitions using the AdventureWorks database using AMO (Analysis Management Object).

In this sample we are going to create a partition according to the current date. For example, if today is September 21, 2012 the T-SQL script will create a partition named Internet_Sales_2012 as shown below.

Partition created

If it were the year 2013, the partition name would be Internet_Sales_2013.

The idea for this tip is to create partitions automatically using XMLA and variables. In this tip we are going to use SSIS and Visual Basic, but you can also use C# to automate the partition process in SSIS.

Requirements

I am assuming that you already have the following components installed:

Getting Started

In this example, we are going to create a Script Task with code to create the partitions dynamically.

To start open the SSDT or BIDS and create a new SSIS project.

Once you have a new project started, in the Toolbar choose Script Task and drag and drop to the design pane.

Script task

Write a descriptive name for the Script Task.

script amo task

Create two variables, go to the SSIS menu and choose Variables:

Menu

Create two variables named Connection and Database as shown below. These variables are going to be used to store the SSAS connection information and send it to our code in the script.  Type "ssas" for the Connection and "AdventureWorksDW2012Multidimensional-EE" for the Database as shown below.

Variables

In the Solution Explorer, we are going to create a connection to the SSAS Server. Go to Connection Manager > New Connection Manager:

Connection.

In the Add SSIS Connection Manager, choose MSOLAP100.

Add connection

In the Add Analysis Services Connection Manager press the Edit button.

Add Analysis Services connection

In the Connection Manager type the SSAS Server Name and select the AdventureWorsDW2012 database.

Connection Manager

Rename the connection from "ssas.conmgr" to "ssas" in order to match with the variable that was created.

Connection and variables

In the Solution Explorer, select Add Reference.

add reference

In the Add Reference window, go to .NET and select Analysis Management Objects. What we are doing is adding the AMO libraries to the VB code.

Add Analysis Management Objects

Now edit the ScriptTask and in the ScriptLanguage, select Microsoft Visual Basic 2010 (that is what we are using for this example) then click on the ellipsis near ReadWriteVariables.

Script tasks editor

In the Select Variables windows select the User::Connection and User::Database variables as shown below.

Select variables

Click on the Edit Script... button and paste the code below into the script.  Below I explain the code.

Imports Microsoft.AnalysisServices


Shared myServer As New Server()
Public Sub Main()
'
' Add your code here
'
Dim olapConnection As ConnectionManager
olapConnection = Dts.Connections(CStr(Dts.Variables("Connection").Value))


Dim Server_Name As String = CStr(olapConnection.Properties("ServerName").GetValue(olapConnection))
Dim Database_Name As String = CStr(olapConnection.Properties("InitialCatalog").GetValue(olapConnection))
Dim db_name As String = CStr(Dts.Variables("Database").Value)


Dim objCube As Cube
Dim objMeasureGroup As MeasureGroup
Dim oPartition As Partition
Dim oDataSourceView As New DataSourceView
Dim sdate, syear As String
sdate = Format(Now,"yyyyMMdd")
syear = Format(Now,"yyyy")
myServer.Connect(Server_Name)


Dim db As Database = myServer.Databases.GetByName(db_name)
oDataSourceView = db.DataSourceViews("Adventure Works DW")
objCube = db.Cubes.FindByName("Adventure Works")
objMeasureGroup = objCube.MeasureGroups("Fact Internet Sales 1")
oPartition = objMeasureGroup.Partitions.Add("Internet_Sales_" + syear)
oPartition.Source = _
New QueryBinding(oDataSourceView.DataSourceID, _
"SELECT * FROM [dbo].[FactInternetSales]  _
WHERE OrderDateKey = '" + sdate + "'")


oPartition.StorageMode = StorageMode.Molap
oPartition.ProcessingMode = ProcessingMode.Regular
oPartition.Update(UpdateOptions.ExpandFull)
oPartition.Process(ProcessType.ProcessFull)
myServer.Disconnect()


Dts.TaskResult =ScriptResults.Success

 
End Sub

The first line allows us to reference the AMO components.

Imports Microsoft.AnalysisServices

In the next line we create the server:

Shared myServer As New Server()

Then we create the connection using the Connection and Database variables created above. We are also populating the Server Name and Database Name are obtained from the values of the connection that was created above. 

Dim olapConnection As ConnectionManager
olapConnection = Dts.Connections(CStr(Dts.Variables("Connection").Value))
Dim Server_Name As String = CStr(olapConnection.Properties("ServerName").GetValue(olapConnection))
Dim Database_Name As String = CStr(olapConnection.Properties("InitialCatalog").GetValue(olapConnection))
Dim db_name As String = CStr(Dts.Variables("Database").Value)

In the next section we are defining the variables used for the SSAS cube, the measure group, the partition and the source view. The values for sdate and syear are used to store the current date and current year in a specific date format. 

Dim objCube As Cube
Dim objMeasureGroup As MeasureGroup
Dim oPartition As Partition
Dim oDataSourceView As New DataSourceView
Dim sdate, syear As String
sdate = Format(Now,"yyyyMMdd")
syear = Format(Now,"yyyy")
myServer.Connect(Server_Name)

In this section you have to specify the Database, DataSourceView, Cube, MeasureGroup and Partition names. In addition, we specify the Source.

Dim db As Database = myServer.Databases.GetByName(db_name)
oDataSourceView = db.DataSourceViews("Adventure Works DW")
objCube = db.Cubes.FindByName("Adventure Works")
objMeasureGroup = objCube.MeasureGroups("Fact Internet Sales 1")
oPartition = objMeasureGroup.Partitions.Add("Internet_Sales_" + syear)
oPartition.Source = _
New QueryBinding(oDataSourceView.DataSourceID, _
"SELECT * FROM [dbo].[FactInternetSales]  _
WHERE OrderDateKey <= '" + sdate + "'")

If you are unsure of the values for these items you can generate the XMLA from an existing partition using SQL Server Management Studio.  To do this, connect to SQL Server Analysis Services and right click on an existing database partition for this database and select Script Partition as > CREATE To > New Query Editor Window. This will generate XML like the image below. The values can be pulled from this as follows:

  • DataSourceID (Adventure Works DW) is the DataSourceView
  • CubeID (Adventure Works) is the Cube
  • MeasureGroupID (Fact Internet Sales 1) is the MeasureGroup
  • Partition / Name (Internet_Sales_2005) is the Partition.  We are using just "Internet_Sales_" and will make the year dynamic.
XMLA

Finally, you have to choose the Storage Mode. MOLAP, ROLAP or HOLAP.

  • MOLAP is a multidimensional storage which is faster.
  • ROLAP is a relational database store which is slower
  • and finally HOLAP is an Hybrid of both methods.

You can find more information about Storage Modes here. The Processing Mode refers to the index and aggregation process. It is regular if it the index and process are created during processing and it is LazyOptimization if it is created after processing. The Update option is used to update the changes. Also the Process Method is used to process to Process the partition. You can process all (ProcessFull), you can process data (ProcessData), perform an incremental update (ProcessAdd). For the complete list of Process options click here. 

oPartition.StorageMode =StorageMode.Molap
oPartition.ProcessingMode =ProcessingMode.Regular
oPartition.Update(UpdateOptions.ExpandFull)
oPartition.Process(ProcessType.ProcessFull)
myServer.Disconnect()


Dts.TaskResult =ScriptResults.Success

Once you have the script ready, save it and press OK in the Script Task editor.

Run the SSIS package and verify the results.

Run package

Congratulations! You have just created a new partition using AMO in SSIS!

Open the SQL Server Management Studio
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Wednesday, May 27, 2015 - 12:37:54 PM - John Tunnicliffe Back To Top (37289)

Check out the SSAS Partition Manager project on Codeplex which will dynamically partition your cube automatically with minimal configuration. See https://ssaspartitionmanager.codeplex.com/

Monday, May 18, 2015 - 11:52:26 PM - John Tunnicliffe Back To Top (37214)

If you are looking to automate creation of partitions in your SSAS multidimensional cubes, then take a look at the OlapPartitionManager, a free utility which does it all.  https://olappartitionmanager.codeplex.com/

Wednesday, June 25, 2014 - 2:20:50 AM - nojetlag Back To Top (32385)

How can this be integrated with SSDT-BI SSAS project deployment process ?

Tuesday, February 25, 2014 - 5:32:34 AM - Rajesh Back To Top (29564)

Hi

Just by forums suggestions I have written below code to add calculated measure using AMO. its running without errors. but changes are not replicating in Management Studio. In BIDS script also my MDX query(for creating calculated measure) is not updated. hope some one already worked on this and will through some light on this.

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.AnalysisServices;

namespace AMOdotNET
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                Server srv = new Server();
                srv.Connect("test");
                Database db = srv.Databases["test1"];
                Cube cb = db.Cubes["test"];

                MdxScript ms = cb.MdxScripts["MdxScript"];
                Command cmd = new Command();
                string strcmd = "\nCREATE MEMBER CURRENTCUBE.[Measures].[MyTestMember]";
                strcmd += " AS [Measures].[Count] * 2, ";
                strcmd += "VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Fact1';";
                cmd.Text = strcmd;
                ms.Commands[0].Text = ms.Commands[0].Text + strcmd;

                cb.MdxScripts["MdxScript"].DefaultScript = true;
                cb.MdxScripts["MdxScript 1"].DefaultScript = false;
                cb.MdxScripts["MdxScript 2"].DefaultScript = false;
                cb.MdxScripts["MdxScript 3"].DefaultScript = false;
                cb.MdxScripts["MdxScript 4"].DefaultScript = false;
                cb.MdxScripts["MdxScript 5"].DefaultScript = false;

                ValidationErrorCollection returnederrors = new ValidationErrorCollection();
                db.Validate(returnederrors, true);
                if (returnederrors.Count > 0)
                {
                    #region Error Collection
                    String s = "";
                    int i = 0;
                    foreach (ValidationError err in returnederrors)
                    {
                        s = String.Format("{0}{1}:{2}\n", s, ++i, err.FullErrorText);
                    }
                    Console.WriteLine(s);
                    #endregion
                }

                ms.Update();
                cb.Update(UpdateOptions.ExpandFull);
                cb.Process();

                //srv.Dispose();
                srv.Disconnect();
            }
            catch (Exception ex)
            {
            }

        }
    }
}

Friday, October 11, 2013 - 9:22:43 AM - Daniel Back To Top (27118)

You need to work and open the script task in the script task you have to open the project.

Friday, October 11, 2013 - 7:45:19 AM - Asaduzzaman Back To Top (27117)

Hi,

after rename the connection to ssas, I can't go to the next step! How you added VB files there? and How you get Add Reference option? If you please explain.

Can you please email me you solution?

 

Thank you ! :)

Monday, February 25, 2013 - 5:51:49 AM - pscorca Back To Top (22394)

Hi Daniel, you can refer to my thread: http://social.technet.microsoft.com/Forums/it-IT/sqlanalysisservices/thread/c382769d-66b5-4a43-a6c3-a376e087fd12

Thanks

 

Saturday, February 23, 2013 - 11:02:34 AM - Daniel Calbimonte Back To Top (22374)

Please send us the error message in order to help.

Thursday, February 21, 2013 - 1:02:08 PM - pscorca Back To Top (22345)

Hi, I've used this script for a SSAS 2012 Tabular model, but an exception is generated for the oPartition.Process(ProcessType.ProcessFull) statement. I can use with success oPartition.Update(UpdateOptions.ExpandFull) or oPartition.Update() creating a partition, but when I tru to run the statement oPartition.Process(ProcessType.ProcessFull) or oPartition.Process(ProcessType.ProcessAdd) or oPartition.Process(ProcessType.ProcessDefault) and exception occurs in SSIS.

Any ideas?














get free sql tips
agree to terms