The CUNY Data Warehouse Crystal Reports

1. The CUNY Data Warehouse & Crystal Reports Utilizing the Data Warehouse and Crystal Enterprise to provide a robust environment for campus legacy systems reporting needs.

2. Who Are We

3. Presentation Breakdown

4. Project Background

5. Project Purpose & Background Need for a streamlined SIMS data reporting and distribution process in a flexible & secure environment The creation of CUNY�s Data Warehouse & Crystal Enterprise System? The opportunity to change our processes Expand the functionality and service provided by campus Mainframe/Production department Ease departmental end-users into the CUNY First Era ? end users can generate their own reports Currently we have over 40 departments that require data outputs such as lists and labels from SIMS. Our Mainframe/Operations programmers generate these outputs directly from SIMS using JCL, COBOL and EzTrieve. This current method was proving to be inefficient and needed to be improved We a few years back we discovered that CUNY established a Data Warehouse consisting of SIMS data. CUNY�s introduction of the Oracle DataWarehouse initiated this project and provided us an opportunity in which we could change our reporting and report distribution process. The Datawarehouse became the catalyst for transformational change. Streamlining our reporting process has also provided us an opportunity to change and expand what services are provided by the Mainframe/Operations division Additionally, as this presentation will demonstrate, the method in which we have changed our reporting process, will enable departmental end-users to have a greater part in generating their own reports and thereby enhance their skills which will prove to be beneficial when the Student Pillar of CUNY First is launched. Currently we have over 40 departments that require data outputs such as lists and labels from SIMS. Our Mainframe/Operations programmers generate these outputs directly from SIMS using JCL, COBOL and EzTrieve. This current method was proving to be inefficient and needed to be improved We a few years back we discovered that CUNY established a Data Warehouse consisting of SIMS data. CUNY�s introduction of the Oracle DataWarehouse initiated this project and provided us an opportunity in which we could change our reporting and report distribution process. The Datawarehouse became the catalyst for transformational change. Streamlining our reporting process has also provided us an opportunity to change and expand what services are provided by the Mainframe/Operations division Additionally, as this presentation will demonstrate, the method in which we have changed our reporting process, will enable departmental end-users to have a greater part in generating their own reports and thereby enhance their skills which will prove to be beneficial when the Student Pillar of CUNY First is launched.

6. Citytech�s Current report distribution process Our Eztrieve/JCL/COBOL library consists of over 1300 programs! It�s obvious that many of these reports are redundant, and unnecessary. The Programs have evolved throughout the years and each department wants a slight variation of the same report Our departmental end-users are often times unclear or unsure as to what they want and about definitions of criteria.. So for example, The SEEK department may consider a certain set of criteria as a �SEEK� student, but the Social Sciences department may consider a �SEEK� student to have a different criteria. Work continues to grow but staffing does not. Throughout the years, the need for reports and output increased dramatically. OurMainframe/Operations division was completing literally thousands of requests for lists and labels for departments within a fiscal year Running reports during registration is cumbersome and almost impossible -We have been printing reports massively. If the end user realizes that they left out a field that they needed, we must print again, and again. We print certain reports every morning for several departments � as they requested. There are often times that a few of the end users won�t pickup the report and thereby that paper gets wasted. Our Eztrieve/JCL/COBOL library consists of over 1300 programs! It�s obvious that many of these reports are redundant, and unnecessary. The Programs have evolved throughout the years and each department wants a slight variation of the same report Our departmental end-users are often times unclear or unsure as to what they want and about definitions of criteria.. So for example, The SEEK department may consider a certain set of criteria as a �SEEK� student, but the Social Sciences department may consider a �SEEK� student to have a different criteria. Work continues to grow but staffing does not. Throughout the years, the need for reports and output increased dramatically. OurMainframe/Operations division was completing literally thousands of requests for lists and labels for departments within a fiscal year Running reports during registration is cumbersome and almost impossible -We have been printing reports massively. If the end user realizes that they left out a field that they needed, we must print again, and again. We print certain reports every morning for several departments � as they requested. There are often times that a few of the end users won�t pickup the report and thereby that paper gets wasted.

