How McKesson Successfully Delivered the Largest HR Analytics Project

1. How McKesson Successfully Delivered the Largest HR Analytics Project Ling Xiang Oracle BI Applications Product Manager Ian Leath Enterprise Solutions Director McKesson Barry Cooper Head of Development McKesson

2. The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle Corporation.”

3. Oracle BI Applications Product Family Rapid Performance Insight CRM ANALYTICS ERP ANALYTICS ORACLE BUSINESS INTELLIGENCE FOUNDATION AND OTHER OPERATIONAL AND ANALYTIC SOURCES SOURCE ADAPTERS:

4. Oracle BI Apps: Deeper than Dashboards 1. 2. Pre-Built Analytic Warehouse Certified Application Integration • Business Analytics Warehouse • Over 300 star schemas • Designed for analysis and reporting • Pre-mapped integration • Fusion, EBS, PSFT, SEBL , JDE, IBM • Role-based Security 3. 4. Best Practice Metrics Role-Based Dashboards • Over 10,000 metrics • Additional pre-defined calculations • Based on best practices • Over 500 Dashboards and Pages • Over 3,000 reports • Across Lines of Business

5. What’s in Human Resources Analytics (11.1.1.7.1) Dashboards 11 Dashboard Pages 55 Reports 323 Attributes 683 Metrics 600 • Workforce Effectiveness • Corporate Contribution • Effectiveness • Trends • Workforce Deployment • Overview • Staffing • Demographics • Trends • Workforce Development • Overview • Employee Performance by Supervisor • Employee Performance • Supervisor Performance • Internal Mobility • Top Performers • Under Performers • Retention • Overview • Retention Hotspots • Top Performers • Trends • Absence and Accrual • Overview • Accrual • Absence Trends • Absence Details • Absence Calendar • Compensation • Overview • Salary Compression • Pay for Performance • Fusion Embedded Content • Workforce Trend • Turnover Trend • Recruitment • Overview • Requisitions • Vacancy Analysis • Recruitment Pipeline • Offer Analysis • New Hires • Applicant Pool • Quality of Hire • Recruiter Effectiveness • Requisition Recruitment Activities • Recruitment Event Details • Candidate Sourcing • Learning • Overview • Manage Enrollments • Training Results • Delivered Training • Time and Labor • Overview • Timecard Monitoring • Processed Time • Time Coverage • Time Trend • Estimated Labor Cost • Payroll • Overview • Payroll Trend • Payroll Cost • Overtime Spend • Payroll Labor Hours

6. What Is New HR Analytics 11.1.1.7.1 Time and Labor Benefits of Time and Labor Analytics … • Gain quick insight into key time collection metrics and monitor timecard status • Analyze reported time trend and time usage pattern • Track productive and non-Productive time and control overtime usage • Check reported and processed payable time variance • Analyze time posting to payroll and projects • Easy navigation from key metrics, time trend to transactional timecard details • Support Oracle and PeopleSoft Time and Labor; Support for Fusion Time Labor is planned

7. What Is New HR Analytics 11.1.1.7.1 Payroll Benefits of Payroll Analytics… • A new star schema to store pay run transactions including earnings, deductions, taxes and special balances • Aggregate payroll balances into configurable summary measures for better total compensation and cost analysis, e.g. total base pay, employer-paid healthcare cost and taxes, etc. • Time trend analysis on payroll measures e.g. year, quarter, month and year ago • Custom fields to extend payroll balances • Support Fusion, EBS and PeopleSoft Payroll (including North American and Global Payroll)

8. What Is New HR Analytics 11.1.1.7.1 Core Workforce Enhancements • Dimensional enhancements • New HR Assignment dimension to support multiple assignments • New HR Person dimension captures the static view of a person (no history); new HR Person Legislation dimension to support legislative attributes, e.g. ethnicity and captures history • New Cost Center dimension (derived from an employee’s department) • New HR Supervisor hierarchy at the assignment level instead of at employee level • New HR Position hierarchy • New Compa-Ratio Band dimension • New Legal Entity dimension • New Reporting Establishments dimension

