Procurement Pipeline Data Mart Project Review Data Mart Support Group 11/28/2007

1. Procurement Pipeline Data Mart Project Review Data Mart Support Group 11/28/2007

2. Agenda • Overview of Procurement Pipeline Data Mart (PPDM) • Project Objectives • Star Schema Concepts • Application Architecture • Business Areas - Review of Data Model • Future Phases and Releases • Appendix • Demonstration of Discoverer Reports • Requisition Transaction Star • PO Transaction Star • Invoice Transaction Star • E-document Encumbrance Transaction Star • Procurement Pipeline Star • Procedures • Requesting Access to the PPDM and Discoverer • Discoverer and Data Mart Reporting Support • Discoverer Training Levels • Report Naming Conventions • Questions

3. Project Objectives • Procurement Pipeline Data Mart is a comprehensive single data source that will allow University management to track and monitor the Procurement Process as well as to provide consistent and accurate reporting of data to the State of Virginia. • Apply agreed-upon business rules for consistent interpretation of raw data • Facilitate retrieving most commonly requested information with ease.

4. PPDM – STAR Schema Concepts • Fact Table • The primary table in a dimensional model where the additive numerical performance measurements (known as Measures) of the business are stored. • Dimension Table • Dimensions are business factors (entry points) into the fact table • Contains the textual descriptors (known as attributes) of the business • Conformed Dimension • A dimension that is shared across fact tables. Dimensions are conformed when they are either exactly the same or one is subset of the other. • A conformed dimension is important because it allows queries to be executed across star schemas. • Grain • Is the level of detail associated with the fact table measurements. It describes a single row in a fact table. • All fact table grains fall into one of three categories: transaction, periodic snapshot, and accumulating snapshot, hence yields the three fundamental types of fact tables.

5. PPDM – STAR Schema Concepts (Cont’d)

6. Application Architecture

7. Conceptual Data Architecture • The conceptual data architecture has been defined to include 5 star schemas – the focus is to monitor the procurement process for the university.

8. PPDM Business Areas • Requisition Transaction Star • PO Transaction Star • Invoice Transaction Star • E-document Encumbrance Transaction Star • Procurement Pipeline Star

9. Illustration-- Subject Areas by Business Analysis Factors

10. PPDM Business Areas • Requisition Transaction Star – • AP_REQ_DIM • One line per requisition line item • AP_VND_DIM • One line per vendor • AP_VNDTYPE • Zero or more types for each vendor • AP_REQ_TRANS_FACT • One record per requisition line item per FOAPAL • AP_REQTRANS_FACT_DEL_HIST • History tracking table, stores all deleted (record removal) requisition line item FOAPAL records.

11. PPDM Business Areas • Identifying Cancelled Requisitions: • AP_REQ_DIM • The field REQ_CANCEL_IND is set to ‘Y’ if a requisition or requisition line item is cancelled in banner. • AP_REQ_TRANS_FACT • The field REQ_TRANS_CANCEL_IND is set to ‘Y’ for the requisition line FOAPAL record. • Identifying Deleted Requisitions from Banner (known as record removal): • The field REQ_DELTD_FRM_SRC in AP_REQ_DIM table is set to ‘Y’. • The Requisition line item FOAPAL record is moved from AP_REQ_TRANS_FACT table to the history tracking table AP_REQTRANS_FACT_DEL_HIST.

12. PPDM Business Areas • PO Transaction Star – • AP_PO_DIM • One line per PO line item • AP_REC_DIM • One line per Receiver line item • AP_VND_DIM • One line per vendor • AP_VNDTYPE • Zero or more types for each vendor • AP_PO_TRANS_FACT • One record per PO line item per FOAPAL • AP_POTRANS_FACT_DEL_HIST • History tracking table, stores all deleted (record removal) PO line item FOAPAL records.

