• No results found

HR Analytics at Wells Fargo

N/A
N/A
Protected

Academic year: 2021

Share "HR Analytics at Wells Fargo"

Copied!
46
0
0

Loading.... (view fulltext now)

Full text

(1)

Start Here

Jeff McQuigg

Ramesh Ponaganti

Sept. 26, 2013

Oracle Open World, CON5792

HR Analytics at

Wells Fargo

(2)



Oracle’s largest HR Analytics implementation

 Wells Fargo has 300,000+ employees and contractors

 Targeted to 40,000+ managers, starting with 1,000

 And 1,000+ Ad-hoc users, started with 100



Enterprise BI on a massive scale

• Crossing 6 distinct Lines of Business + Corporate HR



Unique technical solutions marrying

usability, flexibility and security



Project that won the 2013 Excellence award for KPI

Partners

(3)

Introduction

Business Need & Solution Project Implementation Status & Future Plans The Solution & Key Aspects Data Model and ETL Efforts

Challenges, Best Practices & Keys to Success Wrap up & Q&A

(4)

www.kpipartners.com

Strategic Consulting | Systems Implementation | Training

Depot Repair Analytics

Fixed Asset Analytics

Manufacturing Analytics

Salesforce.com Analytics

Student Info Analytics

Subledger (SLA) Analytics

Real Time Analytics and more

FSG Solution

Transform Data Into

Insight

Transform Data Into

Insight

 Staff built from

Oracle/Siebel/Hyperion engineering teams  On-site, off-shore and blended shore delivery models

 Exclusive pre-built solutions for Oracle BI & E-Business Suite

Oracle BI Hyperion Endeca Exalytics Oracle BI Hyperion

(5)

 Senior Architect at KPI Partners

 10+ years OBIEE consulting experience, 20+ years overall

 Personally been involved with over 45+ OBI projects in every capacity (BI Architect, Data Modeling, RPD Metadata, Business Analyst, Report Developer, ETL Architect/Developer, Project Manager, Pre-Sales)

 Oracle Ace thought leader for BI & OBI:

 Blogging about OBI & DW best practices since 2006 at

GreatOBI.WordPress.com (Oldest running OBI blog in the world)

 Frequent Oracle Open World speaker

