Customer Story and Reviews
We trust in diverse collaboration and timely problem-solving

Migration of BI publisher SQL to HCM extracts for a US-based fashion retailer

An American specialty retail company with a world-famous family of brands selling lingerie, personal care, beauty products, apparel, and accessories continued to rapidly expand its global footprint. Its operations across the United States, Canada, the United Kingdom, Ireland, and Greater China had grown to 3000 company-owned retail stores with even more in the pipeline. To enable it's over 80000 global associates across all locations to manage this scale, this 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 with BI publisher using custom SQL queries for data extraction. Many reports required over 100 columns and the system failed to generate them due to large data volumes and the resulting bottlenecks. Based on the recommendation to migrate from BI Publisher to HCM extracts, the customer chose DataTerrain to lead this migration.

The following highlights of DataTerrain's track record in helping organizations set up HCM extracts as the primary Outbound integration tool were noted by the customer -

Extensive experience in enabling a high volume of data to be gathered from database, processes, and archives

Technical capabilities in converting these archived data to the format of choice and delivering to identified recipients using -

In-built formatting options

BI publisher

Domain knowledge with a pool of SMEs(subject matter experts) across all major HCM areas like HR, payroll, benefits, etc.

DataTerrain's proprietary optimization methodology using HCM extracts

DataTerrain deployed its proprietary approach for the migration of BI Publisher SQL based reports to Oracle HCM extracts. The major phases and key activities performed are summarized as below -

1.Analysis and Design - During this process, the following items were reviewed and documented -

a.DataTerrain SMEs created mapping documents for each report with the BI Publisher SQL Table/column and mapped it to the equivalent Oracle HCM Extract User Entity/Attribute to minimize run times

b.RTF, Excel, and XSL templates were designed to produce outputs matching the Excel/text/CSV formats in existing reports. During this process, DataTerrain's repository of pre-built HCM extracts was also leveraged as an accelerator

c.Where columns were unavailable in User Entities, suitable Valuesets and Fast Formulas were created

d.Columns displayed in the layouts were revisited and redundancies were identified to reduce report complexity and improve readability for users. After stakeholder approvals, these format changes were appropriately documented.

A detailed migration plan was created based on the above and calendars of users and key stakeholders were blocked for key activities needing their involvement at every major project milestone.

2.Development and validation - The following major activities were performed by DataTerrain during this -

a.Development of reports based on the design in the migration document. Refine Extracts feature was used as appropriate to manage HCM parameters as below -

i.Choosing the appropriate filter type like choice list, list of values, radio button, and date picker

ii.Controlling which parameters would be visible to the user

iii.Using Lookup to choose the parameter to be used to refine Extracts

iv.Setting default values to run extracts based on identified parameters for a specific user group like hiring team or payroll.

b.Performance tuning using techniques like reducing report level calculations, using efficient user entities, filters in extracts, reducing the size of extract XMLs, and configuring threads.

c.Manual execution of test cases and documentation of run times of each test case to ensure they meet the required performance criteria

d.User validation of test results and run times in the test environment and approval for promoting to production.

e.Creation of appropriate documentation, instruction, and report catalogs for promotion of developed reports up to the production environment.

3.Knowledge transfer, Production deployment, and roll-out - Deployment of reports were handled using the documentation provided and key users performed a final round of validation before the respective reports were signed-off. As a key activity during this phase, the DataTerrain team provided detailed documentation and conducted knowledge transfer sessions for the customer technical team.

The DataTerrain difference

DataTerrain completed this project on-time and within budget, following which the customer highlighted the following as major differentiators of DataTerrain that contributed to this successful migration -

1.DataTerrain's Highly skilled and experienced SME pool - DataTerrain SMEs effectively resolved all the bottlenecks preventing successful execution of production reports. Besides, they bridged a major skill gap in the customer organization. The customer also noted that their knowledge helped to quickly narrow down the problems and where required, help stakeholders make informed decisions for workable solutions.

2.DataTerrain's Oracle partnership - Major issues were resolved quickly with help from Oracle through well-established working relationships between DataTerrain and Oracle support.

3.DataTerrain's Global Delivery Model - The velocity of this project was much higher since DataTerrain team members from their global delivery centers ensured 24X7 execution of project tasks. During critical testing phases, time was saved as technical support was always at hand to progress activities and to resolve issues or defects. Daily/weekly/monthly forums ensured timely reporting and appropriate issues escalation for speedy resolution.

DataTerrain has enabled the customer to improve decision making and effectively manage its business by enabling the timely generation of reports required by its business users. After DataTerrain resolved all major performance issues and bottlenecks, the customer is now able to generate value from the investments made in HCM reporting.?