For over 10 years the “Microsoft BI Stack” has established itself as an integrated, stable and affordable business intelligence solution and is used in countless companies.

After 15 years of almost unchanged technology, it is time to think about a renewal, the cloud is waiting with modernity, scalability and cost savings.

How do I get my “MS BI” into the cloud?

It is obvious, Microsoft BI in the Microsoft Cloud, that should be the easiest.

How does it work and what does that have to do with a burger that takes off?

We want to explain that here ...

The burger

It is an sweet nickname for the structure of all integrated services that can be used for the MS BI overall solution.

MS BI Stack

The main components that are mostly used:

  • MS SQL Server (MSSQL): The database server
  • Integration Services (SSIS): The ETL tool for loading and transformation
  • Analysis Services (SSAS): The analysis cube as the basis of reporting

Additionally as a reporting tool

  • Reporting Services (SSRS)
    oder
  • Excel

Sometimes also

  • Master Data Services (MDS): For data quality and master data maintenance

And all these components are an integral part of the MS SQL Server, automatically available and can also be used with the licenses.

The tools

The development tools, also Microsoft:

  • Visual Studio (VS)
  • SQL Management Studio (SSMS)
  • The languages T-SQL and MDX

Not only are all components integrated, the development was graphical and with very little code, even then, progressive and very flexible.

The ETL processes with many possibilities

The analysis cube with data model, dimension mapping and measures.

So all in all an innovative overall solution with which you can implement even complex projects efficiently, very cheaply and quickly with little programming.
It's just getting a little old.

Why in the cloud at all?

Costs
Not only the acquisition costs of the hardware, but also the operation (updates, rent, energy, personnel) can cause high costs that are not applicable in the cloud.

Scaling
The hardware does not have to be dimensioned for the peak load, but can be adapted at any time as required, which also has a direct effect on the costs.

Availability
Redundante Systeme, automatische Replikation, mehrere Geo-Zonen, automatische Failover und Backups erlauben einen ausfallsicheren Betrieb.

Innovation
Equipped for new requirements such as “streaming”, “data science”, “data lake”, whether as an extension of the system landscape or as an experiment and knowledge building for a short time.

Agility
The overall architecture can be expanded and adapted at any time, new technologies can be tried out very easily, which allows a quick reaction to new market situations.

Good news and bad news

The good news:

A BI solution can be set up and operated entirely in the Azure cloud.

The bad news:

Of the four classic components, there is only one in the cloud!

CheckMSSQL (database)

No Check SSIS (loading / transformation)

No Check SSAS Multidimensional (analysis cube)

No Check SSRS (reporting)

The not so bad news:

The existing technology can also be operated in the cloud:

Lift & Shift

Now the burger takes off and lands in the cloud.

So that all the laborious developments and processes that have been stable for a long time do not have to be recreated, there is a migration scenario in which the technologies are retained - the on-premise solution is operated in the cloud.

Database (SQL)

Azure SQL Managed Instance
It is a very special database service in the cloud that is almost identical to an on-prem SQL Server 2019, a VM hosted by Azure.
The usual services such as “SQL Server Agent”, “SSISDB”, “Linked Server” can be used.
Not to be confused with “Azure SQL Server”, which is a “real” cloud service.

Azure SQL Server
If the special on-prem features are not necessary, the intended service is the better choice with more real cloud features and is future-proof.

ETL (SSIS)

The existing packages can be kept.

They can be in an SSISDB (Managed Instance) or on an Azure BLOB Storage.
However, the packages are executed on a service of the “Azure Data Factory”, for which there is a specially available “Azure SSIS Integration Runtime”.

Either only the “Integration Runtime” is used as the runtime environment and the control is operated via the “SQL Server Agent” as before, or the “Data Factory” is used for orchestration.

Analysis Cube (SSAS)

The multidimensional model does not exist in the cloud!

There is only “tabular” here.

This means, if the model was not “tabular” before, a complete new design of a model, with all its differences and the calculations (MDX) must be solved again or differently (DAX).

Das Tabuar Modell läuft dann auf einem “Azure Analysis Services” (AAS) oder eventuell auf Power BI Premium, was im Prinzip technologisch gleich ist.

If the multidimensional model is to be retained in any case, there is only the alternative with a virtual machine in Azure, which runs just like on-prem SSAS on an SQL server, which is accordingly integrated into the Azure vnet.

Reporting (SSRS)

SSRS does not exist in the cloud.

The modern reporting is called “Power BI” and can dock directly to the AAS model, integrate further sources or the model can also be part of Power BI (“Power BI Premium”).

If the very outdated SSRS is still to be operated, only one virtual machine in Azure can do it.

Excel can be used as a front end, but it no longer corresponds to modern reporting and can also be used from Power BI if necessary (“Analyze in Excel”).

Azure Cloud BI

Why aren't the same services just in the cloud?

Cloud services have completely different criteria and thus also different technologies and features.
A real cloud solution looks completely different, but it has all the advantages mentioned, which is why you want to migrate to the cloud.

Here is an exemplary architecture:

Azure BI mit Azure SQL und Power BI Premium

