data systems

Case Study: Government Data Solutions

Smile IT has extensive experience in offering IT solutions and services to Queensland Government agencies. A deep understanding of the government sector, coupled with our extreme levels of compliance exemplified by our ISO 27001 certification, makes us a reliable choice for IT support in the public sector.

Modernising a Data Warehouse

data warehouse engineersYou might not associate Smile IT with complex data solutions, but we have extensive capabilities in that department. We recently completed a data warehouse project for a large government agency, which we want to tell you more about today. Operating state-wide, this agency needed to give its data warehouse a complete overhaul.

They wanted to do the revamp in-house, whilst enlisting the specific skills of an outside expert. They recognised our skills as being a great match for the key areas they needed help, and the rest is history. Smile IT were taken on to help out.

We’re going to take a look at some of the details of the project in this case study. But first, let’s answer an important question!

What is a Data Warehouse?

A data warehouse is a data management system that takes data from a large range of sources and places it in a central repository of information. This data can then be accessed and analysed to help support BI (Business Intelligence) activities and make better-informed decisions. A data warehouse can contain huge swathes of historical data, so will come with analytics and data visualisation capabilities for ease of use.

The Government Agency’s Mission

Our client needed to improve their operational efficiency, and they identified their data warehouse as being key to doing this.  There were three integral steps that needed to be completed for the overall project to be a success. They were:

  1. A complete reconstruction of the Government Agency’s data warehouse.
  2. Moving to the cloud by migrating from their legacy on-premises SQL server to Microsoft Azure cloud-based data warehouse architecture.
  3. Aligning their ageing operational system to the new data landscape.

Their Data Warehouse Architecture

data architecture

The agency opted for a robust and popular three-tier approach to its data warehouse architecture. This is a beneficial approach because there’s a separation in the functionality of each tier, meaning an individual tier can be optimised and customised individually of the other two.

The three tiers utilised in this instance were:

  1. Raw Data Layer: Data originating from various source applications is stored in its pure, unaltered form.
  2. Lightly Curated Layer: This layer is a comprehensive historical Data Lake with vital support for Slowly Changing Dimensions (SCD) and date tracking.
  3. Highly Curated Layer: This segment housed meticulously engineered data marts and datasets, catering to the specific demands of the organisation.

Skills Smile IT Provided to the Project

There were a number of key skills Smile IT could provide in building the new data warehouse and optimising the agency’s operational efficiency. These included:

Azure Data Warehouse Engineering

The new data warehouse was to be built in the cloud on Microsoft Azure. Our software engineers played a massive role in moving the data from the previously used on-premises server to the cloud-based modern data warehouse.

Data Architecture

The overarching data architecture on Azure had to be designed in alignment with the three-tier structure. Close cooperation between data modellers and data warehouse engineers was required. Data Lake design, dimensional modelling, data vaults and relational constructs were all included.

Data Modelling

Data modellers need to craft detailed entity relationship diagrams. To do this they facilitated effective communication across business subject matter experts, stakeholders and technical specialists.

Technical Business Analysis

The output of analysts included highly detailed source-to-target mapping that outlined data movements, transformations and rules. Analysts had to bridge the gap between business requirements and technical specifications. Data analysis, wrangling, reporting and management were part of the role.

The Benefits of the Revamped Data Warehouse

data modellingThis collaborative effort with Smile IT helped pave the way for a more data-driven future for the Queensland Government Agency. Here are some of the benefits of their new three-tier data warehouse:

Improved reliability: If there is an outage or issue in one tier, it won’t affect the other tiers. Their performance and data availability will continue as normal. Also once the data is in the warehouse, it won’t change.

Integrated consistency: Data from multiple disparate sources is presented in a consistent, indexed and organised manner.

Scalability: Data storage and functionality can be scaled easily. The tiers can be scaled independently of each other if required.

Enhanced decision-making: There’s an abundance of data that can be leaned on for making decisions in all aspects of the business. The warehouse supports BI functions to collate and present the relevant data, including artificial intelligence and machine learning.

Contact Smile IT for Public Sector IT Support

We hope you’ve enjoyed this breakdown of our collaboration with a Queensland Government Agency on their new data warehouse. If you’re in the public sector and need IT support on an ongoing or project basis, you want an ISO 27001-certified MSP like Smile IT on your side. Contact us today and we’ll answer any questions you might have.

When he’s not writing tech articles or turning IT startups into established and consistent managed service providers, Peter Drummond can be found kitesurfing on the Gold Coast or hanging out with his family!