Recently I was discussing some thoughts on how to apply the current Microsoft technology to solving Business Intelligence problems. Microsoft’s business intelligence or analytics software is named Power BI and more and more companies are investing in it at the moment. You can click here to find courses on how to use Power BI if you’d like to do the same, and hopefully this article can give you further guidance too. Although I am not a BI guy and instead I am really an integration and cloud guy, but people still seemed to like my interpretation of how Business Intelligence could be delivered in an effective way for the organisation. I thought it was worth sharing for discussion and so someone more knowledgeable than me about business intelligence architecture approaches can add their thoughts.

3 Modes of BI working together

First off I talked about three different types of BI you might implement in your organisation. The below diagram highlighted some of the common attributes about these approaches.

The main points here were that the different approaches work with different scenarios and different constraint and it isn’t really an either/or. You should probably be thinking about your positioning with all of these approaches. There are some component examples (click here) that will highlight a few approaches for you outside of what we will be discussing here, that you may find useful. While you may favour one more than the others the likelihood is that to get the most from your data you probably need some of each approach.

One of the ideas I like too is the idea that rather than spending loads of time and money modelling your entire data warehouse solution you could use the agile approach to find things of value which you would decide needed to be delivered with some of the characteristics of Enterprise BI so this mode would feed your Enterprise BI with requirements which are already proven.

Cortana Analytics Suite

After introducing some different approaches I talked about some of the technologies within the Cotrana Analytics Suite and where they fit. This diagram from the Microsoft Cortrana suite positions things very nicely.

Applied Architectures

As an integration architect, the bit that starts to excite me is how do you make all of these features work together to deliver a great solution. I put together a few architecture blueprints to show what this might look like.

Agile Business Intelligence

In this diagram we can see how we are using the power of Power BI and its ability to connect to a range of data sources. There are content packs for many of the SaaS providers but also we would use the Power BI enterprise gateway to talk to on premise systems.

In reality there are 2 types of gateway for Power BI, the Enterprise Gateway is used for your production reports and the personal gateway is used for report developers using Power BI Desktop. In the interests of the diagram/blueprint we will represent these as the same thing.

Enterprise Business Intelligence

In the enterprise business intelligence approach we now have a data warehouse. You can see in the below diagram that Azure Data Factory is being used for the ETL processes of feeding the data warehouse in the typical ways. This is really just a cloud implementation of classic BI architectures which used to be done on premise.

One of the interesting points to consider however is the options for things like Machine Learning and Azure Search to also use the data warehouse and to perform in the analysis of data.

There are also other ways you could feed data to the data warehouse in a more real-time approach.

Big Data – ETL Channel

In the below blueprint we are looking at Big Data but in the form of unstructured or semi-structured data. In this particular blueprint we are focused on an ETL and batch style use case where we have used Data Factory to pull in data but because the structure of the data is not strongly defined and modelled up front we are using Azure Storage and Azure Data Lake Storage to store the data.

From here we would use the tools such as Data Lake Analytics and HD Insight to take advantage of their analytics capabilities within this space to process the data and to find interesting things about it. Once the data is processed and analysed we would still be likely to use Power BI for the visualisation.

Big Data – Real Time Event Channel

In addition to an ETL channel we also expect to have a real time event channel for Big Data. This would be telemetry type data happening within the organisation and within areas of interest. In this blueprint we would expect to take the data into the BI platform via a stream approach – you can look more into stream processing for data by looking into a page such as this “What is Stream Processing? – Ververica” as an example. IoT Hub and Event Hub on Azure would be good candidates for this entry point of receiving telemetry from applications and devices.

Once we have the data we can then feed it to our persisted storage via Stream Analytics. Stream Analytics gives us an excellent capability to be able to perform aggregations on the data so we could do things like dump everything into storage or perhaps have an aggregate or values over a tumbling 5 minute window. This is really the first stage in making the data easier for us to process.

Once we have the data in storage we can then use the same tools as before for further processing and analysis with the same visualization via Power BI.

How do I get the Data

As an integration architect the key challenge for making sure the BI platform is successful is “how do I get the data to the platform to be analysed”.

The below diagram illustrates some of my thoughts on this.

Technology Summary

If we think back to the Microsoft Technology stack and look at what features/products we are talking about using here then we can see in the diagram below that there is some interesting overlap between the Cortana Analytics Suite and the Microsoft Integration Suite where some features of Azure would really fall into both areas.


This article is me just writing up a few thoughts around some architecture blueprints and thinking around implementing a BI solution on the Microsoft Technology Stack. Hopefully this is useful for a few people out there.

The slides are available below:


Buy Me A Coffee