Moving to the Cloud
SQL Server Analysis Services Multi-Dimensional (SSAS MD), Microsoft’s venerable implementation of OLAP, has been the heart of thousands of business intelligence implementations for over 20 years. But it’s beginning to show its age. Cloud-scale volumes of data are moving out of reach. Newer use cases and varieties of data sources are increasing. And there hasn’t been a major upgrade to SSAS MD in over ten years. It’s time to migrate to something that can meet modern data performance requirements and continue to do so.
There are many target migration options from SSAS MD, but it would be very nice if the migration were as apples to apples as possible. Migration to Kyvos offers what is pretty much that easy migration path since SSAS MD and Kyvos are both OLAP services built on pre-aggregation. Other options require significant changes, for example:
- In-memory options such as SSAS Tabular are limited by expensive memory.
- Client-side in-memory processing off extracts, using tools such as PowerBI and Tableau are limited by the memory of the client.
- Data Virtualization options risk inconvenient impact on source systems. Performance is also poor since many of the data sources aren’t optimized for OLAP workloads.
This series is targeted towards architects and developers responsible for technical evaluation. It will demonstrate how Kyvos provides the best apples to apples migration from SSAS MD to a modern platform. You will be able to evaluate the feasibility and check out the minimal level of effort required for the migration. If a deeper dive is needed, a meeting can be scheduled with a Kyvos expert.
In this three-part blog, we take a mid-level dive into a technical feature comparison between SSAS MD and Kyvos.
Part 1: This series begins with a comparison of the high-level architecture of SSAS MD and Kyvos.
Part 2: We’ll continue to compare the primary features and objects.
Part 3: We close with a comparison of the cube design processes and end on the next steps for migration.
Before continuing to the comparisons, you may be wondering if an OLAP solution is still necessary on the cloud. Here are some points:
- OLAP mitigates query-time aggregating, which facilitates sub-second query response time of analytics loads.
- Mitigating redundant recalculation of millions to trillions of facts reduces expensive cloud compute costs.
- The data schemas are presented in clean, easy to understand semantic layers.
SSAS MD to Kyvos Basic Comparison
Some of the following content is probably review for some readers. However, it’s important to offer a quick review of these topics to ensure we are on the same page.
| SSAS Component | Kyvos Counterpart |
| Aggregations | Aggregations |
| Formula Engine | OLAP Server |
| Storage Engine | Query Engine |
| Query language: MDX | MDX and SQL |
Table 1-1 – High-level SSAS objects and Kyvos counterparts.
Dimensional Modeling and Pre-Aggregates
The purpose of both SSAS MD and Kyvos is to accelerate the responsiveness of analytics queries to the point where analysts can analyze at the speed of thought. No more wasting time and derailing your train of thought while waiting many seconds or even hours for query results to return.
This is accomplished through the pre-aggregation of large numbers of facts in the range of millions to trillions of rows. Facts are transactions (related events) that occur throughout an enterprise and are collected in a table that can become quite large. Examples of facts include sales, readings from sensors, or visits to a website. A fact table, the centerpiece of a dimensional model, is a collection of a type of transaction.
Further, facts are associated with a variety of entities. For example, a sale is associated with a customer, product, purchase date/time, store, method of payment, etc. Each of these entities tied to the fact are dimensions. These dimensions along with the fact table form a star schema, or sometimes a slightly more complicated snowflake schema.
With these very large collections of facts and their associated entities, we search for sums and counts on various permutations. During analysis, sums and counts from these facts are repeatedly calculated from a wide variety of angles. Pre-aggregation mitigates redundant recalculation of sums and counts of these large numbers of facts. These pre-aggregations form a cube, the “business-end” of SSAS MD and Kyvos.
Both products deal with the same sort of dimensional modeling of objects such as dimensions, dimension attributes, facts, and of course cubes. That means from the outside view of analysts and dimensional modelers of SSAS MD cubes, Kyvos will look rather familiar.
SSAS MD Scale-Up vs. Kyvos Scale-Out
However, on the inside, from the view of DBAs administrating SSAS MD databases, Kyvos is quite different. SSAS MD is deployed onto a scale-up SMP server, and Kyvos is deployed onto a scale-out cloud platform. They serve the same function but are built in very different ways. It reminds me of how a shark and an orca may be apex predators and look similar on the outside but very different on the inside. Therefore, the configuration of the infrastructure will be less familiar to the SSAS MD administrator.
Since SSAS MD is implemented on a single SMP server, the only way to increase capacity is by switching to bigger, more expensive servers. That would be fine, but for the fact that those servers are only so big. The bottleneck for SSAS MD is the limited CPU and memory resources available on a single server to process and compute massive volumes of data. For SSAS MD, storage capacity isn’t the bottleneck. Storage devices have been able to accommodate terabytes of data for a long time.
Kyvos is implemented on scale-out clusters. Processing, storage of the cube, and querying of the cube are split into independent tasks distributed across some number of servers that scale out until SLAs are met. Scale-out increases performance through that division of labor to levels well beyond what can be accomplished with a single server.
The OLAP Engine
For SSAS MD, the computation of massive volumes of data consists of three primary facilities that must share limited resources on that single server:
- Storage Engine – This maintains an in-memory cache of recently queried data. The caching mitigates re-reading data from the much slower physical storage.
- Formula Engine – Manages computation of queries and maintains results caches in highly-reusable formats.
- Processing – These are the SSAS MD services that create the SSAS MD cube objects, most notably the pre-aggregated data. This involves reading data from data sources, aggregating the data, writing out the data objects, and building a variety of indexes.
The Storage Engine and the Formula Engine usually run concurrently, especially when SSAS MD is servicing more than one query concurrently. Both consume the limited CPU and RAM on the single SMP server.
Kyvos is implemented across two layers of scale-out clusters, the BI Servers and the Query Engines. These two layers are somewhat analogous to SSAS’s Formula Engine and the Storage Engine, respectively.
SSAS MD’s Formula Engine receives a parsed query from the Query Parser, creates an execution plan, requests data from the Storage Engine, performs calculations, caches those calculations and returns those results. Likewise, servers within Kyvos’ BI Server cluster perform similar tasks, only at a much larger scale.
SSAS MD’s Storage Engine is responsible for retrieving data requested by the Formula Engine. It is responsible for the logic of determining whether an aggregation can serve the needs or whether it needs to aggregate the data on the fly from the underlying facts. The Storage Engine is also responsible for writing out MOLAP data generated from cube processing.
Kyvos’ cloud architecture scales out query processing in two dimensions. As mentioned above, there is the separation of the Formula Engine and Storage Engine counterparts into BI Server and Query Engine clusters, respectively. The second dimension is self-evident in that they are clusters that can each scale-out onto potentially large numbers of commodity servers.
SSAS MD’s cube processing is multi-threaded, but as with query processing, the processing occurs on the same server. Kyvos differs in this respect in that cube processing is handled by a cluster of Spark servers. Spark, of course, is a scale-out solution.
Query Language
SSAS MD and Kyvos support both MDX and SQL. However, for SSAS MD, MDX is clearly its native language. Both support SQL, but Kyvos supports a much more robust dialect.
While I personally feel that MDX elegantly expresses the graph theory and set theory nature of OLAP, it is yet another language that is unlike any other. Therefore, learning MDX is not a trivial task. On the other hand, SQL is ubiquitous and less complicated as it deals with less complicated table structures as opposed to multi-dimensional cellsets.
While MDX is used to query SSAS MD and define calculated measures, calculated members, and security expressions, Kyvos only requires MDX for calculated measures and calculated members. Kyvos handles the security of a cube through a UI where all of the options are laid out neatly.
The SSAS to Kyvos Migration Tool
Kyvos has developed a migration utility that drastically eases the bulk of the migration process. It consumes the XMLA-formatted specification of your SSAS MD cube, then automatically creates the Kyvos objects. As with all migration tools, there will be varying levels of clean-up left over, but the bulk of the tedious work is done.
Next Steps
In Part 1, I presented a higher-level architectural comparison of SSAS MD and Kyvos. In Part 2, I’ll present a comparison of the objects of a cube design.
This blog series takes a mid-level dive into the major comparison points between SSAS MD and Kyvos. For anything not covered or that needs a deep-dive elaboration, please contact us now. We will schedule a meeting with our experts to answer any questions.
Furthermore, these blogs provide more information related to migrating from SSAS MD to Kyvos: