SSAS 教程：什么是 SSAS Cube， Archi结构与类型

什么是 SSAS？

SQL Server 分析服务 (SSAS) is a multidimensional OLAP server as well as an analytics engine that allows you to slice and dice large volumes of data. It is part of Microsoft SQL Server and helps perform analysis using various dimensions. It has two variants, multidimensional and tabular. The SSAS full form is SQL Server Analysis Services.

ArchiSSAS 结构

First, we will learn about the SSAS architecture.

The architectural view of SQL Server Analysis Services is based on a three-tier architecture, which consists of:

1. RDBMS: data from different sources like Excel, database, and text can be pulled with the help of an ETL工具 进入 RDBMS。
2. SSAS: aggregated data from the RDBMS is pushed into SSAS cubes using analysis services projects. The SSAS cubes create an analysis database, which can then be used for many purposes.
3. Client: clients can access data using dashboards, scorecards, portals, and so on.

SSAS 的历史

Now we will go through the history of SSAS:

• The MSOLAP feature was first included in SQL Server 7.0. This technology was later purchased from an Israeli company called Panorama.
• It soon became one of the most used OLAP engines because it was included as part of SQL Server.
• SSAS 经过彻底改造，发布了 MS SQL Server 2005.
• That version also offered a feature for “subcubes” with the Scope statement, which increased the functionality of SSAS cubes.
• The SSAS 2008 R2 and 2012 versions were mainly concerned with query performance and scalability.
• In Microsoft Excel 2010, an add-in called PowerPivot arrived, which uses a local instance of Analysis Services with the new xVelocity engine to increase query performance.

重要的 SSAS 术语

Now we will learn some important terminologies of SSAS:

• 数据源
• 数据源视图
• 鱼块
• 尺寸表
• 维度
• 水平
• 事实表
• 测量
• 架构

数据源

A data source is a kind of connection string. It establishes a connection between the analysis database and the RDBMS.

数据源视图

A data source view is a logical model of the database.

鱼块

A cube is a basic unit of storage. It is a collection of data that has been aggregated to allow queries to return data quickly.

莫拉普

MOLAP is made of a data cube that contains measures and dimensions, including all the members that may be in a hierarchical relationship. It is a specific set of rules that helps determine how certain cells are computed in a sparse cube and how measure values are rolled up inside those hierarchies.

尺寸表

• A dimension table contains the dimensions of a fact.
• They are joined to the fact table using a foreign key.
• 维度表是非规范化的表。
• Dimensions offer characteristics of the facts through their attributes.
• There is no set limit for a given number of dimensions.
• A dimension contains one or more hierarchical relationships.

维度

A dimension offers the context surrounding a business process event. In simple terms, it gives the who, what, and where of a fact. In the sales business process, for the fact “sales number”, dimensions would be:

• Who – customer names.
• Where – location.
• What – product name.

In other words, a dimension is a window to view the information in the facts.

水平

Each type of summary that can be retrieved from a single dimension is called a level.

事实表

A fact table is the most important table in a dimensional model. It contains measurements or facts and a 外键 to the dimension table, for example payroll operations.

测量

Each fact table contains one or more measures that should be analyzed. For example, a book sales information table can measure the profit or loss for the number of books sold.

架构

此 数据库 schema of a database system is its structure described in a formal language, supported by the database management system. The term “schema” refers to the organization of data as a blueprint of how the database is constructed.

Types of Models in SSAS

Now we will learn the types of models in SSAS.

多维数据模型

此 multi-dimensional data model consists of a data cube. It is a group of operations that allows you to query the value of cells by using cube and dimension members as coordinates. It defines rules that decide the way measure values are rolled up within hierarchies or how specific values are computed in a sparse cube.

表格建模

Tabular modeling organizes data into related tables. The tables are not designated as “dimensions” or “facts”, and development time is shorter with tabular because all related tables can serve both roles.

Tabular vs Multidimensional Model

参数	表格	多维的
内存	内存缓存	基于文件的存储
结构	结构松散	刚性结构
最大的特点	数据不需要从源头移动	Best when data is put into a star schema
Type of model	关系模型	维度模型
查询语言	DAX	MDX
复杂	简易	复杂
尺寸	较小	较大

SSAS 的主要特点

The essential features of SSAS are:

• It offers backward compatibility at the API level.
• You can use OLE DB for OLAP for the client access API and MDX as a query language.
• SSAS helps you build MOLAP, HOLAP, and ROLAP architectures.
• It allows you to work in client-server mode or offline mode.
• You can use the SSAS tool with different wizards and designers.
• 数据模型的创建和管理灵活。
• You can customize applications with extensive support.
• It offers a dynamic structure, ad-hoc reports, shared metadata, and security features.

SSAS vs PowerPivot

参数	SSAS	的PowerPivot
它是什么	SSAS 多维是“企业 BI”	Microsoft PowerPivot is “Self-Service BI”
部署	Deployed to SSAS	Deployed to SharePoint
用于	Visual Studio project	Excel
尺寸	Limited to memory	Capacity limited to 2 GB
分区支持	Supports partitioning	无分区
查询类型	DirectQuery and Vertipaq	Allows only Vertipaq queries
管理工具	Server admin tools (e.g., SSMS)	Excel and SharePoint admin
安保防护	Row-level and dynamic security	工作簿文件安全

SSAS 的优势

The benefits of SSAS are:

• Helps you avoid resource contention with the source system.
• 它是进行数值分析的理想工具。
• SSAS enables the discovery of data patterns that may not be immediately apparent, using the data mining features built into the product.
• It offers a unified and integrated view of all your business data for reporting, KPI scorecard analysis, and data mining.
• SSAS 提供来自不同数据源的数据的在线分析处理 (OLAP)。
• It allows users to analyze data with a host of tools, including SSRS 和 Excel.

Disadvantages of Using SSAS

• Once you select a path (tabular or multidimensional) you cannot migrate to the other version without starting over.
• You are not allowed to merge data between tabular and multidimensional cubes.
• Tabular can be risky if requirements change midway through the project.

Best Practices for Using SSAS

• Optimize cube and measure group design.
• Define useful aggregations.
• Use the partitions method.
• Write efficient MDX.
• Use the query engine cache efficiently.
• Scale out when you can no longer scale up.

常见问题