The UCSB Data Warehouse (http://wh.isc.ucsb.edu/) is a repository of university data from several campus transaction systems (general ledger, payroll, budget, equipment). The Data Warehouse allows users to perform flexible queries (search on any data element; search multiple months or years; construct complex search criteria; join data from different transaction systems) and create flexible reports (sort on any data element(s); put subtotals and totals on any data elements; group, pivot and chart results to best serve your needs).

Equipment data is updated weekly, on Saturday afternoons, usually by 5:00 P.M., the update incorporating all new data entered into CATS, either by Equipment Management or by department users, during the previous week.

There are now two ways to access information stored in the Data Warehouse: by using the Brio program (for which a limited number of named user licenses are available from the Data Warehouse project) or by using EZ Access (a web interface, no license required, with an unlimited number of users). Brio is the preferred tool for users who need to create their own queries or significantly modify existing queries and who use the Data Warehouse on a frequent, if not daily, basis; EZ Access is the preferred tool for users who only need to run standard reports and who use the Data Warehouse only infrequently. At this time, most of the EZ Access reports are intended for export to Excel rather than printing. Brio is the preferred tool for users who need formatted, printable reports.

Users who only need access to the Data Warehouse for running equipment reports will probably choose to use EZ Access.

********************

Seven standard Inventorial Equipment reports have been created for departments. All of them are available if you are using Brio. EZ Access is slightly different, with some of the reports consolidated or accessible only within another inquiry. The following table summarizes the Inventorial Equipment reports as they appear in Brio and EZ Access:

The Brio standard reports and EZ Access Inquiries were created to provide departments with immediate access to their equipment records, without having to request printouts of mainframe CATS reports from Equipment Management. Departments can view only their own equipment records, access being restricted by the mapping of home department and custody codes. The data from all reports and inquiries can be exported into Excel and then incorporated into departmental databases. All of the standard reports and inquiries can be modified to restrict or customize the results.

A Standard Report Profile has been created for each of the Brio standard reports (no separate profiles have been created for the EZ Access inquiries); they are found under the Equipment section of the Standard Report Profiles at: http://dw.isc.ucsb.edu/rp_index_new.html

The following summaries briefly describe each report. The Standard Report Profiles supply considerably more detail.

DEPT INVENTORY REPORT

The report resembles the printed EQ920-Physical Inventory that Equipment Management mails to departments every two years. Like the EQ920, the Dept Inventory Report is divided into two sections:

1. Assets over $5,000, Non-UC Assets (regardless of value) and Firearms. These assets must be physically inventoried.

2. UC Assets under $5,000, Not Fully Depreciated. These assets do not need to be physically inventoried, but their disposal should be reported to Equipment Management via EIMR.

You can choose to print both sections of the report, or just one section, by using the Yes/No “Physical Inventory Required” prompt. The default selection prints both reports.

If your department has multiple custody codes, the report first displays all custody codes containing assets that must be physically inventoried, followed by a separate list of all custody codes containing assets that do not need to be physically inventoried.

The report allows you to process a single request and then view and print the results in three different formats, depending upon your needs:

1. Inventory Report: a list of all active assets in a department’s custody, no Alternate Location or Off-Campus Use information displayed. It most closely resembles the printed EQ920-Physical Inventory.

2. Inventory Report with Alternate Location: the same list as in the preceding format but with Alternate Location and Off-Campus Use information displayed for those assets whose equipment record includes it.

3. Inventory Report only Alternate Location: a list of only assets whose equipment record includes data in the Alternate Location or Off-Campus Use fields. This report will be empty, if a department has no assets with information in those fields.

DEPT INVENTORY ASSET REPORT

The report displays the same assets that appear on the preceding Inventory Report; the only difference being the report grouping: whereas the Inventory Report, like the EQ920, is sorted by custody code, the Inventory Report by Building is sorted by Building and then Room Number.

The report first prints a list of all building and room numbers in which your department has assets requiring physical inventory (those worth over $5,000, non-UC assets and firearms); it then prints a list of all buildings and room numbers in which your department has active assets not requiring physical inventory (i.e., UC-title assets not yet fully-depreciated).

Under each room number the list of equipment is sorted by Asset (Property) Number. Assets with no room number entered into CATS appear as the first entry for each building, grouped together under the subheader “Room -----.”

DEPT INVENTORY ASSET REPORT

The report displays, on a single page, full equipment information for a single asset, including transaction and custody history – a level of detail which can only be viewed by using PF keys to access subsidiary screens, if you look up records on-line in CATS via ALLN01. Sometimes, if the transaction and/or custody histories are sufficiently long, the report continues onto additional pages.

DEPT INVENTORY WRITE-OFF LIST

The report returns a list of fully-depreciated under $5,000 UC-title assets disposed of (written off) by UCSB Equipment Management on September 29, 2004 per a master list supplied by Office of the President.

The information is displayed in a format similar to the Dept Inventory Report, in custody code order, with an emphasis upon fields that will assist your department in locating the assets and either removing property tags or flagging the assets as non-inventorial.

In the future, as additional write-offs are processed, a prompt will be added to allow departments to select a specific write-off year when running the report.

DEPT DEPRECIATED VALUE – ALL

and

DEPT DEPRECIATED VALUE – ASSET

These are two versions of the same report, one returning results for an entire department, the other only for a selected asset, showing the current depreciated value of items of inventorial equipment.

Depreciated value is calculated by multiplying the Acquisition Value of an asset by its Remaining Useful Life (itself calculated by subtracting Current Age from Useful Life) divided by the Useful Life assigned to the asset’s equipment category.

For example, the depreciated value, as of October 31, 2003, of a $5,000 computer with a receival (acquisition) date of January 1, 2003:

Acquisition Value: $5,000

Useful Life: 5 years
(Calcode H0780, for all computers)

Current Age: 1 year
(each year is calculated at the beginning of the annual cycle, so a 10-mont old computer is considered to have a Current Age of 1 year)

Remaining Useful Life: 4 years
(Useful Life minus Current Age [5 –1 = 4])

Current (Depreciated) Value: $4,000
(Acquisition Value multiplied by [Remaining Useful Life divided by Useful Life])
$5,000 x (4 / 5) = $4,000

The depreciated value of an asset can never be less than $0. Artwork and other special collections items (Calcode H5000) do not depreciate. UC always maintains their full acquisition value in the inventorial equipment system (in fact, their value tends to appreciate).

These reports are useful for providing departments with estimates of the current value of assets, sometimes useful if items are to be sold to other departments or institutions, or for insurance calculations.

DEPT DISPOSED INVENTORY REPORT

The report lists all assets that have been disposed of by a department within a specified period.

It report lists not only those assets directly disposed of by a department and reported on an EIMR (in cases such as loss, theft, destruction or cannibalization for parts) or Purchase Order (trade-ins), but also assets disposed of via the surplus process and transferred to Central Stores on an ESPD.

The report lists only DISPOSED assets. Therefore, it may not include assets recently picked up by Surplus, if Central Stores has not yet trashed or sold them; it will not include assets if an EIMR has not yet been processed by Equipment Management; and it will not include any assets “disposed of” by transferring them to another UCSB department: all of these assets are still ACTIVE.

********************

Equipment Management has tried to create standard reports which it believes will be of use to most campus departments. If you have ideas for other reports, please contact Vaughn Boyle (vaughn.boyle@purc.ucsb.edu; x7377).

Please address all systems questions regarding the Data Warehouse – access (Brio and EZ Access), navigation, query and inquiry processing and report customization – to either Mary Wenzel (x8882; mary.wenzel@isc.ucsb.edu) or Yentran Tran (x5533; yentran.tran@isc.ucsb.edu) in Information Systems and Computing.

Back to Top