Author: Alex Lim
Editor: Simon Robinson

Introduction

Analysis Management Object (AMO) is a new object model in SQL Server 2005 that can be used to manage and administer Analysis Services 2005. The AMO consists of .NET classes that you can use to build managed applications in the various .NET languages. The AMO object model is built on top of the XML for Analysis (XMLA), a standard that defines communication and data access between different types of analytical data providers. All commands submitted using AMO exist in the XMLA form. Using AMO, developers can automate the creation and processing of Analysis Services objects. AMO replaces the Decision Support Object (DSO) object model used to manage and administer Analysis Services 2000. DSO is provided in Analysis Services 2005 to support existing applications built on top of the Analysis Services 2000. However, if you're dealing with new managed or unmanaged applications, or if you wish to take advantage of the new functionality in Analysis Services 2005, you should use AMO.

In this article, you'll learn the basics of the AMO object model, including the classes in the AMO and common properties and methods within the AMO classes. After learning the basic data model, you'll use your knowledge to create a sample application that allows for the generation and processing of Analysis Services OLAP objects.

Note: This article requires knowledge of the concepts and usage of the OLAP objects in Analysis Services 2005. For more information on OLAP objects, see the Related Links section. Also note that this article focuses on OLAP objects only-it doesn't cover Data Mining objects.

System Requirements

To work with the solution in this sample, you should have the following:

• Window 2000 Server with Service Pack 4 or Window XP with Service Pack 1
• IIS 5.0
• .NET Framework 2.0 (Version 2.0.50125)
• VS .NET 2005 Beta 1 (Version 8.0.50215.44)
• SQL Server 2005 Beta 2 with AdventureWorksDW sample database
• Analysis Services 2005 (Version 9.00.1116) Beta 2

The Sample Code

The sample download for this article contains a VS .NET 2005 solution. This solution was developed using VB .NET and contains a reference to the Microsoft.AnalysisServices namespace. The sample also uses the AdventureWorksDW database available with MS SQL Server 2005.

To install this sample, extract the zip file to the C:\ directory, create an IIS virtual directory called AMO Sample , and point the virtual directory to the location of the sample. Then open Default.aspx in the browser.

Understanding the AMO Object Model

The AMO provides a complete set of .NET classes for managing and administering Analysis Services 2005. The AMO classes are in the Microsoft.AnalysisServices namespace. Figure 1 shows a high-level view of the AMO object model for OLAP objects.

Figure 1. The AMO Object Model for OLAP Objects

In this section, I'll provide brief overview of the AMO objects and their commonly used methods and properties.

The Server Class

• Is the main class in the AMO
• Exposes the server functionality of Analysis Services
• Contains a collection of Database objects

