Data Warehouse

Business Intelligence is a primary focus for Information Services, as the sheer volume of data continues to increase and as we all need to analyze that data in ever more sophisticated ways. Accordingly, we are developing an implementation of Banner Operational Data Store and Enterprise Data Warehouse and a new reporting tool. Collectively, these are known as ODS/EDW. In the meantime, Information Services continues to support the existing Data Warehouses and Data Marts for the OUS and OSU communities.

Data Warehouse Models

IS supports both Oregon University System Data Warehouses and Oregon State University Data Warehouses. The Data Warehouses contain a subset of the information available in the OUS/OSU administrative databases and are optimized for data retrieval and reporting.

OSU Data Warehouse Models

OUS Data Warehouse Models

Data Requests & Data Extracts

There may be an occasion when a research or business operational need requires access to data or documents from the ECS managed Oracle tables, including Banner data. If you or your department require this data for use in your operations, whether it is a one time need or you plan to store and maintain the data on department databases or computers, you are required to enter into a Memorandum of Understanding (MOU) with the appropriate Records Custodian(s). The specific agreements detailing the use, storage and release of data must be specified in an MOU between the parties.

Download the MOU document (.doc)

The MOU document should be completed by the requesting department in coordination with the Records Custodian (identified in the Acceptable Use of University Information) and IS department(s) doing the work. When the MOU document is completed and signed by all parties, please submit it to Kent Kuo, Milne 206, Oregon State University, Corvallis, OR 97331.

All data usage, storage and release must adhere to the Acceptable Use of University Information and the Acceptable Use of Computing Resources. The Records Custodian(s) are responsible for determining acceptable use, storage and release of data within their authority. All data addressed in the MOU must be regularly reviewed for continued acceptable use. Copies of the completed MOU document will be retained by ECS and the Records Custodian. You should also keep a copy of the MOU for yourself in your own records.

Data Warehouse Technical Instructions & Documents

Hardware Requirements for Client Machines

Hummingbird BI Query should run on all modern Windows computers. See the Hardware Requirements page for details.

Data Warehouse Installation Instructions

Typically, Departmental Computing Administrators (DCAs) install relevant software on networks and individual workstations in their departments. All of the software is available free of charge to OUS/OSU departments and consists of three major components.

  • The BI Query User application software
  • The Oracle connection software
  • The data model representation of data that the user sees and interacts with

Please access the Data Warehouse Installation Instructions page for more information.

BI Query Handouts & Manuals

Hummingbird BI Query handouts and manuals provide instructions and troubleshooting tips for both new and advanced users.

BI Query Handouts & Manuals

Handouts

Hummingbird BI Query Manuals & User Guides

Hummingbird BI Query Version 9

Hummingbird BI Query Version 8

Advanced Report Building Training Manual (from Hummingbird BI Query Version 6)

The Hummingbird BI Query Version 6 Advanced Report Building handouts still have relevant explanations of BI Query Reports functionality and are useful as the basis for training materials, even though more recent versions have different icons and some additional features.

Data Warehouse Installation Instructions

Software to access the OUS/OSU Data Warehouses is available for DCAs (Departmental Computing Administrators) to install on networks and individual workstations in their departments. All of the software is available free of charge to OUS/OSU departments.

The software to access the Data Warehouse consists of three major components.

  • The BI Query User application software
  • The Oracle connection software
  • The data model representation of data that the user sees and interacts with

The software and links to these installation instructions are available on the software distribution server located at \\TSS-SoftStore.tss.oregonstate.edu\

Installation instructions for the default configuration of the software are available here. Automated installation packages are also available for almost every step of the process. If you prefer or need to walk through the installations manually, instructions are provided as well.

Windows 7 / Vista Installation Instructions (.PDF format)

Install BI Query 10 & Oracle 11 Automated Manual
BI 10 Troubleshooting Tips

Windows XP Installation Instructions (.PDF format)

