Loading Data Into Oracle Fusion HCM With The File-Based Loader

Loading Data into Oracle Fusion HCM with the File-Based LoaderSenior Technical Consultant – Brian KimOracle OpenWorld 2013

AgendaIntroductionSolution Overview – File Based LoaderData MappingImplementation ConsiderationsQuestions?

About Emtec“We are a dedicated team of highly-skilled consultants, engineers, deployment and integrationspecialists committed to delivering world-class service to your organization.”Our offerings span the entire IT lifecycle:– Consulting Services– Packaged Applications– Cloud Computing– Cloud Technologies– Application Outsourcing– Infrastructure Services

IntroductionGoals of this Presentation Describe the File Base Loader process Share lessons learned Share anecdotal experience

IntroductionFusion Data Integration and Conversion Options HR2HRSpreadsheet LoaderFile Based LoaderEnd to End packagedIntegrationLeverages ADFdiBulk import from standard data filesClosed/End to endintegrationRestricted sourcesystems (Oracle EBS,PeopleSoft) ADFdi Spreadsheets for loadingobjects supported by HR2HRFlattened containers Data conversion utility that will load data from astandard flat fileLoads complex business objects like Person andWorkerSupports Date Effective history and high volumefor full data conversion of supported objects

IntroductionFile Based Loader Data conversion utility that will load data from astandard flat file Loads complex business objects like Person andWorker Supports Date Effective history and high volumefor full data conversion of supported objects

IntroductionSupported Business Objects in File Based LoaderWork Structures Business Units Locations Job Families Jobs Grades Grade Rates Positions Departments Dept HierarchyTransaction DataTalent Management Person Work Relationships Content Items Rating Models Establishments Profiles (Qualifications,WorkforceCompensation Salary Basis Salary Element EntriesCompetencies, latestPerformance Rating)

Solution Overview - File Based Loader

Solution Overview - File Based Loader

Solution Overview - File Based LoaderStep 1 – User Performs Setups in Fusion

Solution Overview - File Based LoaderStep 1 – User Performs Setups in FusionConfiguration Items Reference Data SetReference Set AssignmentEnterprise StructureLegislative Data GroupLegal AddressLegal EntityJob CodeAction and ReasonsAction Reason LocationDivisionJob FamilyJob FunctionManager LevelJob Grade and Grade RateGrade LadderBusiness Unit Set AssignmentActionAssignment Status

Solution Overview - File Based Loader

Solution Overview - File Based LoaderStep 2 – Extract Fusion Reference Data

Solution Overview - File Based LoaderStep 2 – Extract Fusion Reference Data

Solution Overview - File Based LoaderStep 2 – Extract Fusion Reference Data

Solution Overview - File Based Loader

File Based LoaderStep 3 – Data Mapping

File Based LoaderStep 3 – Data Mapping

Data MappingOptions Manage the Files Manually Develop a ETL program in your source system Use a third party ETL Tool

Solution Overview - File Based LoaderStep 3 – Data Mapping1. Upload Source File2. Upload Target File3. Load Files into memory

Solution Overview - File Based LoaderStep 3 – Data MappingGenerate FBL File

Data MappingDeparment File Based Loader File

Data MappingData Transformation Types One-to-One MappingDefault ValuesDate Format ConversionsConcatenation of two columnsConcatenation of a column and a default valueCross Referencing of ValuesCross Referencing of Fusion Configuration

Data MappingData Transformation Types One-to-One MappingSourceFBL FileID001Column1ABCColumn2 002BCD 003CDE IDColumn1Column2

Data MappingData Transformation Types Default ValueSourceFBL FileDefault ValueID001Column1ABCColumn2 002BCD DEFAULT003CDE DEFAULT“DEFAULT”IDColumn1DEFAULTColumn2

Data MappingData Transformation Types Date Format ConversionsSourceFBL FileFusion Date 2 00212/31/2012 2012-12-3100312/31/2013 2013-12-31IDColumn12011-12-31Column2

Data MappingData Transformation Types Concatenation of two columnsSourceFBL Column2ABC123 234BCD234 345CDE345

Data MappingData Transformation Types Concatenation of a column and a default valueSourceFBL FileDefault Value“DEF”ID001Column1ABCColumn2 002BCD BCDDEF003CDE CDEDEFIDColumn1ABCDEFColumn2

Data MappingData Transformation Types Cross Referencing ValuesXref TableSourceID001Column1 Column2USA FBL FileSource ValueFusion ValueUS 001 USAUS002CAN 002 CANCACA 003MEX 003 MEXMXMX IDColumn1Column2

Data MappingData Transformation Types Cross Referencing FusionConfigurationIDFUSION ID1Fusion ValueDescr 1FUSION ID2Descr 2FUSION ID3Descr 3Xref TableSourceID001Fusion Config FileColumn1 Column2ABC Source ValueFusion ValueDescr 1 001 ABC FUSION ID1002BCD Descr 2 002 BCD FUSION ID2003CDE Descr 3 003 CDE FUSION ID3FBL FileIDColumn1Column2

Solution Overview - File Based Loader

Solution Overview - File Based LoaderStep 4 – Produce Upload File

Solution Overview - File Based Loader

Solution Overview - File Based LoaderStep 5 – Transfer ZIP File to SFTP Server

Solution Overview - File Based Loader

Solution Overview - File Based LoaderStep 6 – Invoke Fusion Data Process

Solution Overview - File Based LoaderStep 6 – Invoke Fusion Data Process

Solution Overview - File Based LoaderStep 6 – Invoke Fusion Data Process

Solution Overview - File Based LoaderStep 6 – Invoke Fusion Data Process

Solution Overview - File Based LoaderStep 6 – Invoke Fusion Data Process

Solution Overview - File Based LoaderStep 6 – Invoke Fusion Data Process

Solution Overview - File Based LoaderStep 6 – Invoke Fusion Data Process

Solution Overview - File Based LoaderStep 6 – Invoke Fusion Data Process

Implementation ConsiderationsLessons Learned Choosing Effective Dates wisely iscritically important Loading the “Department” and “Location”objects can be done relatively quickly Loading the “Person” and“WorkRelationship” objects take moretime Email addresses are tied to Security

Implementation ConsiderationsRequired Skills Fusion HCM functionalknowledge Legacy System functionalknowledge Detailed oriented Data conversion experience

Implementation ConsiderationsImplementation Resources Fusion Functional ResourceSource System Functional ResourceTechnical Resource Dedicated to FBLTechnical Resource for remainingtechnical tasks (Security, Reporting,Issue resolution )

Implementation ConsiderationsAllocation of Timeduring Mapping85%

Questions?Thank you for your time! Brian Kim Senior Technical Consultant, Emtec Inc. Brian.Kim@emtecinc.com

Loading Data into Oracle Fusion HCM with the File-Based Loader Senior Technical Consultant –Brian Kim Oracle OpenWorld 2013. Agenda Introduction Solution Overview –File Based Loader . Fusion HCM functional knowledge Legacy System functional knowledge Detailed oriented Data conversion experience.