SQL Data Warehousing
Business Intelligence Data Warehouse
Introduction
Mr Fothergill’s is a major UK-based supplier of seeds and gardening products, supplying both retail stores and online platforms such as Amazon. With a strong reliance on financial reporting to support business decision-making, the organisation had already been using Tableau to visualise data sourced from Sage.
However, as reporting requirements evolved and the internal analytics strategy matured, the business made the decision to transition away from Tableau and adopt Microsoft Power BI as its primary reporting platform. This shift required a more structured and scalable data foundation, leading to the need for a redesigned SQL-based data warehouse and a modern ETL pipeline.
Dolphin IT Solutions was engaged to design and implement a new reporting architecture that would support Power BI development, improve data accessibility, and provide a robust foundation for financial analytics.
Challenges
Mr Fothergill’s existing reporting environment was functional but limited in terms of scalability, flexibility, and long-term maintainability.
Key challenges included:
- Reliance on Tableau-based reporting, requiring migration to Power BI
- Financial data stored in Sage accounting software, with limited reporting structure for analytics use
- Lack of a dedicated data warehouse layer, meaning reporting logic was tightly coupled to source systems
- Existing data structures were not optimised for BI consumption, making report development more complex than necessary
- Requirement to introduce row-level security (RLS) to control data visibility across users
- Need for improved documentation and clarity to support internal Power BI developers
The business required a modern, structured reporting platform that would allow Power BI to operate efficiently while ensuring data accuracy, security, and ease of development.
Our Solution
Dolphin IT Solutions designed and delivered a new SQL Server-based data warehouse and ETL solution, specifically built to support Power BI reporting and replace the legacy Tableau-dependent approach.
The solution focused on creating a clean, well-structured data layer that separated reporting logic from the underlying Sage system, enabling a more scalable and maintainable analytics architecture.
Data Warehouse Design
A new SQL Server 2019 data warehouse was implemented to serve as the central reporting layer.
Unlike the legacy structure, the new model was designed with clarity and usability in mind, using developer-friendly naming conventions for tables and columns. This significantly improved the ability of internal Power BI developers to understand and work with the data without constant reliance on technical support.
The redesigned schema provided a stable foundation for financial reporting, enabling consistent definitions and improved performance across all Power BI reports.
ETL and Sage Integration
To populate the data warehouse, Dolphin IT Solutions developed a structured SSIS (SQL Server Integration Services) ETL process.
This pipeline extracted financial data from the live Sage environment, transformed it into a reporting-ready format, and loaded it into the SQL Server data warehouse.
The ETL process was scheduled to run nightly, ensuring that Power BI reports were always based on up-to-date financial information while maintaining system stability and performance.
This approach also ensured that reporting workloads were removed from the live Sage environment, reducing operational impact on core accounting systems.
Power BI Enablement and Security
In addition to the data platform, Dolphin IT Solutions supported Mr Fothergill’s with key Power BI implementation requirements.
A major focus was the introduction of Row-Level Security (RLS), enabling the organisation to control data access at a granular level. This ensured that users could only view financial data relevant to their role or permissions, improving both governance and compliance.
The structured data model also simplified Power BI development, allowing internal teams to build dashboards more efficiently and with greater consistency.
Documentation and Knowledge Transfer
To support long-term adoption and reduce reliance on external support, Dolphin IT Solutions produced comprehensive technical documentation covering:
- Data warehouse structure and schema design
- ETL processes and scheduling
- Data mappings from Sage to SQL Server
- Power BI integration guidance
- Row-Level Security configuration
This ensured that internal developers had the information required to extend and maintain the solution independently.
Results
The completed solution delivered a modernised reporting architecture built around Microsoft technologies, replacing the previous Tableau-based approach with a scalable Power BI-ready platform.
Key outcomes included:
- Migration from Tableau to a Power BI-focused reporting environment
- Deployment of a structured SQL Server 2019 data warehouse
- Automated nightly ETL process integrating Sage financial data
- Improved clarity and usability through developer-friendly data modelling
- Implementation of Row-Level Security for controlled data access
- Reduced complexity for Power BI report development
- Improved documentation and long-term maintainability
The result was a robust, scalable analytics platform that enabled Mr Fothergill’s to improve financial reporting capability, streamline Power BI development, and establish a stronger foundation for future data initiatives.
Client Testimonial
Dolphin helped the team at Mr Fothergills build and scope a BI warehouse. During this journey they provided great training and expertise. We would highly recommend them.