Install BI Query 9 & Sample Data Models Automated Manual
Migrate Existing Models
OR
Install New Data Models
Automated Manual
Automated
Uninstall Oracle 9.2 (Optional) Automated Manual
Install and Test Oracle 10 Automated Manual
User Setup & Overview of BI Query 9
Troubleshooting Tips
Miscellaneous Instructions:
Manual Installation of Sample Data Models
Uninstall BI Query 9 and Oracle 10 Automated Manual

Hardware Requirements for BI Query Version 9

  Minimum Recommended
CPU PIII 350 MHz Intel Core i Series
RAM 256 MB 4 GB
Free HD Space 500MB Required*
Operating System Windows XP Pro SP 2 Windows 7 Professional

*After installation on our test machine, Hummingbird BI Query plus the Oracle connectivity software was less than 100MB.

In addition, users must have:
  • A 256-Color, VGA-compatable display
  • A CD-ROM drive (required only for installation if using CDs)
  • A campus network connection with TCP/IP access, or from home you must use a VPN to connect to campus.
  • BI Query User and Oracle Net Client installed. See the installation instructions for more information.
In addition to hardware and software, OSU users must have:

ODS/EDW

Information Services has embarked on a two year project to transition from the existing Data Warehouse systems to a next-generation Business Intelligence solution. For details, please access oregonstate.edu/leadership/provost/initiatives/core.

OSU Data Warehouse Models

Oregon State University has three Data Warehouse models:

Information Services supports these models, administers access to the data, and supports Departmental Computing Administrators (DCAs) with troubleshooting installation and other technical problems.

Getting Started with the OSU Data Warehouse

It is particularly important that Human Resources paperwork be completed for new employees. An employee must be set up in Banner by the Office of Human Resources before we can process the Request for Access form. This is true of transferred personnel as well. Even if they had Banner or Data Warehouse access previously, their old account has to be terminated when we start processing the form for their new department. Be aware that it can take a few days to process the form and that they will not be able to access systems during that time.

Finance (FIS) Data Warehouse

OSU's Finance Information System (FIS) Data Warehouse contains data extracted from Banner FIS and reorganized a bit to enhance accessibility. It is designed primarily for ad hoc querying and unofficial reporting by accounting staff in the various departments at OSU. The data is organized on a fiscal year basis.

The FIS Data Warehouse contains the following general subject areas (since FY 96, except as noted):

  • General Ledger (balance sheet accounts)
  • Operating Ledger (revenue and expense accounts)
  • Transaction Ledger (invoices, journal vouchers, and cancelled checks since FY 00)
  • Payroll Ledger (payroll expenses by employee - RESTRICTED ACCESS)
  • Fixed Assets (info about the assets and their depreciation)
  • Encumbrances
  • Approval Queues

The data are refreshed nightly, except for the Payroll Ledger, which is refreshed a couple of times a month, whenever payroll expenses are posted.

