Transformational Architecture and Analysis

Challenge

For many years our client had maintained a large repository of market data based on their legacy, Excel format. New datasets were added on a case-by-case basis, siloed according to source and only a limited set of key charts and tables could be displayed.

Over the years the volume, quality, and quantity of data available within the client’s industry increased dramatically and this led to multiple additional requirements from the client’s research team, both for cross-dataset analysis and graphic visualisation based on the newly available data.

Action

In consultation with their key stakeholders, we reviewed their historical usage and sought synergy with current project requirements. Having a complete picture meant that we could apply some "blue sky thinking" with confidence we had understood all our client's needs.

Following an assessment of the existing database architecture, processes and analytical methodologies, we determined that a radical transformation of the underlying data structure could allow analysts to interrogate data with increased granularity across datasets. This would also allow for further additions and integrations, unencumbered by an outdated structure that no longer reflected their industry or its position in the wider world.

Close collaboration between our development and analytical teams and many years experience within the client’s industry allowed us to devise and build a data management system that would ingest and validate incoming data, process and interpret it according to specific methodologies and provide secondary and tertiary checks and alerts along the way.

Our analysts were also able to capture inconsistencies in the client’s historical archives which required reconsideration of existing methodologies in order to more accurately reflect industry data and its real-world impacts. 542’s developers were tasked with transforming the existing database into a swift, secure platform for interrogating industry data that was deeper, more complex, and more complete than ever before.

Solution

Across multiple iterations in a continuous development process, 542 would deliver:

• Rolling integration of all existing datasets into the new architecture
• New historical depth and capacity for geographical analysis
• Additional data frequencies, units, and calculations to generate accurate, relevant comparisons
• New user interface for viewing key charts and taxonomies
• New user interface for creating custom charts and taxonomies

Rolling integration of all existing datasets into the new architecture

During the course of the database transformation, the development team was able to incorporate not just the planned datasets into the new architecture, but also include change requests and new suppliers of industry data. The flexibility of the architecture allowed 542 to introduce, validate, and reconcile datasets above and beyond the client’s initial request, resulting in a more robust analytical environment.

New historical depth and capacity for geographical analysis

In discussing future analytical potential, the client expressed an interest in incorporating publicly-available, country-level data, such as a region’s inclusion in an intergovernmental organisation; for example the European Union, ASEAN, or BRICS. This meant logging when a country joined an organisation as well as if and when it left, and applying comparative analyses of the impact of these movements over time. 

These distinctions would also require compatibility with data sources using their own categorisations for data storage and calculation; such as datasets that list country-level data for five countries in a region, but group the rest into an “Other” category. 

In addition, our user interface would provide guardrails for future analysis, notifying users as to when data was available, unavailable, or unsuitable for comparison due to conflicts in grouping or calculation.

Additional data frequencies, units, and calculations to generate accurate, relevant comparisons

With the integration of new datasets and increased historical depth, the database needed to be able to accommodate a wider range of data configurations. This meant ensuring methodologies were in place for new like-for-like calculations and comparisons, particularly when data frequency went from the 20th-century monthly and annual reporting to modern, API-led weekly, daily, and intraday data. Improvements to how historical geographical data was stored and viewed also meant adapting to changing currencies and exchange rates.

New user interface for viewing key charts and taxonomies

The overhaul of the database architecture also meant changing the client’s user interface in ways beyond adding guardrails to improve accuracy - we needed to ensure that each stakeholder was able to quickly find the data they were responsible for analysing and reporting. This meant not only maintaining existing capacities around key charts and tables, but new menu interfaces for exploring existing datasets and cross-dataset taxonomies.

We also added functionality around “snapping” data when performing scheduled updates so that previous reporting was still accessible, even when a data provider had been required to issue changes or updates to improve the accuracy of their historical data feeds.

New user interface for creating custom charts and taxonomies

While the original database had been designed to provide a clean but direct duplicate of an Excel workbook, the increased analytical capacity achieved with the new architecture also introduced the need for client-led creation of new taxonomies, charts, and reports. Our development team was able to provide a user-friendly, client-branded template builder for new charts and tables, improving the client’s internal, and external-facing reports. Without depending on developer intervention, users would be able to create their own key charts and tables, decreasing the amount of time and effort required to share their new, deeper analyses of industry data. 

Conclusion

By helping our client break free from their long-established data storage mindset, 542 was able to introduce a data architecture concept that was forward-thinking, user-friendly, and flexible enough to withstand the challenges of a changing data landscape while providing unprecedented analytical depth and breadth within their industry.