Skip to main content

Oregon State Flag An official website of the State of Oregon »

Oregon.gov Homepage

Datamart & OBIEE


Oregon state government's Department of Administrative Services (DAS), Financial Datamart is an electronic warehouse of data, which helps employees develop reports for business information and decision making. 

Choose a Topic

​​​​​​​​​​​​​​​​​​​​​​​​Ove​rview

Oracle Business Intelligence Enterprise Edition (OBIEE or OB1) is the query tool set to provide access to Oregon state government's financial Datamart. It is the replacement for the Hyperion (Brio) application. 

​The application can be found on the following sites. To fully view the site, a user must be connected to state government's network.

Production environment: 
https://obi.das.oregon.gov:9503/analytics/

Test environment (used for Admin only):
https://obitest.das.oregon.gov:9503/analytics/ 

Availa​ble Data

Currently, the OBIEE application accesses SFMS, Workday, and ORBITS data in the DAS Datamart. (Also refer to the Repository section, below.) 

FAQ​

OBIEE Common Questions​

​Passwords

When logging in to OBIEE there are two types of agencies, 'Sync' and 'Non-sync.' Learn which type applies to your agency here.

A 'sync' agency will use a password that is the same as the user's work computer. When the user updates their work computer password, then OBIEE automatically syncs and changes. 

A 'non-sync' agency will use the password initially provided by the Agency ARS (Active Roles Server) admin team. This password will be maintained through the OBIEE password manager site. 

Here are ​instructions to help non-sync users create a profile and manage their password: Password Manager User Guide.

Passwords should be updated every 90 days.

Form​ula Creation

The OBIEE Formulas document provides guidance to employees who will create formulas that access the DAS Datamart using the OBIEE application. 

Grouping Fields (Bins)​ 

The How to Crea​te a Group document detailing how to group fields in OBIEE. This introduces the "bins" function. 

Training

Training on the use of OBIEE is required prior to using the application. Please refer to the Training section of this page for course information. ​

Repository

​The OBIEE repository functions within the OBIEE server environment. All files are created with the OBIEE tool and saved to the OBIEE server. The Datamart team as well as agency users are working on building files for this repository similar to the original Datamart Hyperion repository. Thanks to users throughout the state, we continually obtain new reports to help in day-to-day operations.

​To suggest that one of your reports be added to the repository, send a sample and detailed description to datamart.support@das.oregon.gov.​​

​​​​​​​​​​​​Securi​ty Access Forms​​​

SF​MA and Workday Standard View Access  

The agency security officer completes and submits the Datamart Standard View Access Request Form found in the Security Access Request section at: Systems Security. 

Oracle Business Intelligence Enterprise Edition (OBIEE or OB1)  

OBIEE Security Access Process    ​

Note: Please use the ​OBIEE security request form on the SARS System Security​​ website. Authorized ASOs use this form to request OBIEE access.

Password Resets

OBIEE passwords: visit the OBIEE tab of this accordion and review the section regarding passwords. 

Datamart RACF passwords: visit the Datamart User Maintenance site. 

All Datamart passwords are valid for 90-days. You must update your password before the end of the 90-days or be required to obtain a reset.

Once you receive Datamart security access and an initial temporary password, you have 48 hours to change the password. 

The site is compatible with Internet Explorer, Firefox and Chrome.

Your password also is used when accessing programs that use the RACF ID password structure.   ​​

​​​​​​​​Overview

The DAS Datamart can be accessed by various programs. Currently, the state agencies have two types of security access. One is web based and used with OBIEE. The other is RACF based and used with any program that accesses DB2.  ​​

Troublesh​ooting Non-OBIEE Program Queries

If your query returns unpredicted results, check the following table for a solution. 

PROBLEM

​POSSIBLE SOLUTION

​Run away query

​​Stop qu​ery on Datamart Maintenance site​

​Query returns no results

​Check filters for proper restrictions

​Query returns no results​

​Check request  is in proper date format (20210229 or 2/29/21)

​Duplication of data

