1 / 13

UCCSC 2014 UCPath Common Data Warehouse

UCCSC 2014 UCPath Common Data Warehouse. The Setting: UCPath. A cornerstone project of the UC Working Smarter Initiative Standardize and streamline payroll and human resources processes system-wide

jason
Télécharger la présentation

UCCSC 2014 UCPath Common Data Warehouse

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. 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

More Related