A Guide to Moving Business Intelligence to the Cloud – Part 1

Posted by Guest Author on 06 November 2020

A Guide to Moving Business Intelligence to the Cloud

Tech evangelism is more about listening than talking. Having presented at Azure Data Fest in Atlanta – on the topic of “BI Workloads and How They Change in the Cloud” – this latest Tech Notes blog is less about my PowerPoint slides and much about what the audience had to say.

I met around 50 people at the session – one-third of the attendees at the event – and they were an audience hungry for education, so I asked some leading questions…

“How many of you and your business predominantly on-premises with BI/DW data platform?” At least 80% of the room was still on-premises.
“How many have been directed you must move BI to the cloud?” A small number of hands went up.
“How many of you would NOT consider yourselves BI/DW/Data Science type of people?” No hands went up. This was surprising as the audience was diverse. There were DataOps folks and DevOps folks. There were more traditional Data Platform stakeholders like DBAs.

The reality is likely most people working with data are broadening or seeing their roles broaden to include BI/DW/Data Science.

The Modern Data Platform

I used one graphic to portray many of the capabilities of the more modern BI/DW/Data Science platform and it just happened to be from Microsoft:

The session spent time using the above as a means of talking to the differences between “On Premises” and “Cloud.”

Collecting and Managing Data – On Premises

I delved into the realm of “Collect and Manage” on the Data Platform wheel Microsoft had in their graphic. Specifically this:

After sharing my view of the traditional “On Premises” architectures typically supporting only the non-whited out boxes below…

…I asked “What are your Data Sources and where do they end up today?” The group confirmed their architectures were predominantly fed by relational data sources coming from applications and agreed with my assertion that the data sources were generally relational (or structured). Pressed a bit further most offered there were Data Marts and/or Data Warehouses. The crowd was still warming up and I had assigned them to teams, BLUE or RED, where each response earned a point for the responding team.

I found some people starting to verbalize concern about moving their Data Warehouse to the Cloud while a small group (probably two from the same company) had taken bigger steps with the cloud (Azure specifically) and were happy to report positive progress!

(You might also be interested to read "Migrating Data and BI to Azure Cloud – Your Top 3 Questions Answered")

Transforming and Analyzing Data – On Premises

As we made our way around the Microsoft Data Platform wheel, we discussed “Transform and Analyze”. This is a fairly heavy-duty part of ANY Business Intelligence/Data Warehouse/Data Science platform as the real value starts to materialize here. Information is being transformed, prepared and modeled for inclusion in data stores, which support Analysis.

We talked a fair amount about the below elements which are support “On Premises” and the whited out items again being more “likely” in Cloud environments:

Most in the room agreed with there being variances depending on if your approach to loading the data used “ETL” or “ELT”. ETL or Extract, Transform and Load – is a process by which data is collected from a data source and transformed in flight on its way to a Data Mart, Data Warehouse or other decision supporting data store.

ELT or Extract, Load and Transform – is a process by which data is collected from a data source and loaded initially in its originating form. The originating format of this “staged” data is then transformed after it has landed in the BI/DW environment.

(We also recommend checking out "What are the 3 Main Differences between ELT and ETL?")

There was also agreement on data, especially “On Premises”, being modeled in a more traditional Dimensional Model, popularized by the Kimball Group.

What makes the concept of Transform and Analyze that much heavier architecturally is not only is there a level of relational preparation of the data but there are potentially other modeling activities for structuring the data for Analysis. In Microsoft’s world, this involves Analysis Services, which was originally offered years ago and has been refined on the SQL Server platform. Today, Analysis Services is ALSO available as an in-memory offering on Azure (and it is the technology which underlies Power BI’s in-memory data modeling) called Tabular Models.

Interestingly, I asked Team BLUE and Team Red to share their perspective on Analysis Services and the response to “Is Analysis Services in use or do you simply provide data relationally from the Data Warehouse or Data Mart?”

The answers were roughly 15-20% on Analysis Services (in some form) and the remainder said they either didn’t use it or didn’t use it and relied on Power BI to provide the modeling (which means they were still using tabular technology, just in a limited fashion).

(We also recommend reading "Do Tableau and Power BI replace the need for a Data Warehouse?")

Interestingly, there was a much LARGER dependency on Microsoft’s Integration Services technology (SSIS). I’ve spent literally two decades with this technology (including DTS) and I imagine this is one of the big anchor points for most when moving from On-Premises to the Cloud. Microsoft has a technology called Azure Data Factory, which we’ll go into further detail a bit later on.

The session I delivered wasn’t the time to be wearing a ZAP Data Hub hat and talking about our products but what I will also discuss in a bit more detail, later, is the simple reality that ZAP’s Data Hub seriously speeds up and makes easier the processes around “Collecting and Managing” and “Transforming and Analyzing” the data, weather information or the Data Platform be On Premises, In the Cloud or a Hybrid of the two.

Visualizing and Deciding – On Premises

As we completed the circle of the Microsoft Data Platform Wheel, we reached the “Visualize and Decide” section.

We spent a lesser amount of time speaking to the elements of this area of the Data Platform.

This was due to the fact most of the audience agreed with my assertion that On Premises or in the Cloud, much of the visualization activities are still supported when you move between the two.

The IT Workloads being Managed on Premises

We’ll conclude this blog post with a view of the Workloads being managed on Premises.

Applications – ETL/ELT, Model and Database Engines
Data – Raw Sourced Data all the way through to Curated Data Stores
Runtime – Execution of all Processes PLUS platform performance
Middleware – integrations between applications
O/S – Patching, Firewalls and Security
Virtualization – VMWare, Hyper-V environments
Servers – DB/OLAP Engines – particularly work for DBAs
Storage – similar to Servers plus Disk Arrays, etc..
Networking – Security and interconnectedness of Data Platform + Users

The group agreed with all of the above and we focused on the Pro’s and Con’s of having a BI/DW/Data Science Platform On Premises.

(You might also like "5 Reasons Why a Data Warehouse Improves Business Reporting")

Pros and Cons of the Data Platform – On Premises

Some of the Pros discussed included:

Speed of Local Data Transfer
High Degree of Oversight on Executions
Inherent Security (Local Traffic)
Non-Throttled Connectivity
Flexibility of BI Tools and their Connectivity to Data

Some of the Cons the group agreed with and raised included:

Management Overhead – lots to manage
Higher Ongoing Ownership Costs
Lack of Scalability
Lack of ability to address HOT spots or PEAK demand
Difficulty Sharing (if everything is behind firewall)
Server Ops (Patching, etc…)
Latency around Realtime
Having to maintain Rigid Data Models due to complex ETL/ELT processes

My final questions to Teams BLUE and RED were:

Is it an absence of “Pros” or the presence of “Cons” which are driving you to look to Azure?

The consensus was they were wanting to really understand and leverage the opportunity of the Cloud technologies and innovations. Some were focused more so on specific Cloud-oriented technology but all seemed to have a level of interest (which is good considering the event was called “Azure Data Fest”!)

Do you have other concerns about moving to Azure?

There really weren’t many objections but honestly, I wasn’t expecting much in this environment. At ZAP, we’ve heard people concerned about unforeseen costs and risks but it so rarely comes through with a highly technical audience.

So, that’s where we will start our post next time, finishing off the recap of the session and talk about all the great interactions around the Azure technologies, which make up the Microsoft Data Platform. Who knows, maybe you have a vision of your Data Warehouse looking like the diagram below?

(Want to continue reading? Check out the next post in this blog series here: "A Guide to Moving Business Intelligence to the Cloud – Part 2")