In this article, I’m going to list out some of the most common warnings we encounter while developing SSAS Cubes and the reason for those warnings. As you are aware, SSAS cubes are developed using the SQL Server Data Tools (SSDT) for Analysis Services (Visual Studio), all the examples will be taken from Visual Studio 2017 only.
The warnings that I’m going to explain in this article are listed as follows:
- Define attribute relationships as ‘Rigid’ where appropriate
- Create hierarchies in non-parent child dimensions
- Avoid visible attribute hierarchies for attributes used as levels in user-defined hierarchies
- The database has no Time dimension. Consider creating one
- Do not ignore duplicate key errors. Change the KeyDuplicate property of the error configuration so that it is not set to IgnoreError
- Attribute relationships do not exist between one or more levels of this hierarchy. This may result in decreased query performance
Warning 1: Define attribute relationships as ‘Rigid’ where appropriate
This is one of the very common warnings that we encounter while building specific dimensions in the SSAS cubes. While developing dimensions we must define proper attribute relationships between the members of the dimension. For example, how does a city relate to a state and how a state relates to a country and so on. Another example would be the relations between the product, how it relates to the product category and finally to the product line. These are specific business requirements and need to be implemented accordingly.
The warning in SSAS is when the designer thinks that the values in the members are fixed or might change over a period. For instance, if we take a date dimension, we know that the date “01-Jan-2020” will always relate to the month “January” and year “2020” and it will not change ever. Something similar can also be considered for geographic dimensions. For example, the city “Paris” will always relate to the country “France” which in turn will relate to the continent “Europe”. These relationships are rigid and should be defined explicitly while designing the SSAS cubes. On the other hand, if we consider an Employee dimension, it is possible that the employees might change their department at some point. Such relationships must be defined as flexible.
The logic behind defining the relationship as flexible is that the SSAS engine is forced to drop and re-compute all the exiting aggregations for that dimension. However, this is not the case when the dimensions are defined as rigid. This helps in reducing the processing time of the SSAS cubes if proper relationship attributes are defined.
Figure 1 – Attribute Relationship Type
Warning 2: Create hierarchies in non-parent child dimensions
This is one of the simplest warnings that appear in SSAS cubes. When the SSAS engine processes the data, it automatically detects the underlying relationships between the various members of the dimension. It also considers the cardinality between two or more-dimension members of the same dimension. If the engine finds a one-to-many relationship in the underlying data, then it automatically suggests defining a user-based hierarchy for those members in the dimension.
For example, if you consider the Employee dimension, you can take the relationship between the “Employee” and “Department” members. If we consider the relational data structure, there is often a one-to-many relation between the department and the employees such that one department can relate to many employees. In such cases, when we design the SSAS cubes, we should also define a user-defined hierarchy that will include the “Department” as the higher member of the hierarchy and “Employee” being at the lower level.
Figure 2 – User Defined Hierarchies
Warning 3: Avoid visible attribute hierarchies for attributes used as levels in user-defined hierarchies
This is from a best practice perspective, that we should try to hide dimension members from the view list of any dimension if that member is used in some hierarchies. What is meant by this warning is if you have a dimension member which is also used in a hierarchy, then you’d like the user to use the hierarchy instead of using the individual member.
For example, let us take the date dimension. You might have members for “Year”, “Quarter”, “Month” and “Days” in the dimension along with other members as well. Since all these are individual members, you might consider creating a Calendar hierarchy that will include all these four members. Since now you have the hierarchy defined, it is not required for the user to report on the individual members. Hiding these individual members will also allow for some room for the other dimensions to be visible in the list. This might be a very simple tip but affects the performance while processing larger cubes.
Warning 4: The database has no Time dimension. Consider creating one
This warning is rather a very descriptive one. Clearly, it mentions that we do not have any time or date dimension defined for the SSAS cube. As it is highly likely, that whenever we create a multi-dimensional cube, we also tend to analyze the metrics across a time dimension as well. If the time dimension isn’t built for the cube, the users will not be able to use the pre-defined time intelligence functions provided by the SSAS engine.
You might consider ignoring this error based on the requirements, however, it is highly recommended to have a time dimension defined whenever you design a cube in SSAS.
Warning 5: Do not ignore duplicate key errors. Change the KeyDuplicate property of the error configuration so that it is not set to IgnoreError
This is another important warning that we should never ignore while designing the dimensions in SSAS cubes. It is displayed when the developer or the user developing the cube has turned off the error configuration for duplicate keys in the dimension member attributes which also happens to be default error configuration. Often, we might come across dimensions and hierarchies where one member attribute can be linked to other members of that dimension.
To explain this better, let us take the example of the date dimension again. If we consider the attributes of the Month member, we can find that the attributes “January”, “February”,…,”December” repeats for all the year attributes as well. So, when the key value is defined for the member “Month” as the month value or the month number, the SSAS engine encounters duplicate values for the same attributes. The way to resolve this error or warning is to define proper key members for the attributes. For this, we must define the Key Property as “Year-Month” and set the Name Property as “Month”. Doing this the SSAS cubes will consider each month attribute as unique as they appear only once for each year.
Figure 3 – Dimension Error Configuration
Warning 6: Attribute relationships do not exist between one or more levels of this hierarchy. This may result in decreased query performance
Attribute relations in SSAS cubes are one of the most important steps that need to be handled carefully while designing the SSAS cubes. These relationships define how the data is stored in the multi-dimensional structure and how the querying engine should select data from the underlying data structure. As this warning suggests that there are no attribute relationships defined, you must go ahead a create the proper relationships among the members for that dimension.
Figure 4 – Attribute Relationships
Conclusion
In this article, I have explained the various warnings that we encounter while developing SSAS cubes in Visual Studio. These warnings are displayed based on the design of the cube and it is essential to understand the meanings of these warnings so that any potential decrease in performance can be mended. Although all the warnings in SSAS cubes cannot be removed, it should be taken into consideration while building the project.
He is a prolific author, with over 100 articles published on various technical blogs, including his own blog, and a frequent contributor to different technical forums.
In his leisure time, he enjoys amateur photography mostly street imagery and still life. Some glimpses of his work can be found on Instagram. You can also find him on LinkedIn
View all posts by Aveek Das
- Getting started with PostgreSQL on Docker - August 12, 2022
- Getting started with Spatial Data in PostgreSQL - January 13, 2022
- An overview of Power BI Incremental Refresh - December 6, 2021