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 time scales 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 hand over to the client's development team.