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.
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.
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.
Hummingbird BI Query should run on all modern Windows computers. See the Hardware Requirements page for details.
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.
Please access the Data Warehouse Installation Instructions page for more information.
Hummingbird BI Query handouts and manuals provide instructions and troubleshooting tips for both new and advanced users.
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.
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 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.
|Install BI Query 10 & Oracle 11||Automated||Manual|
|BI 10 Troubleshooting Tips|
|Install BI Query 9 & Sample Data Models||Automated||Manual|
|Migrate Existing Models
Install New Data Models
|Uninstall Oracle 9.2 (Optional)||Automated||Manual|
|Install and Test Oracle 10||Automated||Manual|
|User Setup & Overview of BI Query 9|
Manual Installation of Sample Data Models
|Uninstall BI Query 9 and Oracle 10||Automated||Manual|
|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.
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.
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.
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.
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):
The data are refreshed nightly, except for the Payroll Ledger, which is refreshed a couple of times a month, whenever payroll expenses are posted.
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.
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.
Although encumbrances in FIS show the OPE component, the default pay model does not.
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.
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.
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.
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.
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.
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.
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.
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):
These data are refreshed nightly, except for the Pay and Benefits objects, which are refreshed each weekend, and occasionally during the week as necessary.
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.
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.
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.
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.
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.
The Oregon University System maintains their Information Security policy at http://www.ous.edu/departments/controllers-division/policies-procedures/ous-fiscal-policy-manual/information-security. 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.
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:
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.
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):
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).
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:
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.
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:
These data are available from systemwide Banner HR instances (UO, OSU, PSU, EOU, SOU, WOU, OIT, Controllers Office) and are refreshed each Sunday night.
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 Mart data includes:
It also provides many standard reports, ready to be refreshed with the latest data. These reports cover topics like: