Data Platform Modernisation and Power BI Transformation
Introduction
The Max Barney Estate is a family run property company who have been intrinsically linked with central London’s properties for the last 50 years. The Max Barney Estate also encourages tenants to put their distinctive stamp on properties, to express their character and communicate their individual message. This is one of the many reasons why their properties are home to a community of over 1,000 businesses.
The Max Barney Estate also owns Containerville, located next to the Regents Canal. Containerville is described as a home for start-ups and small businesses, made up of 45 containers that are arranged over three floors, and have been up cycled into modern workspaces
Challenges
Many organisations rely on flat file exports and spreadsheet-based reporting to gain insight into their business operations. While this approach can work initially, it often creates challenges as reporting requirements become more sophisticated and historical analysis becomes increasingly important.
Dolphin IT Solutions was engaged to modernise a reporting environment that relied on daily flat file exports and an unstable Power BI model. The objective was to create a scalable data platform capable of tracking changes over time, improving reporting accuracy, and providing a robust foundation for future business intelligence initiatives.
By introducing a centralised database, automated data processing, and a dimensional reporting model, Dolphin transformed a fragile reporting solution into a scalable and reliable business intelligence platform.
Challenges
The existing reporting environment was built around a simple flat file structure delivered daily through an SFTP process.
Whilst the files contained the information required for operational reporting, they presented several limitations that prevented the business from gaining meaningful long-term insights from its data.
One of the biggest challenges was the way data was supplied. Each file represented a snapshot of information at a specific point in time, replacing the previous day's data. As a result, there was no reliable way to understand how records had changed historically, making trend analysis and historical reporting extremely difficult.
The reporting layer presented further challenges. The existing Power BI model had evolved over time without a consistent design approach, resulting in a complex and inflexible structure that was difficult to maintain. Performance issues were becoming more common, and introducing new reporting requirements often required significant redevelopment effort.
The business required a more robust solution that would:
- Preserve historical changes over time
- Provide a reliable single source of truth
- Improve Power BI performance and maintainability
- Support reporting at multiple levels of detail
- Create a scalable foundation for future analytics
Our Solution
Dolphin IT Solutions designed and implemented a modern data platform that transformed daily flat file snapshots into a structured reporting environment capable of supporting historical analysis and advanced business intelligence.
The solution centred around a dedicated SQL database that acted as the organisation's reporting warehouse.
Automated Data Ingestion
The first stage involved automating the retrieval of source files from the organisation's SFTP environment.
A custom .NET data processing service was developed to securely collect and process incoming files as they became available. This removed the need for manual intervention and ensured a consistent and reliable ingestion process.
Once received, the files were loaded into a staging area where the data could be validated, transformed, and prepared for further processing.
Historical Change Tracking
One of the most significant improvements delivered by the project was the ability to track changes over time.
Because the source system only supplied daily snapshots, there was no native mechanism for identifying what had changed between file deliveries.
To address this, Dolphin developed a custom change-detection process within the data platform.
Each incoming file was compared against previous data sets to identify additions, modifications, and removals. These detected changes were then converted into historical delta records and stored within the database.
This approach effectively transformed simple flat file snapshots into a historical data source capable of supporting trend analysis, auditing, and long-term reporting.
For the first time, users could see not only the current state of the business, but also how key values and metrics had evolved over time.
Dimensional Data Warehouse Design
With historical data available, Dolphin designed a structured reporting warehouse using a dimensional modelling approach.
Rather than exposing raw transactional data directly to reporting tools, the information was organised into a series of fact and dimension tables using a star schema design.
This architecture provided several benefits:
- Improved Power BI performance
- Simplified report development
- Greater consistency across reports
- Easier maintenance and future expansion
- Support for reporting across multiple business dimensions
The dimensional model allowed users to analyse data at different levels of granularity while maintaining consistent calculations and definitions across the reporting estate.
Power BI Modernisation
Once the new data platform was established, Dolphin rebuilt the Power BI reporting layer to take advantage of the improved data architecture.
The previous model's complexity was replaced with a structured and scalable semantic model aligned to the underlying warehouse design.
By leveraging the fact and dimension structure, reports became easier to maintain, more performant, and significantly more flexible when accommodating future reporting requirements.
Users gained the ability to analyse data across multiple perspectives without the limitations imposed by the previous reporting model.
Results
The completed solution transformed a fragile reporting environment into a scalable business intelligence platform capable of supporting both current and future reporting needs.
By combining automated data ingestion, custom .NET processing, historical change tracking, and dimensional warehouse design, Dolphin IT Solutions delivered a robust foundation for enterprise reporting.
Key outcomes included:
- Automated retrieval and processing of daily SFTP data feeds
- Historical tracking of changes previously unavailable within source data
- Creation of a centralised reporting database
- Improved Power BI performance and stability
- Flexible dimensional reporting through a star schema architecture
- Reduced maintenance overhead for reporting teams
- A scalable platform capable of supporting future analytics initiatives
The result was a modern data platform that provided the business with deeper insight into its operations, improved confidence in reporting, and the ability to analyse trends and performance over time rather than relying solely on daily snapshots.
