Query tool software – 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.
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- Added July 14, 2020.
Provides guidance to ASOs about the process to assign or revoke user access to OBIEE, statewide policy surrounding OBIEE, and procedures to request a password reset.
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 Department of Administrative Services (DAS), Chief Human Resources Office, HR Systems section manages access to the
Position & Personnel Database (PPDB) and the related tables in Datamart. Please
contact them at
group.ppdb@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 ORBITS/PICS security form appears at the following link:
CFO Forms. Contact
orbits.help@oregon.gov.
OBIEE:
Oracle Business Intelligence Enterprise Edition (OBIEE or OB1) is the query tool set to provide access to the State of Oregon's Financial Datamart. It is the replacement for the Hyperion (Brio) application. The DAS Datamart OBIEE application 'Go live' date was August 01, 2020 (see agency rollout process below). This application is web based and can be found on the following sites. To fully view the site, a user must be working on the State of Oregon network.
Production environment: https://obi.das.oregon.gov:9503/analytics/
Test environment: https://obitest.das.oregon.gov:9503/analytics/ (Used for Admin only)
Note: State of Oregon agencies were provided access to the OBIEE production environment from 8/01/20 to 11/01/20.
OBIEE-Common_Questions-20210408.pdf
The OBIEE pdf file describes common questions for the OBIEE project.
OBIEE Admin Facts.pdf: The pdf document will help Agency Security Officers and OBIEE users understand why an agency would need an OBIEE user with 'admin view' rights.
Passwords: When logging into the OBIEE application there are two types of agencies, 'Sync' and 'Non-sync'.
A 'sync' agency will use a password that is the same as they use for their work computer. If you update your work computer password then OBIEE will automatically sync and change as well.
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 to create a profile and manage their password (Password_Manager_User_Guide.pdf).
Passwords should be updated every 90 days.
If you do not know if your agency is 'sync' or 'non-sync', please review this document: Sync-vs-Non-Sync_20210510.xlsx:
Formula: This pdf document was created to provide guidance with formula creation for State of Oregon employees who access the DAS Datamart using the OBIEE application. OBIEE FORMULA-June2021.pdf
Important note: Training is required, prior to using the product. A beginning level training is available through iLearn.
Important note: Currently, the OBIEE application only accesses SFMS and OSPS data in the DAS Datamart.
User Maintenance site:
Go here to change your Datamart (RACF) password, request your account be reset, view your current query connections or stop a query. This password is used when accessing programs that use the RACF ID password structure. Once you receive Datamart security access and the initial temporary password, you have 48 hours to change the password on the
Datamart User Maintenance site. The site is compatible with Internet Explorer, Firefox and Chrome.
Training
Scheduled classroom trainings: (Trainings are normally scheduled once a month; however, due to covid-19, they are on an extended break. Sorry for the inconvenience.)*
Datamart & Hyperion: Back to Basics
None at this time
Datamart & Hyperion: Payroll
None at this time
Datamart & Hyperion: A Cut Above
None at this time
Online Training Courses:
Course:
DAS - FBS - Datamart: OBIEE: The Oracle Business Intelligence Enterprise Edition (OBIEE or OB1) training is a beginner level iLearn 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 'Home page' 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. Class reference material:
OBIEE_Basic_Training_Final_Content.docxCourse:
DAS - FBS - Fundamentals of the Datamart:
This introductory training is for employees who use the Department of Administrative Services (DAS) Datamart.
This training will cover the following topics: What is the Datamart? What statewide data is in the Datamart. A history of Business intelligence applications used with the Datamart. Tables within the Datamart. Connecting to the Datamart database. What is the Repository? Am I ready to work with the Datamart?
Class reference material: OBIEE_Basic_Training_Final_Content.docx
Classroom training - Times and locations:
SALEM Location: Classes are 8:00 am - 4:30 pm and will be held at the General Services Building in the DAS East Training Lab, located in the Mt Hood room at 1225 Ferry St SE, Salem, OR 97301.
TIGARD Location: Classes are 8:15 am - 4:30 pm and will be held at the PERS Building in the Training Lab, located at 11410 SW 68th St, Tigard, OR 97223.
PERS overflow parking:
PERS-Overflow-Parking.pdf (close to Chang's Mongolian Grill) **Note: Must include the PERS parking sign on dashboard of vehicle.
Training manuals:
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. If you would like to use the file (GL Detail 15.bqy) that is associated with Chapter 4B exercise in the 'Back to Basics' manual, please contact
datamart.support@oregon.gov.
Datamart & 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 & Hyperion: A Cut Above - Manual Learn advanced topics including: complex query building, pivots, free-form reports, computed fields, and dashboards.
Other Training:OSFA Training 2021: OBIEE introduction and Tips (includes discussion on Security, Analysis, Filters, Povit and Formula.
OBIEE_OSFA_Training-2021.pdf
Datamart/Hyperion Tips and Tricks:
This document is a helpful tool that will provide users with valuable tips and tricks for the Datamart and Hyperion application.
Tips and Tricks 2015
*Updated class scheduling information will be sent over the Datamart News List.
**Live Datamart trainings are free of charge; however, agency's will be accessed a $50 fee if the user does not show for the scheduled course. Agencies do have the option to send a substitute user, if the original user is unable to attend.
Processing calendar and system information
Important dates during month 13:
Update Schedule:
'YE GL Detail’ and ‘YE GL Summary’ 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/21/2021, 07/23/21, 07/26/21, 07/28/21, 07/30/21, 08/02/21, 08/04/21, 08/06/21, 08/09/21, 08/11/21 and 08/13/21.
'YE Active Accounting Event' table: Data is only within this table during Month 13. The rest of the year the table is blank.
Updated data is loaded and viewable the last three Thursdays of Month13: 07/29/21, 08/05/21, and 08/12/21.
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 2021). 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 2018 to current.
Archive Accounting Event - Contains SFMA data from FY 2016 to current.
Work request form
Datamart Work Request form- Use this form to request improvements and/or changes to the Datamart, for example, new fields, tables, joins, etc. This form is to be completed and submitted electronically by using the 'Submit by E-mail' button.
Repository
The DAS Datamart has two main repository associated with it. The Datamart Hyperion repository and the OBIEE repository. These are two separate repository yet both pull data from the DAS Datamart. These repositories are a great tool to help shared standard reports with users on a statewide level.
The Datamart Hyperion repository contains 49 files, which access data from SFMA, ORBITS, & OSPA. To learn more about how to access the repository and what each file includes:
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, 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.
If you have any reports which you believe would be a good fit to join either repository, please send them, along with a detailed description, to
datamart.support@oregon.gov.
Potential (Hyperion) query tool issues:
Common things that can make your query return unpredictable results include:
Problem Possible solution
Query returns no results Check filters for proper restrictions
Query returns no results Check request is in proper format (20120229 or 2/29/12)
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 oce's from this site
SFMA tables
SFMA Datamart Tables- 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-Diagram2013- 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 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:
- Deductions
- 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 troubles. 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 enquire 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.
Datamart table reference lists
These 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.
Follow
javascript: SP.SOD.executeFunc('followingcommon.js', 'FollowDoc', function() { FollowDoc('{ListId}', {ItemId}); });
0x0
0x0
ContentType
0x01
1100
Compliance Details
javascript:if (typeof CalloutManager !== 'undefined' && Boolean(CalloutManager) && Boolean(CalloutManager.closeAll)) CalloutManager.closeAll(); commonShowModalDialog('{SiteUrl}'+
'/_layouts/15/itemexpiration.aspx'
+'?ID={ItemId}&List={ListId}', 'center:1;dialogHeight:500px;dialogWidth:500px;resizable:yes;status:no;location:no;menubar:no;help:no', function GotoPageAfterClose(pageid){if(pageid == 'hold') {STSNavigate(unescape(decodeURI('{SiteUrl}'))+
'/_layouts/15/hold.aspx'
+'?ID={ItemId}&List={ListId}'); return false;} if(pageid == 'audit') {STSNavigate(unescape(decodeURI('{SiteUrl}'))+
'/_layouts/15/Reporting.aspx'
+'?Category=Auditing&backtype=item&ID={ItemId}&List={ListId}'); return false;} if(pageid == 'config') {STSNavigate(unescape(decodeURI('{SiteUrl}'))+
'/_layouts/15/expirationconfig.aspx'
+'?ID={ItemId}&List={ListId}'); return false;}}, null);
0x0
0x1
ContentType
0x01
898
Document Set Version History
/_layouts/15/images/versions.gif?rev=40
javascript:SP.UI.ModalDialog.ShowPopupDialog('{SiteUrl}'+
'/_layouts/15/DocSetVersions.aspx'
+ '?List={ListId}&ID={ItemId}')
0x0
0x0
ContentType
0x0120D520
330
Send To other location
/_layouts/15/images/sendOtherLoc.gif?rev=40
javascript:GoToPage('{SiteUrl}' +
'/_layouts/15/docsetsend.aspx'
+ '?List={ListId}&ID={ItemId}')
0x0
0x0
ContentType
0x0120D520
350
*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 to 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:
| LC | OSPS_LABOR_COST | | LV | OSPS_LV_ACCRUAL | | WS | OSPS_WORK_SCHED | | JSL | OSPS_JS_LAB_ADJ | | NP | OSPS_NET_PAY | | DED | OSPS_DED |
|
Follow
javascript: SP.SOD.executeFunc('followingcommon.js', 'FollowDoc', function() { FollowDoc('{ListId}', {ItemId}); });
0x0
0x0
ContentType
0x01
1100
Compliance Details
javascript:if (typeof CalloutManager !== 'undefined' && Boolean(CalloutManager) && Boolean(CalloutManager.closeAll)) CalloutManager.closeAll(); commonShowModalDialog('{SiteUrl}'+
'/_layouts/15/itemexpiration.aspx'
+'?ID={ItemId}&List={ListId}', 'center:1;dialogHeight:500px;dialogWidth:500px;resizable:yes;status:no;location:no;menubar:no;help:no', function GotoPageAfterClose(pageid){if(pageid == 'hold') {STSNavigate(unescape(decodeURI('{SiteUrl}'))+
'/_layouts/15/hold.aspx'
+'?ID={ItemId}&List={ListId}'); return false;} if(pageid == 'audit') {STSNavigate(unescape(decodeURI('{SiteUrl}'))+
'/_layouts/15/Reporting.aspx'
+'?Category=Auditing&backtype=item&ID={ItemId}&List={ListId}'); return false;} if(pageid == 'config') {STSNavigate(unescape(decodeURI('{SiteUrl}'))+
'/_layouts/15/expirationconfig.aspx'
+'?ID={ItemId}&List={ListId}'); return false;}}, null);
0x0
0x1
ContentType
0x01
898
Document Set Version History
/_layouts/15/images/versions.gif?rev=40
javascript:SP.UI.ModalDialog.ShowPopupDialog('{SiteUrl}'+
'/_layouts/15/DocSetVersions.aspx'
+ '?List={ListId}&ID={ItemId}')
0x0
0x0
ContentType
0x0120D520
330
Send To other location
/_layouts/15/images/sendOtherLoc.gif?rev=40
javascript:GoToPage('{SiteUrl}' +
'/_layouts/15/docsetsend.aspx'
+ '?List={ListId}&ID={ItemId}')
0x0
0x0
ContentType
0x0120D520
350
Download/Save OCE file - Required to connect 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 network or personal.