Overwiev

Client: A leading global chemical enterprise.

Location: Kaiseraugst, Switzerland.

Industry: Material s/ Chemicals

Technologies Used: Informatica Integration Cloud Services (IICS), Amazon Web Services (AWS), Azure DevOps, Python, Power BI

1. Background

Merging two companies with different data platforms and business models presented significant challenges:

• Data Quality Standards During Transformation:
• Ensuring data quality amid new employees, changing roles, and ongoing data maintenance.
• Data Accessibility and Silos:
• Difficulty accessing files stored in SharePoint for analytics, leading to delays and inaccuracies.
• Scalability Issues:
• Existing data processes couldn't handle increasing volumes and diversity.
• Data Availability:
• Inability to perform timely analytics and reporting due to integration limitations.

2. Objectives

The main goal was to integrate HR files from SharePoint into Amazon Redshift using IICS, enabling analytics and reporting while ensuring data consistency and quality. Key objectives included:

• Efficient Data Ingestion:
• Handle various file formats and ensure efficient loading of high-volume files.
• Implement file naming and versioning conventions for automated ingestion.
• Enhanced Data Quality:
• Utilize IICS's data quality tools for profiling, cleansing, and validation.
• Apply complex data quality rules to reduce errors and improve integrity.
• Scalability:
• Leverage IICS and AWS capabilities for scalable data integration.
• Detection of Corrupt Data:
• Configure IICS to monitor data quality metrics and trigger alerts for failed data.

3. Solution

We implemented an end-to-end data pipeline integrating IICS, AWS S3, Amazon Redshift, and Power BI:

• Infrastructure Implementation:
• Extracted data from SharePoint, processed it through IICS, and loaded it into Amazon Redshift.
• Created Parquet files in AWS S3 for efficient storage.
• Built a Power BI dashboard for reporting and data stewardship.
• Data Integration and Quality:
• Developed mappings and transformations in IICS to cleanse and enrich data.
• Implemented data quality rules allowing business users to manage them easily.
• Applied validations for accuracy, completeness, consistency, and uniqueness.
• Customization and Integration:
• Leveraged platforms familiar to the client (IICS, AWS, Power BI).
• Tailored transformation logic to the client's data structures.
• Employed source control using Azure DevOps.

4. Challenges

• Technical Challenges:
• Data Profiling Issues: Faced challenges with data profiling due to large volumes; improved success rate by optimizing infrastructure and configurations.
• Operational Challenges:
• Communication and Alignment: Ensured clear communication between technical teams and stakeholders to manage evolving requirements.

5. Results

• Improved Data Quality Metrics:
• Reduced new failed records by 30%.
• Visualized improvements through a Power BI dashboard.
• Enhanced Reporting Capabilities:
• Replaced the legacy reporting tool with the Global Data Governance Dashboard.
• Reduced manual efforts in tracking and managing data quality.
• Positive Client Feedback:
• High satisfaction with quick access to clean data.
• Training sessions empowered team members to use new tools effectively.

6. Lessons Learned

• Effective Requirement Management:
• Breaking down requirements into manageable tasks with clear acceptance criteria.
• Collaboration Across Teams:
• Maintaining open communication to ensure mutual understanding of scope and timelines.
• Adaptability:
• Managing changes in data requirements and integration processes.

7. Conclusion

The project significantly enhanced the organization's data management by implementing a scalable, end-to-end data pipeline. Key achievements included:

• Improved Data Quality:
• Automated data quality rules reduced errors by over 30%.
• Cost Efficiency:
• Automation decreased time spent on manual data cleaning.
• Scalability:
• Designed a solution adaptable to future needs using IICS and AWS.
• Actionable Insights:
• Provided data quality metrics supporting data stewardship initiatives.
• User Adoption:
• Empowered team members through training, leading to effective use of new tools.

Next Steps:

Building on this success, we will participate in Master Data Management and Data Quality projects for other domains like Customers and Vendors, supporting both operational and development tasks.