7. What CUNY�s Data Warehouse Has Initiated - Working with CUNY�s Oracle Datawarehouse would now require our Mainframe programmers to be knowledgeable in other reporting tools and database languages, thereby expanding our staff�s skills to move towards CUNYFirst and changing the division�s role in terms of systems reporting - Working with CUNY�s Oracle Datawarehouse would now require our Mainframe programmers to be knowledgeable in other reporting tools and database languages, thereby expanding our staff�s skills to move towards CUNYFirst and changing the division�s role in terms of systems reporting

8. Data Warehouse &Crystal Enterprise - The Catalyst for Change at Citytech Therefore, as a result, the creation of the Data warehouse and the Crystal Enterprise became the opportunity, or catalyst for change at Citytech. So, how exactly have we utilized this tool.Therefore, as a result, the creation of the Data warehouse and the Crystal Enterprise became the opportunity, or catalyst for change at Citytech. So, how exactly have we utilized this tool.

9. How Citytech Is Using The Warehouse and The Crystal Enterprise -On our left we can see our current report distribution process -right side shows new and improved version using Crystal Enterprise. - Department users customize/share and run reports designed for them - CIS programmers design/develop and deploy Crystal Reports using Business Objects and Data Warehouse -Data Warehouse is refreshed/populated daily with data from SIMS-On our left we can see our current report distribution process -right side shows new and improved version using Crystal Enterprise. - Department users customize/share and run reports designed for them - CIS programmers design/develop and deploy Crystal Reports using Business Objects and Data Warehouse -Data Warehouse is refreshed/populated daily with data from SIMS

10. Causes: Large quantities of requests received. Redundant reports increasing the size of the library. Need of campus wide secured report sharing system.

11. Large Quantity of Report Requests Citytech Library consists of approximately 1300 EzTrieve Reports On a typical day, CIS receives 20-25 requests for ad-hoc data extraction reports. The Mainframe/Operations division of CIS completes over 3000 requests every fiscal year.

12. Report of all SEEK students (program code C and indicator Y) enrolled in MAT 0650, MAT 1175 and MAT 1275 for Fall 2008. List of SEEK students in MAT 0550, MAT 0630, ENG 0900R, ENG 0920R and ENG 0920W. Please also include ADM codes Please run all mid-semester grades for SEEK students (C, CI and Y indicator) Please no CP program code. Please give a list of students that have only CP program code for Summer 2008 and or Fall 2008. Some Examples of Redundant Requests -These are few examples of daily requests for ad-hoc reports. -requests received within a couple days from different users of SEEK department. -show redundancy-These are few examples of daily requests for ad-hoc reports. -requests received within a couple days from different users of SEEK department. -show redundancy

13. To satisfy all the user requests and due to lack of scalability, the EZTrieve library consists of several different versions of similar programs There are versions A-J of a report for Potential Drops (REG548) Various Versions Of The Same Report -here the slide shows various versions of SEK102, which essentially serve the same purpose-here the slide shows various versions of SEK102, which essentially serve the same purpose

14. Crystal Report provides interactive drop down menus with which users can customize their own reports with parameters of their choice. Several EZTrieve reports can be consolidated into one Crystal Report. Crystal Enterprise facilitates report sharing among multiple users and departments. How Will Crystal Enterprise Help Reduce Redundant Reports? Judith Ullman will explain more on Crystal Enterprise Server. Judith Ullman will explain more on Crystal Enterprise Server.

15. Using Crystal Enterprise, data is delivered using row level and column level security. In other words, you can design a single school wide report and have individual departments see only their data. Department users can get their reports within minutes instead of weeks. Reports are accessible on the web with a secured connection. How Will Crystal Enterprise Improve Report Generating and Sharing?

