A 129+ years old west coast university which is ranked among the top educational institutions in the world by major educational publications is on a journey to reinvent itself and continue to remain a thought leader. With 7 schools, 40 academic departments, and graduate programs in diverse areas like law, medicine, education, and business the effort to track HCM information on the 2200+ faculty, 12500+ administrative staff, and almost 17250 students increased in criticality and continued to grow in complexity. Besides, as one of the top fundraising institutions and the first to raise over a billion dollars in a year, robust financial reporting and analytics became a critical requirement. The University leadership invested in Informatica 10.4 as their solution for Business Intelligence(BI) and reporting.
To accelerate time to value and improve responsiveness to the user community, the University selected DataTerrain to provide support and maintenance services. DataTerrain was asked to draw upon its established track record in improving service levels, fine-tuning DWH(Data warehouse) systems, improving their performance, and make improvements to the University's application.
DataTerrain's unique approach to EDW support services
Using its decades of experience in managing production support and maintenance of large scale EDW implementations, DataTerrain has developed a robust methodology which it deployed for this University. Combining in-depth technical knowledge and expertise in Informatica with proven practices, this methodology extends to the entire range of services as below -
1.)Application services
a.EDW(Enterprise data warehouse) design changes,
b.implementation, build, maintenance using Informatica Power Center,
c.Data integration, and merging across sources,
d.data profiling and cleansing,
e.performance tuning.
2.)EDW maintenance -
a.Maintaining all EDW tables loaded from a variety of internal and external source systems,
b.running audit processes, and ensuring consistency and
c.adherence to specifications
3.)Development/Enhancement/Bug fixing -
a.Best practices based development of new ETL adhering to the specific business standards of the university,
b.Enhancements to existing EDW and ETL implementation, implementation of SCD(slowly changing dimensions) to track historical data
c.Implementation of business rules in Informatica by creating transformations, robust mappings/Mapplets, utilizing varied transformation logic like a router, filter, expression, aggregator, joiner, Sequence Generator
d.Management of and ownership for incident life cycle, debugging, troubleshooting, root cause analysis(RCA), and issues resolution within SLA
e.Preventive measures for repetitive issues, and development of permanent resolutions
f.Resolution of complex bugs with long term fixes following best practices and collaboration with University teams for solution reviews and incident resolution.
4.)Load plan/monitoring -
a.24X7 production system support including "off hours" and weekend "on-call" production support
b.Support for daily builds run on UAT and production environment, management of application releases
c.Implementation of change control processes, continuous integration, and ownership for the smooth release of changes into production
d.Daily spot checks of system health, session monitoring and support for ETL processes,
e.Logging load statistics, failure, and log analysis,
f.communication with stakeholders in case of delays or SLA breaches
g.resolving failures and delays during daily process execution
h.coordination with DBA to resolve table space or file system related issues
Customer benefits due to DataTerrain's services
The best-in-class methodology and approach of DataTerrain have made a visible impact on the customer with the following major benefits -
1.)Improved productivity and efficiency through performance tuning:- ETL load times which were as high as 6-8 hours have now been significantly reduced by the DataTerrain SME team by implementing the following actions -
a.Eliminating bottlenecks by applying performance tuning best practices at source, target, mapping, and DB level.
b.Reducing build execution times using Informatica's push down optimization functionality and using the integration service to translate the transformation logic into SQL queries for execution in the corresponding source or target databases
c.Accelerating data loads by using Informatica's partitioning feature to enable parallel execution of the pipeline ETL.
2.)Increased User satisfaction and effectiveness through 24X7 support - The issue resolution time was drastically reduced and users get through their tasks much faster. Ticket backlogs have also been brought down to eliminate any queueing time.
3.)Reduction in technical debt - Continual system improvement has resulted in a significant reduction in technical debt and the impact is visible through the reduction in the number of technical issues and incidents along with a reduction in average resolution time for issues.
The DataTerrain difference
The customer has called out the following unique attributes of DataTerrain's service as the major contributors to the success of this engagement -
1.)Deep technical expertise - DataTerrain removed technical complexity related bottlenecks and has instituted many best practices to improve performance and maintainability.
2.)Global delivery model - Round the clock support to end-users is ensured by leveraging constituting distributed teams across the global delivery centers of DataTerrain.
3.)Deep domain expertise - DataTerrain's rich experience in working with major educational institutions has helped create unique and customized solutions for different user groups like the school of medicine, school of finance, MAIS, School of admission, and faculty department management
DataTerrain has set the University on a course to accelerating its returns on BI investments and is a trusted partner in its journey as a global thought leader in this forthcoming decade of rapid change.
References -
https://dataterrain.com/dtservices/
https://intellipaat.com/blog/an-audit-of-the-etl-process/
https://www.informatica.com/in/about-us/customers/customer-success-stories/informatica.html