Dict-Physical

Table MD_IssueTrackingAsset

Card of table MD_IssueTrackingAsset

Name	MD_IssueTrackingAsset
Dimensional Type	Dimension
Comment	Generic dimension for holding assets identified in issue-tracking software. This dimension is not intended to replace a full 'asset' dimension used in facilities asset management. Rather, it simply holds a list of computers, UPSs, montors, etc. identified in an issue tracking system (e.g., WebHelpDesk). Note that there is an 'Unknown' asset, intended to make mandatory joins possible between this dimension and the MF_IssueTrackingTicket fact table. A future enhancement, it is hoped, will bring a new field containing the projected asset retirement date. This date would allow us to calculate the count and/or value of assets up for replacement in a given time period. And it would also allow us to calculate how we're doing retiring assets, by taking the difference between the projected retirement date and the actual disposal date (the latter being already available, as a custom field).

Check constraint name of the table MD_IssueTrackingAsset

CKT_MD_ISSUETRACKINGASSET

List of incoming references of the table MD_IssueTrackingAsset

Relationship_115

MF_IssueTrackingTicket

MD_ITAsset_SurrogateKey

Relationship_127

MF_IssueTrackingAssetValue

MD_ITAsset_SurrogateKey

Relationship_244

MF_KeyedComputerInUse

MD_ITAsset_SurrogateKey

Relationship_250

MF_KeyedComputerSoftwareUsage

MD_ITAsset_SurrogateKey

Relationship_269

MF_KeyedComputerEvent

MD_ITAsset_SurrogateKey

List of referencing views of the table MD_IssueTrackingAsset

VMD_IssueTrackingAsset

VMD_ISSUETRACKINGASSET

List of diagrams containing the table MD_IssueTrackingAsset

IssueTrackingDiagram

List of columns of the table MD_IssueTrackingAsset

MD_ITAsset_SurrogateKey

<None>

numeric

MD_ITAsset_ID

System-specific identifier for a particular asset identified in a trouble ticket/helpdesk submission. The system in which the identifier occurs is supplied in the MD_ITAsset_IssueTrackingSystem field.

This variable should not normally be exposed to users, who may confuse it with the CCID (MD_ITAsset_Number). The value of this variable is something that the ticket/asset tracking system does not expose, although it keeps it internally as a unique identifier.

DW_CodeDesc

varchar(32)

MD_ITAsset_IssueTrackingSystem

As of 2011, this variable is always set to 'Web Help Desk'.

If we bring in multiple asset or ticket-tracking systems, we will acquire alternate values here.

DW_CommonName

nvarchar(64)

MD_ITAsset_Number

This is the CCID, i.e., the Carleton College asset ID.

DW_CodeDesc

varchar(32)

MD_ITAsset_Model

Vendor-designated model name for a given asset. Hierarchizes below manufacturer. E.g., the manufacturer for a given asset might be 'Dell' and the model might be 'Optiplex 445-blah-blah'.

DW_CodeDescLong

varchar(64)

MD_ITAsset_VendorSerialNumber

Serial number provided by vendor for a piece of equipment. Defaults to 'Unknown'.

Not the same as our Carleton asset number (CCID).

DW_CodeDescLong

varchar(64)

MD_ITAsset_Type

General type of asset: Server, System, Monitor, etc.

DW_CodeDesc

varchar(32)

MD_ITAsset_SubType

Custom attribute created to allow us to flesh out the MD_ITAsset_Type attribute in Web Help Desk (WHD). For example, we have an asset type of 'Server' in WHD. But there are many kinds of servers, and not all are replaced on the same cycle. We therefore divide servers up into sub-types. The sub-types are recorded in this attribute.

DW_CodeDesc

varchar(32)

MD_ITAsset_Manufacturer

DW_CommonName

nvarchar(64)

MD_ITAsset_PurchaseYYYYMM

Note that this field (unlike MD_ITAsset_PurchaseDate) refers to the built-in purchase date field in the asset-tracking system. MD_ITAsset_PurchaseDate refers to the custom attribute defined by ITS. Normally this field should be hidden.

DW_CodeDesc

varchar(32)

MD_ITAsset_PurchaseFiscalYear

Fiscal year in which an asset was originally purchased. Note that the fiscal year runs from July 1 to June 30, and bears the name of the second year. E.g., the fiscal year running from 1 July 2011 until 30 Jun 2012 is 2012.

<None>

int

MD_ITAsset_YearsUntilReplacement

