Key elements to drive solution design

You've got the key people in the room. You know the insights that will make a difference to the organisation. You’ve built a data integration matrix to deliver them to the right people at the right time. Now you need to drive your solution forward.  

But how do you do that?  

Choosing your architecture  

90% of the projects we work on use either Tableau, Qlik, or Microsoft and they all provide the data visualisations end users will be looking for.  While we have experts in our team in all three, we are Microsoft Partners so do recommend Azure Synapse Analytics and Power BI.  It delivers a great product for a good price point and the analytics framework can deliver for any size of business from start-up to enterprise scale. 

Cloud or onsite? 

Before pushing on you’ll need to decide where your data will sit – do you want a cloud, on-site or hybrid solution? Synapse is primarily cloud based and this fits with the set-up of many organisations but some, public sector organisations for example, prefer a hybrid approach. 

Designing the solution  

When we’re designing a solution, these questions are always front of mind: Where's the data? How can we extract the data? How can we transform the data? How do we load the data into the place that's going to be used for reporting and visualisation?  

Pipelines to cover ETL  

The first step in designing the solution is to develop an ‘Extract, Transform, and Load’ pipeline. This is the process of moving data from often multiple sources to a database or data warehouse. Increasingly this includes structured and unstructured data. The former being typically from a relational database and the latter from websites and sources like Google. 

The Data Lake  

This then drives the use of a data lake – following the Microsoft template this would be the Azure Data Lake. A data lake like this can store data of any size, shape, and speed, and process it ready for the data warehouse.  

Developing a data warehouse  

When you're designing a solution, this is where most of the challenging work will be. Getting the data from the source to the data lake can be done quite quickly but getting the tables and schema right in the warehouse takes skill and expertise.  

At this stage existing business rules need to be incorporated too. These can be complex, especially with large clients like big industrial manufacturers, NHS Trusts, or large universities, where there might be 20 data sources to navigate. Because business rules might use lots of sources to get to the exact analytic you want as a key measure for your reporting so these rules must be be programmed into the data warehouse. 

Output analysis and visualisation  

Strong foundations in place you’re ready to move onto reporting and with Microsoft that's where Power BI comes in. It brings disparate data sets into outputs they can visualise and reporting dashboards.  With the increasing interest and potential for AI there is also huge potential and demand for adding in tools like ChatGPT to ensure the analytics strategy can grow with the organisation over time. me.  

A solution that supports growth and can be self-managed 

One of the key reasons we chose to be a Microsoft partner is the potential for organisations to manage their Business Intelligence after the initial implementation period.  

We often collaborate with internal IT teams on implementation to ensure good knowledge transfer so clients can look after the solution on a day-to-day basis. There’s plenty of training available online, or through partners like ourselves, and a large support network.  

Giving ownership of Business Intelligence to the internal team – whether that’s users at the start of the process or IT specialists at implementation stage – usually brings smoother implementation, a more aligned roll-out, and higher adoption levels.  

Want to discuss how our Business Intelligence experts could support your move to a true data-driven organisation? 

If you’re just starting out on your data journey, check out our blog (link to first blog here) or have a listen to our recent podcast series with Business Intelligence and Data Strategy Consultant Graham James here.