Property/Method	Description
Connected	Checks the current connection state (whether it's open or closed) and returns a Boolean value of true or false
Name	Retrieves the Analysis Services name
ConnectionString	Retrieves the server connection information
Connect()	Connects to Analysis Services
Disconnect()	Disconnects from Analysis Services
Add()	Adds a database to Analysis Services
Restore()	Restores a database to Analysis Services from a backup
Reconnect()	Reconnects to Analysis Services

The Database Class

• Provides the functionality of a Database in Analysis Services
• Contains a collection of DataSource , DataSourceView , Dimension , and Cube objects

Property/Method	Description
Name	Retrieves the Database name
Backup()	Backs up a Database to a specific location
Drop()	Drops a Database
Process()	Processes a Database and all the objects contained in the Database
Update()	Saves the Database to Analysis Services
Add()	Adds a DataSource , DataSourceView , Dimension , or Cube object to the database

The RelationalDataSource Class

• Represents the DataSource object in Analysis Services
• Inherits properties and methods from the DataSource class of the AMO
• Should be used instead of the DataSource class when creating a DataSource object

Property/Method	Description
ConnectionString	Sets the data source information
Update()	Saves the DataSource object to Analysis Services

The DataSourceView Class

• Represents the DataSourceView object in Analysis Services

Property/Method	Description
DataSourceID	Associates a DataSource with the DataSourceView ; also retrieves the current DataSource associated with the DataSourceView
Schema	Assigns a table schema to the DataSourceView
Update()	Saves the DataSourceView to Analysis Services

The Dimension Class

• Represents the Dimension object in Analysis Services
• Contains a collection of Attribute and Hierarchy objects

Property/Method	Description
Source	Sets the Dimension 's source
ProcessingMode	Sets the processing mode of the Dimension ; possible values are Regular and LazyAggregation
StorageMode	Sets the storage mode of the Dimension ; possible values are ROLAP and MOLAP
WriteEnabled	Write-enables a Dimension
Update()	Saves a Dimension to Analysis Services
Process()	Processes a Dimension

The Hierarchy Class

• Represents the Hierarchy object in Analysis Services
• Is associated with the Dimension

Property/Method	Description
Name	Sets the name of the Hierarchy
AllMemberName	Sets the name of the AllMemberName in the Hierarchy
Add()	Adds a Level to the Hierarchy

The Level Class

• Represents the Level object in Analysis Services
• Is associated with a Hierarchy

Property/Method	Description
Name	Sets the name of the Level
SourceAttribute	Sets the source Attribute for the Level

The DimensionAttribute Class

• Represents the Attribute object in Analysis Services
• Is used as the source for the Level in the Hierarchy , or as a MemberProperty in the Level

Property/Method	Description
KeyColumns	Sets the key column for an Attribute
NameColumn	Sets the name column for an Attribute
Usage	Sets the Attribute usage. Possible Attribute values are Regular , Key , Parent , and Account
DefaultMember	Sets the DefaultMember in the Attribute
Add()	Adds a MemberProperty

The AttributeRelationships Class

• Represents the MemberProperty object in Analysis Services

Property/Method	Description
Name	Retrieves the MemberProperty name
Visible	Retrieves the visibility of the MemberProperty (whether it's visible to the browser/client application)
AttributeID	Retrieves the AttributeID that is used by the MemberProperty
Parent	Retrieves the Level that contains the MemberProperty

The Cube Class

• Represents the Cube object in Analysis Services
• Contains a collection of MeasureGroup objects

Property/Method	Description
StorageMode	Sets the default storage mode of the Cube ; possible values are ROLAP , HOLAP , and MOLAP
ProcessingMode	Sets the default processing mode of the Cube ; possible values are Regular and LazyAggregation
Source	Sets the source of the Cube
Process()	Processes the Cube and all objects contained in the Cube
Add()	Adds a MeasureGroup and MDX script into the Cube
Update()	Saves the Cube and all the objects contained in the Cube to Analysis Services

The MeasureGroup Class

• Represents the MeasureGroup object in Analysis Services
• Contains a collection of Measure and Partition objects

Property/Method	Description
StorageMode	Sets the storage mode of the MeasureGroup ; the storage mode can be ROLAP , HOLAP , or MOLAP
Add()	Adds a Dimension , a Measure , and a Partition into the MeasureGroup

The Measure Class

• Represents the Measure object in Analysis Services

Property/Method	Description
Source	Sets the Measure class's source
FormatString	Formats the Measure
Visible	Sets the visibility of the Measure ; if set to visible, the Cube browser and client application will be able to view the Measure
DisplayFolder	Creates a folder to group and categorize the Measure

The Partition Class

• Represents the Partition object in Analysis Services

Property/Method	Description
Source	Sets the source of the Partition
ProcessingMode	Defines the processing mode of the Partition ; possible values are Regular and LazyAggregation
StorageMode	Defines the Partition class's storage mode; the storage modes can be ROLAP , HOLAP , or MOLAP
Slice	Specifies the data slice or portion of data contained in the Partition ; is used when creating two or more Partition classes

AMO Programming

In this section, you'll learn how to put the properties and methods together to generate Analysis Services OLAP objects. You'll create an ASP.NET project to automatically generate an OLAP Database , DataSource , DataSourceView , Dimension , Hierarchy , MeasureGroup , Measure , and Partition in Analysis Services. The name of the Database and the Cube will be supplied in the ASP.NET project. The goal of this project is to minimize the time required in constructing an OLAP database by generating an OLAP database based on the table's schema in the data warehouse.

To use AMO, you need to include the Microsoft.AnalysisServices namespace in your project. When you install SQL Server 2005 in the default location, the namespace will be located at C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies . For beta 2, you also need to include the Microsoft.DataWarehouse.Interfaces namespace in your project. This requirement will be removed in the subsequent beta and release of SQL Server 2005.

As you're probably aware, some objects are dependent upon other objects. Therefore, some objects need to be created prior to others that are dependent on them. The logical sequence to generate and process OLAP objects is as follows:

1. Connect to Analysis Services
2. Create a Database
3. Create a DataSource
4. Create a DataSourceView
5. Create Dimensions and Hierarchies
6. Create Cubes , MeasureGroups , Partitions , and Measures
7. Process the objects

I'll provide a detailed explanation, along with the code for each step, in the following sections. All the sample code was developed using VB .NET, but feel free to use your language of choice.

Connecting to the Analysis Services

The first step in creating and processing Analysis Services objects is to connect to Analysis Services itself.

Private Shared objServer As New Server
Private sub ConnectAnalysisServices()
   Dim strASConn As String = "Data Source=LocalHost;Provider=msolap;"
   ‘Disconnect from current connection if it's currently connected
   If objServer.Connected = True Then
      objServer.Disconnect()
   End If
   objServer.Connect(strASConn)
End Sub

A Server object- objServer -is created. This then checks whether the current connection is active in the objServer . If the current connection in objServer is active, it will be disconnected, allowing you to connect to Analysis Services using the connection string in the strASConn variable.

Creating a Database

After connecting to Analysis Services, you can then add a database to it.

Private Shared objDatabase As New Database
Private sub CreateDatabase()
   ‘Add Database to the Analysis Services
   objDatabase = 
objServer.Databases.Add(objServer.Databases.GetNewName("SampleDB"))
‘Save Database to the Analysis Services
   objDatabase.Update()
End Sub

After storing the database in Analysis Services, you can use the Databases.Add() method from the objServerServer object to add the database to Analysis Services. Using the GetNewName() method, a logical number will be appended to the database name if a database with the same name is already present in Analysis Services. If SampleDB Database is already there, a SampleDB1 Database will be added. Then the objDatabase Database object is saved to Analysis Services.

One point to note is that adding an Analysis Services object doesn't automatically save it to Analysis Services. You need to call the Update() method to save the object.

Creating a DataSource

After creating the database, you need to create a DataSource to retrieve the source data for the Analysis Services objects.

Private Shared objDataSource As New RelationalDataSource
Private sub CreateDataSource()
   ‘Add Data Source to the Database
   objDataSource = objDatabase.DataSources.Add("SampleDS")
   objDataSource.ConnectionString = "Provider=SQLOLEDB.1;Initial Catalog= 
AdventureDW;Data Source=LocalHost;Integrated Security=SSPI; Persist 
Security Info=False"
   objDataSource.Update()
End Sub

Since a DataSource is stored in a Database , you'll use the DataSources.Add() method in the objDatabase Database object to add a DataSource to the Database . The DataSources.Add() method requires the DataSource name as the method's parameter. Calling the Update() method saves the DataSource object to the Database .

Creating a DataSourceView

After creating the DataSource , you need to create the DataSourceView . The DataSourceView is required because the structure of the Dimension and Cube is based on the schema in the DataSourceView .

Private Shared objDataSourceView As New DataSourceView
Private sub CreateDataSourceView()
   ‘Add Data Source View to the Database
   objDataSourceView = objDatabase.DataSourceViews.Add("SampleDSV")
   objDataSourceView.DataSourceID = objDataSource.ID
   objDataSourceView.Schema = objDataSet
   objDataSourceView.Update()
End Sub

Since DataSourceView is stored in the Database , you'll use the DataSourceViews.Add() method of the objDatabase Database object to add DataSourceView . The DataSourceViews.Add() method requires a DataSourceView name as the method's parameter. The previously created DataSource is assigned to the objDataSourceView object to retrieve the source data for Dimension and Cube . The table's schema required by the DataSourceView is provided by the objDataSet DataSet object.

The code used to generate the table's schema is contained in the GenerateDWSchema() method. The subsequent code block, which will be used for the explanation that follows, shows only a portion of the code in the GenerateDWSchema() method. The full listing of the code can be found in the sample project.

Private Sub GenerateDWSchema()
   Dim strCommand As String
   'Retrieve table's schema and assign the table's schema to the 
   'DataSet
   'Add primary key to the schema according to the primary key in the
   'tables
   strCommand = "Select EmployeeKey, ParentEmployeeKey, FirstName,  
      Title, DepartmentName" & _
      " from DimEmployee"
   Dim objEmpData As New SqlDataAdapter(strCommand, objConnection)
   objEmpData.MissingSchemaAction = MissingSchemaAction.AddWithKey
   objEmpData.FillSchema(objDataSet, SchemaType.Source, "DimEmployee")

In order to generate a table's schema, you need to create a DataSet object. For each table that you're retrieving the schema from, use a Command object to create a Select statement that will select the records from the required table's. Then create a SqlDataAdapter object, assign the Command object and current Connection object (that is, the connection to the database containing the required table), and set the table's schema in the SqlDataAdapter object to the DataSet object. The MissingSchemaAction.AddWithKey is used to add the primary key to the table's schema. Once the required schemas are assigned to the DataSet object, you can create relationships between the tables.

   'Add foreign key relationship between the tables in the DataSet
   objDataSet.Relations.Add("FactResellerSales_DimProduct_FK", 
   objDataSet.Tables("DimProduct").Columns("ProductKey"), _
   objDataSet.Tables("FactResellerSales").Columns("ProductKey"))

To add a relationship between tables, you can use the Relations.Add() method in the DataSet object. You need to pass the primary key column as the first parameter and the foreign key column as the second parameter. Repeat the preceding code for any additional relationships you want to create.

After assigning the table's schema to the DataSourceView , you need to call the Update() method again in order to save the DataSourceView object to the Database .

Creating the Dimension, Attribute, Hierarchy, and MemberProperty Objects

After creating a DataSourceView , you can create Dimension , Attribute , Hierarchy , and MemberProperty objects. Attribute s are dependent on a Dimension , while both the Hierarchy and MemberProperties are optional objects when creating a Dimension .

Private Shared objDimension As New Dimension
Private sub CreateDimension()
   ‘Add Dimension to the Database
   objDimension = objDatabase.Dimensions.Add("DimProduct")
   objDimension.Source = New DataSourceViewBinding(objDataSourceView.ID)
   Dim objAttributesColl as DimensionAttributeCollection = 
      objDimension.Attributes 
   'Add Dimension Attributes
   Dim objProdKeyAttribute as DimensionAttribute = 
      objAttributesColl.Add("ProductKey")
   Dim objCatKeyAttribute as DimensionAttribute = 
      objAttributesColl.Add("CategoryKey")
   Dim objProdDescAttribute as DimensionAttribute = 
      objAttributesColl.Add("Description")
   'Set Attribute usage and source
   objProdKeyAttribute.Usage = AttributeUsage.Key
   objProdKeyAttribute.KeyColumns.Add("DimProduct","ProductKey",
      OleDBType.Integer)
   objProdKeyAttribute.NameColumn.Add("DimProduct","ProductName",
      OleDBType.WChar)
   objCatKeyAttribute.KeyColumns.Add("DimCategory","CategoryKey",
      OleDBType.Integer)
   objCatKeyAttribute.NameColumn.Add("DimCategory","CategoryName",
      OleDBType.WChar)
   objProdDescAttribute.KeyColumns.Add("DimProduct","ProductDesc",
      OleDBType.WChar)
   'Add Hierarchy and Level
   Dim objHierarchy as Hierarchy = objDimension.Hierarchies.Add
      ("ProductByCategory")
   objHierarchy.Levels.Add("Category").SourceAttributeID = 
      objCatKeyAttribute.ID
   objHierarchy.Levels.Add("Product").SourceAttributeID = 
      objProdKeyAttribute.ID
   'Add Member Property
   objProdKeyAttribute.AttributeRelationships.Add
      (objProdDescAttribute.ID)
   objDimension.Update()
End Sub

As a Dimension is stored in the Database , you'll use the Dimensions.Add() method of the objDatabase Database object to add a Dimension to the Database . A Dimension object's structure needs to conform to the schema in the DataSourceView . Therefore, you need to set the Dimension object's source to the DataSourceView using the DataSourceViewBinding() method. You'll also need to supply the ID of the previously created DataSourceView object as the parameter for the DataSourceViewBinding() method.

A DimensionAttributeCollection object is created to store all the Attribute objects created for the Dimension . The preceding code creates three Attribute objects: ProductKey , CategoryKey , and Description . By default, all the Attribute objects in the Dimension are treated as having a value of Regular . If you need to designate the Attribute type as something other than Regular , then you need to explicitly specify its usage.

The objProdKeyAttribute is designated as key attribute in the DimProduct Dimension . Both the KeyColumns property and the NameColumn property are used to specify the source value for the Attribute . The first parameter required by both of these properties is the source table, and the second is the source column. The optional third parameter is used to specify the source data type. When not specified, the default data type, WChar , will be used. You can omit the NameColumn property if both the KeyColumns property and NameColumn property refer to the same source column. Generally, you'll use both properties in the Attribute when they each refer to different source columns.

In this project, the key column for the objProdKeyAttribute attribute is ProductKey , and the NameColumn is ProductName . When users browse the DimProduct Dimension , the value in ProductName will be shown. Internally, the value of ProductKey is used to ensure that each member in the objProdKeyAttribute attribute is unique.

The preceding code also creates a ProductByCategory hierarchy. This hierarchy contains two levels-the category level and the product level.The argument required by the Levels.Add() method is the level name itself. The category level is created using the objCatKeyAttribute attribute, and the product level is created using the objProdKeyAttribute attribute. For attributes not used as levels in the hierarchy, you can add the attribute to another attribute as a MemberProperty . You can also add the objProdDescAttribute attribute as a MemberProperty to the objProdKeyAttribute attribute. After Dimension , Attribute , Hierarchy , and MemberProperty have been created and added, you need to call the Update() method to save the objects to the database.

Creating the Cube, MeasureGroup, Measure, and Partition Objects

The final objects that you need to create are the Cube , Measure Group , Measure , and Partition objects. You can also create a CalculatedMember object that contains a calculation formula. The Cube you're creating consists of one MeasureGroup object, two Measure objects ( SalesAmount and SalesQuantity ), one CalculatedMember object that calculates the total sales (by multiplying the sales amount by the sales quantity), and one Partition object.

Private sub CreateCube()
   Dim objCube As New Cube
   Dim objSales As New Measure
   Dim objQuantity As New Measure
   Dim objTotal As New MdxScript
   Dim strScript As String
   Dim objProdCubeDim As New CubeDimension
   Dim objRegMGDim As New RegularMeasureGroupDimension
   Dim objMGA As New MeasureGroupAttribute
   Dim objPartition As New Partition 
   Dim objCommand As New Command
   ‘Add Cube to the Database and set Cube source to the Data Source View
   objCube = objDatabase.Cubes.Add("SampleCube")
   objCube.Source = New DataSourceViewBinding(objDataSourceView.ID)
   ‘Add Measure Group to the Cube
   Dim objMeasureGroup As MeasureGroup = objCube.MeasureGroups.Add
      ("FactSales")
    ‘Add Measure to the Measure Group and set Measure source
   objSales = objMeasureGroup.Measures.Add("Amount")
   objSales.Source = New DataItem("FactSales","SalesAmount", 
      OleDBType.Currency)
   objQuantity = objMeasureGroup.Measures.Add("Quantity")
   objQuantity.Source = New DataItem("FactSales","SalesQuantity", 
   OleDBType.Integer)
    ‘Calculated Member Definition
   strScript = "Calculated; Create Member CurrentCube.[Measures].[Total] 
      As [Measures].[Quantity] * [Measures].[Amount]"   
   ‘Add Calculated Member
   objTotal.Name = "Total Sales"
   objCommand.Text = strScript
   objTotal.Commands.Add(objCommand)
   objCube.MdxScripts.Add(objTotal)
   ‘Add DimProduct Dimension to the Cube
   objProdCubeDim = objCube.Dimensions.Add(objDimension.ID)
   ‘Use Regular Relationship Between DimProduct Dimension and FactSales 
   ‘Measure Group
   objRegMGDim = objMeasureGroup.Dimensions.Add(objProdCubeDim.ID)
   ‘Link ProductKey in DimProduct Dimension with ProductKey in FactSales
   ‘Measure Group
   objMGA = objRegMGDim.Attributes.Add(objDimension.KeyAttribute.ID)
   objMGA.Type = MeasureGroupAttributeType.Granularity
   objMGA.KeyColumns.Add("FactSales","ProductKey", OleDBType.Integer)
   ‘Add Partition to the Cube, set Partition source, Storage and 
      ‘Processing mode
   objPartition = objMeasureGroup.Partitions.Add("FactSales")
   objPartition.Source = New TableBinding(objDataSource.ID,"dbo", 
      "FactSales")
   objPartition.ProcessingMode = ProcessingMode.Regular
   objPartition.StorageMode = StorageMode.Molap
   ‘Save Cube and all major objects to the Analysis Services
   objCube.Update(UpdateOptions.ExpandFull)
End Sub

To add the Cube to the database, you'll use the Cubes.Add() method in the objDatabase Database object. The only argument required by the Cubes.Add() method is the Cube name. Next, you'll set the Cube object's source to the DataSourceView using the DataSourceViewBinding() method. You'll need to supply the ID of the previously created DataSourceView object as the parameter for the DataSourceViewBinding() method. After adding the Cube , you need to add a MeasureGroup to the Cube by calling the MeasureGroups.Add() method in the objCube Cube object, and passing the MeasureGroup name as an argument for the method.

To add a Measure to the MeasureGroup , you need to call the Measures.Add() method in the objMeasureGroup Measure Group object and pass the Measure name as the method's argument. The Source property in the Measure object will be used to set the source value for the Measure . The Source property requires two mandatory arguments: the source table and the source column; and one optional argument: the source data type. For a calculation, you can create a CalculatedMember . In the preceding code block, a CalculatedMember definition was created. This CalculatedMember definition is then added to the objCommand Command object using the Commands.Add() method. Then an objCommand Command object will be added to the objCube Cube object using the MDXScripts.Add() method.

The Dimension you create won't automatically get added to the Cube . For every Dimension that you want to add, you'll need to declare a CubeDimension object, assign the Dimension object's ID property to the CubeDimension object, and call the Dimensions.Add() method. You'll also need to specify the ID in the CubeDimension as the method's argument. After adding the Dimension to the Cube , you need to specify the type of relationship between the Dimension and the MeasureGroup . In this sample, the RegularMeasureGroupDimension object is used to specify a Regular relationship which is used to join DimProduct Dimension and the MeasureGroup . Other relationships that are available are Many-To-Many , Reference , and Degenerate , which are represented by the ManyToManyMeasureGroupDimension class, the ReferenceMeasureGroupDimension class, and the DegenerateMeasureGroupDimension class.

Next, you need to create a MeasureGroupAttribute object to link the Attribute in the Dimension with the Attribute in the MeasureGroup . The DimProduct key attribute- ProductKey -is used to join to the ProductKey in the FactSales MeasureGroup . The KeyColumns.Add() method is used to specify the source value for the Attribute in the MeasureGroup that used to be joined to the Dimension .

A Partition is stored in the MeasureGroup object. Therefore, you will need to call the Partitions.Add() method from the MeasureGroup object to add the Partition to the MeasureGroup . Next, call the TableBinding() method to set the Partition source. The TableBinding() method requires three arguments: the ID of the DataSource object, the table schema, and the table name. Next, set the Partition object's processing mode to Regular and its storage mode to MOLAP . Since the Cube contains major objects like MeasureGroup and Partition , you'll need to call the Update() method with the ExpandFull option to save the Cube , and all major objects in the Cube , to Analysis Services.

Processing the Analysis Services Objects

After the Analysis Services objects are created and saved to Analysis Services, you need to call the Process() method to process the objects.

Private sub CreateDatabase()
   ‘Process Database and all objects in the Database
   objDatabase.Process(ProcessType.ProcessFull)
End Sub

If you need to process all the objects in the Database , you call the Process() method in the Database object with the ProcessType.ProcessFull option, as shown in the preceding code block. If you want to process an individual object, like a Dimension object, you can use the Process() method without any option in the Dimension object, such as objDimension.Process() .

After processing the Analysis Services object's, you can browse the Dimension and Cube using the Business Intelligence Development Studio or the SQL Server Management Studio, both of which are located under the Microsoft SQL Server 2005 program group in the Window's Program Menu.

The Sample Application

The sample application is an ASP.NET project that you can use to generate and process Analysis Services OLAP objects. The application uses all the code developed throughout this article to automate the generation and processing of Analysis Services OLAP objects.

The interface is rather simple (see Figure 2)-it consists of two text box controls and one button control on the Default.aspx page. A user enters the database name and cube name in the text box controls, and then clicks the Generate button. In the button's click event the values supplied by the user are validated. If the values are valid, the AMO process described in the article has been followed correctly.

Figure 2. The sample application main screen

To test the application, enter the database name and cube name, or accept the default values. Then click the Generate button. The Database , DataSource , DataSourceView , Dimension , and Cube will be generated and processed. To view and browse the generated objects, open the SQL Server Management Studio and connect to Analysis Services . Expand the Databases node to view the generated database. To view other objects in the database (for example, the Dimension object), expand the generated database node. Figure 3 shows what the cube browser looks like if you're browsing the generated Cube .

Figure 3. Browsing the generated cube

Conclusion

In this article, you were given a basic overview of the new AMO object model available in MS SQL Server 2005. You built a sample application, step by step, in order to produce all the required objects in the correct order. Along the way, I discussed many of the relationships that exist between the various objects in the model. Lastly, I displayed a simple interface that can be placed on top of the code to automate the processing of the AMO objects. This sample should provide a great start in using and automating the new AMO model.