16. Reports can be scheduled to run on the enterprise server at set time. Users can view the prescheduled instances of these reports at any time of the day. IT staff can devote their time in development of more complex user requests. Less burden on IT staff. Staff learning new skills such as relational database programming and architecture. How Will Business Objects Enterprise Improve Report Generating and Sharing? -staff is learning SQL and other high level programming languages that will help them expand their knowledge for Cunyfirst.-staff is learning SQL and other high level programming languages that will help them expand their knowledge for Cunyfirst.

17. Promote Green Reporting Reduced paper usage Online report distribution reduces tremendous amount of paper usage. Users are generating reports as needed, when needed. Users are generating reports as needed when needed Users are generating reports as needed when needed

18. How Do Citytech Departments Share & Generate Reports Using Crystal Enterprise? By logging on to a secured enterprise website https://cisebo.cuny.edu

19. How do departments run their reports?

20. And by selecting parameters of their choice�

21. How do departments run their reports? -more parameters shown here.. semester and special program codes-more parameters shown here.. semester and special program codes

22. How do departments run their reports?

23. Who is using Crystal Enterprise @ Citytech? SEEK department/College Discovery Analytical reports and mailing labels for student academic progress, probation, stop codes etc. Vice President of Enrollment Management Statistical and analytical reports Ongoing development for Admissions department Student Advisement

24. Tools & Software Used in Development & Deployment Citrix : Virtualization and Secure Remote Access software Oracle Data Warehouse : Relational data source Crystal Reports XI: Reporting software Business View Manager : Tool to create Business Views Crystal Enterprise Server: Enables report sharing Please contact Judith Ullman if you need more information on any of the software installations mentioned above.Please contact Judith Ullman if you need more information on any of the software installations mentioned above.

25. Business Objects Enterprise

26. What Is In This Presentation? What is Business Objects Enterprise? What can I do with it? Who owns it? What technology do I need to use it? What human resources are needed? What does it cost? (Nothing!)

27. What Is Business Objects Enterprise? Business Objects Enterprise (BOE), also known as Crystal Enterprise, is a web-based report-sharing system.

28. What Can I Do With BOE? You can access the system from anywhere there is an internet connection. You can access all the folders and reports, and only the folders and reports that you have rights to. You can run reports whenever you want. You can look at any past run of a report no matter how long ago. Reports can be exported to PDF, Excel, and Word.

29. What Can I do with BOE? You can schedule reports to run nightly, weekly, month-end, or on any other schedule, including custom schedules for your office. Automatic notifications can let the appropriate people know if a report fails. You can be automatically alerted if a threshold is met. The Business View Manager is used to create the data sources. You can implement row and column security.

30. You can access the system from anywhere there is an internet connection.

31. You can access only folders and reports that you have rights to.

32. You can run reports whenever you want.

33. (Enter parameter choices.)

34. (See the result.)

35. You can look at any past run of a report no matter how long ago.

36. (Choose the past instance.)

37. (View the past instance.)

38. Reports can be exported to PDF, Excel, and Word.

39. (View the exported file.)

40. (Export to Excel.)

41. (View the exported file.)

42. You can schedule reports to run nightly, weekly, month-end, or on any other schedule, including custom schedules for your office.

43. Automatic notifications can let the appropriate people know if a report fails.

44. The Business View Manager is used to create the data sources.

45. You can implement row security.

46. You can implement column security.

47. Who Owns It? Hosted by CUNY-CIS Judith Ullman is the head system administrator Any office can use it for Administrative Data Warehouse Crystal Reports Reports can be from some other databases such as InSite Asset Management Further databases can be requested, and are subject to network constraints

48. What Technology Do I Need To Use it? An Internet connection. Crystal Reports. Available via a CUNY Citrix account. Some Central Offices can use Crystal Reports on their desktop and a direct connection to Enterprise. Business View Manager. Available via a CUNY Citrix account. Some Central Offices can use Business View Manager on their desktop and a direct connection to Enterprise.

49. What Human Resources Are Needed? A Business View developer. A report developer. An administrator to manage your folders and reports. Must attend a free class, no more than 2 days. Ongoing: A few hours work now and then.

50. Q&A