English | EN 
The other day I was given a small challenge of migrating on-premise multidimensional cubes to Azure. At first it might seem simple, in fact I was optimistic, but as I reviewed my notes and researched, I realized that the process was getting complicated at times, but of course… with what I like to roll up my sleeves, I couldn’t let it go!
So let’s get started!
The first thing is to create a new project in Azure DevOps, as always when we use any type of development. Although as in this case, it is a use case related to Data. Let’s leave behind the fear of using Visual Studio instead of SSMS!
Once the repository and the main develop branches are created, I proceed to clone the repository in my Visual Studio and continue.
When finished, I create my first feature branch from develop and now it’s time to start building the first cube.
The branch I am going to work on is called: feature/first-data-multi and in it, the first thing to do is to build the solution, to later add the project(s) you want to incorporate. In our case, it will be a Data project with a Multidimensional model. Remember that for this you must install an extension in VS. Here’s the link
To do this, we are going to use Microsoft’s AdventureWorksDW database, available at this link . The diagram on which we are going to work is simple. We will have a first database, Azure SQL Database on a basic server in which we have restored the AdventureWorksDW copy. And as the destination of our Cube in Azure, we will use a SQL Server 2019 on a Windows Virtual Machine, since Azure Analysis Services does not support (for now) Multidimensional models.
IMPORTANT :
To deploy the AdventureWorksDW database in Azure Database, the process to follow is to restore the database previously in a SQL Server (it can be local for example) and from it, make a move from SQL to Azure SQL Database directly from SSMS.
Once complete, we verify that our resources are in the Azure SQL Database.
And immediately afterwards, we can start with the creation of the multidimensional model from our Visual Studio, creating the project. For this first part of creating the cube, read this great article by Daniel Calbimonte, for a step-by-step detailed tutorial, that I have also followed. It’s worth it because I haven’t included all the detail of all the steps here. Better to refer to the original.
Once the project has been incorporated into the solution, we will start with the creation of the resources. We will start with Data Sources. That as we have already advanced, it will be the AdventureWorksDW database in Azure. To do this, we configure the connection to said database.
The next step is to create a Data Source View from the newly created Data Source. To do this we select the following dimension tables and a single fact table.
The next step is to define the basic dimension on which we are going to work. In the example it will be the currency and later we will repeat the process to include the Sales Territory dimension. I will do the same with Sales Territory.
When we finish creating the dimensions, we will continue with the configuration of the Cube.
To finally process it, verify that it is correctly built.
Finished, we click on browser and begin to define our query
Once completed, we commit and upload it to our Azure DevOps code repository.
The next step is to create a Virtual Machine in Azure with SQL Server 2017 Analysis Services to deploy the newly created Cube there.
Once configured and deployed in Azure, we will proceed to deploy the cube and check if it is accessible. The first thing is to get access from our SSMS from the virtual machine.
The next thing is to configure the deployment target of the multidimensional model project on this server in VM. Here we run into a problem as Analysis Services does not allow to connect if it is not through Windows authentication. For this reason, what I have done is install Visual Studio on the VM server and clone the Azure DevOps project in order to execute the deploy, defining my localhost as Target Server.
IMPORTANT :
Remember that to handle the data project, you must install Visual Studio Professional and add the Microsoft Analysis Services extension
NOTE: Alternatively, this problem could be solved by including the VM in the same Active Directory domain. Although I have not verified it, but surely it gives for another entry And finally we see that the process ends with a successful completion. Great
So if I access the Analysis Services instance of my VM I can check that the resources have been deployed correctly
We would already have it working!
CONCLUSION
Data analysis using multidimensional models was very popular about ten or twelve years ago. Now this analysis strategy has been quite obsolete, something that is verified, for example, by verifying that Microsoft has developed Analysis Services as a SaaS service in Azure. Of course, exclusively supporting tabular models. If we add to this that SSAS is only supported in VMs on Azure with SQL Server 2017, it gives clues to the urgency of starting to migrate all developments in Cubes, towards tabular models, for example.
Likewise, aspects such as the fact that Analysis Services only has authentication through Windows, limits usability and connectivity quite a bit. Forcing to develop, or at least to deploy the models from the VM itself.
I can only say that, for a long time since I encountered a challenge like this, I have enjoyed it! and that in future posts I will deploy a tabular model on Azure Analysis Services to demonstrate the simplicity of the process compared to multidimensional models.
Cover photo thanks to Rostislav Uzunov on Pexels