FAQ

  1. Why is the data from the Operating Ledger and the Transaction Ledger not equal for my index?
  2. What do I use this data for?
  3. What about OPE? How does it show in the default pay model?
  4. Who will show on the default pay object? Who will not show?
  5. Why is the default pay object sometimes different from the encumbrance amount in FIS? It seems that you should be able to sum all of the remaining months of the fiscal year and match the FIS encumbrance, but it does not always match.
  6. An employee shows up on a wrong index for a past month, even though the index was corrected for that month in PYAHOUR and it posted to FIS correctly? What happened?
  7. I sent paperwork for a change for an employee, but the default pay does not show anything or shows the wrong default pay. Why?
  8. When doesn’t the default pay reflect step increases or any mass pay increases?
  9. How will Sabbatical Pay be reported?
  10. How will employees being paid on the 9-12-month option report?
  1. Why is the data from the Operating Ledger and the Transaction Ledger not equal for my index?

    The Operating Ledger contains a summary of all revenues and expenses. The Transaction Ledger only contains detail for three types of transaction documents: Invoices (INV), Journal Voucher (JV), and Cancelled Checks (CKC). Fixed Asset Adjustment documents are not included in the Transaction Ledger at this time, even though they can affect revenues and expenses. So, for example, if you have revenues from the sale of fixed assets, or depreciation expense, those will be in the Operating Ledger but not the Transaction Ledger. Additionally, most Facilities and Administration Costs (formerly referred to as Indirect Costs), which are automatically calculated on certain transactions, are excluded from the Transaction Ledger, but are summarized in the Operating Ledger. These are the primary reasons for reconciling items between the two ledgers.

    Top of page

  2. What do I use this data for?

    The FIS Data Warehouse Default Pay Object was designed to help departments predict how much salary expense will be charged to each accounting distribution (FOAPAL) for each employee for each pay period in the current fiscal year.

    Top of page

  3. What about OPE? How does it show in the default pay model?

    Although encumbrances in FIS show the OPE component, the default pay model does not.

    Top of page

  4. Who will show on the default pay object? Who will not show?

    Employees with jobs that are active or on leave at some point during the current fiscal year, who have a default earn code, plus those in employee class CD, classified full-time hourly will be included. That’s everyone except hourly paid employees and unpaid appointments, including classified part-time hourly, temporary support staff, academic wage hourly appointments, student workers, and courtesy and emeritus appointments (employee classes CE, TS, UW, XA, and XX). Student positions with a job suffix of 55 will display because these jobs are salaried.

    Top of page

  5. Why is the default pay object sometimes different from the encumbrance amount in FIS? It seems that you should be able to sum all of the remaining months of the fiscal year and match the FIS encumbrance, but it does not always match.

    The default pay data is calculated and loaded into the FIS warehouse nightly. Encumbrances are only calculated and loaded into FIS once a month, near the end of the month. Any changes made to a job or its labor distribution in HR will be reflected in the default pay data the next workday, but won't affect encumbrances until they are recalculated near the end of the month.

    In addition, encumbrances are calculated somewhat differently from the default pay. Both are calculated using the job detail records, default earn codes, and default labor distributions from the jobs records, but the encumbrance process uses the first day of the next month to start encumbering, while the default pay process uses the actual personnel effective date.

    Because of this, you may see some differences between default pay amounts and encumbrance amounts for employees whose job begins midmonth. This will really be apparent for those employees who begin work on September 16. Their encumbrances posted in FIS will not include the time period between September 16 and October 1.

    Top of page

  6. An employee shows up on a wrong index for a past month, even though the index was corrected for that month in PYAHOUR and it posted to FIS correctly? What happened?

    Actual pay amounts do not show. The Default Pay Object is not a record of actual gross wages paid in prior months. The default pay looks at the job record (viewable in NYIJOBS) that was in effect for the month in question. The default labor distribution for that record was incorrect, requiring the correction in PYAHOUR. Once a month has passed, the pay can be redistributed, but the job record cannot be changed and the default pay cannot be corrected. Past months should be ignored.

    Top of page

  7. I sent paperwork for a change for an employee, but the default pay does not show anything or shows the wrong default pay. Why?

    Default pay can only be loaded based on what has been entered into Banner. If the information you are looking for is not there, it was not in Banner as of the time the warehouse loaded. Or, it could be a data entry error. Look in NYIJOBS to see if the changes you requested have been entered. Inputting paperwork for the current month takes priority over future-dated actions. Future-dated actions are input as soon as possible after that, however system limitations only provide the Office of Human Resources with about two weeks per month when input may occur.

    Top of page

  8. When doesn't the default pay reflect step increases or any mass pay increases?

    Pay increases are generally entered by payroll at the beginning of the month in which they became effective. They will not show up as default pay until that month.

    Top of page

  9. How will Sabbatical Pay be reported?

    Sabbatical will display on the employee position, with an "11" suffix, unless the employee has also signed up for the 9-12-month option. In these cases, it will display on the "00" suffix. In both cases, it should display with a 10123 account code.

    Top of page

  10. How will employees being paid on the 9-12-month option report

    The pay is charged to departments across the same 9 month period that the appointment covers, so those are the months where default pay will be projected.

    Top of page

