AMO编程

  连接Analysis Services

       创建和处理Analysis Services对象，必须先要连接到Analysis Services 服务器

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

  首先创建一个Server对象objServer，检查objServer是否已经与Analysis Services服务器连接，如果已经连接，则断开连接，并且使用strASConn指定的连接字符串连接服务器。

 

创建数据库

在连接到Analysis Services之后，就可以添加数据库

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

使用objServer对象的Databases.Add()方法向Analysis Services添加一个数据库。使用GetNewName（）方法后，如果Analysis Services服务器上有相同名字的数据库，则新创建的数据库的名字后面会自动加上一个数字。例如：如果Analysis Services上已经存在一个名为SampleDB的数据库，则以上代码会在Analysis Services服务器上创建一个名为SampldDB1的数据库，随后，objDatabase对象保存该数据库。

 

创建数据源

  数据库创建后，就可以创建一个数据源

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

  由于数据源是存储在数据库中的，因此可以使用DataSources.Add()方法向数据库添加一个数据源。DataSources.Add()方法需要将数据源的名字作为参数。调用Update()方法保存数据源对象。

 

创建数据源视图

       创建数据源后，需要创建数据源视图。数据源视图是必须的，因为立方体和维度的结构是基于数据源视图中的schema

      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

       数据源视图的schema是由DataSet的对象objDataSet提供的。

       用来产生表格的schema是由GenerateDWSchema()方法提供的。

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")

  为了获得一个table’s schema,必须创建一个DataSet对象，对于需要获取schema的每一张表格，使用Command对象创建一个select语句，通过该select语句从表格中提取数据。随后，创建一个SqlDataAdapter对象，将Command对象和Connection对象作为参数传入SqlDataAdapter对象中。

 

创建维度，属性，层次，成员属性等对象

       在数据源视图创建之后，我们就可以创建维度，属性，层次以及成员属性等对象。

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.

处理Analysis Services对象

在Analysis Services中创建并保存Analysis Services对象之后，我们需要调用Process()方法处理这些对象。

Private sub CreateDatabase()

   ‘Process Database and all objects in the Database

   objDatabase.Process(ProcessType.ProcessFull)

End Sub

如果需要处理数据库中的所有对象，那么在调用Process()方法的时候，可以将ProcessType的属性设置为ProcessFull,如果仅仅只需要处理一个单独的对象，比如维度对象，那么只需调用为维度对象的Process(0方法而不需要设置ProcessType属性。

objDimension.Process()

只有在对Analysis Services的对象进行处理后，才能够在Business Intelligence DevelopmentStudio 或者在SQL Server Management Studio中浏览维度和立方体中的数据，