Operational Data Store Presented by Penney Doughtie Revised 1/26/2023
ODS, or Operational Data Store, is a data storage system designed to retrieve production data for reporting purposes. Access to ODS is granted through security protocols, allowing users to access a wide range of finance and human resources data. The system offers detailed transaction history, ledger balance information, payroll details, and more. ODS is a vital tool for querying and analyzing financial and HR data effectively.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
SCT BANNER ODS OPERATIONAL DATA STORE PRESENTED BY PENNEY DOUGHTIE REVISED 1/26/2023 1
WHAT IS ODS? OPERATIONAL DATA STORAGE SCT Banner Product for Reporting Retrieves production data Inquiry only, no update 2
HOW DO I GET ODS ACCESS? STEP 1 STEP 2 STEP 3 Security access to Banner Finance ODS Password Reset Instructions Excel Settings 3
HOW DO I GET THE SOFTWARE I NEED? Create a TeamDynamix ticket https://ecu.teamdynamix.com/TDClient/1409/Portal/Requests/ServiceDet?ID=31565 Request PC installation of ODBC drivers for REPT No cost to the department 4
ODS FINANCE DATA What data is available? Transaction Detail History General ledger balance data Operating ledger balance data Encumbrance Summary Finance data starting 07/01/1995 thru last night Payroll History 5
ODS FINANCE DATA Banner Finance Data July 1, 2005 to present Database = REPT Owner= ECU Table = FS_Transactions_with Check FRS Finance Data July 1, 1995 to January 31, 2006 Database = Rept Owner = ECUHIST Table = FRS_Transactions 6
ODS HUMAN RESOURCES DATA FRS HR Data July 1, 1995 to January 31, 2006 Database = Rept Owner = ECUHIST Table = FRS_Payroll_Trans ******Missing payroll data from February 1, 2006 to June 30, 2007 can be requested by contacting Systems Coordination at 328-2706. This data timeframe is located on SBPD instance in ECUHISTORY.PY_HIST_BASE by payroll check date and SSN, this is not included in ODS security. Banner HR Data July 1, 2007 to present Database = Rept Owner = ECU Table = PY_PAYROLL_HISTORY Use transaction date 7
ODS QUICK QUERIES LOCATED ON THE SYSTEMS COORDINATION WEBSITE (HTTPS://FINANCIALSERVICES.ECU.EDU/SYSTEMS-COORDINATION/) Banner Vendor History Search Document Search Operating Ledger Balances (with Pool Account Subtotals) Transactions Banner Encumbrance Summary Payroll History Banner Cash Balances by Orgn (for Foundations and Special Funds) Banner Account Description 8
GO TO THE SYSTEMS COORDINATION WEBSITE (HTTPS://FINANCIALSERVICES.ECU.EDU/SYSTEMS-COORDINATION/) Scroll down to the Quick Query List Click Banner Vendor History Search Click Enable Content , beside Security Warning 9
ENTER THE BANNER VENDOR NUMBER AND FISCAL YEAR TAB TO CELL A9, THEN CLICK REFRESH 11
SERVICE NAME WILL DEFAULT TO REPT, ENTER PIRATEID IN THE USER NAME FIELD , ENTER REPT PASSWORD. CLICK OK. 12
GO TO THE SYSTEMS COORDINATION WEBSITE (HTTPS://FINANCIALSERVICES.ECU.EDU/SYSTEMS-COORDINATION/) Scroll down to the Quick Query List Click Document Search Click Enable Content 14
ENTER THE DOCUMENT NUMBER, TAB TO CELL A7, CLICK REFRESH 16
SERVICE NAME WILL DEFAULT TO REPT, ENTER PIRATEID IN THE USER NAME FIELD, ENTER REPT PASSWORD. CLICK OK. 17
GO TO THE SYSTEMS COORDINATION WEBSITE (HTTPS://FINANCIALSERVICES.ECU.EDU/SYSTEMS-COORDINATION/) Scroll down to the Quick Query List Click Operating Ledger Balances (with Pool Account Subtotals) Click Enable Content , beside Security Warning 19
ENTER FUND, ORGN, PROGRAM, FISCAL YEAR AND CALENDAR MONTH, TAB TO CELL A9, CLICK REFRESH 21
SERVICE NAME WILL DEFAULT TO REPT, ENTER PIRATEID IN THE USER NAME FIELD, ENTER REPT PASSWORD. CLICK OK. 22
GO TO THE SYSTEMS COORDINATION WEBSITE (HTTPS://FINANCIALSERVICES.ECU.EDU/SYSTEMS-COORDINATION/) Scroll down to the Quick Query List Click Transactions Click Enable Content Enter Fiscal Year Enter Fund or % for all Funds Enter Organization or % for all Funds Enter Effect Cash (Y, B, E, or %) Y = Revenue & Expense Transactions B = Budget Transactions E = Encumbrance Transactions % = All Transactions Enter Beginning Account Number and Ending Account Number 24
ENTER FUND, ORGN, PROGRAM, BEG TRANSACTION DATE, END TRANSACTION DATE, FISCAL YEAR, TAB TO CELL A9, CLICK REFRESH 26
SERVICE NAME WILL DEFAULT TO REPT, ENTER IN THE USER NAME FIELD, ENTER REPT PASSWORD. CLICK OK. 27
FORMAT THE DATA Once the data is returned, it can be reformatted. For example in the Banner Vendor History Search Quick Query Click Review Click Unprotect Worksheet Click the drop down in the desired column heading A pop up window will have options, like sorting or selecting the elements of the FOAPAL. 29
GO TO THE SYSTEMS COORDINATION WEBSITE (HTTPS://FINANCIALSERVICES.ECU.EDU/SYSTEMS-COORDINATION/) Scroll down to the Quick Query List Click Banner Encumbrance Summary Click Enable Content , beside Security Warning 30
SERVICE NAME WILL DEFAULT TO REPT, ENTER IN THE USER NAME FIELD, ENTER REPT PASSWORD. CLICK OK. 33
LIFE OF A PURCHASE ORDER Department enters the requisition in Purchasing PORT Requisition goes through electronic Purchasing PORT approvals Materials Management approves Requisition and turns into Purchase Order (funds are encumbered in Banner and deducted from the budget) Department completes the receiving process in Purchasing PORT Accounts Payable enters the vendor invoice in Banner Funds become automatically liquidated which means the PO is closed and the expense is moved from the Encumbrance column to the Year to Date column on the budget. 35
PURCHASE ORDER MANUAL LIQUIDATION EXAMPLES (CLOSES PURCHASE ORDER) PO price decreases on supplier/vendor invoice Example: PO is $100 and invoice is $95 if appropriate, the department uses Purchasing PORT comments to request a liquidation Credit memos/credit from supplier When AP keys a credit, the funds are re-encumbered. Use Purchasing PORT comments to inform AP if items will be rebilled or reshipped Canceling check and/or invoice in Banner When AP cancels a check or invoice, the funds are re- encumbered. Communicate through Purchasing PORT comments if these funds are to be liquidated. 36
HOW TO LIQUIDATE A PURCHASE ORDER IN PURCHASING PORT Log into ECU PiratePort Click Tools Select PORT Purchasing Request (listed under Purchasing) Enter the desired Purchase Order in the Search Cell (top right) Click the search icon Select Purchase Order in dropdown list Click Comments tab Click Add Comment Check Accounts Payable email If Accounts Payable is not listed, then: Click add email recipient Enter Payable in Last Name cell Click Search Click Select listed under Action column Verify that only Accounts Payable is checked Type detailed explanation as needed in the comment box. Include the reason why the purchase order needs to be manually liquidated. Click Add Comment Contact Accounts Payable at accountspayable@ecu.edu with liquidation questions. 37
CREATE A CUSTOMIZED QUERY Open Microsoft Excel Click Data Click Get Data Click From Other Sources Click From Microsoft Query In the Choose Data Source window, scroll down and select REPT* Click OK Type PirateID in the User Name, type REPT password. Click OK Congratulations You are now in ODS ! 38
SELECT THE DESIRED TABLE Click Option Select ECU as the Owner for Banner Data Select ECUHIST as the Owner for FRS Data Click OK Select the desired table (FS_TRANSACTION_WITH_CHECK) The Query Wizard will then lead you through the development of a query. 39
CUSTOMIZED QUERY TIPS Use Effects Cash as a Filter Y=Receipt and Expense transactions B=Budget and Encumbrances transactions Use Fiscal Year as a Filter Use FOAP as a Filter Account number greater than 49999, this will exclude the Assets and Liabilities that regular users don t need. 40
ODS QUERY TIPS The ODS password is not synced with the PirateID/intra password. To make changes to the data, click Review then click Unprotect The ODS data is as of last night 41
TO REQUEST SECURITY ACCESS The following website provides detailed instructions on how to request Banner Finance Security Access. https://financialservices.ecu.edu/systems-coordination-banner-security/ An automated email will be sent to each user when Banner Finance security access is granted and the temporary password is included. Password = is a temporary number issued by ITCS that will be updated by the user. Follow the directions in the email from ITCS. The ODS password is not synced with the PirateID/intra password. If you have questions regarding security form, please contact Penney Doughtie doughtiep@ecu.edu. 42
DOCUMENT PREFIX CODE EXAMPLES (ASSIGNED BY BANNER, BASED ON RULE CLASS CODE LIST ) PURCHASING and ACCOUNTS PAYABLE B Budget Transactions K Payroll Transactions and Cash Receipts P Purchase Orders J Journal Entries I Invoices CP Copiserv Charges Q Electronic Invoices between vendor and ECU ML Mail Services ! ACH Payment to a Vendor by Direct Deposit RK Risk Management UP University Printing and Graphics CHROME RIVER BK Bank Cards, Credit Cards, ID Charges TO E10 Travel Pre-Approval Original Encumbrance ZZ Campus Operations Journal Entries WC Workmen s Comp TA E020 Partial Encumbrance Liquidation UT Campus Operations Utility Charges TL E032 Full Encumbrance Liquidation KY Campus Operations Key Charges TC INNI Chrome River Travel Reimbursement LB Campus Operations Labor Charges TM INNI Manual Travel Reimbursement GO Campus Operations Gas and Oil Charges PC J60 ProCard Transactions 43
BANNER OPERATING BUDGET ACCOUNTS Also known as Budget Pool Accounts 72000 Supplies Budget 72350 Library Books Budget 72400 Equipment Budget 73000 Contractual Services Budget 73200 Travel Budget 73400 Current Services Budget 73600 Fixed Charges Budget 73800 Undistributed Budget 73950 Grants and Aides 74060 Education Awards 75000 Utilities Budget BANNER SALARY BUDGET ACCOUNTS 60000 Miscellaneous Salary Budget 60100 EPA Non Teaching Salary Budget 60150 EPA Faculty Salary Budget 60200 SPA Staff Salary Budget 60250 SPA LEO Salary Budget 61200 Benefits Budget 44
WEBSITES FOR ADDITIONAL BANNER INFORMATION https://financialservices.ecu.edu/systems-coordination/ https://financialservices.ecu.edu/banner-financial- services/ 45
HELPFUL RESEARCH ACCOUNTING ECUBIC QUERIES ecuBIC Research Accounting link https://ecubic.ecu.edu/Reports/browse/ecuBIC/Finance/Research Accounting 46
