Changing Properties of an Analysis Services Processing Task

You can change some properties of an Analysis Services Processing task by including in its package a Microsoft® ActiveX® Script task that executes before the Analysis Services Processing task. The ActiveX Script task can change property values every time the package is executed or it can change them depending on conditional logic. An ActiveX Script task that updates properties for every execution is particularly useful prior to an Analysis Services Processing task that performs incremental updates of a cube or partition. These incremental updates usually require that the filter or fact table be changed for each package execution.

After you add the two tasks to a package and connect them with a success precedence constraint, the Data Transformation Services (DTS) Designer design sheet looks like this:

The following properties of the Analysis Services Processing task can be changed. Note that all property names are case sensitive.

• ProcessOption. This is the processing option for the object or folder processed by the task. The following table lists valid values for ProcessOption depending upon the object selected.

  Object or folder	Processing option	ProcessOption property value	Description
  Database	Process	0	Completely processes all cubes, partitions, and dimensions in the database.
  Cubes folder	Process	0	Completely processes all cubes in the folder.
  	Refresh data	1	For each cube in the folder, performs a refresh data operation if possible; otherwise, completely processes the cube.
  Cube with single partition*	Process	0	Completely processes the cube, including structural changes. This is the most thorough type of cube processing.
  	Refresh data	1	Reloads cube data and recalculates aggregations. This option processes changes to existing source data but not the addition of source data. This option does not process structural cube changes such as new dimensions, levels, or measures.
  	Incremental update	2	Adds new data to cube and updates aggregations. This option processes the addition of source data. This option does not process changes to the cube's structure or existing source data.
  Cube with multiple partitions*	Process	0	Completely processes the cube, including structural changes. This option is more thorough than the Refresh data option.
  	Refresh data	1	Reloads cube data and recalculates aggregations. This option processes changes to existing source data but not the addition of source data. This option does not process structural cube changes such as new dimensions, levels, or measures.
  Partition, including remote partitions*	Process	0	Reloads partition data and recalculates aggregations. This option processes changes to existing source data but not the addition of source data. This option does not process structural changes to the parent cube such as new dimensions, levels, or measures.
  	Incremental update	2	Adds new data to a partition and updates aggregations. This option processes the addition of source data. This option does not process changes to the structure of the parent cube or existing source data of the partition.
  Linked cube	Process	0	Completely processes the linked cube.
  Virtual cube	Process	0	Completely processes the virtual cube.
  Dimensions folder	Process	0	Completely processes all dimensions in the folder.
  	Incremental update	2	For each dimension in the folder, performs an incremental update operation if possible; otherwise, completely processes the dimension.
  Shared dimension	Rebuild the dimension structure	0	Completely processes the dimension, including structural changes. This option is more thorough than the Incremental update option.
  	Incremental update	2	Processes the addition of members (that is, rows) to the dimension table. This method does not process changes to the structure of the dimension or relationships among members.
  Virtual dimension	Rebuild the dimension structure	0	Completely processes the virtual dimension.
  Mining Models folder	Process	0	Completely processes mining models in the folder.
  	Refresh data	1	For each mining model in the folder, performs a refresh data operation if possible; otherwise, completely processes the mining model.
  Mining model**	Process	0	Completely processes the mining model.
  	Refresh data	1	Adds new data to the source data of the mining model and updates nodes. This option does not process changes to the structure of the mining model or existing source data.

  
  

  * An additional property, IncrementallyUpdateDimensions, is available when this object is selected. Its data type is Boolean.
  ** The TrainingQuery property is an additional string property available when a mining model object is selected.

• Datasource. This is the data source used for an incremental update of a cube or partition. Valid values are strings that contain data source names as they appear in the Analysis Manager tree pane.
  
  
• FactTable. This is the fact table used for an incremental update of a cube or partition. Valid values are strings that contain fact table names.
  
  
• Filter. This is an expression that limits the fact table records selected for the incremental update of a cube or partition. Valid values are strings that contain valid filters. For more information about filters, see Partition Filters and Incremental Update Filters.

In addition to specifying properties and their values, the ActiveX Script task must name the Analysis Services Processing task to change. Task names are displayed in the Analysis Services Processing Task dialog box.

The following example shows how the task name DTSTask_DTSOlapProcess.Task_1 is used to indicate which Analysis Services Processing task is changed.

Example

The following code, written in Microsoft Visual Basic® Scripting Edition, is used in an ActiveX Script task that modifies the properties of an Analysis Services Processing task. The Analysis Services Processing task incrementally updates the sample Sales cube included in Microsoft SQL Server™ 2000 Analysis Services. The incremental update is executed monthly throughout 1998 except in December.

The code selects a fact table different than the original fact table for the Sales cube (sales_fact_1997). It also creates a different filter each month.

Note  The code specifies the sales_fact_1998 fact table in the sample FoodMart 2000 database. This table includes data for only 1998 (except December). Before executing the package containing this code, set your computer's clock to a date between January 1, 1998 and November 30, 1998. Immediately after the package executes, reset the clock to the current date.

Function Main()
   Dim pkg
   Dim task
   Dim props
   Dim currentyear
   Dim currentmonth
   Set pkg = DTSGlobalVariables.parent
   Set task = pkg.Tasks("DTSTask_DTSOlapProcess.Task_1")
   Set props = task.Properties
   props("ProcessOption").Value = 2
   props("Datasource").Value = "FoodMart"
   props("FactTable").Value = "sales_fact_1998"
   ' Create filter based on current year and month.
   currentyear = YEAR(NOW)
   currentmonth = MONTH(NOW)
   props("Filter").Value = "([sales_fact_1998].[time_id] " _
       & "IN (SELECT [time_id] FROM [time_by_day] " _
       & "WHERE [time_by_day].[the_year] = " _
       & currentyear  _
       & " AND [time_by_day].[month_of_year] = " _
       & currentmonth & "))"
   Main = DTSTaskExecResult_Success
End Function
 

After the incremental update is complete, you must merge the selected rows from sales_fact_1998 into sales_fact_1997. For more information, see Fact Table Considerations When Merging Partitions.