Accounting system

Department of Administrative Services > Financial programs > Accounting system > Datamart

Datamart

The State of Oregon, Department of Administrative Services (DAS), Financial Datamart is an electronic warehouse of data, which helps employees develop reports for business information and decision making.​​ 
​  ​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​
OBIEE

​​​​​​Overview

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 DAS Datamart OBIEE application went live on Aug. 1, 2020 (state agencies were provided access to the OBIEE production environment between 8/1/20 and 11/1/20). 

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/ 

Available data

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

FAQ

OBIEE Common Questions​

OBIEE Admin Facts - why an agency would need an OBIEE user with 'admin view' rights 

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 following document provides guidance to employees who will create formulas that access the DAS Datamart using the OBIEE application. OBIEE Formulas

Grouping fields (Bins)​ 

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

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.​


Hyperion

​​Overview

Hyperion is Oregon state government's original tool for accessing data in the state Datamart. For the most current tool, refer to the OBIEE section, above.  ​​

Troublesh​ooting

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

PROBLEM

​POSSIBLE SOLUTION

​Run away query
​​Stop query 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

Repository

The DAS Datamart's Hyperion repository is ​a great tool to help share standard reports with users statewide. The Hyperion repository contains 49 files, which access data from SFMA, ORBITS and OSPA. 

Learn how to access the repository and what each file includes:
To suggest that one of your reports be added to the repository, send a sample and detailed description to datamart.support@das.oregon.gov.


Security

​​​​Security access forms​​​

SFMA and OSPA standard view access  

The agency security officer completes and submits the Datamart Standard View Access SFMA and OSPA Tables 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 see the new ​OBIEE security request form on the SARS System Security​​ website. Authorized ASOs use this form to request OBIEE access.

PPDB standard view access  

The DAS Chief Human Resources Office, HR Systems section manages access to the Position & Personnel Database (PPDB) and the related tables in the Datamart. Please contact them at group.ppdb@das.oregon.gov.

Note: With the implementation of Workday in February 2019, the DAS Datamart will no longer update PPDB information as Workday is the system of record. With this change, the DAS Datamart will only contain PPDB data through Feb 1, 2019.​ ​

ORBITS/PICS standard view access  

The DAS Chief Financial Office, Statewide Audit and Budget Reporting Section (SABRS) manages access to ORBITS and PICS and the related tables in the Datamart. The PICS system has been replaced with ORPICS, the new system of record as of January 2020. The Datamart contains PICS data through January 2020.

ORBITS/PICS security form

Contact: orbits.help@das.oregon.gov ​

Password resets

Visit the Datamart User Maintenance site to change your Datamart (RACF) password, request your account be reset, view your current query connections or stop a query. 

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.   ​​

Training

​​​​​Online 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. 

Fundamentals of​​ the Datamart​

This introductory training covers 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?

Class overview: Fundamentals of the Datamart

In-person ​classes

​​​​In-person trainings are normally scheduled once a month; however, due to Covid-19, they are on an extended break. We are sorry for the inconvenience. Any up​dates to class scheduling will be communicated to the Datamart News list. ​

Classrom trainings are free of charge; however, agencies will be assessed a $50 fee for no-shows. Additionally, substitutions are not allowed. ​​Refer to "Times and locations" (below) for further information. 

Datamart and Hyperion: Back to Basics

No classes at this time 

​Datamart and Hyperion: Payroll 

No classes at this time 

Datamart and Hype​rion: A Cut Above

​No classes at this time


Times an​d loc​at​i​​ons​

SALEM: Classes are from 8 a.m. to 4:30 p.m.

General Services Building (also called ​DAS East)

Training Lab (the Mt. Hood room)

1225 Ferry St SE, Salem, OR 97301

TIGARD: Classes are from 8:15 a.m. to 4:30 p.m. 

PERS Building, Training Lab

11410 SW 68th St, Tigard, OR 97223

PERS overflow parking

Overflow parking is close to Chang's Mongolian Grill. 

You must post the PERS parking sign on the dashboard of vehicle.

Training ma​​n​uals

Datamart and Hyperion: Back to Basics - Manual​
Learn the basics about the SFMA Datamart, table information, the Repository, and how to set up an OCE. Learn about Hyperion, how to build and run queries, manipulate queries and results. In addition, learn about Hyperion tables, joins, filters, formatting and sorting. 

​Chapter 4B exercise: To use the file GL Detail 15.bqy in the Chapter 4B exercise, email datamart.support@das.oregon.gov. 

Datamart and Hyperion: Payroll - Manual​
Learn the basics about the OSPA Datamart, the Repository, and how to set up an OCE. Also learn about the Hyperion application, how to build and run queries, manipulate queries and results, and how to append queries. In addition, learn about Hyperion tables, joins, filters, formatting and sorting.

