About
With over 30 years of building expertise and a solid history in the insurance industry, Bay Building Group (BBG) focuses on using advancements in technology to deliver the highest quality, most professional, accessible, and comprehensive repair and restoration service available industry wide. BBG also offer clients extensive coverage, delivering services throughout both metropolitan and regional Australia.
Made up of Bay Building Services, Linx Restoration Services and Bay Building Commercial & Specialty, the Bay Building Group is uniquely capable of delivering fully integrated repair solutions encompassing both building and restoration services. The companies work independently or together to consistently deliver optimised solutions to all clients.
Challenge
The Chief Technology Officer, BBG requested Ager BI to undertake a review of the current data management and reporting capabilities and develop a data management plan and business intelligence strategy for future state reporting requirements.
Specifically, Ager BI was requested to:
attend workshops with subject matter experts
understand and review current and future state reporting capabilities and requirements
identify any risks or design issues in the current reporting system
consider future state requirements to design the architecture for a future business intelligence solution
develop a data management plan and business intelligence strategy.
Solution
Following a review of the current and future state reporting capabilities and requirements, Ager BI recommended implementing a Microsoft solution that leveraged existing infrastructure and software in place at BBG. This included:
continuing with the current Microsoft Azure virtual machine plus setting up two additional virtual machines to segregate transactional and reporting data whilst providing an extra environment for development and testing purposes
continuing with the current three SQL Server instances plus setting up three additional Microsoft SQL Server instances to separate transactional and reporting data
continuing with the current OLTP and OLAP databases plus creating additional databases for each data source, whilst providing additional environments for development, testing, and production
Additional recommendations include:
The installation of Microsoft SQL Server Integrations Services (SSIS) on each virtual machine which is an extract, transform and load (ETL) tool that will enable data to be moved from data sources to reporting databases, quickly and efficiently.
The use of Microsoft Power BI as the cloud-based reporting platform where users can log in and access up-to-date reports and dashboards on desktop, mobile and tablet devices.
The aim of the proposed solution is to:
reduce database crashes for the OLTP database by removing queries that are used for reporting purposes
create OLAP databases to model data using a star schema methodology that will improve querying performance
extend the number of tables loaded to include the full set of tables available in Timberline to provide complete historical data for financial reporting
load data into a reporting database to provide managers and executive with reports to review historical data
remove manual processing of data refreshes within Excel dashboards so that users do not have to manually refresh data
automate data refreshes using SSIS and Microsoft Power BI service to ensure data is up to date
migrate all business rules to SQL views to remove duplicate rules in multiple reports
centralise reporting data to create a single ‘source of truth’
migrate all reporting in Excel and Board to the Microsoft Power BI service to provide a central cloud-based location where users can access reports on desktop, mobile and tablet devices
create a data model that can be used to design entity-relationship diagrams of tables and relationships to assist developers in the maintenance of the solution.
Benefits
The fully implemented solution addressed the issues and risks identified in the workshops, improved data load and refresh efficiency, minimised disk space usage, enhanced reporting performance and provided a stable reporting platform.
The review undertaken by Ager BI provided BBG with a clear pathway of which direction to take when implementing a data management plan and business intelligence strategy. All problems have been identified, with options about how to progress that will be cost effective, efficient and works within the current BBG infrastructure.
