Case Study
Developing an automated process to gather accounting and other external data into a locally hosted data warehouse
Who was the client?
Our client is a proprietary trading firm who operate across Europe.
What was the issue?
Our client’s accounting software had a disjointed structure. They was an over-reliance on hard coded data extracts and a wealth of cumbersome external data sources to provide their business intelligence. Put simply, it was a muddle that needed structure and a clear future state vision.
What did we do?
We began, as we always do, by spending time listening to the business. We found out that the business had recently implemented PowerBI, Microsoft’s data visualisation tool. However, we quickly saw that there was significant scope for upgrading both the core functionality and the way in which the data was ultimately delivered. With a core objective of providing real time, usable business insight.
Data Strategy is at the heart of what we offer. Before we began the structural work, we hand-picked each of the component parts required for the project. We then mapped out and developed an automated process that would bring together both their accounting data and the various external data sources they subscribe to, allowing it to be placed in a locally hosted SQL Server Data Warehouse.
The technical path
Step 1
We set up an SQL Server Data Warehouse for the transactional data to be fed into.
Step 2
We developed an SQL Server Integration Services (SSIS) data migration solution. This formed an automated extract, transform and loan (ETL) pipeline that gathered all the data, cleansed and standardised it and then pushed it into the Warehouse. At the same time, we decided, due to the volume and nature of the data, to overwrite the Data Warehouse each day. This ensures the Data Warehouse runs smoothly and always provides an accurate reflection of the underlying data.
Step 3
We replicated the business’s existing PowerBI reports but recreated the process and vastly improved the visual output.
Step 4
We created a scripting process that allows the client to tailor what data is imported each day, giving them complete control over the data they need and how they use it.
Contact Us
Our team of experts is ready and waiting to discuss your unique needs.
- +44 (0) 20 4551 9191
- info@itventure.com