Number of years until an asset is due for replacement (or retirement). A simple integer.

This attribute is used, typically, to categorize assets by the replacement cycle we've put them on, theoretically. It is not normally updated after initial entry. So, e.g., if we purchase an asset in January of 2011, and we assign it an MD_ITAsset_YearsUntilReplacement of 3, we are saying that we intend to replace (or retire) an asset by January of 2014. This anticipated replacement/retirement date does not change routinely, although we may do batch updates periodically, if we change our overall replacement cycle policy as applied to a particular asset group or class.

<None>

int

MD_ITAsset_ReplacementFiscalYear

Fiscal year in which an asset was originally planned to be decomissioned/replaced, when first purchased. Note that the fiscal year runs from July 1 to June 30, and bears the name of the second year. E.g., the fiscal year running from 1 July 2011 until 30 Jun 2012 is 2012.

Normally this value is not changed once an asset is entered into the system. But occasionally, when we change replacement cycles, it will change.

<None>

int

MD_ITAsset_DisposalYYYYMM

YYYY-MM in which an asset was taken out of service and recycled or otherwise disposed of.

DW_CodeDesc

varchar(32)

MD_ITAsset_DisposalFiscalYear

Fiscal year in which an asset was disposed of (recycled, etc.).

<None>

int

MD_ITAsset_Location

Not well maintained as of summer 2011. In most cases, this attribute is set to 'Carleton'.

DW_CodeDesc

varchar(32)

MD_ITAsset_Room

Somewhat well maintained as of summer 2011. Cleaned up in August of 2011.

Most problematic are assets that are initially deployed in one location, but are moved.

Also problematic are equipment deployed to computer labs and to network closets. Rooms for such equipment have not traditionally been well maintained.

The room name here contains both a building identifier and a room number, e.g.: CMC*202.

DW_CodeDesc

varchar(32)

MD_ITAsset_Status

This attribute is typically used in filters, and will in fact typically be set to a single value: Deployed. Other possible values include: GONE, Repair, In Stock.

DW_CodeDescLong

varchar(64)

MD_ITAsset_Disposition

Subcategory of MD_ITAsset_Status. Used, generally, to tell us what happened to assets whose status is GONE.

DW_CodeDesc

varchar(32)

MD_ITAsset_CarletonCost

Discretized ('banded') version of original Carleton cost (purchase price, often with things factored in like initial maintenance/warranty).

DW_CodeDesc

varchar(32)

MD_ITAsset_CostCenter

ITS custom designation for where the money is coming from. Often this is a budget number (4 digits out of the 14 total digits that make up an account number).

At least as of 2012, a lot of logic is needed in order to make this field useful analytically, because of the inconsistent way in which data has been entered. Machinery should exist for making it more consistent - in the corresponding user-exposed view, VMD_TicketTrackingAsset.

DW_CodeDescLong

varchar(64)

MD_ITAsset_Purpose

This is where ITS labels things as being grant funded, startup-fund funded, etc.

DW_CodeDescLong

varchar(64)

MD_ITAsset_Exceptions

Applies mainly (exclusively?) to systems. Holds information like whether the system is a refreshing one (i.e., it resets to a base state after each use) or not. Use to eliminate/filter classes of machines from reports when they're not wanted or needed, like lab machines.

This is based on a custom field in Web Help Desk.

DW_CodeDescLong

varchar(64)

MD_ITAsset_MACAddress

Ethernet address; should be available for any hardware with an ethernet connection.

DW_CodeDesc

varchar(32)

MD_ITAsset_IPAddress

Hardware with a fixed IP address should have that address listed here.

DW_CodeDesc

varchar(32)

MD_ITAsset_IsActive

'Yes' if an asset's status is not GONE and the asset is not marked, internally to the asset/ticket tracking system, as deleted. 'No' otherwise.

DW_YesNoOrUnknown

varchar(7)

MD_ITAsset_Warranty

Typically not maintained as of 2011.

DW_CodeDesc

varchar(32)

MD_ITAsset_AuditKey

DW_AuditKey

bigint

MD_ITAsset_Timestamp

<None>

timestamp

List of indexes of the table MD_IssueTrackingAsset

MD_ISSUETRACKINGASSET_PK

TRUE

FALSE

MD_IssueTrackingAsset

MD_ISSUETRACKINGASSET_AK

TRUE

FALSE

TRUE

MD_IssueTrackingAsset

List of keys of the table MD_IssueTrackingAsset

Identifier_1

AltKey

Home