​Check the joined tables within the query section

​More rows returned than expected

​Check filters for proper restrictions

​More rows returned than expected​

​Check the joined tables within the query section

​Hyperion error (Error message not found)

​Download and use the new OCEs from this site

​Contact datamart.support@das.oregon.gov​ with any issues.

​​​​​​​​​​​​​​​Onlin​e Co​​urses

Datamart: ​​​OBIEE

The Oracle The Oracle Business Intelligence Enterprise Edition (OBIEE or OB1) training is a beginner level course. The OBIEE query tool is web-based and is a replacement for the Hyperion (Brio) application. This training provides an overview of the OBIEE structure, details website security, and demonstrates the Homepage and Catalog features. It provides an overview on how to create an analysis, formula and agents. Participants will be able to re-create an existing Hyperion file within the OBIEE application. 

Requirement: OBIEE security access

Datamart: OBIEE Overview

Datamart: OBIEE Workday Link

Fundamentals of​​ the Datamart​

This introductory training cov​ers the following Datamart topics: 

  • A description of the Datamart
  • The statewide data available in the Datamart
  • History of business intelligence applications used with the Datamart
  • Tables within the Datamart
  • Connecting to the Datamart database 
  • The Datamart Repository
  • Am I ready to work with the Datamart?

Fundamentals of the Datamart Overview

Fundamentals of the Datamart Workday Link

OBIEE-Hy​p​erion Conversion Process Recording

This recording covers the steps to convert three Hyperion file examples into OBIEE analyses. The ‘All Acct event’, ‘GL Detail’, and ‘OSPS-Labor Cost’ tables will be demonstrated in the conversion. Questions regarding the content of the course should be directed to Datamart.support@das.oregon.gov.

OBIEE-Hyperion Conversion Process Recording Workday Link

​Datamart/Workday Payroll Information

This recording discusses the Workday tables on the Datamart. In addition, it previews and describes OBIEE Workday reports.

Datamart/Workday Payroll Information Recording Workday Link

Handouts

​Other Trai​​​ning ​​OSFA Training​ 2​​021