9. What Is New HR Analytics 11.1.1.7.1 Other Enhancements • All fact tables have been enhanced to support new dimensional changes • New Performance Band in Learning Enrollment and Completion subject area • New Workforce frozen snapshot to support as-Is and as-was reporting • Enhanced Type 2 slowly-changing dimension joins (use Type 1 original key plus Effective Date) • Redesigned Compensation dashboard to analyze salary compression

10. Next Generation Data Integration BI Apps 11.1.1.7.1 with Oracle Data Integrator and Golden Gate Oracle Data Integrator provides • In-database E-LT Transformation vs. E-T-L • Best in class performance, scalability, availability • Lightweight deployment for lower TCO • Faster time-to-value through declarative design Oracle Golden Gate option for BI Apps • Reduce load on source system by streaming data continuously into persistent store in the DW • More reliable change data capture and delete support • Enables near real time DW refresh

11. HR Analytics Roadmap Current Releases and Roadmap • Fusion & AU: release alignment • Dual ETL ODI and Informatica • New content: Talent Profile Analytics • New cloud adapters: Adapters to Fusion HCM Cloud Services, Taleo Recruiting adapter • Co-existence of Fusion, Taleo Clouds and Apps Unlimited in HR Warehouse • New: Better support flexfield extension • Cost of Ownership Improvements: Data Lineage, ETL Validation, platform support • Endeca Extensions • For Applications Unlimited customers • ETL with Informatica • For Application Unlimited customers • New ETL with ODI • New Golden Gate option • New Time and Labor Analytics • New Payroll Analytics • New Adaptor: PeopleSoft Global Payroll • BI Applications as Oracle Cloud Service • New content: HR/Financial integration, continuous Talent Management enhancements • New Cloud Adaptor: Fusion Time and Labor • Unstructured/big data analytic applications e.g. employee survey analysis • Continue user experience redesign by leveraging BI Foundation Suite platform developments including Mobile App Designer • Additional cost of ownership improvements What’s Next 0-12 month planning cycle Future DirectionsPost 12 month planning cycle BI Apps 7.9.6.4January 2013 BI Apps 11.1.1.7.1 May 2013

12. HR Analytics Cloud Integration OTBI BI Common Semantic Model On Premise OBI Applications BI View Objects BI Common Semantic Model PSFT HCM ETL OTBI OBIA Data Warehouse ETL OBIEE Semantic Model Release 8 EBS HCM ETL Taleo Recruiting Other Sources Flat Files Extract Release 9 SaaS Cloud Fusion HCM SaaS

13. What’s Planned: Higher Value Prebuilt Analytics Workforce Sentiment (unstructured analysis of surveys and reviews) • Endeca Extensions • “Why is attrition in the Midwest spiking? Operational Modeling (domain specific forecasting and performance mgmt) • Essbase Extensions • “How many should I hire to meet projected demand?” Predictive Metrics (smarter KPIs) • Advanced Analytics Extensions • “Will more training increase employee productivity and by how much?” Future HR Analytics (historical analysis on structured metrics) HR Analytics (historical analysis on structured metrics) Today Fusion OTBI (real-time operational analysis & reporting) Fusion OTBI (real-time operational analysis & reporting)

14. How to Successfully Deliver the Largest Oracle Human Resources Analytics ProjectOpen World 2013 • Ian Leath • Enterprise Solutions Director • Barry Cooper • Head of Development

15. Leading change for 180 years Revolutionizing Industry Practices Innovating Quality, Safety and Efficiency Solutions Pioneering Drug Distribution and Manufacturing 1800–1900 1900–1990 1990–Today 2012The UK's leading Health and Social Care IT Solutions and Services provider Mid to late 1800sEstablished first U.S. national drug distribution system, with covered wagons delivering products to 17 states 1968McKesson Labs became No. 1 maker of Vitamin C and aspirin in United States Early 1900sBecame leading distributor of drugs in U.S. 1990Launched McKesson IOG in England & Ireland 1999Launched McKesson IOG in France & Netherlands 1989Introduced first PC-based real-time ordering system for pharmaceuticals 1990sMerged with leading IT, supply and automation companies to become largest healthcare services company 1833Founded as drug importer and wholesaler 1870McKesson Labs introduces first easy-to-swallow gelatin-coated pill 1964First to manufacture generic form of Tetracycline 2011McKesson acquire System C Healthcare At the forefront of innovationin healthcare since 1833

