ssas 面试题_多维模型的SSAS面试问题

ssas 面试题

介绍 (Introduction)

SSAS interview questions is a new article for people looking for SSAS jobs.

SSAS面试问题对于正在寻找SSAS职位的人们来说是新文章。

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.

在上一篇文章中，我们讨论了SSIS采访问题 。 在这篇新文章中，我们将讨论为使用SQL Server Analysis Services进行的工作面试准备的SSAS面试问题。

There are 2 models in SSAS. Multidimensional and Tabular. In this series of SSAS interview questions, we will talk about multidimensional models.

SSAS中有2个模型。 多维和表格形式。 在这一系列的SSAS访谈问题中，我们将讨论多维模型。

工作常见问题 (Job FAQ)

SSAS开发人员专家的平均薪水是多少？ (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.

根据ziprecruiter网站，在美国，平均工资为103,859美元。

哪个城市需要更多SSAS开发人员？ (What city requires more SSAS developers?)

Redmond, WA

华盛顿州雷德蒙德

什么州需要最多的SSAS开发人员？ (What states require the most SSAS developers?)

Washington and Florida

华盛顿和佛罗里达

面试常见问题 (Interview FAQ)

有SSAS认证吗？ (Is there a certification for SSAS?)

Yes, the official Microsoft certifications are 70-466, 70-768, 70-767, 70-466, 70-467.

是的，微软官方认证是70-466 ， 70-768 ， 70-767 ， 70-466 ， 70-467 。

For Excel and SSAS, the official certification is the 70-779

对于Excel和SSAS，官方认证是70-779

For PowerBI and SSAS, the official certification is the 70-778

对于PowerBI和SSAS，官方认证是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.

您不需要全部获得认证，但是拥有一些认证可能有助于找到工作。 这些认证将帮助您增加知识。

什么是SSAS？ (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.

它是SQL Server Analysis Services。 它是由Microsoft创建的软件，该软件与SQL Server安装程序一起提供，用于创建多维数据集和多维数据。

SSAS中的表格和多维模型是什么？ (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.

安装SSAS时，有两个主要选项：多维模型（与原始版本SQL 2005和在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.

多维模型是更成熟的模型，对于新手来说更难学习。 另一方面，表格格式更简单，但它依赖于内存。 Microsoft试图在表格模型中创建功能强大的Excel。 因此，Excel用户可能会更容易学习和使用表格模型创建项目。

For more information about tabular vs multidimensional models, refer to this link:

有关表格模型和多维模型的更多信息，请参考以下链接：

• Comparing tabular and multidimensional solutions 比较表格和多维解决方案

什么是SSAS分区？ (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:

有关分区的更多信息，请参考以下链接：

• How to partition an SSAS Cube in Analysis Services Multidimensional 如何在Analysis Services多维中对SSAS多维数据集进行分区

SSAS中有哪些措施？ (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:

有关度量的更多信息，请参考以下链接：

• Measures and Measure Groups 度量和度量组

SSAS中的维度是什么？ (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:

有关尺寸的更多信息，请参考以下链接：

• Dimensions – Introduction 尺寸–简介

Another typical example of common SSAS interview questions is the following:

常见的SSAS面试问题的另一个典型示例如下：

SSAS中的多维数据集是什么？ (What is a cube in SSAS?)

One of the more common SSAS interview questions is the fundamental question of what is a “cube”.

SSAS访谈中最常见的问题之一是什么是“多维数据集”的基本问题。

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:

有关多维数据集的更多信息，请参考以下链接：

• Cubes in Multidimensional Models 多维模型中的多维数据集

MOLAP，ROLAP，HOLAP有什么区别？ (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.

在您的SSAS采访问题中，可能会询问您有关分区存储模式的信息。 他们是3。

• MOLAP is the multidimensional database. Using the cubes. This option has faster queries.
  MOLAP是多维数据库。 使用多维数据集。 此选项具有更快的查询。
• ROLAP uses the relational database directly, it is slower, but it does not require time to process the cubes.
  ROLAP直接使用关系数据库，速度较慢，但​​是不需要时间来处理多维数据集。
• HOLAP is and Hybrid model. It creates aggregations on partitions.
  HOLAP是和Hybrid模型。 它在分区上创建聚合。

For more information about MOLAP, ROLAP, HOLAP, refer to this link:

有关MOLAP，ROLAP，HOLAP的更多信息，请参考以下链接：

• Partitions – Partition Storage Modes and Processing 分区–分区存储模式和处理

您将如何处理性能问题？ (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:

在其他时候，分区的设计可能会有用。 缓存和优化查询也有帮助。 有关性能的更多信息，请参考以下链接：

• Analysis Services Performance Guide for SQL Server 2012 and 2014 SQL Server 2012和2014的Analysis Services性能指南

Note: For SSAS performance monitoring see Foglight

注意：有关SSAS性能监控的信息，请参见Foglight

SSAS中的汇总是什么？ (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:

聚合是预先计算的数据，可提高多维数据集的查询性能。 您可以使用向导创建聚合。 有关聚合的更多信息，请参考以下链接：

• Designing Aggregations (Analysis Services – Multidimensional) 设计聚集（Analysis Services –多维）

您在MDX中的技能水平是多少？ (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.

在SSAS面试问题中，您肯定会遇到MDX问题。 可以肯定的是，因为这并不容易，而且他们可能需要有经验的人。

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.

即使对于许多有经验的用户，MDX都很困难。 简单的查询就像SQL一样，差别很小，但是带有层次结构和其他要求的更复杂的查询太复杂了。

Here you have the official MDX documentation:

这里有官方的MDX文档：

• MDX Language Reference (MDX) MDX语言参考（MDX）

This video tutorials may help you a lot also:

该视频教程还可以为您提供很多帮助：

• Videos about MDX 有关MDX的视频

什么是XMLA？ (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.

在SSAS面试问题中，您可能有关于XMLA的问题。 这是一个XML扩展，用于创建多维数据集，脚本，过程多维数据集，维度等。它基于SOAP，我们使用XMLA脚本来自动化管理任务。

In the traditional relational databases, we use SQL to create, alter and drop objects and also to query data.

在传统的关系数据库中，我们使用SQL创建，更改和删除对象以及查询数据。

In SSAS, we use XMLA for DDL operations (create dimensions, cubes, etc) and MDX to query data.

在SSAS中，我们将XMLA用于DDL操作（创建尺寸，多维数据集等），并使用MDX查询数据。

For more information about XMLA, refer to this link:

有关XMLA的更多信息，请参考以下链接：

• Developing with XMLA in Analysis Services 在Analysis Services中使用XMLA开发

您知道SSAS中使用哪些任务来自动执行SSIS中的任务吗？ (Do you know which tasks are used in SSAS to automate tasks in SSIS?)

There are nice tasks in SSIS to automate SSAS tasks.

SSIS中有不错的任务可以自动执行SSAS任务。

The Analysis Services Processing task for example can help to automate processing cubes, dimensions, tabular and data mining models.

例如，Analysis Services处理任务可以帮助自动化处理多维数据集，维度，表格和数据挖掘模型。

For more information, refer to this link:

有关更多信息，请参考以下链接：

• Analysis Services Processing Task 分析服务处理任务

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:

您还可以找到Analysis Services执行DDL任务。 该任务允许执行DDL操作，例如创建，删除，处理和更改对象。 有关更多信息，请参考以下链接：

• Analysis Services Execute DDL Task Analysis Services执行DDL任务

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:

在数据流中，有一些任务要处理维和分区。 维度处理任务允许处理维度。 有关维度处理的更多信息，请参考以下链接：

• Dimension Processing Destination 维度处理目的地

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:

数据流中的另一个任务是分区处理，该分区处理用于处理多维数据集中的分区。 有关分区处理的更多信息，请参考以下链接：

• Partition Processing Destination 分区处理目的地

SSAS中的计算是什么？ (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:

计算可以是使用数学运算或分组数据的度量或维数。 计算结果存储在多维数据集中，但是值是在执行查询时计算的。 有关计算的更多信息，请参考以下链接：

• Lesson 6-1 – Defining Calculated Members 第6-1课–定义计算所得成员

什么是命名集？ (What are named sets?)

Named sets are MDX expression that returns a set of dimensions. They can be part of the cube definition.

命名集是返回一组维度的MDX表达式。 它们可以是多维数据集定义的一部分。

For more information about named sets, refer to this link:

有关命名集的更多信息，请参考以下链接：

• Lesson 6-2 – Defining Named Sets 第6-2课–定义命名集

SSAS中的KPI是什么？ (What are the KPIs in SSAS?)

KPIs are the Key Performance Indicators. They are used to measure if the goals are accomplished.

KPI是关键绩效指标。 它们用于衡量目标是否实现。

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.

KPI包含要测量的值，目标，趋势。 我们使用图形指标来检查值是否完成。

For more information about KPIs, refer to this link:

有关KPI的更多信息，请参考以下链接：

• Lesson 7: Defining Key Performance Indicators (KPIs) 第7课：定义关键绩效指标（KPI）

SSAS的观点是什么？ (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:

有关透视的更多信息，请参考以下链接：

• Lesson 9-1 – Defining and Browsing Perspectives 第9-1课–定义和浏览观点

SSAS中的动作是什么？ (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.

在SSAS上下文中，动作是可以合并到客户端应用程序中的MDX表达式。 例如，它可以是在命令提示符下执行命令的命令行。 数据集用于返回客户端应用程序中的数据集，HTML在浏览器中执行HTML脚本。

For more information about Actions, refer to this link:

有关操作的更多信息，请参考以下链接：

• Lesson 8: Defining Actions 第8课：定义动作

SSAS中的翻译是什么？ (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.

在本文中，我们学习了一些典型的SSAS面试问题。 我们学习了一些概念，自动化工具和编程语言。 我真的希望他们对您的面试有用。 欢迎在文章末尾发表您的评论。

翻译自: https://www.sqlshack.com/ssas-interview-questions-for-multidimensionals-models/

ssas 面试题