Archive reporting tables

Project archiving allows you to generate reports on historical data. Archiving is the process of copying historical data into new database tables, specifically so that you can generate reports and find answers to your questions.

The archive data store contains information about projects, tasks, and scoping, which is maintained indefinitely. You can write complex, dynamic reports answering questions such as:
  • How many projects were created in each of the last 12 months?
  • What was the average time for a translation project?
  • Which vendors had the fastest turnaround time?
  • How many active projects and tasks were there on each day over the last two months?
During the life of an active task, the workflow engine sends a message to start copying task-related information to an archive table. Until the General Cleaner deletes the original task information, it will appear in two places—in its original table and in the archive table. This also means that an active project with at least one completed task appears in the archive. Each project is updated as its tasks are completed. The archive tables are mostly mirror copies of the existing tables. The archive tables have some of the same columns as the main WorldServer tables, but some client-related data, such as the client ID and the name, are not available in the reporting schema.

In summary, the archive tables provide historical information about work that has been performed in WorldServer. Initial project information is added to these tables when projects are created; task information is added after the successful completion of each task. WorldServer does not remove Information from the archive tables automatically. These tables will store historical information indefinitely, unless end users remove old information from them.

Useful notes

Note the following while working with tasks and archiving:
  • When a task is canceled, it remains in the tasks table until garbage collection runs.
  • When a task is completed, it also remains in the tasks table for at least 30 days (by default). After 30 days, it can be removed during garbage collection.

    It does not matter if a task is canceled or completed; it always remains in the tasks table. The General Cleaner removes it from the tasks table after a configured number of days. The TaskDeleteAfterDays property determines the amount of time a task remains in the tasks table.

  • Tasks are always archived after they are completed successfully, even if their corresponding projects are not completed. Therefore, a completed task can be both in the tasks table and in the archiveTasks table for a certain period of time, until garbage collection runs.
  • Archive tables are never included in garbage collection.

    Use the spDeleteArchive stored procedure to clean up archive tables manually.

archiveProjectGroups

Historical information about WorldServer project groups:
ColumnTypeDescription
projectGroupIdintegerThe internal ID of the project group. Primary key
namestringThe name of the project group.
descriptionstringThe description of the project group (optional).
workgroupIdintegerThe internal ID of the workgroup with which the project group is associated.
workgroupNamestringThe name of the workgroup with which the project group is associated.
creationDatedateThe date when the project group was created.
dueDatedateThe date when the project group is due. This field is currently set only for projects created through TransPort. Reports should probably not depend on this and should use the due dates of individual projects instead.
completionDatedateThe date when the project group was completed. Not set if the project group is still active.
creatorIdintegerThe internal ID of the user who created the project group.
creatorNamestringThe name of the user who created the project group.
typeintegerIgnored.
sourceLocaleIdintegerThe internal ID of the source locale of the project group.
sourceLocalestringThe name of the source locale of the project group.

archiveProjects

Historical information about WorldServer projects:
ColumnTypeDescription
projectIdintegerThe internal ID of the project. Primary key.
namestringThe name of the project.
descriptionstringThe description of the project (optional).
localeIdintegerThe internal ID of the target locale of the project.
localeNamestringThe name of the target locale of the project.
workgroupIdintegerThe internal ID of the workgroup with which the project is associated.
workgroupNamestringThe name of the workgroup with which the project is associated.
creationDatedateThe date when the project was created.
dueDatedateThe date when the project is due (optional).
completionDatedateThe date when the project was completed. Not set if the project is still active.
creatorIdintegerThe internal ID of the user who created the project.
creatorNamestringThe user name of the user who created the project.
typeintegerIgnored.
lastTaskNumintegerThe task number given to the last task in the project. This is ordinarily the same as the number of tasks in the project.
projectGroupIdintegerA foreign key into the archiveProjectGroups table, identifying the project group that this project is a part of.
activeTasksintegerThe number of active tasks in the project.
completedTasksintegerThe number of completed tasks in the project.
canceledTasksintegerThe number of canceled tasks in the project.
totalTasksintegerThe total number of tasks in the project, regardless of their status.
rfqIdintegerThe internal ID of the quote for this project.
rfqNamestringThe name of the quote for this project.
clientIdintegerThe internal ID of the client for this project.
clientNamestringThe name of the client for this project.
projectTypeIdintegerThe internal ID of the project type for this project.
projectTypeNamestringThe name of the project type for this project.
costdoubleThe total cost for the project, as calculated by applying the associated cost model. Not set until the project is completed.
adjustmentdoubleA manual adjustment to the project cost (optional).
vendorNotesstringVendor notes related to the cost adjustment (optional).
customerNotesstringCustomer notes related to the cost adjustment (optional).
costModelArchiveIdintegerA foreign key into the archiveCostModels table, indicating the cost model associated with this project.
reviewModelArchiveIdintegerA foreign key into the reviewModelArchive table, indicating the review model associated with this project.