16. Our global presence USAMcKesson Corporation founded 1833 Employs more than 36,000 staff globally Leaders in information technology, pharmaceuticals, pharmacy automation and disease management solutions UNITED KINGDOMMcKesson founded 1990 Staff 750 + Leading health and social care IT provider Used by every NHS healthcare provider Fastest growing system supplier in social care NETHERLANDSMcKesson founded 1999 Employs more than 90 staff McKesson systems support 1/5 of all patients in hospital or mental care GERMANY & SWITZERLANDMcKesson Cardiology imaging technology is used in more than 30 hospitals IRELANDOur Picture Archiving and Communications System is being installed in more than 30 hospitals across the Republic. FRANCEMcKesson founded 1999 Employs more than 320 staff #1 Supplier of e-prescribing and nursing systems in the acute sector US$112 billion Ranked #14 turnover worldwide Fortune 500

17. Location & Staff Over 99% of all NHS organisation in England and Wales have McKesson solutions and services

18. Organised into 2 Business Units Workforce Solutions A unique portfolio of products and services designed to accelerate the benefits of the NHS Electronic Staff Record (ESR) Health & Social Care Responds to the market’s increasing requirement for solutions which reflect the care journey ignoring boundaries between hospital, community and social care

19. Workforce Solutions The Electronic Staff Record (ESR) HR and payroll system for the NHS in England & Wales McKesson Shared Services (MSS) provides payroll outsourcing services to NHS organisations • World’s largest integrated HR and payroll system, paying 1.4 million NHS staff every month (7% of the working population) • Provides HR, payroll and pensions services to 50 NHS organisations • Delivering the ALBs contract with Logica • Includes electronic efficiency tools such as absence management, electronic timesheets and electronic expenses management – the Electronic Payroll Suite • Also covers Client Services – everything from education and consultancy to project management • ESR has delivered major savings to the NHS through replacing multiple separate HR and payroll systems. It replaced 67 HR systems, 1702 in-house trust systems and 28 payroll systems • It continues to offer NHS managers the tools needed to more efficiently manage their workforce and meet the NHS’ requirements for higher productivity and lower costs • 10 year contract value is £500m

20. The Electronic Staff Record and the NHS

22. The National Health Service (NHS) • The NHS is the fifth largest employer in the world • The largest employer in the UK • Employs over 1.4million people in England and Wales • 574 organisations • Free at the point of care

23. The NHS Electronic Staff Record (ESR)… …replaced …is the complete workforce management tool for the NHS Terms and conditions Training history and qualification Absence history Occupational health information Training management and E-Learning delivery Single workforce IT system for a devolved NHS 67 HR systems 1,702 in-house 28 Payroll systems

24. The National Solution for the NHS in England and Wales 1.4 420 7% of the working population million employees organisations million payslips per month 300,000 2 350,000 joiners a year paygrades million lines of custom code supported 265,000 12 250,000 allowances leavers a year The World’s largest integrated HR & Payroll system million lines of code and configuration 51.5 175,000 Assignments an hour

25. Unique Challenges • Unique issues and challenges • Error affecting 0.1% of assignments means 2,000 problems for ESR; • Replication of faults for a resolution can sometimes be extremely difficult; • There is nothing else on the scale of ESR. • Very Severe SLAs • 100% Pay Accuracy; • 99.6% Availability; • 100% of the Service Fee is at Risk each Month. • All new Functionality needs to be scalable • Performance testing of paramount importance; • 100% goals with penalties attached makes change a major risk.

26. What the ESR Solution Delivers Today…

27. Operating Facts of ESR • Application HCM 12.1.3 • Database 11gR2 • IBM Power7 running AIX OS • 20 TB Database • 5 million assignments processed each month • $3 billion per month payroll value • Payroll processing peak rate circa 175k assignments per hour • 44,000 Professional Users • 4500 concurrent users (Maximum) • Approx. 1m Enabled Self Service Users (Manager/Employee)

28. ESR Solution Components HR/ Payroll Self Service OLM Reporting eLearning Data Warehouse Interfaces Registrations NHS Layer Configuration 500+ Customisations Oracle HCM – R12.1 Oracle Database 10G IBM POWER7 AIX 6.1 E-Business Suite

