By: Dattatrey Sindol | Comments (2) | Related: More > Professional Development Interview Questions BI
Problem
Aggregation is an important aspect of any OLAP/SSAS solution and is a major differentiating factor between OLTP and OLAP solutions. The ultimate goal of building SSAS solutions is to make the right data available to the business users in the intended format in an easy to understand, convenient, and secure way. This is where Translations, Perspectives, and Security comes into the picture. Hence it is important to understand these aspects of SQL Server Analysis Services before preparing for an interview. Check out this tip for SSAS interview questions.
Solution
In the previous tips in this series, I have covered questions on basic concepts such as Data Sources, Dimensions, Measures, Actions, and Storage. In the fifth tip of this series, I will be covering some of the questions on aggregations, translations, perspectives, security, etc.
What are aggregates? What is the purpose of defining an aggregation design in Analysis Services?
Aggregates are summarized values, each of which corresponds to a combination of an attribute from each dimension and a measure group. An aggregate in SSAS is the differentiating factor between OLAP and OLTP, and is the fundamental principle of SSAS/OLAP, which offers blazing fast performance.
In theory, many people believe that SSAS stores aggregated values for every combination of each attribute from each dimension and in each measure group. However, in reality, SSAS stores only a part of all combinations and not all the possible combinations. In some scenarios it might be helpful to create certain percentage of aggregations every time the cube is processed, without leaving the decision to SSAS. This is achieved by defining aggregation design.
The Aggregation Design Wizard is used to design aggregations in SSAS and it provides the following options as part of the aggregation design process:
- Design aggregations until estimated storage reaches "X" MB.
- Design aggregations until performance gain reaches "X" percentage.
- Design aggregations until the person designing the aggregations, clicks the "Stop" button.
- Do not design aggregations at all (0% aggregation).
What is Usage Based Optimization in SSAS? How is it performed?
Usage Based Optimization is the process of defining aggregation design based on the actual queries executed by the users/applications accessing the cube, which is captured using a log.
SSAS offers a wizard called Usage Based Optimization Wizard to perform Usage Based Optimization by defining aggregation design. The Usage Based Optimization Wizard provides the following options as part of the Aggregation Design Process:
- Design Aggregations until estimated storage reaches "X" MB.
- Design Aggregations until performance gain reaches "X" percentage.
- Design Aggregations until the person, designing the aggregations, clicks the "Stop" button.
As we can see, the above options offered by the Usage Based Optimization Wizard are similar to the ones offered by the Aggregation Wizard. However, in the case of the Aggregation Wizard, all possible queries are given equal importance, whereas in the case of the Usage Based Optimization Wizard, the importance is based on the actual queries being fired.
To use the Usage Based Optimization Wizard, Query Log Properties need to be configured at the SSAS Server Level for the tool to be able to log the queries fired by users for accessing the cube.
What are the different aggregation functions available in SSAS? Can you explain each one of them?
SSAS supports the following types of aggregation functions for Measures:
- Fully Additive
- Sum: This is the default aggregation type and calculates the sum of all the child members of a member at any level in the hierarchy, except for leaf level.
- Count: Calculates the count of all the child members of a member at any level in the hierarchy except for the leaf level.
- Semi Additive
- Min: Returns the lowest value among all the child members.
- Max: Returns the highest value among all the child members.
- FirstChild: Returns the value of the first child of a member.
- LastChild: Returns the value of the last child of a member.
- FirstNonEmpty: Returns the value of the first non-empty child of a member.
- LastNonEmpty: Returns the value of the last non-empty child of a member.
- ByAccount: This aggregation type is applicable only if there is an Account Dimension in the cube. A Dimension is treated as an Account Dimension by SSAS, only when the Type property of the dimension is set to "Account". This function aggregates the data based on the aggregation function set for members of an Account Dimension. If an Account Dimension does not exist, then setting this aggregation type is equivalent to No Aggregation (None).
- AverageOfChildren: Calculates the average of values of all the non-empty child members of a member at any level of a hierarchy, except at the leaf level.
- Non Additive
- None: When an aggregation function is set to None, no aggregation is performed and the value of the member (both leaf and non-leaf) is returned directly.
- DistinctCount: Returns the count of unique/distinct child member of a member at any level of a hierarchy, except for leaf level.
What is Time Intelligence? How is it implemented in SSAS?
Time Intelligence is a technique, which allows us to align the Time Dimension in SSAS with our actual Calendar, thereby making time calculations like Period to Date, Comparison across Parallel Time Periods, Cumulative aggregates, etc. very simple without the need for us to write explicit MDX queries/expressions.
For implementing Time Intelligence, the Type property of the Time Dimension should be explicitly set to "Time" and the attributes of this dimension should be set appropriately like year, month, etc.
SSAS offers a wizard called Business Intelligence Wizard which allows us to add different types of Intelligence to SSAS like Time Intelligence, Account, Intelligence, and Dimension Intelligence etc.
What are translations? What is its significance in SSAS?
Translations in SSAS allow us to bind labels/properties of those objects in SSAS which can be represented in multiple languages. In simple terms, Translations allow us to display the Labels and Captions of various SSAS objects in different languages. In SSAS both metadata and data can be translated. The objects which support Translations in SSAS include databases, cubes, dimensions, attributes, hierarchies, measure groups, calculated members, KPIs, and various other objects.
In today's world, business are growing and expanding to a very large extent and tend to have presence internationally. In such situations, it would be essential that, SSAS objects support localization and people from different geographical locations be able to see the information in their local language.
What are perspectives? How they can be used to implement security in SSAS?
A perspective is a visual layer on top of SSAS and is used to display a subset of the cube/dimension based on either a specific subject area or based on the target audience, or any other scenario which might require exposing a subset of cube/dimension to the users/applications to simplify the view of data.
Perspectives are a very useful feature especially when the cubes are too large in size containing many dimensions and measure groups. Such large cubes can be too much data for the users, if the data is not exposed using suitable perspectives.
Perspectives are not a security mechanism. They cannot be used to implement security in SSAS. The security settings/restrictions applied at the cube/dimension/data level apply at the perspective level and no separate security settings can be defined at the perspective level.
What are the different levels where security can be implemented in SSAS?
Security is one of the most common and most important needs for any application especially when working with sensitive data. In SSAS, security can be implemented at various levels:
- Server Level: A user is either allowed or denied access to the OLAP Server.
- Database Level: A user is either allowed or denied access to specific databases on the SSAS Server.
- Cube Level: Users can be allowed or denied access to specific cubes within a database especially when a database contains multiple cubes.
- Dimension Level: Users are either allowed or denied access to specific dimensions and thereby enabling or restricting them from browsing the data using those specific dimensions.
- Measure Level: Either one or more measures can be exposed or restricted from the user.
- Cell Level: This can be of two types: Security for dimension members and security for data cells. With dimension members, users can be allowed or restricted to see specific members of a dimension. With data cells, users can be allowed or restricted to see a specific range of cells. In both of these scenarios, users can be given read, read/write, etc. permissions.
What is writeback functionality in SSAS? In what scenarios is it useful?
Writeback is a functionality in SSAS which allows the users to write the data back into the cube. Meaning, while browsing the cube, they can make changes to the data and those changes are written back into the cube.
Writeback can be enabled primarily at two levels, either at the dimension and/or partition. For dimension level writeback, the users can modify the members of a dimension. For partition writeback, users are allowed to modify the cells of data and hence it is commonly referred to as cell level writeback.
Users require special permissions to be able to use the writeback functionality to write the data back into the cube. For dimension writeback, users should have the read/write permissions and the "enable dimension writeback" property should be set. In the case of cell/partition level writeback users need to have read/write permissions and the partition should be enabled for writeback.
Writeback functionality can be useful in many scenarios such as to perform What-If analysis. Say a business user wants to understand how much impact a change in freight and other taxes can have on profitability, which they can simply achieve by trying to set a different set of values for freight and taxes then see the profitability changing.
Next Steps
- Check out the other tips in this series with questions on:
- Check out the SQL Server Analysis Services tutorial
- Check out the following additional resources:
- SSAS - Best Practices and Performance Optimization: Part1 | Part2 | Part3 | Part4
- Developer Interview Questions
- DBA Interview Questions
- Check out my previous tips
About the author
Dattatrey Sindol has 8+ years of experience working with SQL Server BI, Power BI, Microsoft Azure, Azure HDInsight and more.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
