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.
Content
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.
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!
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.
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).
The Tabuar model then runs on an "Azure Analysis Services" (AAS) or possibly on Power BI Premium, which is technically the same in principle.
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:
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:
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.
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