Share via


ETL Process for the Report Preparation DTS Task

The Report preparation DTS task is responsible for processing data after it has been imported into the Data Warehouse. The task is composed of four distinct processing phases: processing stored procedures, defining cube partitions, processing OLAP dimensions, and processing physical cube partitions and virtual cubes.

The DTS task can be run in three processing modes: Full, Refresh Data, and Incremental. The DTS task always performs the following steps:

  • Execute each procedure listed in the following table that has the ProcessingEnabled column set to 1.

Attribute name

Data type

Constraint

Default

Description

Name

nvarchar (150)

NULL

None

Name of the stored procedure.

Ordinal

int

NULL

None

Specifies the order in which the stored procedure will be processed by the Report preparation DTS task.

ProcessingEnabled

bit

NULL

1

If True (1), the stored procedure will be processed. If False (0), the procedure will not be processed.

  • Process each dimension listed in the following table that has the ProcessingEnabled column set to 1.

Attribute name

Data type

Constraint

Default

Description

DimName

nvarchar (50)

NOT NULL

None

Dimension name.

IncEnabled

bit

NOT NULL

1

If True (1), the dimension will be processed incrementally. If False (0), the dimension data will always be fully refreshed, regardless of the task settings.

LastSuccessfulDate

datetime

NULL

None

Date and time that the dimension was last processed successfully.

LastProcessedDate

datetime

NULL

None

Date and time of the last attempt to process the dimension.

LastProcessedDescr

nvarchar (255)

NULL

None

Contains text that describes the result of the last attempt to process the dimension.

LastProcessedStatus

int

NULL

None

Status of the last attempt to process the dimension. If 0, the cube was processed successfully.

Ordinal

int

NULL

None

Specifies the order in which the dimension is to be processed by the Report preparation DTS task.

ProcessingEnabled

bit

NOT NULL

1

If True (1), the dimension will be processed. If False (0), the dimension will not be processed.

  • Process each partition listed in the CubePartitionInfo for any cube listed in the following table that has ProcessingEnabled set to 1.

Attribute name

Data type

Constraint

Default

Description

AutoPartition

bit

NOT NULL

1

If set to True (1), new partitions will be added to the cube as needed by the Report preparation DTS task. If set to False (0), partitions will not be automatically created for the cube.

CubeName

nvarchar (50)

NOT NULL

None

Name of the cube to be processed.

FactTable

Sysname

NULL

 

Name of the fact table associated with the cube name.

IncEnabled

bit

NOT NULL

None

If True (1), the cube will be processed incrementally if the Report preparation task is running in incremental mode. If False (0), data for the cube will always be fully refreshed when the Report preparation task is run.

IsVirtual

bit

NOT NULL

0

If False (0) the cube is standard. If True (1) the cube is virtual.

Optimization

tinyint

NULL

 

Indicates targeted optimization percentage for the Report preparation DTS task when it is run in full mode and aggregations are designed for the cube. If set to NULL, no aggregations will be designed.

PartitioningKey

Sysname

NULL

 

Specifies the name of the column in the table defined in PartitionKeySource that will be used to partition data in the cube. This column is used by auto-partitioning if the AutoPartition bit is set to 1.

PartitionKeySource

Sysname

NULL

 

Specifies the name of the table that provides partition key values.

PartitionSize

Bigint

NULL

Validate that PartitionSize is greater than zero (0).

Specifies the key range that will be covered by the next partition added to the cube by auto-partitioning.

ProcessingEnabled

bit

NOT NULL

1

If True (1), the cube will be processed. If False (0), the cube will not be processed.

ProcessingKey

Sysname

NULL

 

Specifies the name of the column in the table defined in ProcessingKeySource that will be used to identify new rows that have been added to this table. This column is used by incremental processing if the IncEnabled bit is set to 1.

ProcessingKeySource

Sysname

NULL

 

Specifies the name of the table that provides processing key values.

ProcessingPass

int

NOT NULL

0

The Report preparation DTS task groups cube processing operations into separate passes. All cubes in one pass are processed before cubes in the next pass are processed.

See Also

Other Resources

ETL Processing for DTS Tasks