Introduction
SSAS interview questions is a new article for people looking for SSAS jobs.
In our previous article, we talked about SSIS interview questions. In this new article, we will talk about the SSAS interview questions to be prepared for an interview for a job using SQL Server Analysis Services.
There are 2 models in SSAS. Multidimensional and Tabular. In this series of SSAS interview questions, we will talk about multidimensional models.
Job FAQ
How much is the average salary for an expert in SSAS Developer?
According to the ziprecruiter web site, the average salary is 103,859 USD in USA.
What city requires more SSAS developers?
Redmond, WA
What states require the most SSAS developers?
Washington and Florida
Interview FAQ
Is there a certification for SSAS?
Yes, the official Microsoft certifications are 70-466, 70-768, 70-767, 70-466, 70-467.
For Excel and SSAS, the official certification is the 70-779
For PowerBI and SSAS, the official certification is the 70-778
You do not need to be certified in all, but to have some certification may help to get a job. These certifications will help you to increase your knowledge.
What is SSAS?
It is SQL Server Analysis Services. It is a software created by Microsoft that comes with the SQL Server installer to create cubes and multidimensional data.
What are tabular and multidimensional models in SSAS?
When you install SSAS, there are 2 main options the Multidimensional model (that did not change too much from the original version of SQL 2005 and the tabular model which started on SQL Server 2012.
The multidimensional model is a more mature model that is harder to learn for newbies. By the other hand, tabular is simpler, but it is memory-dependent. Microsoft tried to create a powerful Excel in the tabular model. So, Excel users may feel easier to learn and create projects with tabular models.
For more information about tabular vs multidimensional models, refer to this link:
What are SSAS partitions?
If your database is big, you may want to partition your data by date, by zone or other criteria of your preference.
The partitions are logical units to divide the data. That reduces the process time and the load time is divided.
For more information about partitions, refer to this link:
What are measures in SSAS?
In a multidimensional model, it is an aggregation of numeric values. It shows average values, sum, count, distinct and other functions.
For more information about Measures, refer to this link:
What is a dimension in SSAS?
It is a group of attributes that are based on columns. Some typical dimensions are the time, geography, employees, customers.
For more information about dimensions, refer to this link:
Another typical example of common SSAS interview questions is the following:
What is a cube in SSAS?
One of the more common SSAS interview questions is the fundamental question of what is a “cube”.
It is an Analytical multidimensional database composed mainly by measures and dimensions. It is used to extract multidimensional data. It is like a table in a relational database, but it has multiple dimensions.
For more information about cubes, refer to this link:
What is the difference between MOLAP, ROLAP, HOLAP?
In your SSAS interview questions, you may be asked about the partition storage modes. They are 3.
- MOLAP is the multidimensional database. Using the cubes. This option has faster queries.
- ROLAP uses the relational database directly, it is slower, but it does not require time to process the cubes.
- HOLAP is and Hybrid model. It creates aggregations on partitions.
For more information about MOLAP, ROLAP, HOLAP, refer to this link:
How would you deal with performance problems?
Most of the problems could be solved with a good design, but sometimes aggregations can help.
Other times, a design of partitions can be useful. Cache and optimizing queries can help also. For more information about performance, refer to this link:
Note: For SSAS performance monitoring see Foglight
What are aggregations in SSAS?
Aggregation is precalculated data for faster query performance in cubes. You can use the wizard to create aggregations. For more information about aggregations, refer to this link:
What is your skill level in MDX?
You will certainly have an MDX question in your SSAS interview questions. That is for sure, because it is not easy and they may require someone with experience.
MDX is hard even for many experienced users. Simple queries are like SQL with small differences, but more complex queries with hierarchies and other requirements are too complex.
Here you have the official MDX documentation:
This video tutorials may help you a lot also:
What is XMLA?
In your SSAS interview questions you may have questions about XMLA. This is an XML extension to create cubes, scripts, process cubes, dimensions, etc. It is based in SOAP and we use XMLA script to automate administration tasks.
In the traditional relational databases, we use SQL to create, alter and drop objects and also to query data.
In SSAS, we use XMLA for DDL operations (create dimensions, cubes, etc) and MDX to query data.
For more information about XMLA, refer to this link:
Do you know which tasks are used in SSAS to automate tasks in SSIS?
There are nice tasks in SSIS to automate SSAS tasks.
The Analysis Services Processing task for example can help to automate processing cubes, dimensions, tabular and data mining models.
For more information, refer to this link:
You can also find the Analysis Services Execute DDL Task. This task allows to execute DDL operations like creating, dropping, processing and altering objects. For more information, refer to this link:
In the data Flow, there are some task to process dimensions and partitions. The dimension processing task allows to process a dimension. For more information about the Dimension Processing, refer to this link:
Another task in the Data Flow is the Partition Processing which is used to process partitions in cubes. For more information about Partition Processing, refer to this link:
What are calculations in SSAS?
Calculations can be measures or dimensions using mathematical operations or grouping data. The calculations are stored in cubes, but the values are calculated when the queries are executed. For more information about calculations, refer to this link:
What are named sets?
Named sets are MDX expression that returns a set of dimensions. They can be part of the cube definition.
For more information about named sets, refer to this link:
What are the KPIs in SSAS?
KPIs are the Key Performance Indicators. They are used to measure if the goals are accomplished.
The KPI contains a value to be measured, a goal, a trend. We use indicators that are graphics to check if the values are accomplished.
For more information about KPIs, refer to this link:
What are the perspectives in SSAS?
They are like views to restrict the number of visible dimensions or measures in order to have a simpler view.
This is useful if the Cube is too complex and has many views. You can create perspectives for different regions, professional areas, job positions.
For more information about perspectives, refer to this link:
What are actions in SSAS?
An action, in the context of SSAS, is an MDX expression that can be incorporated into the client application. For example, it can be the command line to execute commands in the command prompt. Dataset is used to return the dataset in the client application, HTML executes an HTML script in the browser.
For more information about Actions, refer to this link:
What are translations in SSAS?
Translations allows you to handle the same cube, but handle several languages. You can select the language of your preference. This is useful for international companies which branches in different countries with different languages.
For more information about translations, refer to this link:
Conclusions
In this article, we learned some typical SSAS interview questions. We learned some concepts, tools to automate, programming languages. I really hope them to be useful in your job interview. Feel free to write your comments at the end of the article.
He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.
He also helps with translating SQLShack articles to Spanish
View all posts by Daniel Calbimonte
- PostgreSQL tutorial to create a user - November 12, 2023
- PostgreSQL Tutorial for beginners - April 6, 2023
- PSQL stored procedures overview and examples - February 14, 2023