13. PPDM Business Areas • Identifying Cancelled POs: • AP_PO_DIM • The field PO_CANCEL_IND is set to ‘Y’ if a PO or PO line item is cancelled in banner. • AP_PO_TRANS_FACT • The field PO_TRANS_CANCEL_IND is set to ‘Y’ for the PO line FOAPAL record. • Identifying Deleted POs from Banner (known as record removal): • The field PO_DELTD_FRM_SRC in AP_PO_DIM table is set to ‘Y’. • The PO line item FOAPAL record is moved from AP_PO_TRANS_FACT fact table to history tracking table AP_POTRANS_FACT_DEL_HIST.

14. PPDM Business Areas • Invoice Transaction Star – • AP_INV_DIM • One line per Invoice line item • AP_CHK_DIM • One line per check • AP_VND_DIM • One line per vendor • AP_VNDTYPE • Zero or more types for each vendor • AP_INV_TRANS_FACT • One record per Invoice line item per FOAPAL • AP_INVTRANS_FACT_DEL_HIST • History tracking table, stores all deleted (record removal) invoice line item FOAPAL records.

15. PPDM Business Areas • Identifying Cancelled Invoices: • AP_INV_DIM • The field INV_CANCEL_IND is set to ‘Y’ if a Invoice or Invoice line item is cancelled in banner. • AP_INV_TRANS_FACT • The field INV_TRANS_CANCEL_IND is set to ‘Y’ for the Invoice line FOAPAL record. • Identifying Deleted Invoices from Banner (known as record removal): • The field INV_DELTD_FRM_SRC in AP_INV_DIM is set to ‘Y’. • The Invoice line item FOAPAL record is moved from AP_INV_TRANS_FACT table to history tracking table AP_INVTRANS_FACT_DEL_HIST.

16. PPDM Business Areas • E-document Encumbrance Transaction Star – • AP_EDOC_ENCMB_DIM • One line per E-doc Encumbrance line item • AP_VND_DIM • One line per vendor • AP_VNDTYPE • Zero or more types for each vendor • AP_EDOC_TRANS_FACT • One record per E-doc Encumbrance line item per FOAPAL

17. PPDM Business Areas • Identifying Deleted E-doc Encumbrance from Banner (known as record removal): • The field ENCMB_DELTD_FRM_SRC in AP_EDOC_ENCMB_DIM is set to ‘Y’. • The field ENCMB_TF_DELTD_FRM_SRC in AP_EDOC_TRANS_FACT table is set to ‘Y’.

18. PPDM Business Areas • Procurement Pipeline Transaction Star – • AP_REQ_DIM • One line per Requisition line item • AP_PO_DIM • One line per PO line item • AP_REC_DIM • One line per Receiver line item • AP_EDOC_ENCMB_DIM • One line per E-doc Encumbrance line item • AP_INV_DIM • One line per Invoice line item • AP_CHK_DIM • One line per check • AP_VND_DIM • One line per vendor • AP_VNDTYPE • Zero or more types for each vendor • AP_PROCUR_PIPELINE_FACT • One record per Requisition, PO/E-doc Encumbrance, Invoice line item per FOAPAL • AP_PROCUR_PIPELINE_CANCLD_FACT • Archives cancelled procure pipeline fact record

19. PPDM Business Areas • Identifying Cancelled Processes: • If any of the processes in the pipeline is cancelled, the key is set to default value ('9999999999') and the record is marked as not effective i.e., the EFF_PROCUR_PIPELINE_IND is set to ‘N’.

20. PPDM Business Areas • Identifying Cancelled Processes (Cont’d): • When a PO is cancelled in banner the relationship between the PO and requisition is lost. In the data mart the association between PO and requisition line FOAPAL is retained and the EFF_PROCUR_PIPELINE_IND is set to ‘N’. All other records have the EFF_PROCUR_PIPELINE_IND set to ‘Y’.

21. PPDM Business Areas • Conformed Dimensions – Shared Dimension from Finance Data Mart Common Business Area. • FUND_CHANGES • ORGN_CHANGES • ACCT_CHANGES • PROG_CHANGES • ACTIVITY_CHANGES • CURRENT_INDEX • CALENDAR_FISCAL