archiveCostModels

Historical information about the translation costs associated with WorldServer projects:
ColumnTypeDescription
costModelArchiveIdintegerThe internal ID of the cost model. Primary key.
nameintegerThe name of the cost model.
icePricedoubleThe price of translating each ICE match word.
repeititionPricedoubleThe price of translating each repetition word.
perfectPricedoubleThe price of translating each word with a perfect (100%) TM match.
range1PricedoubleThe price of translating the highest fuzzy match range (for example, 90-100%).
range1MinintegerThe lower boundary of the highest fuzzy match range (for example, 90).
range2PricedoubleThe price of translating the second fuzzy match range (for example, 80-90%). Not set if there are fewer than two fuzzy match ranges in the associated scoping configuration.
range2MinintegerThe lower boundary of the second fuzzy match range (for example, 80). Not set if there are fewer than two fuzzy match ranges in the associated scoping configuration.
range3PricedoubleThe price of translating the third fuzzy match range (for example, 70-80%). Not set if there are fewer than three fuzzy match ranges in the associated scoping configuration.
range3MinintegerThe lower boundary of the third fuzzy match range (for example, 80). Not set if there are fewer than three fuzzy match ranges in the associated scoping configuration.
range4PricedoubleThe price of translating the fourth fuzzy match range (for example, 60-70%). Not set if there are fewer than four fuzzy match ranges in the associated scoping configuration.
range4MinintegerThe lower boundary of the fourth fuzzy match range (for example, 60). Not set if there are fewer than four fuzzy match ranges in the associated scoping configuration.
range5PricedoubleThe price of translating the fifth fuzzy match range (for example, 0-60%). Not set if there are fewer than five fuzzy match ranges in the associated scoping configuration.
range5MinintegerThe lower boundary of the fifth fuzzy match range (for example, 0). Not set if there are fewer than five fuzzy match ranges in the associated scoping configuration.
vendorIdintegerThe internal ID of the vendor associated with the cost model (optional).
vendorNamestringThe name of the vendor associated with the cost model (optional).
scopingConfigIdintegerThe internal ID of the scoping configuration associated with the cost model.
scopingConfigNamestringThe name of the scoping configuration associated with the cost model.
currencystringThe three-letter currency code for the currency that the prices in this cost model are listed in (for example, USD).

archiveLineItems

Historical information about special charges or discounts on projects, as calculated by cost model rules:
ColumnTypeDescription
lineItemIdintegerThe internal ID of the line item. Primary key.
costModelArchiveIdintegerA foreign key into the archiveCostModels table, indicating the cost model to which the line item applies.
namestringThe name of the line item.
descriptionstringThe description of the line item.
costdoubleThe additional charge (or discount, if negative) associated with the line item.

archiveTasks

Historical information about successfully completed WorldServer tasks:
ColumnTypeDescription
taskIdintegerThe internal ID of the task. Primary key.
projectIdintegerA foreign key into the archiveProjects table, indicating the project to which the task belongs.
workflowIdintegerThe internal ID of the workflow that the task is flowing through.
workflowNamestringThe name of the workflow that the task is flowing through.
stepCountintegerThe ordinal step number of the currently active task step.
parentIdintegerIf this task is part of a sub-workflow, this is the taskId of the task in the parent workflow that spawned this one. Otherwise, not set.
parentStepCountintegerIf this task is part of a sub-workflow, this is the step number of the currently active task step in the parent workflow. Otherwise, 0.
statusintegerIndicates the last event that occurred with this task. One of the following values:
  • 0 - Not started
  • 1 - In progress, successful so far
  • 2 - In progress, with failures
  • 3 - Completed successfully
  • 4 - Failed
  • 5 - Canceled
