Contains the information of open and closed cases in the ASMS suite - DataWareHouse
Data model
Fields
| Field | Type | Description |
|---|---|---|
| applicant_id | INT | Case requester ID, reference table AFW_USERS.user_id |
| applicant_name | NVARCHAR (1020) | Case Applicant’s Name |
| author_email | NVARCHAR (510) | Email of the user who created the case |
| author_id | INT | Identifier of the user who created the case, references the table AFW_USERS.user_id |
| author_name | NVARCHAR (510) | Case Author Name |
| available_cost | BIT | Indicates that the case has an available cost quota |
| available_count | BIT | Indicates that the case has an available count quota |
| available_effort | BIT | Indicates that the case has an available effort quota |
| available_price | BIT | Indicates that the case has an available price quota |
| calendar_ola_id | INT | OLA calendar identifier, reference table ASMS_CALENDAR. CALENDAR_ID |
| calendar_ola_name | NVARCHAR (510) | Name of the OLA calendar associated with the case |
| calendar_sla_id | INT | SLA calendar identifier, reference the ASMS_CALENDAR table. CALENDAR_ID |
| calendar_sla_name | NVARCHAR (510) | Name of the SLA calendar associated with the case |
| calendar_uc_id | INT | UC calendar identifier, reference table ASMS_CALENDAR. CALENDAR_ID |
| calendar_uc_name | NVARCHAR (510) | Name of the UC calendar associated with the case |
| category_hierarchy | NVARCHAR (510) | Case category hierarchy |
| category_id | INT | Case category identifier, reference table ASDK_CATEGORY.ctg_index |
| category_name | NVARCHAR (510) | Case Category Name |
| cause | NVARCHAR (MAX) | Case Cause |
| cause_nohtml | NVARCHAR (MAX) | Case Cause Without HTML Elements |
| ci_asset_tag | NVARCHAR (510) | Property label of the CI associated with the case |
| ci_id | INT | Identifier of the configuration element (CI) associated with the case, references the CMDB_CI.id table |
| ci_name | NVARCHAR (510) | Name of the IC associated with the case |
| ci_serial | NVARCHAR (510) | Serial number of the IC associated with the case |
| closed_date | DATETIME | Date the case was closed |
| closed_date_sk | INT | Reference to the Case Closing Date Dimension |
| commentary | NVARCHAR (MAX) | Case Solution |
| commentary_nohtml | NVARCHAR (MAX) | Case solution without HTML elements |
| company_id | INT | Company identifier for the case, reference table ASMS_COMPANY. Id |
| company_name | NVARCHAR (510) | Name of the company assigned to the case |
| contract_id | INT | Identifier of the associated contract, reference to table ASMS_CONTRACT. ContractId |
| cost | FLOAT | Case Cost Value |
| cost_center_id | INT | Cost center identifier for the case, reference to table ASMS_COST_CENTER.id |
| cost_model_id | INT | Cost model identifier for the case, reference to table ASDK_MODEL_COST_PRICE.mod_id |
| created_date | DATETIME | Date the case was created |
| current_progress | FLOAT | Current Progress of the Case |
| current_time | FLOAT | Time elapsed during the case |
| customer_id | INT | Client ID assigned to the case, reference the AFW_USERS.user_id table |
| customer_name | NVARCHAR (510) | Case Client Name |
| description | NVARCHAR (MAX) | Case description |
| description_nohtml | NVARCHAR (MAX) | Description of the case without HTML elements |
| effort | FLOAT | Recorded effort to attend to the case |
| estimated_date | DATETIME | Estimated date of the case model primary timer |
| estimated_time | FLOAT | Estimated time of the main timer of the case model |
| group_id | INT | Group ID for the case, reference the table AFW_GROUPS.grou_id |
| group_name | NVARCHAR (510) | Case Group Name |
| id* | INT | Case ID |
| id_by_project* | NVARCHAR (512) | Case identifier by project |
| impact_id | INT | Impact identifier for the case, reference table AFW_CATALOG_CODE.code_id |
| impact_name | NVARCHAR (510) | Case Impact Name |
| income_center_id | INT | Revenue center ID for the case, reference to ASMS_COST_CENTER.id table |
| integration_id | INT | Identifier of the external system to which the case belongs, references the ASMS_INTEGRATIONS.id table |
| interface_id | NVARCHAR (200) | Identifier of the source system associated with the case, this is given by integration |
| location_id | INT | Identifier of the associated location, reference to the ASMS_LOCATION table. Id |
| model_id | INT | Model ID for the case, reference to the ASMS_MODEL.id table |
| model_name | NVARCHAR (510) | Case Model Name |
| modified_date* | DATETIME | Date Case Changed |
| modifier_name | NVARCHAR (510) | Name of the last user who modified the case |
| note | NVARCHAR (MAX) | Note associated with the case |
| ola_id | INT | OLA identifier for the case, reference to table ASDK_SLA. SLA_ID |
| ola_name | NVARCHAR (510) | Name of the OLA of the case |
| opened_date | DATETIME | Date on which the case is opened |
| opened_date_sk | INT | Reference to Case Opening Date Dimension |
| previous_status_id | INT | Previous case status identifier, refer table ASMS_STATUS.stat_id |
| price | FLOAT | Case Price Value |
| priority_id | INT | Case priority identifier, reference table AFW_CATALOG_CODE.code_id |
| priority_name | NVARCHAR (510) | Case priority name |
| project_id | INT | Associated project ID, reference table ASMS_PROJECTS.proj_id |
| project_name | NVARCHAR (510) | Case Project Name |
| provider_id | INT | Supplier identifier for the case, reference to table ASMS_COMPANY. Id |
| provider_name | NVARCHAR (510) | Name of the provider associated with the case |
| real_date | DATETIME | Case Model Primary Timer Actual Date |
| reason_id | INT | Case reason identifier, reference table ASMS_STATUS_REASON.reas_id |
| reason_name | NVARCHAR (510) | Name of the reason for the case |
| receptor_id | INT | Identifier of the first specialist assigned to the case, reference the table AFW_USERS.user_id |
| receptor_name | NVARCHAR (510) | Name of the first user to whom the case was assigned |
| registry_type_id | INT | Record type identifier for the case, reference the AFW_CATALOG_CODE.code_id table |
| registry_type_name | NVARCHAR (510) | Case Record Type Name |
| responsible_id | INT | Identifier of the specialist responsible for the case, reference the table AFW_USERS.user_id |
| responsible_name | NVARCHAR (510) | Name of the person responsible for the case |
| rev | INT | Consecutive number of the current case review |
| service_id | INT | Service ID for the case, reference the ASDK_SERVICE.id table |
| service_name | NVARCHAR (510) | Case Service Name |
| sla_id | INT | SLA identifier for the case, reference to table ASDK_SLA. SLA_ID |
| sla_name | NVARCHAR (510) | Case Default SLA Name |
| solution_date_sk | INT | Reference to the Case Solution Date Dimension |
| state_final | INT | Identify whether the status is final or not. (1 = End) |
| state_id | INT | Status ID for the case, reference the ASMS_STATUS.stat_id table |
| state_initial | INT | Identify whether the state is initial or not. (1 = is initial) |
| state_name | NVARCHAR (510) | Case Status Name |
| status_color | NVARCHAR (510) | Current state color |
| subject | NVARCHAR (800) | Describe the subject of the case |
| survey_token | NVARCHAR (200) | Associated survey ID |
| type_id* | INT | Case type identifier, reference table ASMS_TYPES.id |
| type_name | NVARCHAR (510) | Case Type Name |
| uc_id | INT | UC ID for the case, reference to table ASDK_SLA. SLA_ID |
| uc_name | NVARCHAR (510) | Case UC Name |
| unit_id | INT | Identifier of the internal area associated with the case, reference the table AFW_GROUPS.grou_id |
| unit_name | NVARCHAR (510) | Name of the organizational area associated with the case |
| urgency_id | INT | Case urgency identifier, reference table AFW_CATALOG_CODE.code_id |
| urgency_name | NVARCHAR (510) | Name of the urgency of the case |
Primary Key
| PK_ASDK_ITEM_DW_LAST |
|---|
| id |