22. Future Releases of PPDM • PPDM Release 1.1 • Post implementation fixes. • PPDM Release 1.2 • Post implementation enhancements. • PPDM Release 2.0 – Detailed scope of this release is yet to be defined. At a high level here are some of the tasks included for this release: • Application security for the broader audience. • Fully tested parameterized canned reports for the broader audience. • Training for the broader audience.

23. Appendix • Data Warehouse Development Methodology • Top-down • Holds atomic data that is extracted from one or more source systems and integrated within a normalized, relational data model. From there, the data is then summarized, dimensionalized, and distributed to one or more “dependent” data marts. • Bottom-up (Chosen) • Holds atomic and summary data that are required by the business users in dimensional, star schema data models. • Hybrid • Combines rapid development techniques within an enterprise architecture framework. • Holds atomic data in a normalized, relational data model, as well as in dimensional, star schema data models that reflect the business needs.

24. Appendix (Cont’d) • Application Architecture: • Selected Software for PPDM: • Relational Database (RDBMS) – Oracle • Extract Transform & Load (ETL) Tool – OWB • Data Profiling Tool! – None • Data Quality and Cleansing Tool – None • Data Quality Monitoring Tool – None • Meta Data Management Tool! – None • Reporting Tool for both parameterized standard reports and ad-hoc reports – Discoverer & Cognos • MOLAP for trending analysis and data mining – None

25. Demonstration of Discoverer Reports

26. Procedures

27. Requesting Access to the PPDM / Discoverer • REQUESTER (Or requester's manager) to fill out the Banner Administrative Systems Account Request Form.  • New users to Banner must fill out both pages of the form. Existing Banner users fill out the second page only. • In the “Discoverer/Data Mart Access” section on page 2, enter Procurement Pipeline in the section labeled ‘Other (specify here):’, and indicate ADD in the column labeled ‘Add or Delete’. • REQUESTER (Or requester's manager) submits the form to the Finance Administrative Systems Team (FAST@gmu.edu). • FAST BSO reviews and approves access request and submits the request to the DBA Team via Team Track. The signed request form must also be faxed to the DBA team at x3-3403. • DBA creates the user account in DMFP, and grants the necessary role (in this case GMUPPDM_ALL). The DBA then notifies the BSO that the account is created. (Note: Access to the PPDM Discoverer Business Areas is granted to the role GMU_PPDMALL.)

28. Discoverer/DM Reporting Support • The Reporting Support Team (rsteam@gmu.edu) provides the following user support: • Maintain the Discoverer End User Layer (EUL); • Assist users in Discoverer Report/Workbook development; • Develop Discoverer Reports according to user requirements; • Troubleshoot Discoverer Reports and Data issues; • Provide informal 1-1 Discoverer training/consultation; • Coordinate communication with DM users regarding Discoverer upgrades/issues • Discoverer Splash Page and Listserve • http://discoverer.gmu.edu/ provides links to the Discoverer applications as well as links to related sites and material. • Subscribe to DATAMART-L to receive alerts and notices regarding Discoverer and the data marts (instructions are on the Disc Splash page).

29. Discoverer/DM Reporting Support (cont’d) • Coming Soon … • Discoverer Problem Reporting and Tracking System (Winter 08) . This web-based applications allows users to report a Discoverer problem/issue report, and allows the RS Team to track the issue through to resolution. There will be a link to the system on the Discoverer Splash page. • Formal Discoverer Training (Spring 08). The Discoverer topics will be generic, but exercises and examples will be customized for each data mart.

30. Discoverer Training Levels

31. Report Naming Conventions Why? • Ease of retrieval (natural sort by DM/Category). • Provides a standard for use by distributed report developers. • Could provide ease in report sharing. When? • Published reports (i.e. shared with a role) – strongly recommended. • Shared reports (i.e. shared with individuals) – recommended. • Personal reports (not shared) – optional. How? (suggestions) • Begin with a standard for the DM (e.g. PPDM) • Follow with a code indicating the major function (e.g. PO). • Follow with a meaningful description of the report (e.g. Search by PO Number)

32. Questions?