taskNumInProjectintegerThe ordinal number of the task in the associated project.
creationDatedateThe date when the task was created.
dueDatedateThe date when the task is due (optional).
expectedDurationintegerThe number of hours the task is expected to take to complete (optional).
completionDatedateThe date when the task was completed.
currentTaskStepIdintegerA foreign key into the archiveTaskSteps table indicating which task step is currently active.
priorityintegerThe priority of the task.
includeInCostbooleanWhether or not the cost of the task should be included in the total cost of the project.
sourceAssetstringThe AIS Path of the source asset.
targetAssetstringThe AIS Path of the target asset.
typeintegerIgnored.
costdoubleThe cost of the task, based on applying the associated cost model. Not set if there was no associated cost model or if the task never caused an asset to be segmented.
mtWordCountintegerThe number of words processed by the task with an MT match.
totalWordCountintegerThe total number of words processed by this task. Not set if the task never caused an asset to be segmented.
iceWordCountintegerThe number of words processed by the task with a TM ICE match. Not set if the task never caused an asset to be segmented.
perfectWordCountintegerThe number of words processed by the task with a 100% TM match. Not set if the task never caused an asset to be segmented.
repetitionWordCountintegerThe number of words processed by the task with a TM repetition. Not set if the task never caused an asset to be segmented.
range1WordCountintegerThe number of words in the highest fuzzy match range (for example, 90-100%). Not set if the task never caused an asset to be segmented.
range1MinintegerThe lower boundary of the highest fuzzy match range (for example, 90).
range2WordCountintegerThe number of words in the second fuzzy match range (for example, 80-90%). Not set if the task never caused an asset to be segmented.
range2MinintegerThe lower boundary of the second fuzzy match range (for example, 80). Not set if there are fewer than two fuzzy match ranges in the associated scoping configuration.
range3WordCountintegerThe number of words in the third fuzzy match range (for example, 70-80%). Not set if the task never caused an asset to be segmented.
range3MinintegerThe lower boundary of the third fuzzy match range (for example, 70). Not set if there are fewer than three fuzzy match ranges in the associated scoping configuration.
range4WordCountintegerThe number of words in the fourth fuzzy match range (for example, 60-70%). Not set if the task never caused an asset to be segmented.
range4MinintegerThe lower boundary of the fourth fuzzy match range (for example, 60). Not set if there are fewer than four fuzzy match ranges in the associated scoping configuration.
range5WordCountintegerThe number of words in the fifth fuzzy match range (for example, 0-60%). Not set if the task never caused an asset to be segmented.
range5MinintegerThe lower boundary of the fifth fuzzy match range (for example, 0). Not set if there are fewer than five fuzzy match ranges in the associated scoping configuration.
costModelIdintegerA foreign key into the archiveCostModels table. Indicates the cost model associated with the task. This is equivalent to archiveProjects.costModelArchiveId.
reviewModelIdintegerThe internal ID of the review model for this task.
creatorNamestringThe user name of the user who created the task.
scopingConfigIdintegerThe internal ID of the associated scoping configuration.
scopingConfigNamestringThe name of the associated scoping configuration.

archiveTaskSteps

Historical information about each step that was completed during task processing:
ColumnTypeDescription
taskStepIdintegerThe internal ID of the task step. Primary key.
taskIdintegerA foreign key into the archiveTasks table, indicating the task to which the task step belongs.
workflowStepIdintegerThe internal ID of the workflow step that the task step corresponds to.
claimaintIdintegerThe internal ID of the user who has claimed the task step.
claimantNamestringThe user name of the user who has claimed the task step.
statusintegerOne of the following values:
  • 1: The task step has not yet started.
  • 2: The task step is in progress.
  • 3: The task step has been completed.
  • 4: The task step has project scope and is waiting to execute.
  • 5: The task step has project scope and is in progress.
compTransitionIdintegerThe internal ID of the transition taken from this step when it was completed.
transitionNamestringThe name of the transition taken from this step when it was completed.
creationDatedateThe date when the task step was created.
dueDatedateThe date when the task step is due (optional).
expectedDurationintegerThe number of hours the task step is expected to take to complete (optional).
startDatedateThe date when the task step began executing.
completionDatedateThe date when the task step was completed.
vendorIdintegerThe internal ID of the vendor to whom the step is assigned.
vendorNamestringThe name of the vendor to whom the step is assigned.
stepNamestringThe name of the step (for example, Translate).
stepTypestringThe type of the step (Start, Finish, Human, Automatic, Sub-Workflow, Parallel Review, Automatic Error, or Cancel).

archiveTaskHistoryEntries

A record of relevant events with time stamps for historical WorldServer tasks:
ColumnTypeDescription
taskHistEntryIdintegerThe internal ID of the history entry. Primary key.
taskIdintegerA foreign key into the archiveTasks table, indicating the task to which the history entry applies.
theDateDateThe date of an event.
eventTypeintegerThe event that occurred. One of the following values.
  • 1: Task started
  • 2: Task completed
  • 3: Task canceled
  • 4: Task Step claimed
  • 5: Task Step executed
  • 6: Task Step completed
  • 7: Task Step un-claimed
  • 8: Sub-task started
  • 9: Sub-task completed
  • 10: Task assignees modified
  • 11: Task assets exported
  • 12: Task assets imported
  • 13: Task branched
  • 14: Sub-task canceled
