Organisations that embrace a data driven culture see a four-times uplift in revenue and customer satisfaction.

Source: Harvard Business Review

This was the conclusion of our guest speaker and Microsoft Manager, Mark Holmes at our recent webinar, where we showcased how data warehousing in Microsoft Azure enables SMEs to harness the power of their data.

Once the preserve of large enterprises, data warehouses were traditionally too big, complex and expensive. Now SMEs can have a data warehouse in Azure relatively simply and cost effectively, read on to find out how.

In this blog post we highlight how to build a robust data warehouse and reporting solution with AI through industry-leading Microsoft Azure. You’ll discover how AI can help you make the most of the business data across all your systems to provide valuable insights for informed decision-making.

If you’d like to discuss how your business can harness the potential of Data Warehousing in Microsoft Azure, contact us for a free discussion with one of our certified Azure technical consultants.

What is data warehousing?

A data warehouse serves as a centralised repository where relevant data from various source systems is integrated, allowing for a comprehensive view across all aspects. By combining and reporting on this consolidated data, you can gain valuable insights into your customers for example, with a unified, consistent and accurate representation. It also removes the possibility of contradictory information from different sources, providing one single version of the truth.

One significant advantage of a data warehouse is that it separates reporting, business intelligence (BI), and near real-time analytics from the source systems. This means that performing reporting and BI activities on the data warehouse does not directly impact your operational systems.

When we refer to reporting, we are primarily discussing static reporting, which involves examining past and current statuses of various elements. Business intelligence (BI) takes this a step further by exploring relationships between datasets, enabling a deeper understanding of what’s happened in the past and offering insights for improving outcomes. Ultimately, the goal with BI is to obtain actionable insights from the data.

Furthermore, the integration of artificial intelligence (AI) can enhance your data enrichment and uncover additional insights that may have been overlooked. It can take your data analysis to the next level by making predictions about future outcomes.

In summary, once you have your business data in good shape and available in a data warehouse, then you can leverage it for:

  • Making reports and dashboards widely available so that many people and teams have up-to-date, correct, consistent information that they can use to help manage the business.
  • Using BI to gain insights into what’s happening and to find ways to improve your business or service.
  • Using AI to further enrich the data to find actionable insights and predictions.

Here’s how the data warehouse architecture works:

Azure Data Warehouse Architecture

There are four key elements:

  1. Data sources– your business data that could be held in a wide range of systems, for example SQL database, Oracle database,  spreadsheets or flat tables either on-premise or cloud – typical sources are your ERP, CRM and Finance systems.
  2. Orchestration – a service which extracts the relevant data from your source systems into a staging location (eg. Azure Data Lake Storage or a SQL database) – this is known as ETL (extract, transform, load). Azure Data factory performs this function.
  3. Data warehouse – the staged data is then Extracted, Transformed and Loaded into the data warehouse (e.g. Azure SQL Database) which contains the data model that is ready to be reported on.
  4. Visualisation –  connecting to the data warehouse using a visualisation tool such as Microsoft Power BI to view the combined data in order to get business insights.

Data Warehousing using Azure Data Factory

Microsoft has an Azure service called Azure Data Factory, which acts as as an orchestrator, data transformation, and data integration tool.  The Azure Data Factory can connect to a wide range of data sources that are held in your business, but also cloud-hosted systems and even http sources such as social media feeds.

Setting up Azure Data Factory

  1. Create connections to your data sources – SQL, Azure blob storage, http connectors or on-premise databases.
  2. Azure integration runtime – consider this as the resource that is required to run all your processes in Azure data factory.  This will connect to your source and targets to extract and transform the required data.  The Azure integration runtime is a platform as a service (PaaS) where everything is managed by Azure for you. If you have more complex requirements, there is the option to have a self-hosted integration runtime.
  3. Data transformation – once you’ve extracted the data you require, how do you transform and integrate data from different sources? You might want to aggregate data from different sources, then carry out pivot and pivot ranking of the data, or:
    • Filter – view a subset of data based on certain criteria e.g. by country or region
    • Pivot – summarise, sort, reorganise, group, count, total or average our data. It allows us to transform columns into rows and rows into columns.
    • Apart from this, if there are some transformations which are not provided here, you have an option to leverage Azure Data Bricks, HD insights, which can do a lot more complex transformations.
  1. Pipeline and Triggers – use these to orchestrate and automate your data Extract, Transform and Load process.  These can be triggered to run on an event basis or on a schedule.

Visualising your data using Microsoft Power BI

