Case Study: Data Consolidation delivers value and efficiency

The client needed to reduce the complexity of job processing within its data warehouse. Sysware successfully tendered for the job and promptly delivered a cost-effective solution on time, to budget, meeting all expectations for cost, performance and quality

The data warehouse is a major operational system, with a database that contains over 800 tables. It is also a distributed application split into five regional instances, with a structurally identical database existing for each region.

Data was staged and loaded to the data warehouse on three levels:

• Level 1 held atomic level data from the five source systems, storing current and historical views of data.
• Level 2 conformed and normalised the data from the source systems.
• Level 3 contained aggregated and detailed data, modelled dimensionally.

The level 2 data drew directly on all five level 1 sources, causing complexity and delays in processing. A new SAS dataset was needed between levels one and two, to streamline the complexity of level 2 jobs.

Using SAS Data Integration Studio (DIS), Sysware quickly delivered the project, enabling the required improvements, including:

• More efficient records processing.
• Data volume changes processed in a linear manner.
• Improved timescales for the completion of catch-ups following outages, with data chunked for processing if required.
• Automatic recovery from failure, restoring data to a consistent state.
• The ability to enter new tables into level 1.5 without code changes.
• Alerts when structural differences are identified in tables across regions.
• Full documentation and handover to the client’s development team.

Our client was very satisfied with the results. Sysware quickly delivered an efficient and competitively priced solution that matched the brief and fulfilled all objectives.