Table MF_IssueTrackingTicket

Card of table MF_IssueTrackingTicket

Name

MF_IssueTrackingTicket

Dimensional Type

Fact

Comment

Houses data on tickets entered into Carleton College's issue-tracking system(s). As of 2010 data from Web Help Desk is all that's represented here.

Data here comes in at the grain of one row per generated trouble ticket. Mainly this table is used to track ticket counts and resolution times by various criteria.


Check constraint name of the table MF_IssueTrackingTicket

CKT_MF_ISSUETRACKINGTICKET


List of outgoing references of the table MF_IssueTrackingTicket

Name

Parent Table

Foreign Key Columns

Relationship_115

MD_IssueTrackingAsset

MD_ITAsset_SurrogateKey

Relationship_116

MD_CarletonDepartment (Shortcut)

MD_CarlDept_SurrogateKey

Relationship_117

MD_IssueTrackingStatus

MD_ITStatus_SurrogateKey

Relationship_118

MD_IssueTrackingTechGroup

MD_ITTechGroup_SurrogateKey

Relationship_119

MD_IssueTrackingPriority

MD_ITPriority_SurrogateKey

Relationship_120

MD_Building (Shortcut)

MD_Building_SurrogateKey

Relationship_121

MD_Room (Shortcut)

MD_Room_SurrogateKey

Relationship_122

MD_DegreeStudent (Shortcut)

MD_CollPers_SurrogateKeyDStu

Relationship_123

MD_Employee (Shortcut)

MD_CollPers_SurrogateKeyEmp

Relationship_124

MD_IssueTrackingProblemType

MD_ITProblemType_SurrogateKey

Relationship_ClientToTicket

MD_IssueTrackingUser

MD_ITUser_SurrogateKeyClient

Relationship_CloseDate

MD_Date (Shortcut)

MF_ITTicket_CloseDate

Relationship_CloseTime

MD_Time (Shortcut)

MF_ITTicket_CloseTime

Relationship_FirstResponseDate

MD_Date (Shortcut)

MF_ITTicket_FirstResponseDate

Relationship_FirstResponseTime

MD_Time (Shortcut)

MF_ITTicket_FirstResponseTime

Relationship_LoggedByToTicket

MD_IssueTrackingTech

MD_ITUser_SurrogateKeyLoggedBy

Relationship_ReportDate

MD_Date (Shortcut)

MF_ITTicket_ReportDate

Relationship_ReportTime

MD_Time (Shortcut)

MF_ITTicket_ReportTime

Relationship_TechToTicket

MD_IssueTrackingTech

MD_ITTech_SurrogateKey

Relationship_WorkEndDate

MD_Date (Shortcut)

MF_ITTicket_WorkEndDate

Relationship_WorkEndTime

MD_Time (Shortcut)

MF_ITTicket_WorkEndTime

Relationship_WorkStartDate

MD_Date (Shortcut)

MF_ITTicket_WorkStartDate

Relationship_WorkStartTime

MD_Time (Shortcut)

MF_ITTicket_WorkStartTime


List of referencing views of the table MF_IssueTrackingTicket

Name

Code

VMF_IssueTrackingTicket

VMF_ISSUETRACKINGTICKET


List of diagrams containing the table MF_IssueTrackingTicket

Name

IssueTrackingDiagram


List of columns of the table MF_IssueTrackingTicket

Name

Comment

Domain

Data Type

Length

MF_ITTicket_ID

Degenerate dimension housing the unique key for a given trouble ticket in a given help desk system. See MD_ITTicket_IssueTrackingSystem attribute for the generic name of the system. This also is a degenerate dimension.

DW_CodeDesc

varchar(32)

32

MF_ITTicket_IssueTrackingSystem

Name of system from which the MF_ITTicket_TicketID was extracted.

DW_CommonName

nvarchar(64)

64

MD_ITStatus_SurrogateKey

 

<None>

numeric

 

MD_ITTech_SurrogateKey

 

<None>

numeric

 

MD_ITPriority_SurrogateKey

 

<None>

numeric

 

MD_ITProblemType_SurrogateKey

 

<None>

numeric

 

MD_ITTechGroup_SurrogateKey

 

<None>

numeric

 

MD_ITAsset_SurrogateKey

 

<None>

numeric

 

MD_Room_SurrogateKey

 

<None>

numeric

 

MD_Building_SurrogateKey

Foreign key pointing to a building in MD_Building. Note that Web Help Desk doesn't have buildings per se. Buildings are inferred, in general, from rooms.

<None>

numeric

 

MD_CarlDept_SurrogateKey

 

<None>

numeric

 