Human Resources (HRIS) Data Warehouse

OSU's Human Resource Information System (HRIS) data warehouse contains data extracted from Banner HRIS and reorganized to enhance accessibility. It is designed primarily for ad hoc querying and unofficial reporting by staff in the various departments at OSU.

The HRIS Data Warehouse contains the following general subject areas (current or last known status since 1/99, except as noted):

  • Persons
  • Employees
  • Positions
  • Budget for Position Labor Distributions (two current years)
  • Jobs (history, too)
  • Job Labor Distributions (history, too)
  • Benefits and Deductions (two current years; RESTRICTED ACCESS)
  • Pay (three current years)
  • Skill codes recording Employee Training

These data are refreshed nightly, except for the Pay and Benefits objects, which are refreshed each weekend, and occasionally during the week as necessary.

FAQ

  1. Why are the dollars for pay for a specific month in the HRIS data warehouse Pay object different from the FIS data warehouse Payroll Ledger?

    In addition to the HRIS DW Pay object organizing data by Calendar Year (CY) and the FIS DW Payroll Ledger organizing data by Fiscal Year (FY), they also differ because in the HRIS DW data is shown by pay period, and in the FIS DW, data is shown by fiscal period. Initially both amounts are usually the same. However, any manual checks or adjustments and redistributions will cause reconciling items. If you process an adjustment in November that corrects September's pay, in the HRIS DW Pay object the correction will show up in pay period 9 September, but in the FIS DW Payroll Ledger it will show up in fiscal period 5 November.

Student (SIS) Data Warehouse

The SIS Data Warehouse provides a method for advisors and academic departments to access information about students and courses at OSU in a graphical and business-oriented way. The front-end tool used to access information in the SIS Data Warehouse is Hummingbird BI, a product from Hummingbird Ltd.

Additional Resources

  • Accounts Receivable (AR) Data Warehouse

    The Accounts Receivable (AR) Data Warehouse is designed to provide a method for department accountants to access detail and summary information for person and non-person accounts.

  • Financial Aid Data Warehouse

    The Financial Aid Data Warehouse is designed to provide a method for the Financial Aid department to access detail about financial aid applications that have been completed by students, financial aid awards that have been granted, tracking requirements, and financial aid status.

    Access to this data model is restricted. Any questions should be directed to the Office of Financial Aid and Scholarships, 541-737-4494.

OUS Data Warehouse Models

The Oregon University System Data Warehouse contains a subset of the information available in the OUS administrative databases. The Data Warehouse utilizes many data models optimized for data retrieval and reporting.

The OUS Data Warehouse group supports five data models on this site:

Information Services at Oregon State University provides functional and technical support for the OUS Data Warehouse models. IS also designs, develops, and supports enhancements. You can download the OUS Data Warehouse software via the OUS/OSU BI Software Distribution page.

Information Security Policy for OUS

The Oregon University System maintains their Information Security policy at http://www.ous.edu/dept/cont-div/fpm/genl-56-350. It is each user's responsibility to read and understand this policy as it pertains to issues including, but not limited to, information identification and data classification; the handling of personal information; and incident response.

OUS Budget Mart

The Budget Mart has data and functions that have been tailored for very specific needs. It is designed primarily for preparation of reports to the State Board or Higher Education, DAS, the legislature and for ad hoc use by Budget Directors at OUS institutions. It's defined and managed by Budget And Management of the Chancellor's Office in coordination with Budget Representatives from each campus.

Budget Mart features include:

  • Provide a basis for reporting accurate and consistent:
    • FTE (budget and current)
    • Salary (budget, current and projections)
    • Labor Distributions
    • Filled and Vacant position data
    • Actual vs. Projected payroll expenses
  • Match PICS calculations for pay adjustment and roll up costs for the biennial budget process
  • Provide many budget related reports, ready to be refreshed with the latest status
  • Project and report Future Salary Costs (based on a snapshot of selected HR data)

