Processing Objects (XMLA)

In Microsoft SQL Server SQL Server Analysis Services, processing is the step or series of steps that turn data into information for business analysis. Processing is different depending on the type of object, but processing is always part of turning data into information.

To process an SQL Server Analysis Services object, you can use the Process command. The Process command can process the following objects on an SQL Server Analysis Services instance:

  • Cubes

  • Databases

  • Dimensions

  • Measure groups

  • Mining models

  • Mining structures

  • Partitions

To control the processing of objects, the Process command has various properties that can be set. The Process command has properties that control: how much processing will be done, which objects will be processed, whether to use out-of-line bindings, how to handle errors, and how to manage writeback tables.

Specifying Processing Options

The Type property of the Process command specifies the processing option to use when processing the object. For more information about processing options, see Processing Options and Settings (Analysis Services).

The following table lists the constants for the Type property and the various objects that can be processed using each constant.

Type value Applicable objects
ProcessFull Cube, database, dimension, measure group, mining model, mining structure, partition
ProcessAdd Dimension, partition
ProcessUpdate Dimension
ProcessIndexes Dimension, cube, measure group, partition
ProcessData Dimension, cube, measure group, partition
ProcessDefault Cube, database, dimension, measure group, mining model, mining structure, partition
ProcessClear Cube, database, dimension, measure group, mining model, mining structure, partition
ProcessStructure Cube, mining structure
ProcessClearStructureOnly Mining structure
ProcessScriptCache Cube

For more information about processing SQL Server Analysis Services objects, see Processing a multidimensional model (Analysis Services).

Specifying Objects to be Processed

The Object property of the Process command contains the object identifier of the object to be processed. Only one object can be specified in a Process command, but processing an object also processes any child objects. For example, processing a measure group in a cube processes all the partitions for that measure group, while processing a database processes all the objects, including cubes, dimensions, and mining structures, that are contained by the database.

If you set the ProcessAffectedObjects attribute of the Process command to true, any related object affected by processing the specified object is also processed. For example, if a dimension is incrementally updated by using the ProcessUpdate processing option in the Process command, any partition whose aggregations are invalidated because of members being added or deleted is also processed by SQL Server Analysis Services if ProcessAffectedObjects is set to true. In this case, a single Process command can process multiple objects on an SQL Server Analysis Services instance, but SQL Server Analysis Services determines which objects besides the single object specified in the Process command must also be processed.

However, you can process multiple objects, such as dimensions, at the same time by using multiple Process commands within a Batch command. Batch operations provide a finer level of control for serial or parallel processing of objects on an SQL Server Analysis Services instance than using the ProcessAffectedObjects attribute, and let you to tune your processing approach for larger SQL Server Analysis Services databases. For more information about performing batch operations, see Performing Batch Operations (XMLA).

Specifying Out-of-Line Bindings

If the Process command is not contained by a Batch command, you can optionally specify out-of-line bindings in the Bindings, DataSource, and DataSourceView properties of the Process command for the objects to be processed. Out-of-line bindings are references to data sources, data source views, and other objects in which the binding exists only during the execution of the Process command, and which override any existing bindings associated with the objects being processed. If out-of-line bindings are not specified, the bindings currently associated with the objects to be processed are used.

Out-of-line bindings are used in the following circumstances:

  • Incrementally processing a partition, in which an alternative fact table or a filter on the existing fact table must be specified to make sure that rows are not counted twice.

  • Using a data flow task in Microsoft SQL Server Integration Services to provide data while processing a dimension, mining model, or partition.

Out-of-line bindings are described as part of the Analysis Services Scripting Language (ASSL). For more information about out-of-line bindings in ASSL, see Data Sources and Bindings (SSAS Multidimensional).

Incrementally Updating Partitions

Incrementally updating an already processed partition typically requires an out-of-line binding because the binding specified for the partition references fact table data already aggregated within the partition. When incrementally updating an already processed partition by using the Process command, SQL Server Analysis Services performs the following actions:

  • Creates a temporary partition with a structure identical to that of the partition to be incrementally updated.

  • Processes the temporary partition, using the out-of-line binding specified in the Process command.

  • Merges the temporary partition with the existing selected partition.

For more information about merging partitions using XML for Analysis (XMLA), see Merging Partitions (XMLA).

Handling Processing Errors