userIdintegerThe unique ID of the user who performed the event, if relevant.
theCommentstringA user-provided comment for the event (optional).

archiveCustomProjectAttributes

Custom attributes and values for historical WorldServer projects. Attributes with multiple values will have multiple rows in this table for the same attribute and project:
ColumnTypeDescription
projectIdintegerA foreign key into the archiveProjects table, indicating the Project whose custom attribute this is.
namestringThe name of the custom attribute.
uiNamestringThe UI display name of the custom attribute.
valueStringstringThe string value of the attribute.
valueDatedateThe attribute value as a date. Not set for non-date-value attributes.
valueNumberintegerThe numeric value of the attribute. Not set for non-numeric attributes.
valueClobCLOBThe attribute value, for attributes that require large amounts of storage (Large Text Area, Comment, HTML attributes).

archiveCustomTaskAttributes

Custom attributes and values for historical WorldServer tasks. Attributes with multiple values will have multiple rows in this table for the same attribute and task:
ColumnTypeDescription
taskIdintegerA foreign key into the archiveTasks table, indicating the task whose custom attribute this is.
namestringThe name of the custom attribute.
uiNamestring.The UI display name of the custom attribute
valueStringstringThe string value of the attribute.
valueDatedateThe attribute value as a date. Not set for non-date-value attributes.
valueNumberintegerThe numeric value of the attribute. Not set for non-numeric attributes.
valueClobCLOBThe attribute value, for attributes that require large amounts of storage (Large Text Area, Comment, HTML attributes).

archiveCustomCostModelAttributes

Custom attributes and values for historical WorldServer cost models. Attributes with multiple values will have multiple rows in this table for the same attribute and cost model:
ColumnTypeDescription
costModelArchiveIdintegerA foreign key into the archiveCostModels table, indicating the cost model whose custom attribute this is.
namestringThe name of the custom attribute.
uiNamestringThe UI display name of the custom attribute.
valueStringstringThe string value of the attribute.
valueDatedateThe attribute value as a date. Not set for non-date-value attributes.
valueNumberintegerThe numeric value of the attribute. Not set for non-numeric attributes.
valueClobCLOBThe attribute value, for attributes that require large amounts of storage (Large Text Area, Comment, HTML attributes).

archiveCustomVendorAttributes

Custom attributes and values for historical WorldServer vendors. Attributes with multiple values will have multiple rows in this table for the same attribute and vendor:
ColumnTypeDescription
vendorIdintegerA foreign key matching the vendorId column in the archiveCostModels table, indicating the vendor whose custom attribute this is.
archiveVendorAttributeIdintegerA unique ID.
namestringThe name of the custom attribute.
uiNamestringThe UI display name of the custom attribute.
valueStringstringThe string value of the attribute.
valueDatedateThe attribute value as a date. Not set for non-date-value attributes.
valueNumberintegerThe numeric value of the attribute. Not set for non-numeric attributes.
valueClobCLOBThe attribute value, for attributes that require large amounts of storage (Large Text Area, Comment, HTML attributes).

periodicWorkStatus

A record of WorldServer activity over time. This table is populated by the special PWS_TASK recurrence, which records periodic snapshots of activity on a schedule:
ColumnTypeDescription
timedateThe timestamp when the snapshot was taken.
activeProjectsintegerThe number of projects active when the snapshot was taken.
activeTasksintegerThe number of tasks active when the snapshot was taken.
wordsInActiveTasksintegerThe number of words in assets associated with active tasks when the snapshot was taken.

archiveReviewModels

ColumnTypeDescription
reviewModelArchiveIdintegerPrimary key.
reviewModelIdintegerThe internal ID of the review model.
namestringThe name of the review model.
descriptionstringThe description of the review model.

archiveReviewModelData

ColumnTypeDescription
reviewModelArchiveIdintegerPrimary key.
dataTypeintegerThe data type of the review model.
datastringThe data for the review model.
displayOrderintegerOrder in display.

archiveTaskReviewErrors

ColumnTypeDescription
archivedTaskIdintegerPrimary key and foreign key.
errorSeveritystringPrimary key. Severity of error.
errorTypestringPrimary key. The data for the review model.
errorCountintegerNumber of errors.

Archive reporting schema diagram

The following diagram shows the relationships between archive reporting tables:A diagram that shows the relationships between archive reporting tables.