MD_CollPers_SurrogateKeyEmp

 

<None>

numeric

 

MD_CollPers_SurrogateKeyDStu

 

<None>

numeric

 

MD_ITUser_SurrogateKeyClient

 

<None>

numeric

 

MD_ITUser_SurrogateKeyLoggedBy

 

<None>

numeric

 

MF_ITTicket_ColleagueIDDStu

Colleague ID (if available) of degree student that a trouble ticket is associated with.

DW_ColleagueID

char(7)

7

MF_ITTicket_ColleagueIDEmp

Colleague ID (if available) of employee that a trouble ticket is associated with.

DW_ColleagueID

char(7)

7

MF_ITTicket_ReportDate

Integer representation of the date (e.g., 20070101 for 1 Jan 2007). Defaults to 99991231, for situations where a date is required but none is available.

DW_DateInteger

int

 

MF_ITTicket_ReportTime

Integer representing the time of day in the format HHMMSS (e.g., 23:01:01 at one minute one second after eleven o'clock in the evening). Not typically used for reporting.

Unlike MD_Date_Dateinteger (in MD_Date), this integer is not really human-readable, because it's not set up for base-10. Rather, it's calculated using the formula: (hour << 16) Or (min << 8) Or (sec), where the Or's are logical, and the << operator is a binary left shift.

<None>

int

 

MF_ITTicket_FirstResponseDate

Integer representation of the date (e.g., 20070101 for 1 Jan 2007). Defaults to 99991231, for situations where a date is required but none is available.

DW_DateInteger

int

 

MF_ITTicket_FirstResponseTime

Integer representing the time of day in the format HHMMSS (e.g., 23:01:01 at one minute one second after eleven o'clock in the evening). Not typically used for reporting.

Unlike MD_Date_Dateinteger (in MD_Date), this integer is not really human-readable, because it's not set up for base-10. Rather, it's calculated using the formula: (hour << 16) Or (min << 8) Or (sec), where the Or's are logical, and the << operator is a binary left shift.

<None>

int

 

MF_ITTicket_WorkStartDate

Integer representation of the date (e.g., 20070101 for 1 Jan 2007). Defaults to 99991231, for situations where a date is required but none is available.

DW_DateInteger

int

 

MF_ITTicket_WorkStartTime

Integer representing the time of day in the format HHMMSS (e.g., 23:01:01 at one minute one second after eleven o'clock in the evening). Not typically used for reporting.

Unlike MD_Date_Dateinteger (in MD_Date), this integer is not really human-readable, because it's not set up for base-10. Rather, it's calculated using the formula: (hour << 16) Or (min << 8) Or (sec), where the Or's are logical, and the << operator is a binary left shift.

<None>

int

 

MF_ITTicket_WorkEndDate

Integer representation of the date (e.g., 20070101 for 1 Jan 2007). Defaults to 99991231, for situations where a date is required but none is available.

DW_DateInteger

int

 

MF_ITTicket_WorkEndTime

Integer representing the time of day in the format HHMMSS (e.g., 23:01:01 at one minute one second after eleven o'clock in the evening). Not typically used for reporting.

Unlike MD_Date_Dateinteger (in MD_Date), this integer is not really human-readable, because it's not set up for base-10. Rather, it's calculated using the formula: (hour << 16) Or (min << 8) Or (sec), where the Or's are logical, and the << operator is a binary left shift.

<None>

int

 

MF_ITTicket_CloseDate

Integer representation of the date (e.g., 20070101 for 1 Jan 2007). Defaults to 99991231, for situations where a date is required but none is available.

DW_DateInteger

int

 

MF_ITTicket_CloseTime

Integer representing the time of day in the format HHMMSS (e.g., 23:01:01 at one minute one second after eleven o'clock in the evening). Not typically used for reporting.

Unlike MD_Date_Dateinteger (in MD_Date), this integer is not really human-readable, because it's not set up for base-10. Rather, it's calculated using the formula: (hour << 16) Or (min << 8) Or (sec), where the Or's are logical, and the << operator is a binary left shift.

<None>

int

 

MF_ITTicket_TicketTimeAsOfLastUpdate

Integer reflecting the amount of time a given ticket has been open as of the last update, in minutes.

Will be set to zero if a ticket has been deleted.

<None>

int

 

MF_ITTicket_ElapsedTime

Integer representing the amount of time elapsed, in minutes, between when a ticket was initially set up or reported (MD_ITTicket_ReportDate) and either now, or time ticket close datetime, whichever is less (earlier in time). If a ticket was deleted, it is marked as MF_ITTicket_IsDeleted = 'Yes', and this field is set to zero.

This figure is naturally current only as of the last time this fact table was updated.

The elapsed time does not take into account periods where a ticket is idling. For that, see MF_ITTicket_TicketTimeAsOfLastUpdate.

<None>

int

 

MF_ITTicket_IsDeleted

Degenerate dimension. 'Yes' if a ticket has been deleted. 'No' otherwise.

Having this flag allows us not to actually delete deleted tickets. Instead we simply mark them as deleted (as Web Help Desk does). When a ticket is deleted, though, we do set the TicketTime and TicketTimeAsOfLastUpdate to zero.

DW_YesOrNoNOTNULL

char(3)

3

MF_ITTicket_AuditKey

 

DW_AuditKey

bigint

 

MF_ITTicket_Timestamp

 

<None>

timestamp

 


List of indexes of the table MF_IssueTrackingTicket

Name

Unique

Cluster

Primary

Foreign Key

Alternate Key

Table

MF_ISSUETRACKINGTICKET_PK

TRUE

FALSE

TRUE

FALSE

FALSE

MF_IssueTrackingTicket

RELATIONSHIP_LOGGEDBYTOTICKET_FK

FALSE

FALSE

FALSE

TRUE

FALSE

MF_IssueTrackingTicket

RELATIONSHIP_CLIENTTOTICKET_FK

FALSE

FALSE

FALSE

TRUE

FALSE

MF_IssueTrackingTicket

RELATIONSHIP_TECHTOTICKET_FK

FALSE

FALSE

FALSE

TRUE

FALSE

MF_IssueTrackingTicket

RELATIONSHIP_115_FK

FALSE

FALSE

FALSE

TRUE

FALSE

MF_IssueTrackingTicket

RELATIONSHIP_116_FK

FALSE

FALSE

FALSE

TRUE

FALSE

MF_IssueTrackingTicket

RELATIONSHIP_117_FK

FALSE

FALSE

FALSE

TRUE

FALSE

MF_IssueTrackingTicket

RELATIONSHIP_118_FK

FALSE

FALSE

FALSE

TRUE

FALSE

MF_IssueTrackingTicket

RELATIONSHIP_119_FK

FALSE

FALSE

FALSE

TRUE

FALSE

MF_IssueTrackingTicket

RELATIONSHIP_120_FK

FALSE

FALSE

FALSE

TRUE

FALSE

MF_IssueTrackingTicket

RELATIONSHIP_121_FK

FALSE

FALSE

FALSE

TRUE

FALSE

MF_IssueTrackingTicket

RELATIONSHIP_122_FK

FALSE

FALSE

FALSE

TRUE

FALSE

MF_IssueTrackingTicket

RELATIONSHIP_123_FK

FALSE

FALSE

FALSE

TRUE

FALSE

MF_IssueTrackingTicket

RELATIONSHIP_124_FK

FALSE

FALSE

FALSE

TRUE

FALSE

MF_IssueTrackingTicket

RELATIONSHIP_REPORTDATE_FK

FALSE

FALSE

FALSE

TRUE

FALSE

MF_IssueTrackingTicket

RELATIONSHIP_FIRSTRESPONSEDATE_FK

FALSE

FALSE

FALSE

TRUE

FALSE

MF_IssueTrackingTicket

RELATIONSHIP_WORKSTARTDATE_FK

FALSE

FALSE

FALSE

TRUE

FALSE

MF_IssueTrackingTicket

RELATIONSHIP_WORKENDDATE_FK

FALSE

FALSE

FALSE

TRUE

FALSE

MF_IssueTrackingTicket

RELATIONSHIP_CLOSEDATE_FK

FALSE

FALSE

FALSE

TRUE

FALSE

MF_IssueTrackingTicket

RELATIONSHIP_REPORTTIME_FK

FALSE

FALSE

FALSE

TRUE

FALSE

MF_IssueTrackingTicket

RELATIONSHIP_FIRSTRESPONSETIME_FK

FALSE

FALSE

FALSE

TRUE

FALSE

MF_IssueTrackingTicket

RELATIONSHIP_WORKSTARTTIME_FK

FALSE

FALSE

FALSE

TRUE

FALSE

MF_IssueTrackingTicket

RELATIONSHIP_WORKENDTIME_FK

FALSE

FALSE

FALSE

TRUE

FALSE

MF_IssueTrackingTicket

RELATIONSHIP_CLOSETIME_FK

FALSE

FALSE

FALSE

TRUE

FALSE

MF_IssueTrackingTicket


List of keys of the table MF_IssueTrackingTicket

Name

Identifier_1


Previous
Home
Next