The ErrorConfiguration property of the Process command lets you specify how to handle errors encountered while processing an object. For example, while processing a dimension, SQL Server Analysis Services encounters a duplicate value in the key column of the key attribute. Because attribute keys must be unique, SQL Server Analysis Services discards the duplicate records. Based on the KeyDuplicate property of ErrorConfiguration, SQL Server Analysis Services could:

  • Ignore the error and continue processing the dimension.

  • Return a message that states SQL Server Analysis Services encountered a duplicate key and continue processing.

There are many similar conditions for which ErrorConfiguration provides options during a Process command.

Managing Writeback Tables

If the Process command encounters a write-enabled partition, or a cube or measure group for such a partition, that is not already fully processed, a writeback table may not already exist for that partition. The WritebackTableCreation property of the Process command determines whether SQL Server Analysis Services should create a writeback table.

Examples

Description

The following example fully processes the Adventure Works DW Multidimensional 2012 sample SQL Server Analysis Services database.

Code

<Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">  
  <Object>  
    <DatabaseID>Adventure Works DW Multidimensional 2012</DatabaseID>  
  </Object>  
  <Type>ProcessFull</Type>  
  <WriteBackTableCreation>UseExisting</WriteBackTableCreation>  
</Process>  

Description

The following example incrementally processes the Internet_Sales_2004 partition in the Internet Sales measure group of the Adventure Works DW cube in the Adventure Works DW Multidimensional 2012 sample SQL Server Analysis Services database. The Process command is adding aggregations for order dates later than December 31, 2006 to the partition by using an out-of-line query binding in the Bindings property of the Process command to retrieve the fact table rows from which to generate aggregations to add to the partition.

Code

<Process ProcessAffectedObjects="true" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">  
  <Object>  
    <DatabaseID>Adventure Works DW Multidimensional 2012</DatabaseID>  
    <CubeID>Adventure Works DW</CubeID>  
    <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>  
    <PartitionID>Internet_Sales_2006</PartitionID>  
  </Object>  
  <Bindings>  
    <Binding>  
      <DatabaseID>Adventure Works DW Multidimensional 2012</DatabaseID>  
      <CubeID>Adventure Works DW</CubeID>  
      <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>  
      <PartitionID>Internet_Sales_2006</PartitionID>  
      <Source xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="QueryBinding">  
        <DataSourceID>Adventure Works DW</DataSourceID>  
        <QueryDefinition>  
          SELECT  
            [dbo].[FactInternetSales].[ProductKey],  
            [dbo].[FactInternetSales].[OrderDateKey],  
            [dbo].[FactInternetSales].[DueDateKey],  
            [dbo].[FactInternetSales].[ShipDateKey],   
            [dbo].[FactInternetSales].[CustomerKey],   
            [dbo].[FactInternetSales].[PromotionKey],  
            [dbo].[FactInternetSales].[CurrencyKey],  
            [dbo].[FactInternetSales].[SalesTerritoryKey],  
            [dbo].[FactInternetSales].[SalesOrderNumber],  
            [dbo].[FactInternetSales].[SalesOrderLineNumber],  
            [dbo].[FactInternetSales].[RevisionNumber],  
            [dbo].[FactInternetSales].[OrderQuantity],  
            [dbo].[FactInternetSales].[UnitPrice],  
            [dbo].[FactInternetSales].[ExtendedAmount],  
            [dbo].[FactInternetSales].[UnitPriceDiscountPct],  
            [dbo].[FactInternetSales].[DiscountAmount],  
            [dbo].[FactInternetSales].[ProductStandardCost],  
            [dbo].[FactInternetSales].[TotalProductCost],  
            [dbo].[FactInternetSales].[SalesAmount],  
            [dbo].[FactInternetSales].[TaxAmt],  
            [dbo].[FactInternetSales].[Freight],  
            [dbo].[FactInternetSales].[CarrierTrackingNumber],  
            [dbo].[FactInternetSales].[CustomerPONumber]  
          FROM [dbo].[FactInternetSales]  
          WHERE OrderDateKey > '1280'  
        </QueryDefinition>  
      </Source>  
    </Binding>  
  </Bindings>  
  <Type>ProcessAdd</Type>  
  <WriteBackTableCreation>UseExisting</WriteBackTableCreation>  
</Process>