Seit über 10 Jahren hat sich der „Microsoft BI Stack“ als integrierte, stabile und günstige Bussines Intelligence Lösung etabliert und wird in zahllosen Unternehmen eingesetzt.
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.
Inhalt
Wie bekomme ich mein „MS BI“ in die 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.
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
Gerüstet für neue Anforderungen wie „Streaming“, „Data Sience“, „Datalake“, ob als Erweiterung der Systemlandschaft oder als Experiment und Wissensaufbau für kurze Zeit.
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!
MSSQL (database)
SSIS (loading / transformation)
SSAS Multidimensional (analysis cube)
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.
Es können damit die gewohnten Dienste genutzt werden wie „SQL Server Agent“, „SSISDB“, „Linked Server“.
Nicht zu verwechseln mit „Azure SQL Server“, der ein „echtes“ Cloud-Service ist.
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.
Ausgeführt werden die Pakete allerdings auf einem Service der „Azure Data Factory“, dafür gibt es eine eigens verfügbare „Azure SSIS Integration Runtime“.
Es wird entweder nur die „Integration Runtime“ als Laufzeitumgebung genützt und die Steuerung wie bisher über den „SQL Server Agent“ betrieben oder man nutzt die „Data Factory“ zur Orchestrierung.
Analysis Cube (SSAS)
The multidimensional model does not exist in the cloud!
Hier gibt es nur „Tabular“.
Das bedeutet, wenn das Modell nicht schon zuvor „Tabular“ war, einen kompletten Neuaufbau eines Modells, mit all seinen Unterschieden und auch die Berechnungen (MDX) müssen neu, bzw. anders gelöst werden (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.
Das moderne Reporting heißt „Power BI“ und kann direkt an das AAS-Modell andocken, weitere Quellen integrieren oder überhaupt das Modell ebenfalls Teil von Power BI sein („Power BI Premium“).
If the very outdated SSRS is still to be operated, only one virtual machine in Azure can do it.
Excel als Frontend kommt zwar in Frage, entspricht aber auch nicht mehr einem modernen Reporting und kann im Bedarfsfall auch aus Power BI heraus verwendet werden („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:
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
- Analysis model: Azure Analysis Services or integrated with 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:
low effort / cost
known existing technology
Skillset for further development and operation remains almost the same
Cons:
SSIS will be around longer, but it is not a cloud technology!
SSAS multidimensional is mature and popular, but at some point it will be superseded by tabular
SSRS was already outdated 10 years ago and no longer meets the requirements (neither in development nor with the end user)
Slowly through DB access
Operation with VMs in the vNet and gateways becomes more complicated and more maintenance-intensive in the cloud
Scaling is only possible to a very limited extent
Ev. License costs for multiple SQL servers
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:
low effort / cost
known existing technology
Fast performance through in-memory (AAS)
Tabular is optimized for analysis queries
Skillset for further development and operation remains the same for ETL
Operation can be largely scaled
Cons:
SSIS will be around longer, but it is not a cloud technology!
Additional effort for analysis model and new reporting
New skills for Tabular, DAX, Power BI (doesn't have to be a disadvantage)
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:
Sustainable modern cloud solution
Fast performance through in-memory
Tabular is optimized for analysis queries
Zukunftssicher für Erweiterungen: Data Lake, Data Science, Streaming, Lambda-Architektur
Integrated security between all Azure services (DB, AAS, ADF, PBI)
CI / CD development and deployment via integrated Azure DevOps and GIT
Flexible and cost-saving operation through scaling
Cons:
Higher migration effort
Additional effort for new technologies except for the database
Greater effort if complex logic is in SSIS and not in DB
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.
Database
Usually not a big problem, because it is again an SQL database and T-SQL is also available.
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:
Das Analysemodell ist meist nicht so kritisch, allerdings gibt es einige Aspekte bei „multidimensional“ die es in „Tabular“ nicht gibt, sehr viele und koplexe MDX-Berechnungen können es auch erschweren und grundsätzlich ist die Denkweise in „Tabular“ anders, bietet aber auch neue Vorteile.
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.
Es ist ein häufiger „Fehler“, die bisherigen Reports einfach nachzubauen, besser ist es, nicht die bestehenden Reports zu analysieren, sondern zu erheben was die Fachanwender genau benötigen an Funktion und Inhalt und daraus das Modell und Reports abzuleiten.
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.
The migration to the cloud with proven on-premise technologies makes perfect sense, but not in terms of technology or future expansion, but only from short to medium-term cost and time considerations.
Share your experience with a comment below!
Leave a comment