29. ESR Functional Components Data Warehouse Smartcard Local Reports eRec eKSF GMC NMC T&A BACS GL BACS EDI eProd Employee/Manager Self Service and Shared Service Strategic Decisions Absence Management Talent Management Learning Management Core HR Payroll Recruitment HR/Finance Establishment (Workstructures)

30. HR Analytics – How Did We Do It?

31. Drivers to Move to OBIA

32. Drivers to Move to OBIA • Provide a new reporting facility which would become the strategic reporting tool of the future. • An independent data warehouse. • Provide Line Managers with more analytical analysis reporting capability. • Report scheduling (not available in Discoverer) • Replacement of the Discoverer Reporting solution (this is a much longer term aim/goal)

33. The Proof of Concept

34. Focus of the Proof of Concept (PoC) • Primary focus on ETL incremental timing • Ability to load the data from a typical business day (online and batch activity) within the incremental window • Target 3 hour window for the incremental load • Secondary focus on functional data model and capability • PoC was jointly run exercise with Oracle providing specialist OBIA expertise alongside our own application and technical specialists.

35. PoC  Customer Needs & Expectations • Warehouse populated every night with incremental data amendments – therefore data is maximum 24 hours old • Empower the customer to take ownership for the dashboard and analyses development • OBIA facility should be launched from within ESR, logging the user into OBIA with their correct security context • Used the out-the-box dashboards and analyses as a template / guide for developing their own • Kept the seeded subject areas

36. PoC  McKesson Expectations • Some standard EBS functionality not replicated by OBIA • Competencies, Qualifications, E-Learning (player aspect) • Element entries, Grades and Spine Points (non Comp workbench salary) • Various Extra Info Types (EITs) and Special Info Types (SITs) • Some of the things we knew we would need to customise • Custom Data Security Model (VPD and STC) • Absences (assignment level) – timeline and costing • Payroll costing and GL mappings

37. The Project

38. Project Goals & Deliverables • Aggressive timeline, with Programme-level commitment to go-live end of March 2013 • Project kick-off in September 2012 • Multiple streams to cover analysis, infrastructure commission and build, education, development and test • Structured end user education approach – “train the trainers” • Phased rollout to all organisations

39. Project Structure • Governance • Internal Project Board • External Project Board • Delivery • Development team • Functional Analyst/Design • Testing • Education • Project Manager, Technical Architect and DBAs

40. Things we expected to be straightforward…

41. #1 - Security • Security Profiles • No out-of-the-box organization security – although examples on how to implement are included in the manual • No generic out-of-the-box integration of EBS security profiles in OBIA • EBS Responsibility mapping to OBIA roles • No automatic mapping • Mapping is a customization in OBIEE. The documentation does mention how to do it but its not out the box

42. #2 - Assignments • There is no discrete Assignment dimension. Assignment level data items are included in the Person dimension • We had to build our own Assignment dimension and link it into every FACT • #3 - Others (quick round-up) • No out-the-box support for EITs and SITs • Organization Reporting (drill) hierarchy • Terminology

44. Data Change History/Date Tracking • Beware how you implement date tracked changes in the ETL so that all date track changes from EBS are accurately reflected • OBAW by default does not cater for history stored in source (Siebel) system, so has its own implementation for recording history of change • Date tracked deletes not implemented out the box • Some date tracked changes not replicated across correctly

45. Other Challenges • Integration • Not as tight as between Discoverer and EBS • Data security • Agents / Scheduling • Others • Payroll mapping • Data truncation • Absences mapping, especially with open-ended absences • Data quality and the ETL

46. Lessons Learned…

47. BI Caching • Is it for you and your implementation? Why we turned it off. • Functional breadth • Data depth • Implementation • “Don’t rely on the cache to fix what you think may be performance problems – design your solution with performance in mind.”

48. Development Practice • BI repository not ideally suited to collaborative working • DAC & Informatica is easier to collaborate on • Various 3rd parties have recommendations on multi-user-development techniques, and plenty of information in the Blogosphere. Worth the research up front • As per EBS, observe rules about custom object naming conventions

49. A Positive Outcome… • Success of the project relied heavily upon our skilled and experienced team • Positive outcome because of the McKesson skills and help from a direct route into Oracle development • Many of our solutions to product issues were fed back to Oracle so that they could be included within the core product at a later date

50. A Successful Project