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. |