Budget Mart data is based on reportable Job and Position data extracted from an HR Data Store. Snapshots of this HR data, as of the end of each month, are kept in the Budget Mart.

OUS Finance Data Warehouses

These warehouses contain essentially unmodified Banner FIS data, although they've been reorganized a bit to enhance accessibility. They are designed primarily for ad hoc querying and reporting by staff at OUS institutions (e.g. some campuses use them for official internal financial reporting). Content and structure are defined and managed by the FIS Data Warehouse User Groups with representatives from each campus and the Chancellor's Office.

FIS Data Warehouses contain detailed data about the following general subject areas (since FY 96, except as noted):

  • General Ledger
  • Operating Ledger
  • 5-Site Transaction Ledger (2002 forward)
  • Payroll Ledger
  • Fixed Assets
  • Approval

These data are available from any one of the Banner FIS instances in the OUS system (or all of them together, in which case data is tagged by institution and refreshed monthly).

  • EOU, SOU, WOU, OIT and CO snapshots occur each workday night
  • PSU, OSU and UO snapshots occur each month

OUS Human Resources 5th Site

This data store contains essentially unmodified Banner HRIS data that have been reorganized slightly to enhance accessibility. It is designed primarily for ad hoc querying and reporting by staff at OUS regional institutions. Content and structure are defined and managed by the HRDS User Groups with representatives from each campus and the Chancellor's Office.

Detail data about the following subject areas are current except as noted:

  • Employees (history too)
  • Persons
  • Positions
  • Position Labor Distributions
  • Jobs (history too)
  • Job Labor Distributions
  • FTE
  • Earnings (history only)
  • Deductions (history too)
  • Bargaining Unit
  • Leaves
  • Reviews
  • Faculty (Unclassified)

These data are available from any one of the regional Banner HR instances in the OUS system (EOU, SOU, WOU, OIT, Controllers Office) and are refreshed each work night.

OUS Human Resources Aggregate Site

This data store contains essentially unmodified Banner HRIS data that have been reorganized slightly to enhance accessibility. It is designed primarily for ad hoc querying and reporting by staff at OUS and OSU. Content and structure are defined and managed by the HRDS User Groups with representatives from each campus and the Chancellor's Office.

Detail data about the following subject areas are current except as noted:

  • Employees (history too)
  • Persons
  • Positions
  • Position Labor Distributions
  • Jobs (history too)
  • Job Labor Distributions
  • FTE
  • Earnings (history only)
  • Deductions (history too)
  • Bargaining Unit
  • Leaves
  • Reviews
  • Faculty (Unclassified)

These data are available from systemwide Banner HR instances (UO, OSU, PSU, EOU, SOU, WOU, OIT, Controllers Office) and are refreshed each Sunday night.

OUS Space Utilization Data Mart

The Space Utilization Data Mart has data and functions that have been tailored for very specific needs. It is designed primarily for preparation of reports about the use of instructional facilities for Facilities Management, OUS Administration, State Board of Higher Ed, DAS, the Legislature, and for ad hoc querying by campus facilities management staff. It's defined and managed by Budget & Management Division of the Chancellor's Office in coordination with Space Analysts from each campus.

Space Utilization Data Mart Documentation

Space Mart data includes:

  • Location data – information about instructional space on each campus as provided by the Space Analyst at each institution
  • Summary Room data – summarizes Event data by building code and room number
  • Student FTE data – information by student level, from the Chancellor’s Office
  • Event data – information about individual classes scheduled by term which is drawn from all OUS Banner Student Information Systems via the Chancellor's Office (SCARF)

It also provides many standard reports, ready to be refreshed with the latest data. These reports cover topics like:

  • Scheduled occupancy (of student stations, by hour, by day, etc.)
  • Rooms scheduled by hour
  • Student Stations scheduled by hour
  • Space optimization (detail and summaries, by rooms, etc.)