1 / 56

Oracle XML Reporting Engine: Empowering MIS Solutions

Understand how to create and customize reports using Oracle XML Reporting Engine. Learn about its flexible querying capabilities, data normalization and transformation, and storage of query results in XML. Explore the implementation process of generating XML documents, saving them in the XML.DB repository, and transforming data for reporting needs.

churchillj
Download Presentation

Oracle XML Reporting Engine: Empowering MIS Solutions

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Report Engine Chris Leuer – Lead Software Engineer California Community College Solution Center SunGard Higher Education January 2010

  2. California Community College Team • Mike Agnew – General Manager of Solution Centers • Mike Reid – Director • TJ Baugus – Student Analyst • Jan Wilder – Human Resources Analyst • Chris Leuer – Lead Software Engineer • Gladys Rocher – Senior Developer • Moises Diaz – Developer • Joel Rodriguez – Developer

  3. Objectives • Understand purpose and design of Report Engine • Explore support considerations • Learn how to create and customize Reports

  4. Purpose • Produce reports from Banner tables • Allow flexible queries • Normalize and transform data • Store query results from relational database in XML • Query results available in report form outside database

  5. Solution • Flexibility of Oracle XML DB allows us to store, transform and report any relational data • Adheres to Extract, Transform, Load (ETL) methodology • Is compatible with a multiple of reporting requirements. (MIS and more)

  6. Why for MIS? • Faster development and deployment of reports, than traditional programs • Community College staff can alter queries to meet institutional needs • A Banner integrated interface is provided to manage reports. • MIS specific formatting provided by Banner job GVPREPT • MIS requirements change overtime

  7. Report Engine Implementation • The engine generates a XML Document from a dynamic SQL statement • The XML Document is saved into a XMLType Column in a table • The XML Document is transformed and saved in XML DB Repository • NOTE: You can now bypass the XML DB repository to save disk space and memory usage

  8. Why Oracle XML • Native support for XML in the Oracle database • Persistence models for XML Content • URL-centric access to XML content • Abstraction of data using native XML type

  9. relationaldata xml db repository run report extract transform jobsuboutput xmltypecolumn

  10. Terminology • Report • A unique identifier for the report defined the reports engine • For example, we say that we are going to run report named “CALENDAR” • Element • After extract and transform steps are complete, an element is a named value in the notation: • <LAST-NAME> James </LAST-NAME>

  11. How do I run a Report • Reports are submitted in the background to run as Oracle jobs on GVAREPT • GVAREPT generates the extract and transformed XML • Banner job gvprept generates final MIS data file

  12. Status button

  13. Questions about overview Next – Support Considerations

  14. Support considerations • Report Engine (REPT) is required for California Baseline (CALB) • REPT 7.5 is highest version available for Banner 7.x • REPT 8.0 is highest version available for Banner 8.x • Dependencies for releases can be found in the C3SC Interdependency matrix

  15. Support from Actionline • Log into the SGHE Customer Support Center. Current link is http://connect.sungardhe.com. • For REPT problems, • Product Line = Banner • Product = General • Module = SC California REPT Report Engine • For MIS problems • Product Line = Banner • Product = Student  (or General) • Module = SC California CALB

  16. Solution Center website • http://confluence.sungardhe.com/display/solcentre/C3SC+Home

  17. Documentation • Documentation is available under custom files SC-California-CALB

  18. Documentation

  19. Report Engine (REPT) releases

  20. Dependencies

  21. Install considerations • REPT install creates REPTMGR schema • No gostage. Check logs for any Oracle errors. • Version table is RVRVERS • Check for patches in SC-California-CALB section of custom files • Source files are migrated to $BANNER_HOME/rept

  22. FAQs • FAQ 1-3SGP7Y < or > characters found in scbcrse_title result in error LPX-00210 for MIS CB report • FAQ 1-4MTD7J Oracle jobs can conflict with poorly written auditing code • FAQ 1-4OKYPV – work around for memory • FAQ 1-4QPZDQ – space problem

  23. Patches • All patch data is inserted into gurpost • All patch fixes are rolled into next release • apply p_memory_rept70500 for REPT 7.5 • Apply (1st) p_memory_rept80000 (2nd) p_rept_bund01_rept80000 for REPT 8.0 • Other patches are available for O/S specific defects and forms issues

  24. Security in REPT • All reports defined in Report Engine validation table GVVRPDF need to be defined in Banner Security • Use GUASECR (or) • Report Engine security scripts • REPT delivers one class BAN_REPT_C • Users need the BAN_REPT_C class to run MIS reports • Records are created in bansecr.GURUOBJ and bansecr.GURAOBJ tables for each report • For custom classes, create bansecr.GURUOBJ and bansecr.GURAOBJ record • Reports do not need defined as Banner objects in GUAOBJS

  25. Utility Scripts

  26. Setup • Oracle job setup (7.5) JOB_QUEUE_PROCESSES=5 JOB_QUEUE_INTERVAL=60 (replaced by _job_queue_interval)

  27. Common Issues • Aborted upgrades , check CALB logs • No background Oracle job processes • Extract Query returns no results • Missing MIS data , check CALB logs • Oracle errors on transform, check user functions

  28. Questions about support Next – how to create and customize reports

  29. Creating Reports • How to define a report • How to code a report • Improvements/Questions

  30. Skill set • Banner Report Engine interface • Query language SQL • Programming PL/SQL • Minimal XML • Knowledge of MIS data dictionary

  31. Oracle XML Repository

  32. Extract XML top element <STUDENT> <STUDENT_ROW> <First Name>Francois</First Name> <ID>210009406</ID> <Last Name>Pare</Last Name> <Street>18 Westward Way </Street> <ZIP_Code>19382 </ZIP Code> <State> PA </State> </STUDENT_ROW_> <STUDENT_ROW> <First Name>Bob</First Name> <ID>210009406</ID> <Last Name>Barker</Last Name> <Street>2457 Queens Way </Street> <ZIP_Code>19902 </ZIP Code> <State> NY </State> </STUDENT_ROW> </STUDENT> parent element child element Banner report name + row Banner report name

  33. Transformed XML top element parent element <STUDENT> <STUDENT_ROW> <Name>Francois Pare</Name> <Ident>210009406</Ident> <State>Pennsylvania</State> </STUDENT_ROW> <STUDENT_ROW> <Name>Bob Barker</Name> <Ident>210009406</Ident> <State>California</State> </STUDENT_ROW> </STUDENT> new child element Banner function element Banner direct element Banner map element

  34. Transform elements Direct - direct element value to element value relationship Function - value derived from user created function Map- maps to value in mapping form GVATRMP

  35. direct example MIS element SB last name

  36. map example MIS element SB11 Education level

  37. Map example (cont) GVATRMP

  38. function example MIS item SB00

  39. Transform function -- support cursor in gv_mis_global_bp CURSOR Get_SPBPERS_C(p_pidm SPRIDEN.SPRIDEN_PIDM%TYPE) IS SELECT SPBPERS_SSN FROM SPBPERS WHERE SPBPERS_PIDM = p_pidm;

  40. Function (cont) row of elements is a collection of element from extract parameter set has 1 parameter FUNCTION f_get_identifier(rowofelements IN rept_rowofelements) RETURN VARCHAR2 IS lv_extract_pidm gvbtrel.gvbtrel_element%TYPE := 'PIDM'; lv_pidm SPRIDEN.SPRIDEN_PIDM%TYPE; lv_extract_id gvbtrel.gvbtrel_element%TYPE := 'ID'; lv_id SPRIDEN.SPRIDEN_ID%TYPE; lv_ssn SPBPERS.SPBPERS_SSN%TYPE; ..

  41. Function (cont) BEGIN -- find element PIDM lv_pidm:=gv_rept_xml.f_find_element_value_inrow(lv_extract_pidm, rowofelements); lv_id:=gv_rept_xml.f_find_element_value_inrow(lv_extract_id, rowofelements); ..

  42. Function (cont) OPEN Get_SPBPERS_C(lv_pidm); FETCH Get_SPBPERS_C INTO lv_ssn; IF Get_SPBPERS_C%NOTFOUND OR lv_ssn IS NULL OR F_is_all_numeric(lv_ssn) = 'N' OR LENGTH(lv_ssn) <> 9 THEN CLOSE Get_SPBPERS_C; RETURN lv_id; ELSE CLOSE Get_SPBPERS_C; RETURN lv_ssn; END IF; END f_get_identifier;

  43. Banner map/function combo MIS element SB22 Academic Standing

  44. GVATRMP

  45. Use F_mapvalue() OPEN Get_SHRTTRM_C(lv_pidm,lv_term); FETCH Get_SHRTTRM_C INTO lv_cast_code; .. RETURN gv_rept_xml.F_MapValue(lv_report,'SB22',lv_cast_code);

  46. How to create a report • define report GVVRPDF • create query GVARSQL • define column mapping GVAMAPD • define parameters GVAPDEF • define final elements GVATREL • Define any element maps GVATRMP • Questions about forms?

More Related