Data: The Queen Bee of the Digital Hive (Part Two)
Part 2: Data management in the real world
Continuing from our discussion on the fundamentals of data management in part one, this blog focuses on how we at PDMS leverage our expertise and advanced technologies to create robust business applications. We will explore our approach to database design, security, and reporting – as well as how you can ensure the integrity, performance and scalability of your data assets.
The aspects of data capture, storage, and usage described above are exactly what PDMS (Professional Data Management Services) was created for, and remain our “bread and butter” work to this day.
Building robust applications
We build robust and scalable business applications using the Microsoft technology stack, which gives us peace of mind that all the fundamentals are in place, and provides technical tools we can rely on to “do what it says on the tin”.
Central to the technical stack is Microsoft SQL Server, their relational database management system. The SQL Server gives us the tools to create and enforce the logical relationships between system entities. It ensures that data elements are stored in an appropriate data format and optimises the performance when retrieving the data back out of the database. Of course, tools are only as good as the people using them, and we have a wealth of experience using this technology formed over our 30+ years in business.
Data model accuracy
A good database starts with the data schema, which must accurately reflect the properties of the real-world entities being modelled, and the relationships between them.
If this isn’t right at the start, the application built on the database will fill with “bodges”, which inevitably results in data inconsistencies. So, it’s worth taking the time to accurately build the data model to prevent errors and ensure a clean, reliable application.
We have designed and built many application databases for a range of clients including ship and aircraft registries, customs and financial intelligence systems, ferry configuration and booking systems, and a National Insurance system for a small jurisdiction. Many of these systems have been in place for a number of years and the longevity of our applications is a testament to the quality of the original database and application design.
Data validation approach
We have always adopted a robust “belt and braces” approach to data validation, applying checks at client-side, server-side and even database level where appropriate. Preventing invalid data from being stored in the first place is far better than having to rectify it later.
We use the industry-standard tools offered by Microsoft, augmented by tried-and-tested reusable components we’ve built ourselves. Reusing our own established components provides a further level of assurance that validation rules will be applied as expected.
Security and segregation
Storing data and letting everyone read it is easy. But robustly applying security and segregation rules can be challenging, especially as performance must be maintained. The THEMIS system we provide for three financial intelligence units has some very complex “row level” security rules which need to be applied when accessing the data tables. For instance:
-
Does the current user have sufficient security clearance to see the record?
-
Is the current user in a group which is permitted to see the record?
-
Has the record been further restricted to a specific subset of people?
-
And layered on top of this, is the user authorised to access this data table at all?
More typical record filtering includes scenarios such as: only showing ship records to their owners, only allowing users with sufficient authorisation to see certain details of registered companies, and only allowing a website customer to see their own bookings.
Whatever the security requirements, we can meet them, because we build bespoke application security to exactly meet the requirements of our clients: and we don’t rely on pre-built solutions from other providers which may not flex to the specific needs of a given client.
Management reporting
Getting data out of a database for fixed management reports is relatively easy. We often implement management reports with a default set of filters applied and provide search criteria controls. This allows the user to filter and sort the data in different ways. A consideration here is the performance impact of reading large volumes of data from an operational system which is also undertaking a continuous stream of “write” transactions.
It’s important to carry out sufficient load-testing to assess the impact of running a big report and mitigate this by optimising the database indexing. In some cases, replicating the data into a read-only database dedicated to reporting is a viable solution. SQL Server (and other database engines) generally provides this feature.
Analytical reporting
When analytical reporting is required, integration with specifically designed applications can help. For example, Microsoft Power BI is a platform that “ingests” data from a variety of data sources, including application databases in SQL Server, and allow users to build their own queries on top. These queries can be used to generate interactive graphics to represent the summaries of the filtered datasets - essentially draw useful “meaning” and insights out of an otherwise unintelligible mass of data.
However, for a “lay user” to be able to do this, the datasets must have been “shaped” and “labelled” to allow the user to understand the model. This is almost always NOT the same model as the source database; therefore, expertise is needed to reshape the data into a user-friendly form, without compromising the integrity and accuracy of the data – something we have considerable experience of within PDMS.
Legacy system modernisation
Making the most of vital data assets “trapped” within legacy business applications is essential for preparing your business for the future. PDMS has a track record of “re-platforming” such applications in a controlled and incremental way. This ensures business continuity is maintained while building new windows to the existing data.
An old application can be gradually replaced with modern user interfaces and APIs, as required. This helps to avoid the inevitable delay in building an entirely new application, as well as the risk associated with a “big bang” switch-over. A new application not only replaces business functionality “like for like”, but also unlocks all sorts of opportunities to make more use of your valuable data.
Conclusion
Data is arguably the most vital asset of any business or public body. Accurate capture and robust storage of data are essential for day-to-day operational use, management reporting, and further analytics and reporting. It’s also important to remember that the success and versatility of generative AI depends heavily on the quality, quantity and variety of data it’s trained on.
When choosing a technology partner to help with your data, you need someone with a proven track record of “doing the basics” well and making the most of well-structured data assets. After all, Data is our middle name (Professional Data Management Services) 😊
Topics
Author