(6)

 BI Apps Architect / Lead at KPI Partners

 6 years of BIApps consulting experience, 9 years overall

 Ramesh has been involved with over 15 OBI projects (BI Apps Architect, Data Modeling, Business Analyst, ETL Architect/Developer, Pre-Sales)

 KPI’s HR Analytics expert

 Expertise in integrating 3rd party source systems with BI Applications  ADP Payroll, Ceridian (Recruiting), JobVite (Recruiting), BLS.com (Benchmark

(7)

Introduction

Business Need & Solution

Project Implementation & Team Status & Future Plans

The Solution & Key Aspects Data Model and ETL Efforts

Challenges, Best Practices & Keys to Success Wrap up & Q&A

(8)

 HQ in San Francisco

 Founded in 1852 by the founders of American Express

 Now the world’s largest bank (by Market Capitalization) as of July 2013

 300,000 employees & contractors – 14th largest in the US and ~ 50th

in the world

 2.9 million job applicants per year!

 Growth via dozens of acquisitions

 More recent large mergers: Norwest – 1998 and Wachovia - 2008  Consists of several distinct lines of business:

 Community Banking, Home Mortgage, Wealth Brokerage & Retirement, Wholesale Banking, Securities

(9)



6 LOBs & Corp HR managed differently

 Differing systems and tools  Differing metrics & definitions

 Differing HR management processes



Time wasted on manual tasks for

 Gathering data

 Answering HR questions

 Basic reconciliation



No ability to spot trends and

become proactive



Technical skills needed were too

high with existing systems

(10)



Wanted to operate in a common manner across LOBs



Reduce effort for basic reporting and analysis



Reduce technical skills needed



Enhance reporting and analysis

 Rich data set for analysis

 View HR data by Financial Hierarchies



Line manager (40,000!) self-service

(11)

 Wells Fargo selected Oracle HR Analytics 7.9.6.3  Pre-built integration with PeopleSoft

 Overall flexibility and capability

Workforce Profile

Demographics

Headcount, Race & Gender, Temp Workers, Retirement

Movement

Transfers, Terminations, Promotions

Performance Management

Reviews and career progression

Compensation

Pay Equity, Pay vs. Performance, Industry Benchmarking

Recruiting

Requisition Activity

Time to fill, Open Positions

Applicant Tracking

Applicant profile, source effectiveness

Recruiter Performance

Conversion rates by stage, Offer acceptance

Recruiting Process

Times, drop off and bottlenecks along the pipeline

(12)

Introduction

Business Need & Solution

Project Implementation & Team Status & Future Plans

The Solution & Key Aspects Data Model and ETL Efforts

Challenges, Best Practices & Keys to Success Wrap up & Q&A

(13)



Release 1 began Oct. 2011, completed Oct. 2012



KPI Team of ~15 consultants



WF IT team of 3-4 plus 3-4 key business analysts

 Over 100 total involvement



Technical Environment

 Oracle 11g database (11.2.0.1.0)

 PeopleSoft 8.9 on DB2 System 390

 OBIEE 11.1.1.6

 SiteMinder



1 year BI Apps implementation? Why?

1. Extensive customizations to the data model and ETL logic

2. 100% custom developed Dashboarding solution (150 complex reports)

3. Largest data volume to run through HR ETL

(14)

Implementation followed a basic Waterfall approach, except for the reporting/UI content which was highly iterative and followed a prototype to completion approach.

Oct Install ETL/Model Design ETL Dev. QA UI Reqs/Design/Dev Gap Analysis Perf Tune Enterprise Metric Definitions

Iterative Prototype -> Prod KPI Begin 2012 2011

Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct

Go Live

(15)

KPI lead the combined team:

 Overall KPI Team Lead – (Jeff M.)

 KPI Partner - Client Manager (part time)

 BI Apps Architect & ETL Lead - HR Analytics expert (Ramesh P.)

 4-6 ETL developers

 OBI Team

 1 OBI Architect

 2 OBI developers

 1 technical infrastructure expert for initial security integration

 Technical PeopleSoft SMEs

 1 SME – during requirements and design phases

 1 Project Manager (part time)

• Total: 13-16 KPI resources throughout the life of the project

• Core team consisted of 9 consultants

(16)

 1 Business Sponsor (Sr. VP HR

Insight and Metrics)

 1 IT Director

 1 IT Manager

 2 Project Managers, 1 reporting to business sponsor, 1 reporting to IT

 1 Data modeler / Overseer

 2 ETL developers

 1 OBI developer

 2 PeopleSoft SMEs

 “Metrics Team” of 4-5 analysts

 “Security Team” of 3-4 people

 Business user involvement: 80+

 2 Oracle DBAs

 1 performance tuning DBA

 2 OBI Infrastructure Admins

 6 from corporate testing team, mostly offshore

Wells Technical and Business team members involvement:

(17)

Introduction

Business Need & Solution

Project Implementation & Team Status & Future Plans

The Solution & Key Aspects Data Model and ETL Efforts

Challenges, Best Practices & Keys to Success Wrap up & Q&A

(18)



Deployed Release 1.0 Oct 2012



1,000 HR specialists as users



150 custom reports over 9 dashboards



3 ad-hoc subject areas for 100 power users



Processing 100 Million rows daily

(19)



“This is a 5-star app!”

- Exec



“I am blown away!! Fantastic and

very intuitive!”

- Exec



“WOW!!! … Needless to say I am very

impressed. I definitely believe this can be a game

changer … Just a brief exposure into the tool

provides many insights about my team that have

been difficult to find in the past.”

- Exec



“Workforce Analytics tool is open - I love it!”

- User

Very Impressed

I amR Blown Away

(20)

Wells & KPI are continuing enhancements:

 Since Release 1:

 Increased ad-hoc users 50%

 Redevelopment of custom hierarchy ETL code

 Continual ETL performance tuning

 Analysis, design & development tasks for November

 November Timeframe

 GL Hierarchies w/ enhanced security model

 Controller Data Mart integration to link GL

 Payroll Analytics

 2014

 Learning Integration

 Replacing PSFT Recruiting

(21)

Introduction

Business Need & Solution

Project Implementation & Team Status & Future Plans

The Solution & Key Aspects Data Model and ETL Efforts

Challenges, Best Practices & Keys to Success Wrap up & Q&A

(22)



Targeted to advanced users (HR Consultants)



Result: highly interactive pages

 Many prompts

 Heavy use of column selectors to change content

 Heavy use of view selectors for different perspectives or visuals

 Heavy use of navigations into details



Innovative Security model and usability solution



Extensive customization of core hierarchies and

metrics

(23)
(24)
(25)
(26)

Highlight two important aspects of the project



Ensuring user

adoption of

Enterprise BI

with an iterative UI

process



Hierarchy solution

for Security, Flexibility

and Usability

Usability Flexibility

(27)

 6 LOBs plus Corporate HR with input

 Sessions had 80 participants

 Agree to definitions, reports, UI, flow

 KPI lead sessions on reporting requirements and detailed data definitions

• WF began defining metrics before the project began

• Representatives from each LOB joined the “Metrics Team”

• Worked through common definitions and terminology

• Sometimes very difficult to nail down:

 Ex. 1: 6 months to define a Promotion!

 Ex. 2: When does a job seeker become an applicant?

(28)



Pure top down BI approach

for identifying reporting

content



OOB reports were not

considered due to WF

expertise in HR



“Prototype to Production”



Continual iterative report

development with constant

user feedback



No detailed report specs!

Approach

1. Identify Key Business Questions 2. Prioritize the list

3. Data to answer the questions 4. Find key comparisons

5. Whiteboard visualizations 6. Convert to Excel mockups 7. Enhance & Refine mockups 8. Move mockups to OBI

9. Iterative report refining 10. UI development complete For each Dashboard:

 2 weeks elapsed Excel mockup  4 weeks elapsed in OBI

(29)

KPI designed a unique solution enhancing

dashboard capability and improving usability

Hierarchies



Support 2 different hierarchies

Employee-Position 6 Tier / Executive Enterprise direction Legacy (for some LOBs) 22 levels 6 levels 100% Custom 100% Custom As Is As Is As Was As Was



“As-is” and “As-Was” versions



Users select from 4 hierarchies at any time

(30)

Security Requirements



Data security by user selected hierarchy (all 4!)

+ HR consultants assume the role of selected

managers

 E.g., an HR consultant can “become” a VP of Retail Banking  Similar to a proxy feature

+ Security differs by subject area

 E.g., a Recruiting consultant has greater access rights on Recruiting than on Workforce Demographics



One of the more complex and challenging security

models

(31)

Usability



Easily change all of the following:

1.

Hierarchy

2.

Hierarchy

version

3.

Manager

to become

4.

Any

sub-manager

to analyze



Fixed levels are a common hierarchy solution

 Not realistic with a 22 level hierarchy

 Each hierarchy has different # levels

 Not possible with 4+ hierarchies

VP #1 Director #1 VP #2 Director #2 HR Consultant

(32)

Solution

 All hierarchies are mapped into a single OBI logical table selectable by the user

 They select a version - As Is or As Was

 OBI determines physical tables

 Select an Access Point, or who they “are”

 Select a sub-manager to run the reports for on the page

 Hierarchies are exploded into special tables (4x) allowing this to happen

(33)

Result:



The entire page can be

changed to support different

hierarchies, versions and

different managers in only a

few clicks



Data security follows the

selected hierarchy



Enhancements for excellent

performance

 Many pages for CEO (300,000+ employees) < 10 seconds!

(34)

Introduction

Business Need & Solution

Project Implementation & Team Status & Future Plans

The Solution & Key Aspects Data Model and ETL Efforts

Challenges, Best Practices & Keys to Success Wrap up & Q&A

(35)

Sheer quantity and complexity of custom requirements

 Highly customized PeopleSoft system

 13 years old, after many mergers and exception cases

 Data model extensions to support ~100 new metrics (Key and Variations)

 Many had complex definitions: Promotions (Actions & MRP), Internal Hires, External Hires, Requisitions vs. Positions, Applicants Vs. Job seekers etc.

 Addition of Full Position Management (custom)

 Complex Customizations in Workforce/Recruitment Stars

 Custom Performance Ratings

(36)



4 custom hierarchies

 With support for open positions



Extended dimensionality

 Custom banding for 6 metrics

 Corp Accounting Unit (Cost Center)  Area Differentials

 Complex security dimensions

 30+ new columns on employee dim

 Extensions on other OOTB dimensions



15+ new Aggregate tables to improve reporting performance

Band Min Mons. Max Mons.

Now -9999 1 < 1 yr. 1 12 1 – 2 yrs. 12 36 3 -4 yrs. 36 60 5+ yrs. 60

(37)

 Domain/CSV configurations

 Complex domain configurations to meet PSFT customizations

 Definitions dependency on both CSV files and ETL code

 Metric definition/configuration conflicts

o Promotions Vs. Internal Hires o Applicants Vs. Job Seekers

o Employment Status, Full Time Part time etc.

 ETL Performance

 Large volume of data being run through ETL engine (Oracle’s largest data set yet for HR Analytics!)

 Complex HR Analytics ETL process

 Extensive use of DAC Action framework

 Added table partitioning to 20+ tables

 Extensive efforts on tuning to meet nightly SLA

 Mainframe DB2 issues

 Extract times – running into ASU Limit issues

 Network Issues – impacted ETL loads

 Modified code to support Dirty Reads

(38)



PeopleSoft Effective Dates not reliable on key tables



Re-architected OOB incremental logic



Solution (table by table basis):



Full extracts for source tables



Full/Incremental refreshes for DW tables



Relinking foreign keys on Facts on Full refreshes



Special Data Scenarios

 Missing change capture triggers for Incumbent changes

 Coding around Recruitment data quality issues

 Added support for same day Hire/Terminations

 Logic enhancements for many metrics like Promotions, Transfers and Recruiting metrics etc.

(39)

Introduction

Business Need & Solution

Project Implementation & Team Status & Future Plans

The Solution & Key Aspects Data Model and ETL Efforts

Challenges, Best Practices & Keys to Success Wrap up & Q&A

(40)



Security Model

 Difficult requirements to implement and discuss

 Difficult communications with PeopleSoft and Security teams

 Challenging technical solution



Consensus building across LOBs

who operate differently

 Questions, Reports, Features, Definitions



Large # of users involved

(41)

 Draining Requirements sessions

 4 hours per day

 2x per week

 8+ weeks

 All on phone

 80+ people on calls

 Changing Recruiting definitions



Geographically diverse team

• Difficult communication and sessions



Constant plan reworking due

to external factors

(42)

Pure top-down approach to BI

Functional prioritization

Iterative development via prototyping to drive user adoption

Strong focus on interactive application

BI Apps Performance Layer for improved query times

(43)



KPI expertise in inner

workings of HR Analytics



Back End expertise for

design and development of

custom hierarchies and sources



Strong architects to solve complex problems



Iterative report design & build approach

(44)
(45)

Email:

info@kpipartners.com

Web:

kpipartners.com/contact

KPI World Headquarters

39899 Balentine Drive Suite #375 Newark, CA 94560 Phone: (510) 818-9480 Contact Us New York, NY Chicago, IL Boston, MA Minneapolis, MN San Diego, CA Greensboro, NC

North America Offices

Bangalore, India Hyderabad, India

(46)

References

Related documents

NOW IS THE TIME FOR HEROES! MUTANTS & MASTERMINDS A G R E E N RONIN PRODUCTION Design & Development Steve Kenson Cover Art Ramón Pérez Editing Jon Leitheusser Executive Producer

ó9ê¶Ø/ô9Õ~Ú;çuցè9ÚÕAÙ%Ú;ïˆ×¼ê£ð~Ù%Øué¼Ø7ÕÇÖwêŸÚ åaååaååaååaåHååaåHååHåaååaååaååaåaå õ ä/å¬ò9å~ä

[r]

[r]

Ö %HÑ Ø ÓUÓ1ÜåÖlðÒç1ÖÝ1ÝLÜ éçoæ ç!ÑÓ1Ô Ó1éÐÖRÓ1ܹԂälÑ ç!ÐÜsî·éçfÑ ØóÑ

[r]

(ii) The ratio of visual signal level to coherent disturbances which are frequency- coincident with the visual carrier shall not be less than 47 decibels for coherent channel

Analytics Solution Business Analytics: • Business Intelligence • Predictive Analytics ETL: Integrate, cleanse, transform, &amp; load production data to Data Warehousing