Datamart and Hyperion: A Cut Above - Manual​ 
Learn advanced topics, including complex query building, pivots, free-form reports, computed fields and dashboards.

Other trai​​ning

  • ​​OSFA training 2​​021: OBIEE introduction and tips (includes discussion on security, analysis, filters, povit and formula: OBIEE OSFA Training (2021)

  • OSFA ​training 2​​014: Hyperion chart design and tips: OSFA 2014 Presentation Summary

  • Datamart/Hyperion tips and tricks: This document is a helpful tool, providing users with valuable tips and tricks for the Datamart and Hyperion application: ​Tips and Tricks 2015

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


Processing calendar and system information
Datamart Calendar 2022
Dates are subject to change.​​

​Datamart System Load data
Detailed listing of statewide systems and their load schedules.

​Important dates during Month 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/18/2022, 07/20/2022, 07/22/2022, 07/25/2022, 07/27/2022, 07/29/2022, 08/01/2022, 08/03/2022, 08/05/2022, 08/08/2022, 08/10/2022, and 08/12/2022.
  • '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 2022). 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 2019 to current.
  • Archive Accounting Event - Contains SFMA data from FY 2017 to current.​
SFMA Datamart tables

​​​SFMA Datamart ​T​ables
Listing of Financial and Profile tables. Contains data warehouse fields, field length, source, and descriptions. Note: To obtain additional detailed information about the SFMA tables, please refer to the 'Datamart and Hyperion: Back to Basics Manual' (page 2-16 through 2-22), in pdf form, shown in the 'Datamart Training' section of this website. 

Join - Diagram 2013
This file displays the 'All Acct Event' table joined to the various 'Profile' tables within the SFMA Datamart.

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).​

OSPA Datamart tables

​​The OSPA Datamart contains data processed through the Oregon Statewide Payroll Application. Data is updated monthly following the close of the payroll month, which is around the 14th of the following month. Check the OSPS Processing Calendar for specific dates.​

The OSPA Datamart contains individual employee payroll information, including:

  • Deduct​ions​

  • Job Status

  • Labor Costing

  • Leave Accrual

  • Gross and Net Pay Detail

  • Work Schedules

  • Custom Reports: Just about anything you can think of can be queried from the OSPA Datamart and customized to your specific needs. Do you need to complete a PERS salary breakdown? How about finding out how often someone uses sick leave? Curious about usage statistics for parking? All of these and much more can be queried from the OSPA Datamart.

  • Identify Errors: Use the OSPA Datamart to query for specific pay or deduction codes that give you trouble. For example, use the OSPA Datamart to ensure that each SEIU union deduction code has an accompanying issues code. Or, query to ensure that insurances are being forced using post tax rather than pre-tax plan codes.

  • Monitor for Potential Abuse: The OSPA Datamart is ideal for doing spot queries to ensure that usage of pay codes fall within normal tolerances. For instance, you can run a query on a specific class to inquire about comp time accrual, then measure that against an expected average. Or, query specific employee groups for abnormal overtime usage.

​​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 AND FILED LIST​ING


​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_RETIREMENT***
​
​OSPS_WORK_SCHED
​Work schedules table reference guide
​OSPS_LV_ACCRUAL
 ​Leave accrual table reference 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. 
​

Table extensions reference

Each of the OSPA Datamart tables has historic data available from 07/01/11 to current.

Please note: As of Aug. 11, 2017, four tables have been combined into one, eliminating three extra file extension views (i.e. OSPS_DED vs. OSPS_DED_B, OSPS_DED_PB, and OSPS_DED_PP).

It will be important to update your queries that are using the old file extensions (_B, _PB, and _PP). These queries must be updated to point to the OSPS table with no file extension. Make sure to go into the properties of the table (double click on the top of the table) and alter the 'physical name' to contain no extension.

With this change, it has become more important to make sure each OSPS query has at least two filters. One is 'Agency' and the other is a time period such as 'Pay Period End', 'Pay Proc Date', etc. These two filters will allow your query to run at its optimal level.​

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

​OSPS_LV_ACCRUAL

​WS

​OSPS_WORK_SCHED

​JSL

​OSPS_JS_LAB_ADJ

​NP

​OSPS_NET_PAY

​DED​​

​OSPS_DED​


Work request

​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.

Tech tools and information

​Query tool s​​oftware – IR Studio/Hyperion

Information to complete a full user setup, including IR Studio application download, ODBC configuration, IBM DB2 driver install, and Open Catalog Extension (OCE) updates are available here​​.   

Outages

Report Datamart outages or connectivity issues to DAS Datamart Support: datamart.support@das.oregon.gov.

Open Catalog Extension (OCE) files

Open Catalog Extension (OCE) files are required to connect the IR Studio to the ODBC. 

Columns will be in alphabetical order.​

Please save these OCEs to a drive that is backed up, such as your agency network.​​

​