IBIS Data Warehouse & User Group

1. If this document is not viewed in PDF format, please use MS Internet Explorer to View the Presentation . Slides DO NOT appear correctly using Netscape or various browsers. IBIS Data Warehouse & User Group What is the University Data Warehouse Accessing Engineering Warehouse Data from College Server College of Engineering - IBIS and ISIS User Groups User Group MS Access File – Available Queries and Reports Original: U:/IBIS/Documentation/IntroPresentation/IBIS_Data_Warehouse.htm (05/27/03) Revised: TrainingWebsite :/IBIS/Documentation/IntroPresentation/IBIS_Data_Warehouse.ppt & .pdf (05/28/09)

2. What is the IBIS Data Warehouse? What is the Data Warehouse? What Data is Available? How do I Access the Data Warehouse?

3. What is the IBIS Data Warehouse? • The IBIS Data Warehouse contains budgetary/business data from the University IBIS System • IBIS - Integrated Business Information Systems • IBIS was created to process financial and personnel documents electronically • Includes Human Resource, Payroll, Purchasing, Financial, Budgeting, Facilities, Vendor, and Pay & Effort systems • Transactions are entered by users into the IBIS System • Purchase Orders, General Stores Requisitions, Vehicle Reservations, etc. • Data Originates from the University Mainframe System • IBIS System – Business Data • ISIS System – Student Data • The Data Warehouse is one of three options to retrieve the data in report form. • There are three different options for retrieving data • IBIS Financial System • Financial Information Tool (FIT) • Data Warehouse

4. IBIS Financial System (ICAG, etc) Real-time data Output IBIS System Using ‘EASY’ Electronic Approval System Forms (Purchase Orders, etc.) FIT Tool (ONE budget at a time) Real-time data Output INPUT Data Warehouse (ALL budgets) Updated each evening Output REPORTING Three Reporting Options to Choose From…

5. What Data is Available from the Warehouse? • Business/Budgetary Data and Student Data is available using the data warehouse tables. • Business and Student Data is separate and each contains multiple tables.

6. Started in 1994 Its goal is to make access to institutional data Easier More Flexible Widely Available To unlock the data from the mainframe and make it accessible from the desktop More than a dozen databases Approximately 100 tables Approximately 100 million records Data is transferred from IBIS and ISIS on a regular schedule Data is Read-Only – it can be read but cannot be modified Represents a snapshot at one point in time University Data Warehouse – General Information

7. Data divided into subsets oriented to various functional areas Alumni and Development Weekly Student Enrollment Graduate Applicant Undergraduate Applicant Student and Course Human Resources Data IBIS Financial Official Student Enrollment Student Retention and Progression Non-credit Student Code Table Data …And More http://ais.its.psu.edu/services/warehouse/default.aspEmail: AIS-Support@psu.edu Phone: (814) 863-2276 University Data Warehouse – Subsets of Data & Contact Information

8. College of Engineering – Downloading Data from University Warehouse • Data is Downloaded Daily from the University Data Warehouse • Data downloaded to the College of Engineering SQL Server, by Walt Beatty, Administrator of the SQL Server. • Data is Separated by Type and into Multiple Tables • IBIS – College Specific Financial Information • ISIS – College Specific Student Information • Using the College of Engineering’s SQL Server Provides for Faster Access to Data because it only includes the College of Engineering Data • Proper Authorization is required to Access the SQL Server

9. IBIS User Group MS Access Database File Available – UsersGroupDatabaseVersion#.mdb • Through the IBIS User Group an MS Access File is Available to College Data Warehouse Users and Contains Created Queries and Reports using the IBIS Data. • Provides Easy Access to Queries, Forms and Reports Already Created • Existing Queries and Reports Can be used as Examples and Guides when Creating New Queries, Forms and Reports. • The File can be copied to your local hard drive from the network general information drive (for most users, this is identified as the Q: drive). • For information on copying this file and setup, visit http://www.ecs.psu.edu/training/ibis/documentation/Copy-Database-Ver-8.htm • Monthly User Group hands-on sessions are scheduled and minutes are posted. Please visit http://www.ecs.psu.edu/training/ibis/ibis.htm.

