UCCSC 2014 UCPath Common Data Warehouse

1. UCCSC 2014 UCPath Common Data Warehouse

2. The Setting: UCPath • A cornerstone project of the UC Working Smarter Initiative • Standardize and streamline payroll and human resources processes system-wide • Replace UC’s 35-year-old payroll/personnel system (PPS) with Oracle’s PeopleSoft Human Capital Management (HCM)

3. The Issue • Oracle PeopleSoft HCM is architecturally distinct from PPS • Significant revisions/replacements of existing PPS-based data warehouses for all Locations • Data warehousing is not part of UCPath • Data warehouse solution left to each Location

4. Business Intelligence Collaboration Group • ITLC subcommittee with membership across UC • Define direction & establish priorities for system wide data services and business intelligence • Recognized the opportunity for Locations to collaboratively design and develop a UCPath Common Data Warehouse • Developed and successfully championed the project proposal funded through the Office of the President

5. Goal • Ensure efficiencies gained through collaborative design & development among all UC Locations • Design once – reuse many Assumptions • UCLA’s existing UCPath DW will serve as initial basis of design • Design will adhere to “standard star schema” – Kimball’s dimensional modeling practices

6. Deliverables • Fully attributed logical and physical dimensional data models • CDW data dictionary • Multi-platform DDL - Oracle, SQLServer & DB2 • ETL specification mapping • Core set of reports, requirements and analytic algorithms

7. Scope - Subject Areas • Person/Employee • Job • Absence Management • Labor Ledger • Payroll/Commitment Accounting • Benefits • Budget Ledger • Job Position Management

8. Resources • BICG Co- Project Leads (UCOP, UCLA) • Project Manager • Core Team • 3 Data Architects (UCB, UCLA, UCOP) • ERwin Data Modeler • Reports Developer • Extended Team • UCPath Functional Experts • Location UCPath and DW Experts

9. Process • Work one subject area at a time • Gather use cases from Locations • Review, identify issues, work with functional experts, and revise UCLA dimensional data models as needed • Hold Design Review Sessions with all Locations to review deliverables • Follow up with comments received, resolve issues and make final revisions • Publish deliverables • Hold to Kimball’s dimensional modeling design practices

10. Challenges • Oracle HCM is new to UC and UC custom-izationsto HCM are still being implemented • HCM data model does not contain referential integrity • Data relationships, business logic enforced by application • Dependency on functional experts • Location requirements, design practices, environments and tool sets are different

11. Immediate Gains • UCLA’s existing UCPath dimensional models serve as basis for CDW Version 1.0 • Able to leverage the method of Location collaboration and the knowledge gained about HCM from previous work on UCPath DDODS • Able to leverage knowledge about HCM from Locations that have already implemented HCM

12. Progress To Date • Designs for Person, Job and Absence Management are complete with data dictionary and ETL specification mapping • Use cases provided by Locations have extended UCLA’s models to meet common as well as Location specific design requirements • Full text data naming in logical model with optional abbreviation algorithm for physical naming provides naming flexibility and an opportunity for short name standardization

13. Key Takeaways • Collaboration Works! • The common solution IS a collaborative solution • Immediate gains are all due to collaboration • All Location alternatives contribute in whole, in part or generating new or hybrid solutions • Takes work! Team work! Communication! • Holding to Kimball’s dimensional modeling practices established the design paradigm, facilitating communication and progress