Migration of SQL Server cubes to Azure Analysis Services tabular
In a prior successful project, Vroon asked element61 to design and implement a modern data warehouse on Azure Cloud for its newly developed Environment, Health and Safety tool (EHS). As a next step,
Vroon’s ambition was to integrate the legacy on-premises cubes in the modern platform.
Vroon is an international shipping company, with more than a century's experience, Vroon delivers reliable, high-quality and cost-effective services, based on traditional values and a proven track record.
Vroon operates and manages a diverse fleet of approximately 150 vessels. All operations meet stringent safety and environmental requirements and the company's vessels follow rigorous maintenance programs.
Vroon is active - through various subsidiaries and under different brand names - in the following business segments:
- Livestock carriers
- Offshore-support vessels and services
- Dry cargo vessels
- Container vessels
- Product tankers
- High-heat tankers
- Car carriers
SSAS Cube Conversion Project
In the past, Vroon implemented an on-premises Microsoft data warehouse and BI solution on SQL Server. SQL Server Analysis Services multi-dimensional cubes are used as the semantic layer. After many years of successful releases and implementations, Microsoft started promoting the tabular technology (in-memory technology, also the engine behind Power BI), over the multi-dimensional approach.
For their latest reporting requirements Vroon has decided to initiate a ‘Modern Data Platform’ with the latest best practice technology on Azure, with the help of element61. This architecture uses Azure Synapse Analytics as data storage for the data warehouse and Azure Analysis Services as the semantic layer, specifically a Tabular Model.
To align the legacy data warehouse with this new source for Reporting in a fast pace, the requirement to include the legacy tables in Azure Analysis Services has risen.
Refactoring the entire legacy ETL to the new data warehouse would take considerable time. Also, the data warehouse itself should stay in place for other purposes. Therefor the decision has been made to add the legacy fact and dimension tables as external tables to the new data warehouse. These external tables are actually files, residing in Azure Blob Storage. This solution is a hybrid of the legacy data warehouse and the new cloud data warehouse merged seamlessly. Where needed, already existing common dimensions are reused or integrated.
This way the addition of legacy DWH tables in Synapse can occur very fast and can easily be kept up to date.
Continue reading here to know more about Synapse Analytics
From SSAS Cube to Tabular
Each cube was evaluated to define which dimensions and facts are relevant. Where needed, more complex MDX calculations were translated into DAX.
All legacy tables that are part of the scope were ingested in a format, aligned with the new data warehouse. The data of the model is synced with the use of pipelines in Azure Data Factory. As a final check, the datasets were cross validated on consistency between the legacy and the new data warehouse.
Today, this is a hybrid solution. However, all options remain open to transform the legacy ETL towards a processing method, applied in ‘The Modern Data Platform’ architecture. To assure continuity and limit dependency in further elaborating and converting the environment, Vroon has been coached to execute these steps themselves if needed.
The assignment for element61 is to incorporate and align the legacy data warehouse Cubes with Azure Analysis Services (Tabular).
Main aspects in this project:
- Definition of the integrated hybrid Azure architecture
- Cube & MDX calculation analysis
- Requirements analysis
- Dimensional modelling (finetuning legacy data model)
- Tabular model development (including DAX calculations)
- Training Tabular development
- Project management
As a result of this project, Vroon is able to use their modern Analytics Platform in Azure with Synapse and Power BI for both the new reporting requirements and reporting over the data from the legacy data warehouse. This assures one single source for Reporting.
Continue reading to know more about:
Or contact us for more information.