Overview of the Primary Objects and Features
As mentioned in Part 1, SSAS MD and Kyvos are both about optimizing star/snowflake dimensional models through the pre-aggregation of large numbers of facts. As a result, the bulk of answers to slice and dice questions are there before you ask for them. Therefore, most of the prime features of SSAS MD have counterparts in Kyvos. Some comparisons are fairly straight-forward, but some need a bit of translation.
The purpose of this article is to compare parts of SSAS and Kyvos cubes before comparing the deeper aspects of the design and implementation experience in Part 3.
The following table lists the objects that we’ll cover.
| SSAS | Kyvos Counterpart | Comparison |
| Data Sources | Connections | |
| Data Source Views | Registered Files, Data Sets, and Data Relationship Diagrams | |
| Measures | Measures | |
| Dimensions and Hierarchies | ||
| Strong/Natural Hierarchies | Hierarchy | Kyvos is Hierarchy-based |
| Weak/Unnatural Hierarchy | None | |
| Attributes | Attributes | |
| Member Properties | Member Properties | |
| Parent/Child Hierarchy | Parent/Child Hierarchy | |
| MDX Script | Calc measures, query scope calc members | No scope or global scope calc member in Kyvos |
| Calculated Measures | Calculated Measures | |
| Calculated Members | Calculated Members | Query-scoped supported |
| Named Sets | Global scope on the roadmap | Query and Session scope avail |
| Supplementary Cube Features | ||
| Actions: Drillthrough and Report | Supports Drillthrough action | No Actions from Kyvos cubes |
| KPI | ||
| Perspectives | Logical View | Not really a perspective but worth mentioning |
| Translations | ||
| Security | ||
| Server and Role-based Security | ||
| Cube Level through Cell-Level | Cube, Row, and Column level |
Table 2-1 – Cube object comparison.
Data Sources
Specifying the data sources is the first step in the cube design process. SSAS MD and Kyvos cubes are built from tables contained in one or more Data Sources. For SSAS MD, these data sources have typically been a data warehouse, usually implemented on SQL Server, and to a lesser extent on Oracle. However, although a data warehouse is typically made up of star/snowflake schemas, that isn’t a requirement. What matters is that star/snowflake schemas can be derived from the data source.
The equivalent of SSAS MD Data Sources in Kyvos is Connections. While SSAS MD data sources are pretty much relational databases, Kyvos’ selection of data sources (“Connections” in Kyvos) is much more compelling. They include cloud-based databases such as Snowflake, Redshift, and Google Big Query, as well as cloud storage platforms such as AWS S3, Azure Data Lake Storage, Google Cloud Storage, or Hadoop.
SQL Server and Oracle are also valid on-prem Kyvos connections. Details on the inclusion of these can be made available if needed.
Please note that for cloud storage sources, and unlike relational databases that SSAS MD folks are used to, there may not be the notion of “table”. In Cloud storage, there are files that may be tabular in nature but require some definition. For example, CSV files. Or they may not be tabular at all, for example, images. Therefore, there may be a need to explicitly define the schema of files stored on cloud storage. Kyvos addresses this with a feature called “Files,” introduced in the next section.
Data Source Views
For SSAS MD and Kyvos, tables from across these data sources are assembled into a Relationship Diagram that conforms to a star schema (or snowflake schema) from which we design our cubes. In SSAS MD, this Relationship Diagram is called the Data Source View (DSV), and for Kyvos, it is called a Dataset Relationship Design (DRD). The DSV and DRD both hold relationships between tables and the columns that join the tables together.
SSAS MD and Kyvos both allow cubes to have multiple fact tables. So a DSV or DRD can be a set of star/snowflake schemas linked through common dimensions.
As mentioned above, although SSAS cubes are intended to integrate data from multiple data sources, they are usually sourced from a single data warehouse. The key reason for the retreat from multiple data sources to a single DW is that the integration of data across a variety of enterprise sources requires a lot of ETL work. In fact, it’s so much work that the ETL development effort usually takes up the bulk of a BI project’s development time.
That level of ETL is beyond the transformation capabilities within SSAS MD itself. Those capabilities are primarily the named queries (a SQL defining a table) and named calculations (SQL expression defining a column) features of the DSV. Most BI implementations involving SSAS MD involves a full-featured ETL tool such as SQL Server Integration Services (SSIS).
Kyvos’ Data Sets feature is much more robust in terms of ETL capability than the named query and names calculation features of SSAS MD. SSAS MD’s DSV features are actually spread out among three Kyvos Features:
- Files – Table structures defined from files in storage-based Connections. These are the equivalent of “tables” in the DSV. These files can also be defined with a SQL statement, which provides transformation capabilities roughly equivalent to the DSV’s named tables and named calculation features.
- Data Sets – This is roughly similar to an SSIS Data Flow. The Data Sets feature outputs a table from one or more Connections that go through a flow of transforms.
- Relationships – This is the Dataset Relationship Diagram composed from Files and Data Sets.
Part 3 includes more details on Kyvos’ Files, Data Sets, and Relationships features.
Measures
Measures are the focus of OLAP. They are the values that are pre-aggregated. It is what is counted and added. Measures are values of the fact table in the star/snowflake schema. For example, a table of sales facts include values for each sale such as quantity, extended amount, tax amount, freight. The notion of measures is the same for SSAS MD and Kyvos.
SSAS MD and Kyvos support the same set of aggregation functions:
- Additive: Sum, Average, Max, Min
- Semi-Additive: Last non-empty
- Non-Additive: Distinct Count
As with SSAS MD, in Kyvos, we can construct globally-scoped calculated measures from these measures. For both, these calculated measures are MDX expressions.
Dimensions and Hierarchies
SSAS MD and Kyvos dimensions are composed of the same objects: Hierarchies and their levels, attributes, and member properties. Dimensions generally represent some sort of entity, such as customers, dates, products, or employees.
Each of those entities consists of a number of attributes. For example, products have a category, a sub-category, and a unique identifying code (ex: ISBN, NIC, ICD10) with a friendly description. They could also have other attributes, such as sizes, style, and color.
Some of these attributes of an entity can be grouped into hierarchies. For example, the category, sub-category, and product form a hierarchy.
On the surface, the dimensions are pretty similar for SSAS MD and Kyvos. However, the way hierarchies affect what aggregations are created are not quite the same. The relationship between hierarchies and aggregations is more complicated for SSAS MD than Kyvos, and will be discussed further in Part 3.
Strong Hierarchies (aka Natural Hierarchies)
Both products highly favor what are in SSAS MD terms, strong hierarchies (aka natural hierarchies). That is, a hierarchy where children roll up to only one parent. For example, for a date hierarchy, 2020-12-08 rolls up only into 2020-12, which in turn rolls up only into 2020. For the Product dimension mentioned above, a strong hierarchy would be the category, sub-category, and the unique code, where a unique code belongs to only one sub-category and a sub-category belongs to only one category.
The value of strong hierarchies being restricted to one-to-many (1:m) relationships between parent and child is that the OLAP engine can assume the sum of the children equals the value of the parent. With that assumption, both products can reduce the number of aggregations that is necessary. For example, if you have an aggregation by year/month, you don’t really need an aggregation for year/quarter. That’s because it’s not that much work to add three months for the sum of a quarter.
Kyvos is based on strong hierarchies, although that term isn’t part of the Kyvos lexicon. The Kyvos term for “strong hierarchy” is just “hierarchy”. This understanding is relevant for understanding how the set of aggregations are determined. We’ll discuss that further in the Aggregation Design section of Part 3.
SSAS was similarly based on strong hierarchies before SQL Server Analysis Services 2005. It was referred to as “hierarchy-based”. From SQL Server 2005, SSAS became “attribute-based”.
Weak Hierarchies (aka Unnatural Hierarchies)
That brings up the subject of what are in SSAS MD called weak hierarchies (aka unnatural hierarchies). Like strong hierarchies, weak hierarchies are specified as a series of levels. But they don’t have the requirement of 1:m relationships between each parent and child level. Weak hierarchies are in essence just a convenient drill-down path presented to the end-user.
Member Properties
SSAS MD and Kyvos support member properties. These are values tied to an attribute. For example, if there were a Size attribute (Sm, Med, Lg) for a Products dimension, a property for size might include volume if the product is bottles of water. Member properties aren’t intended for slicing and dicing, but may play a part in filtering and/or calculations. For the example of bottled water, we could filter products by sizes where the volume is less than 100.
Member properties aren’t recognized in most visualization tools, surprisingly including Power BI. But member properties can be accessed in calculated measures, calculated members, and MDX statements via the PROPERTIES MDX function.
Slowly Changing Dimensions
SSAS MD supports Type 1 and Type 2 slowly-changing dimensions. Type 1 SCD are supported through incremental processing in two ways:
- Member names (not keys) are updated with no further effect.
- Member keys that change are allowed during incremental dimension processing. However, aggregations that include the attribute with changed members are dropped and must be re-created.
Type 2 isn’t really an SSAS feature. It’s implemented by adding a new dimension attributes:
- “Original ID” – This is an attribute that ties all the member versions together.
- Effective Date – Effective Date of a version.
- Expiration Date – Expiration date of the version. This could also help identify the latest version.
Kyvos supports Type 1 and Type 2 in similar ways. Type 1 is akin to setting attribute relationships in SSAS MD to flexible. Kyvos allows a hierarchy level of attribute to be marked as “Slowly Changing”.
Type 2 changes are supported in a similar manner in Kyvos. But because scale is not an issue with Kyvos, it can better handle dimension growth due to Type 2 changes.
MDX Script
The MDX Script of an SSAS MD cube is a globally-scoped collection of calculated measures, calculated members, scopes (sets of cells), and named sets (sets of members). Kyvos doesn’t employ the notion of an MDX script. However, Kyvos does have the ability to define globally-scoped calculated measures and calculated members, the former being the most important.
For the other components of an MDX script, workarounds include:
- SCOPE blocks. SCOPE blocks enable the overriding of the values of groups of cells. But the vast majority of SCOPE statements can be converted into other MDX expressions, particularly nests of IIF.
- Named Sets. Although there are no globally-scoped sets, there are SESSION and QUERY-scoped sets. STATIC named sets are on the roadmap for Kyvos, but DYNAMIC named sets are still in evaluation. A workaround for globally-scoped named sets is to create a dimension attribute. For example, an attribute named “Large Customers” could be added to the Customers dimension. The benefit of a named set in the MDX script is that the grouping can be ad-hoc instead of forcing a change to the dimensional model.
Supplementary Cube Features
By “Supplementary Cube Features”, I’m referring to the cube-level SSAS MD features that step outside of the fundamental OLAP principle of the pre-aggregation of a dimensional model. These features include KPIs, Action, Perspectives, Translations, real-time processing, write-back, and custom functions.
Most of those SSAS MD features (drillthrough and writeback were available from the start) are nice-to-haves that debuted in SQL Server 2005. The intent was to arm SSAS with the features required of a product tagged as a “Unified Dimensional Model”.
Some of SSAS features such as Translations, Custom Functions, Writeback, and Real-Time Data are not supported in Kyvos, but inclusion of several of these into the roadmap is under evaluation.
Actions
By far, the most heavily utilized feature within this category is the “Action”, drill-through. Yes, Kyvos supports drill-through.
The other “Action”, Report Action, isn’t supported in Kyvos. A Report Action is a handy way to construct a URL (ex. SSRS report) from selected members. To my knowledge, no SSAS client beyond Excel recognizes actions, not even Power BI.
KPIs
Kyvos doesn’t support the SSAS MD KPI feature. However, the components of the KPI (value, status, trend, and target) are calculated measures under the hood. Kyvos does support calculated measures. Therefore, there is a reasonable workaround for the four KPI components. For example, KPI-related calculated measures could have a naming convention where the target component could be named, “Net Profit – Target”.
Perspectives
Kyvos doesn’t support SSAS MD’s Perspectives. Perspectives provide a way to hide dimensions, attributes, levels, and measures from groups of users. This is simply a feature of convenience that simplifies a cube for groups of users by hiding selected cube objects. I should mention that this isn’t a security feature. The hidden objects just don’t show up as metadata, but are still accessible through MDX queries.
However, it’s worth mentioning that the Perspectives feature is sometimes used to present objects with customized names. For example, some users may want to see their date dimensions specifically named as “Calendar” and “Fiscal”. For other users, they may only see the Calendar dimension and wish to call it just Date since there’s no need to differentiate.
Kyvos does have the notion of a Logical View and a Physical view. This really has no direct SSAS MD equivalent, although there are some vague similarities to Perspectives and Shared Dimensions. But a better analogy for Logical and Physical views is a comparison to base tables and views in a relational database.
Security
SSAS MD has security at the SSAS instance level and at the role-base cube level.
At the cube-level, SSAS MD and Kyvos can secure data at multiple granularities:
- Cube – Access can be granted to cubes.
- Dimension – Entire dimensions can be hidden.
- Dimension Members – Selected dimension members can be hidden.
- Cells – Selected cells can be hidden.
For SSAS MD and Kyvos, cube-level security is configured primarily through a UI. The UIs differ in approach. SSAS MD revolves more around the selection of dimensions and members. Kyvos resolves around setting up rules that are a collection of row and column selections. But the end result is roughly the same.
A key difference is that SSAS MD allows for securing cube objects with MDX expressions. This capability does allow for more flexibility. However, in my experience with SSAS, selection from a simple UI (check-box-based selection of dimensions, attributes, members) covers the vast majority of cases.
One of the most well-known cases requiring MDX expressions is “dynamic security”, where security based on the user name can be set on-the-fly at query-time.
Dimension-level securing is accomplished in Kyvos through “Column-Level” security.
The most important levels for securing dimensions are the dimension member and cell level. SSAS MD and Kyvos allow for the selection of particular members.
Cell level security for SSAS MD is specified by an MDX expression, whereas for Kyvos, it is expressed by a combination of row-level and column-level specifications.
Next Steps
In this article, we compared the objects of SSAS MD and Kyvos that make up a cube. In Part 3, we’ll compare the cube design experience. Part 3 also includes discussions on topics that are conspicuously missing in Part 1, for example:
- Storage modes of MOLAP, HOLAP, and ROLAP
- Partitions
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: