Automated Migration To Oracle BI Publisher SQL to HCM Extract for a US-Based Fashion Retailer

An American specialty retail company, known for its world-famous family of brands selling lingerie, personal care, beauty products, apparel, and accessories, continued to expand its global footprint. With operations across the United States, Canada, the United Kingdom, Ireland, and Greater China, the company has grown to 3,000 company-owned retail stores, with more in the pipeline. To enable its over 80,000 global associates to manage this scale effectively, the company rolled out the latest HCM system as part of its transformation program.

Given the complexities and unique business model of the customer, an extensive reporting solution was implemented using BI Publisher with custom SQL queries for data extraction. However, many reports required over 100 columns, leading to significant bottlenecks and performance issues in generating them. Therefore, based on the recommendation to migrate from BI Publisher to HCM extracts, the customer chose DataTerrain to lead this migration.

Highlights of DataTerrain's Expertise in HCM Extract Migration

DataTerrain has a proven track record in helping organizations set up HCM extracts as their primary outbound integration tool. Key highlights noted by the customer include:

• Extensive Data Handling: Ability to gather high volumes of data from databases, processes, and archives.
• Technical Capabilities: Expertise in converting archived data into preferred formats, utilizing:
  1. In-built formatting options
  2. BI Publisher
• Domain Knowledge: Access to a pool of subject matter experts (SMEs) across all major HCM areas, including HR, payroll, and benefits.

DataTerrain's Proprietary Optimization Methodology for HCM Extract Migration

DataTerrain deployed its proprietary approach for the migration of BI Publisher SQL to HCM extract. The major phases and key activities are summarized below:

1. Analysis and Design:

• DataTerrain SMEs created mapping documents for each report, aligning BI Publisher SQL tables and columns with Oracle HCM Extract user entities and attributes to minimize run times.
• Designed RTF, Excel, and XSL templates to produce outputs matching the formats in existing reports. DataTerrain's repository of pre-built HCM extracts was leveraged as an accelerator.
• Created suitable valuesets and fast formulas for columns that were unavailable in user entities.
• Revisited the layout to identify redundancies, reducing report complexity and improving readability. After stakeholder approval, these format changes were documented.

2. Development and Validation:

• Developed reports based on the migration document design. The "Refine Extracts" feature was utilized to manage HCM parameters effectively, including:
  1. Choosing appropriate filter types
  2. Controlling parameter visibility to users
  3. Using lookup options for refining extracts
  4. Setting default values based on specific user groups like hiring teams or payroll.
• Employed performance tuning techniques to enhance efficiency, such as reducing report-level calculations and optimizing user entities and filters.
• Conducted manual execution of test cases, documenting runtimes to ensure they met performance criteria.
• Facilitated user validation of test results and runtimes in the test environment, securing approval for production.

3. Knowledge Transfer, Production Deployment, and Roll-Out:

• Deployment of reports was managed using provided documentation, with key users performing final validation before sign-off.
• DataTerrain conducted knowledge transfer sessions for the customer’s technical team, ensuring smooth transitions.

The DataTerrain Advantage

DataTerrain successfully completed the migration of BI Publisher SQL to bHCM extract on time and within budget. The customer highlighted the following differentiators contributing to the successful migration:

• Skilled SME Pool: DataTerrain’s SMEs effectively resolved bottlenecks that hindered the successful execution of production reports. Their expertise bridged major skill gaps within the customer’s organization, allowing for informed decision-making.
• Oracle Partnership: Quick resolution of major issues through established working relationships with Oracle support.
• Global Delivery Model: High project velocity due to 24/7 execution capabilities from DataTerrain’s global delivery centers, facilitating timely progress and issue resolution.

DataTerrain enabled the customer to improve decision-making and manage its business effectively by facilitating the timely generation of required reports. After resolving performance issues and bottlenecks, the customer can now fully leverage its investments in HCM reporting.