This reference architecture has these essential components:

  • Ingestion (staging): data delivery in a BLOB storage or directly in an SQL database.
  • Data warehouse: Azure SQL Server
  • Loading processes / transformations: Azure Data Factory
  • Analysemodell: Azure Analysis Services oder integriert in Power BI Premium
  • Reporting: Power BI

Essentially, the layers and processes are almost the same as on-premise, only the technology is different.

Solutions

What solutions are there now?

Migration with on-prem technologies

If everything is to be retained in the same way as possible, the technologies must also be retained and operated in the cloud.

  • Database (MSSQL): Azure SQL Managed Instance
  • ETL (SSIS): Existing SSIS packages on Azure SQL MI with Azure Data Factory Integration Runtime
  • Analysis Cube (SSAS): If there is no tabular model, only the operation of SSAS (including SQL server) on an Azure VM remains
  • Reporting (SSRS): Can also only be implemented as an Azure VM.

Pros:

Plus low effort / cost

Plus known existing technology

Plus Skillset for further development and operation remains almost the same

Cons:

Minus SSIS will be around longer, but it is not a cloud technology!

Minus SSAS multidimensional is mature and popular, but at some point it will be superseded by tabular

Minus SSRS was already outdated 10 years ago and no longer meets the requirements (neither in development nor with the end user)

Minus Slowly through DB access

Minus Operation with VMs in the vNet and gateways becomes more complicated and more maintenance-intensive in the cloud

Minus Scaling is only possible to a very limited extent

Minus Ev. License costs for multiple SQL servers

Minus CI / CD development and deployment hardly possible

Existing ETL with modern analysis reporting

The database and structure are taken over, the SSIS packages for the ETL remain essentially the same.

For reporting, a new tabular model is designed from the relational database and operated on Azure; this serves as a database for Power BI as a reporting tool.

(If necessary, a drill down to the relational database can also be considered)
(As an alternative to AAS, Power BI Premium could also be used)

Pros:

Plus low effort / cost

Plus known existing technology

Plus Fast performance through in-memory (AAS)

Plus Tabular is optimized for analysis queries

Plus Skillset for further development and operation remains the same for ETL

Plus Operation can be largely scaled

Cons:

Minus SSIS will be around longer, but it is not a cloud technology!

Minus Additional effort for analysis model and new reporting

Minus New skills for Tabular, DAX, Power BI (doesn't have to be a disadvantage)

Minus CI / CD development and deployment very limited

Sustainable cloud solution

Complete cloud solution with the Azure services provided.

Future integration of other architectures (data lake, data science, streaming, etc.).

Pros:

Plus Sustainable modern cloud solution

Plus Fast performance through in-memory

Plus Tabular is optimized for analysis queries

Plus Zukunftssicher für Erweiterungen: Data Lake, Data Science, Streaming, Lambda-Architektur

Plus Integrated security between all Azure services (DB, AAS, ADF, PBI)

Plus CI / CD development and deployment via integrated Azure DevOps and GIT

Plus Flexible and cost-saving operation through scaling

Cons:

Minus Higher migration effort

Minus Additional effort for new technologies except for the database

Minus Greater effort if complex logic is in SSIS and not in DB

Minus New skills for ADF, Tabular, DAX, Power BI (doesn't have to be a disadvantage)

Project scenario

Effort

If a sustainable and flexible cloud solution is sought, it can take longer for a migration to be completed.

Datenbank:
Meist kein großes Problem, denn es ist wieder eine SQL-Datenbank und auch T-SQL ist vorhanden.

ETL:
In particular, the conversion of the ETL can go quickly if the packages are very simple, but become quite complex if there is a lot of logic in SSIS, and there is a completely new tool, the ADF.

Cube:
The analysis model is usually not that critical, but there are some aspects of “multidimensional” that do not exist in “Tabular”, a lot of complex MDX calculations can also make it difficult and basically the way of thinking in “Tabular” is different, but offers also new advantages.

Reporting:
A renewal makes sense anyway, Power BI is a modern, high-performance tool that meets today's key user requirements.
The reports are completely redesigned, but their content is also assessed, interactive reporting and self-service work differently.
It is a common "mistake" to simply copy the previous reports. It is better not to analyze the existing reports, but to collect what the business users need in terms of function and content and derive the model and reports from them.

Project in two phases

In order to get into the cloud and into operation more quickly, it is conceivable to migrate to two parts, whereby the first part can go into operation much earlier.

First, the database and ETL are migrated with the existing SSIS.
Neu erstellt wird nur das Tabular-Modell und die Power BI Reports.

In a second phase, while operations are already running, the possibly more complex ETL can be converted; the business layers can remain unchanged.

Conclusion

In conclusion, it can be said that a simple migration from MS BI solutions to the cloud does not exist, but only makes limited sense, because in order to use the advantages and aspects of the cloud and expand them in the future, other concepts, technologies and tools are needed, so it is also a different, a new solution.

Die Migration in die Cloud mit bewährten on-premise Technologien macht durchaus Sinn, aber nicht in Hinblick auf Technologie oder Zukuftsausbau, sondern nur aus kurz- bis mittelfristigen Kosten- bzw. Zeitüberlegungen.

Share your experience with a comment below!