Once you’ve extracted and transformed your data set, you can use Power BI to create the reports you need.  From your reports you can create bar charts, line charts, scattered plots, maps and more to visualise your data. 

In addition to the visualisations that Power BI provides, there are a lot more visualisations that are available in the Power BI market that you can use to create your own dashboard views.

The AI features of Power BI

Auto create reports

Simply click on auto create report and the Power BI inbuilt AI features, based on the dataset that you provide, can create a report for you which you can review, decide if the suggested visualisation makes sense for your reporting purposes, and then start using.

Natural language query

This AI feature assists you in asking a natural language question, and creates the report for you. For example, if you wish to understand your sales by region as a bar chart, the AI feature will create the visualisation for you.

Key influencers

Use the AI feature to identify factors that influence your sales success – for example, is it one particular salesperson who is achieving the most revenue, or a particular special offer that you have run?

Anomaly detection

Set a sensitivity factor that allows the AI feature to highlight any differences over or under a threshold – drill down and see what is causing the anomaly (e.g. looking into one specific month that is below revenue target)

Decomposition tree

The decomposition tree visual in Power BI lets you visualise data across multiple dimensions. It automatically aggregates data and enables drilling down into your dimensions in any order.

Forecasting with AI

Most business data is historic and reports on what happened in the past.  Power BI’s AI features takes this historic data and provides a forecast, but importantly it gives you the ability to set the forecast length and confidence levels you require too.

We configured the system not to use any of the LLM’s pre-trained knowledge or access the internet, this meant that the internal sales advisor chatbot provides answers based solely on our own tried and tested internal documentation.

We’ve also helped a number of our customers who are Software As a Service Providers enrich their applications with Azure OpenAI. For example we helped a provider of travel software to use Azure Open AI to automatically update online holiday catalogues whenever a travel agent added a new location, and we’ve helped another customer in the legal sector to use Azure Open AI as part of their solution which automates the labour-intensive generation, negotiation and analysis of contracts.

The team at Compete366 would like to thank Mark Holmes, Microsoft Manager and the team at Microsoft for key insights that have informed this article.

What Next?

If you have a potential use case that you’d like to discuss, please contact us for a free discussion with one of our certified Azure technical consultants. We’re a trusted Microsoft Solutions Partner for Data & AI and we’ll be happy to help. 

Published On: January 19th, 2024 / Categories: Azure / Tags: , , /

Contact our Microsoft specialists

Phone or email us to find out more – or book a free, no-obligation call with our technical consultants using the contact form.

“It’s great to work with the Compete366 team, the team members are really knowledgeable, helpful and responsive. No question is too difficult for them. They have really helped us to manage our Azure costs and ensure we have the right environment. When we bring a new customer on-board we can scale up immediately via the Azure portal and quickly make environments available to our customers.”

“We also find that there’s never a heavy sales pitch from them – they are technically focused and recommend what’s right for us.”

Paul Coyne, Rusada

“We had great support from the Compete366 AVD expert, who was really helpful, and guided me through options to tackle issues that arose.”

“The great thing about our AVD set up is that we have a custom set up for each project which Compete366 showed me how to do. And with the scalability and flexibility of AVD – we can meet clients’ expectations and get project users up and running more quickly.”

Amir Dangol, Senior IT Manager, Integrity

“We were immediately impressed with the advice that the Compete366 specialists in Azure Architecture were able to provide. This was all new to us and we really needed some external expertise that we could use to get our questions answered. The beauty of working with Compete366 is that we transferred our Azure consumption to them, and at the same time received all of their advice and guidance free of charge.”

Tim Entwistle, Head of Software Development, Herrco

“Working with Compete366 has been like extending our own team – they are extremely and easy to work with. Right from the outset, it was clear what was on offer – everything was presented to us in a straightforward and uncomplicated way. They also provided just the right level of challenge to our developers and saved us time and money by suggesting better ways to implement our infrastructure.”

Oliver Mackereth, Project Director, Hanse

“Compete366 were able to help us leverage some useful contacts in Microsoft. We really value the expert advice and guidance that they have offered us in setting up a highly scalable infrastructure. We are also setting in place a regular monthly meeting which will allow us to further refine our architecture and ensure we keep on track as our requirements grow and change.”

Matt Brocklehurst, Technical Director - AWOL Adventure

“I have been delighted with the migration, where my team worked very hard, supported by expert advice from Compete366, and achieved everything in the timescale we had set out. Compete 366 made sure that we didn’t make any expensive mistakes, and guided us through the process”

Darrell Cann, Managing Director, APEX
Jon Milward

By submitting your details, you agree to be contacted.