The need for interactive analytics in the cloud necessitates the use of OLAP, especially if you are dealing with big data. OLAP helps you navigate massive data quickly and effectively using a preferred BI tool or custom application. However, it is scary if you have to blow up your existing, sophisticated OLAP models and start from scratch. Kyvos soothes your migration pains, helping you migrate your traditional OLAP models into the cloud easily and quickly.
In this blog, we will cover how our newly launched “SSAS Migration” utility simplifies the migration of your SSAS cubes to the cloud.
Why Should you Migrate your SSAS Cubes
Among many other traditional OLAP based engines, Microsoft SQL Server Analysis Services (SSAS) is a tool most widely used by analysts as it has excellent OLAP features that are suitable for many BI use cases. But, as medium and large-scale enterprises experience significant growth in data volumes, they face several challenges with SSAS-based solutions.
- Supports small volumes of data: SSAS performs well with low volumes, but performance suffers when querying or building cubes on large datasets.
- Does not scale: SSAS does not support a distributed architecture. Server configuration limits the total data and concurrent users that can be supported. In other words, you would have to add additional resources to the server to process larger workloads to meet business requirements, which has its limitations. Also, since the cubes need to be in-memory to serve the queries, it becomes challenging to handle business scenarios with a large number of cubes.
- High Cost: SSAS license fee is based on the number of cores on the device. As data volumes increase, you need high-end servers with a lot of cores and memory, which is expensive. As requirements grow, costs can grow significantly for both the infrastructure as well as the licenses.
- High processing times: SSAS cubes take multiple days to refresh if the volumes of data reach terabytes. This can cause significant delays in processing data, making the service unavailable in time for business.
- Does not integrate with the Cloud Storage or on-premise Big Data platforms: SSAS integrates with only relational databases like SQL Server, MS Access, Oracle, Teradata, IBM DB2, etc.
How Kyvos overcomes these challenges
Kyvos supports the same OLAP features and complex data modeling capabilities as SSAS but at a much larger scale by leveraging massively distributed storage and enormous parallel processing capability of the cloud. With Kyvos, an analyst can model different types of hierarchies such as unbalanced and ragged hierarchies, multiple hierarchies, alternate hierarchy, parent-child hierarchy, add complex MDX calculated measures, custom roll-ups, and many more OLAP capabilities, on much larger volumes of data on the cloud.
Now, when you migrate to the cloud, you do not need to change your existing data models residing in a traditional data warehouse. Just migrate your data into any cloud platform and your SSAS cubes to Kyvos. Kyvos creates OLAP cubes that provide a unified semantic data model that hides the data complexity from the business user and delivers instant response times on BI tools irrespective of scale in terms of data and number of users.
However, manual migration of cubes is very time-consuming and involves a lot of effort. Enterprises that have hundreds and thousands of cubes across multiple SSAS servers find it extremely challenging to migrate their existing cubes. To minimize these efforts and make the switch more seamless, Kyvos has launched an SSAS Migration Utility that can migrate all your cube models from SSAS into Kyvos effortlessly and make them ready to be built.
Launching New Utility for SSAS Cube Migration
The Kyvos SSAS Cube Migration Utility can run on any windows machine. It has almost all the software dependencies embedded within itself, so any setup or installation is not required.
The diagram below represents a high-level flow of the utility which sits between your SSAS servers and the Kyvos server.
To understand how the utility works, let’s take an example of a cube created over Adventure Works database provided by SSAS whose data source view and cube structure look like this:
The data source view contains table-based, as well as SQL-based data inputs. For example, the following image shows one of the dimension DimCurrency is SQL based:
Similarly, the SSAS cube is a combination of hierarchies, attributes, and measures, including calculated measures.
Multiple hierarchy example
Calculated Measure example
This is a single cube, but there could be many such cubes residing across multiple SSAS servers in an enterprise. Now let’s see how the utility can migrate these cubes.
Steps for running the utility
- Migrate all the data definitions into your cloud platform as Hcatalog tables from your current relational database being used by SSAS like SQL server etc.
- Unzip the utility on your Windows machine at a location from where you want to run it. The following folder structure will be seen:
- Inside config folder, there is a JSON config file where we will define the list of Cubes as well as Data Source Views to be migrated. We also need to provide SSAS server details, Kyvos server details, and a flag that will represent if we want the utility to export the XMLAs automatically from the SSAS server or we want to feed in the XMLAs manually to the utility.
- If we choose to extract the XMLAs manually, put all the XMLAs into the input folder. Else, we do not need to perform this step.
- Once all the configurations are set, simply run the Utility windows batch script inside the scripts folder from the command line.
Once the script execution is complete, we can see the migrated cube in Kyvos with all the OLAP functionality defined in the SSAS cube.
Files pointing to data in the data lake for different facts and dimensions
Relationship design corresponding to Data source view in SSAS
Kyvos Cube with all the defined OLAP functionality in SSAS
Once all the data is migrated to the cloud and mapped with the corresponding HCatalog tables, the cube build can be kicked off in Kyvos and is ready to be consumed using any BI tool.
Kyvos not only offers many of the same OLAP features as SSAS, but it also overcomes the limitations of traditional SSAS. This makes it an excellent choice for migrating traditional architecture to the cloud and deal with a much larger scale. This new utility further eases the migration process by automating the manual work required in migrating the cube to modern data platforms.
Read more about our SSAS Migration solution here.
If you want to see how our SSAS Migration offering will work for you,
request a demo now