Correcting Microsoft SSIS and SQL Server Scripts

Download a copy of this case study here

The client is a multinational ocean shipping company with offices worldwide. They are a publicly traded company with over 100 years of contribution to coastal commerce.

The client’s cargo transportation services serve both international and domestic markets. Other services they offer are ship escorting, ship repair, commercial ferry, shipyard and bunkering services.

The Business Need

The client launched a new reporting system for the accruals portion of their financials, but the solution, created by a previous IT vendor, was producing incorrect data. This was highly problematic because the client didn’t know if the information they were seeing was accurate, and they couldn’t rely on it for business decisions or show it to shareholders. It was also causing the IT department a significant amount of manual work to get the correct information.

In looking for a new IT partner, the client was wary because of their negative experience with their previous vendor. Quality of work was their primary concern. They needed to find a a partner they could rely in order to show their business users the value of building their data warehouse and continuing to expand their BI program.

The Optimus Solution

Optimus started with a discovery phase to become familiar with the client’s data warehouse, workflows, business processes and documentation. Once this was complete, Optimus was able to determine the gaps between the current solution and the desired outcome.

A data mapping review was conducted to understand and document the business logic and processes for extracting information from the numerous data sources. From this, new business rules were be determined.

Based off the new business rules, Optimus created new SQL scripts needed to produce accurate outputs. Optimus worked closely with the client as they needed to validate the updated SQL rules and data.

The Challenges

The client chose to repair their current system rather than starting from scratch, but working on another provider’s faulty solution was challenging. When not having a full background or understanding of a solution built by another company, and there are often surprises. Some components could be salvaged while other parts required replacement. This was overcome by a thorough discovery phase and stringent testing throughout as bugs were harder to predict.

A lot of time and involvement was required from the client as they were the only ones who could validate the data that was being produced. This took longer than expected because the client is overworked and they could not dedicate the time needed to review and check the work. This was solved by being additionally flexibility and setting realistic timelines based on the capacity of the client.

The Result

Having a functional system eliminated much of the manual work and relieved pressure from the IT team.

The client was happy with the final results and was pleased to have a solution that delivered accurate data.

Conclusion

The client is planning on working with Optimus again to build out a complete BI solution once they have the internal resources to dedicate to the initiative.