Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast






Learn more about SQL Server tools








Learn more about SQL Server tools


   Got a SQL tip?
            We want to know!

Create SQL Server Analysis Services Partitions using AMO

MSSQLTips author Daniel Calbimonte By:   |   Read Comments (7)   |   Related Tips: > 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


Last Update: 11/9/2012


About the author
MSSQLTips author Daniel Calbimonte
Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Wednesday, June 25, 2014 - 2:20:50 AM - nojetlag Read The Tip

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


Tuesday, February 25, 2014 - 5:32:34 AM - Rajesh Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

Please send us the error message in order to help.


Thursday, February 21, 2013 - 1:02:08 PM - pscorca Read The Tip

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?




 

Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.