10. What Support is Available in the College? College IBIS Data Warehouse User Group • To Hold Discussions and Hands-On Demonstrations on Data Warehouse Information and Use • Provide Support in the use of the College SQL Server and Microsoft Accessto Create Queries/Reports • Supplements what the University is Providing Regarding Training Initiatives, etc. • Meet Approximately Once a Month to Demonstrate and Practice Creating Queries and Reports • IBIS Listserv is set up to notify interested users of meetings, questions or announcements. • To be added to the IBIS listserv, email Sue Pysher (spysher@engr.psu.edu) or training@engr.psu.edu MS Access Basic Training Available • If you need basic training on MS Access, please visit our training schedule at http://www.ecs.psu.edu/training/schedule.html IBIS User Group Documentation Online • Documentation, meeting schedules and minutes are available online. Visit our training home page at http://www.ecs.psu.edu/trainingand click the [College Computer User Groups] linkand select IBIS User Group.

11. IBIS (Mainframe) Data is Input by Users Using Electronic Forms (Purchase Orders, etc.) University Data Warehouse Data is Downloaded from the Mainframe and Stored in Tables College of Engineering DataWarehouse Tables University Data is Downloaded to the Engineering SQL Server - College of Engineering Data Only IBIS User Group College Users Share Queries/Reports Related to the IBIS Warehouse ISIS User Group College Users Share Queries/Reports Related to the ISIS Warehouse IBISUser Group Database (MS Access database file containing Reports created by IBIS User Group) ISIS User Group Database (MS Access database file containing Reports created by ISIS User Group) Review - Organization of the Warehouse and How the Data Gets to You NOTE: Users accessing ‘student’ ISIS warehouse data will connect directly to the University data warehouse tables, but IBISFIN warehouse data is being downloaded to the College’s SQL Server.

12. How Do I Get Access? Request Penn State (ITS) Access Account & IBIS/ISIS/FIT (AIS) Authorization Request Authorization to the College of Engineering Downloaded Data Warehouse Tables (Engineering SQL Server) Copy User Group MS Access Database File to Local Hard Drive Run Queries/Reports Available in User Group File

13. Step #1 – Request Penn State (ITS) Access Account & IBIS/ISIS/FIT (AIS) Authorization You Need: • Penn State (ITS) Access Account • Complete the Penn State Access Account Form at http://aset.its.psu.edu/accounts/accountsforms/AccessAccountForm.pdf • Send form to 101 Computer Building • IBIS/ISIS/FIT (AIS) Authorization • Administrative Aide or department representative contacts Walt Beatty, the College’s Area Security Representative (ASR) • 863-0411 or emailwjbdo@engr.psu.edu

14. Step #2 – Request Access to the College of Engineering’s IBIS Data Warehouse Tables • Department Administrative Aide Contacts Walt Beatty (the Area Security Representative) for access 3-0411 or email wjbdo@engr.psu.edu • Request that your PC be configured for ODBC/SQL through your department technical contact. • Don’t know your Technical Contact? Visit http://www.ecs.psu.edu/ComputerSupport/techcontact.html • ODBC/SQL connection instructions for technical contacts -http://www.ecs.psu.edu/training/ibis/documentation/ODBCSetup.htm • This is the ODBC setup to configure access to the SQL Server in the College (ENGR_Warehouse) • Please note: If you’re using IBIS and ISIS warehouse data, ODBC must also be set up for the University’s SQL server (AIS_Warehouse). See below: • http://www.ecs.psu.edu/training/ISIS/documentation/AIS_ODBCSetup.htm • ISIS users will connect directly to the University’s warehouse tables, not through the College’s SQL server (IBISFIN).