OBIEE introduction and tips (includes discussion on security, analysis, filters, pivot and formula: OBIEE OSFA Training (2021)

Grouping Fields (Bins)​

Here is a document detailing how to group fields in OBIEE. This introduces the "Bins" function: How to Create a Group​​​

​​​​​​​​​Links to Datamart Calendars

​ Datamart Calendar 2023​
Datamart Calendar 2024​ Dates are subject to change.

RestoreClose dialogHTML Source
​Datamart System Load Data
Detailed listing of statewide systems and their load schedules.

Important Dates During Mo​​nth 13:

Update Sche​dule:​

  • 'YE GL Detail’ and ‘YE GL S​ummary’ tables: Data is only within these tables during Month 13. The rest of the year these tables are blank. 
  • Updated data is available within the above two YE tables on the following dates: 07/19/2023, 07/21/2023, 07/24/2023, 07/26/2023, 07/28/2023, 07/31/2023, 08/02/2023, 08/04/2023, 08/07/2023, 08/09/2023, and 08/11/2023.
  • 'YE Active Accounting Event' table: Data is only within this table during Month 13, the rest of the year the table is blank. In addition, it is only loaded with new data and viewable on Wednesday and Friday during Month 13.​

​SFMA Financial Table Year Reference:

The following is a list of the SFMA ‘Accounting Event’ tables and the range of time they currently contain.
 
Note: These tables are archived once a biennium. The Datamart News List will provide users with an alert when the next archive will take place. (Last archive: April 2023). In addition, Capital Construction data is the exception to the rule and is not archived.
  • Active Accounting Event - Most current three months of SFMA system data.
  • All Accounting Event - Contains SFMA data from FY 2020 to current.
  • Archive Accounting Event - Contains SFMA data from FY 2018 to current.​

​​​​​​​Join - Diagram 2013
In OBIEE, joins are completed behind-the-scenes so users do not have to set them manually. In all other programs such as Excel, Access, etc., users may create joins as shown in this document.

GL Compare​
Comparison of fields between the GL Detail and the GL Summary tables. 

R*Stars Actuals at the Budget Level
R*Stars integration established a one-to-one relationship between the ORBITS Cross-Reference number and the R*Stars Program Code; providing a link between budget (ORBITS) and accounting (R*Stars).​

​​​​​​​Overview

Workday is the State of Oregon’s current payroll, time tracking and HR information system. Workday replaced OSPS in December 2022. Currently, Workday data is loaded to the Datamart twice per month (run1 and run2).
​

Available Data

This file displays the 9 main Workday tables in the Datamart along with the 6 combined tables. Each table has a tab in the document that displays field names, description, and examples.

The Workday data in the Datamart does not contain: 
  • Confidential Information, such as Social Security numbers, wage withholding/garnishment info, bank account information or certain insurance surcharges.
  • Data sourced from other applications such as employee addresses.

Training

Please review the ‘Training’ section of the Datamart website for information on how to use Workday data in OBIEE.

With the change from OSPA to Workday, Datamart users should be aware the appropriate payroll period filter changed from the Pay Period End field to the Rpt Effective Date field. Pay Period End does not properly filter Workday data because Workday identifies run 2 as ending in the month it runs versus the month being paid. Users should use the ‘Rpt Effective date’ field and follow the dates on Datamart Calendar for Payroll Data​​.
​

​​​​​​​​The OSPA Datamart contains data processed through the Oregon Statewide Payroll Application. As of November 2022, the OSPS information is no longer being added to the Datamart. The new payroll system of record is Workday. 

The OSPA Datamart contains individual employee payroll information, including:

  • Deduct​ions​
  • Job Status
  • Labor Costing
  • Leave Accrual
  • Gross and Net Pay Detail
  • Work Schedules

​​The OSPA Datamart does not contain: 

  • Confidential Information, such as Social Security numbers, wage withholding/garnishment info, bank account information or certain insurance surcharges
  • Data sourced from other applications such as salary range or employee addresses
  • Detailed daily time information if not using Online Daily Time or eTime.​

​​The following lists detail the fields available in each of the OSPA Datamart's six major tables. For a master list of all available fields, see the last portion of this section.​​

Datamart Table Re​ference Lists

​OSPA DATAM​ART TABLE

​DESCRIPTION & FIELD LIST​

​OSP​​​​S_JS_LAB_ADJ_ALL*
 ​
​OSPS_NET_PAY
​Net pay table reference guide
​OSPS_LABOR_COST
 ​Labor cost table reference guide
​OSPS_JS_LAB_ADJ
 ​Job status, labor and adjustments table reference guide​
​OSPS_RE​TIREMENT***
​
​OSPS_WORK_SCHED
​Work schedules table ref​erence guide
​OSPS_LV_ACCRUAL
 ​Leave accrual table referenc​e guide
​OSPS_DEDN_TBL**
​
​OSPS_DED
 ​Deductions table​ reference guide​
​* This table was created for the ACA data to help agencies view all agency ACA info.   
** This table was created for DAS OSPS statewide unit only.
*** This table was created to provide historical data for various OSPS fields not contained on other OSPS Datamart tables, specifically the 'Final Check Issued date', 'Final Check Period End Date', and the 'Retirement Date' from the P010 screen in OSPS. 
​

Master Field List

OSPA Datamart Master Table Reference Guide.

Guide to table abbreviations used in master list:​​

ABBREVIATION

​TABLE IDENTIFIED

​LC

​OSPS_LABOR_COST

​LV

​O​SPS_LV_ACCRUAL

​WS

​OSPS_WORK_SCHED

​JSL

​OSPS_JS_LAB_ADJ

​NP

​OSPS_NET_PAY

​DED​​

​OSPS_DED​


​​​​​​​Datamart Work Request Form
Use this form to request improvements or changes to the Datamart, such as new fields, tables, joins, etc.​

This form is to be completed and submitted electronically by using the "Submit by Email" button.