15. Step #3 - Copy User Group MS Access Database File to Local Hard Drive • Copy the UsersGroupDatabaseVersion#.mdb MS Access database file located on Q: drive of College Network (Gen_Info network share) to your local hard drive. Link to instructions: • http://www.ecs.psu.edu/training/ibis/documentation/Copy-Database-Ver-8.htm • Please note that the latest ‘version’ of the file will be stored on Q: - versions # will change as revisions are made. • Once User Group file is copied, link to the College of Engineering IBIS Data Warehouse tables by clicking the ‘Utilities’ button from the Main Switchboard form • Once linked to tables, click the ‘Make Active Budgets Table’ button to create a table of your department’s accounts.

16. Step #4 – Run Queries/Reports from the User Group File • Once the User Group file is • copied locally • the data warehouse tables are linked • and your Make Active Budgets table is created in the file • You can then run the queries and/or reports from the Main Switchboard screen that appears when you first open the User Group file. When you open the IBIS User Group file, this Switchboard screen appears.

17. IBIS MS Access User Group File Taking a look at the Main Switchboard and Sublevels in the File that are Available to You (Once You’ve Copied the File to your Local Hard Drive, Linked to the Tables, and Made the Active Budgets Table following the instructions referred to at http://www.ecs.psu.edu/training/ibis/documentation/Copy-Database-Ver-8.htm ) )

18. If you Open the IBIS User Group Database File – Main Switchboard Form Appears First… Click appropriate button on the Main Switchboard to select a sublevel option.

19. If You Click the [Reports] Button on the Main Switchboard Screen… Click [Reports] button

20. The [Reports] Sublevel Options Appear – Select the Type of Report You Wish to Run Click a button to select the type of Report you wish to run Click ‘Return to Main Switchboard’ to return to the first Main Switchboard Screen Note: When you run various reports, you will be prompted to type in a parameter (account number, cost center, fiscal year, etc.)

21. If You Click the [Search] Button on the Main Switchboard Screen… Click [Search] button from the Main Switchboard

22. The [Search] Sublevel Options Appear Click a button to select the type of Search you wish to run Click ‘Return to Main Switchboard’ to return to the first Main Switchboard Screen

23. If You Click the [Utilities] Button on the Main Switchboard Screen… Click [Utilities] button from the Main Switchboard

24. The [Utilities] Sublevel Options Appear Click a button to select the Utility you wish to run This is where you could choose to AutoLink to the Tables and Make your Budgets Table Click ‘Return to Main Switchboard’ to return to the first Main Switchboard Screen

25. NOTE: Reports will be revised or added in this Access database file • Reports that are included in this Access User Group database file will be revised or added as time goes on. • If you have any questions or need assistance, please feel free to contact us at anytime at 863-4666 or Sue Pysher directly at 863-1197 (spysher@engr.psu.edu). • You can request to be placed on the College’s IBIS User Groupand/or ISIS User Group listserv so that you receive email notification about College user group meetings, various warehouse or file changes, and important warehouse information. Email training@engr.psu.eduand ask to be placed on our listservs. • IBISFIN Introduction to the Warehouse training is scheduled periodically in the College of Engineering. Please visit our online training schedule from our home page www.ecs.psu.edu/training or contact us at training@engr.psu.edu or call 863-4666 or 865-1444 to obtain more information.

26. Where Do I Go From Here? Links to All Resources and Documentation

27. Summary of Links for Additional Information

28. Want a printout of this Presentation? If viewing this in PDF format, simply Print before exiting ***OR*** Click here to open the presentation in the PowerPoint file format and print the slides: http://www.ecs.psu.edu/training/ibis/documentation/IntroPresentation/Ibis_Data_Warehouse.ppt Click [Open] from the dialog box that appears. If you click the Printer Icon, it will print one slide per page. If you choose the File menu in 2003 (or OFFICE button in 2007) and choose Print, under the ‘Print What’ area, you can choose to print Handouts and you can select the number of slides to print per page