SSAS Series--"03" Multidimensional data (Cube objects)

Original: SSAS Series--"03" Multidimensional data (Cube object)

1. What is cube?

A simple Cube object consists of basic information, dimensions, and measure groups. Basic information includes the name of the cube, the default measure for the cube, the data source and storage mode, and so on. Dimensions are the actual dimension groups used in the cube. All dimensions must be defined in the dimension collection of the database before they can be referenced in the cube. A measure group is a set of measures in a cube. Measure groups are collections of measures that have common data source views and dimension sets. A measure group is a processing unit for a measure, and a measure group can be processed separately before browsing. This concept Msnd explained very clearly, it is not difficult to understand, let's copy to paste up.

2. Measures and measure groups

Measures are typically mapped to columns in the fact table. attribute columns in dimension tables can be used to define measures, but these measures are generally semi-additive or non-additive in terms of aggregation behavior.

Measures in the Diagram fact table

Simple MeasureGroup objects consist of basic information, measures, dimensions, and partitions. Basic information includes the name of the measure group, the type of measure, the storage mode , the processing mode , and so on. Measures are the actual set of measures that make up a measure group. For each measure, there is an aggregate function definition, a format attribute definition, a data item source definition, and other definitions. A dimension is a subset of the cube dimensions that are used to create a processed measure group. A partition is a collection of physical splits that have been processed by a measure group. In a cube, measures are grouped into multiple measure groups according to their underlying fact tables. Measure groups are used to correlate dimensions and measures with each other. Measure groups can also be used to measure a distinct count as its aggregation behavior. After each distinct count measure is placed in its own measure group, you can optimize the aggregation process.

3. Granularity and aggregation functions

In Oo, if the implementation of an object "motorcade" is only deep into the "car" level, rather than the "engine" "tire" level, then the former is larger than the latter granularity.
As long as you can meet the needs, the size of the bigger the better, simple and practical. The concept of granularity in the Data warehouse is the same as above.

Aggregate functions are used for library table data statistics such as SUM, COUNT, Avg. There's no need to say it here. The aggregation behavior of each measure is determined by the aggregation function associated with the measure.

4. Dimension Relationship

Refers to the attributes of the dimension and the fact table under a certain dimension granularity.

Regular dimension Relationships

star model, nothing to say:

Apply Model Relationships

Snowflake model,:

multiple-to-multi-dimension relationships

fact Dimension Relationship

A fact dimension, often referred to as a degenerate dimension, is a standard dimension that is constructed from a fact table rather than an attribute column in a dimension table. Useful dimension data is sometimes stored in fact tables to reduce duplication.

5. Calculation

Finally, a topic that I am very interested in, it should be one of the core of operation Data Warehouse. A calculation is a Multidimensional Expressions (MDX) expression or script that defines a calculated member, named set, or range assignment in a cube in SSAS. My understanding is that using calculations, you can generate new metrics based on some of the existing measures, and the new metrics will be used with the original when you browse the cube. The use of scripting commands can make computing more complex and flexible to meet business needs.

6. Key Performance Indicators

In business terminology, a key performance indicator (KPI) is a measurable measure that is used to measure business performance. A simple KPI consists of the basic information, the target, the actual value obtained, the status value, the trend value, and the folder in which the KPI is viewed. For example, a unit's sales department can use a monthly gross profit as a KPI, but the human resources department of the same unit can use the employee who loses quarterly as a KPI. This is a more important indicator, which is now understood to this extent.

7. Operation Action

The primary purpose of the operation is to provide a stored MDX statement to the client application that can be used by the client application. He is the execution command of the client defined on the server side. My understanding is similar to stored procedures in the database engine.

8. Partition partition

A partition is a container for some measure group data. A simple partitioned object consists of basic information (name, storage mode and processing mode), slice definition, aggregation design, and so on. SSAS uses partitions to manage and store data and aggregations for measure groups. Partitions are not visible to the business users of the cube. Allows the cube's source data and aggregated data to be distributed across multiple hard drives and multiple server computers. Partitioning and greatly improve query performance, load performance, and the ease of multidimensional data set maintenance. The structure of a partition must match the structure of its measure group.

Partitioned storage

MOLAP, ROLAP, HOLAP

Proactive Caching (partitioning)

Provides multiple proactive cache configuration options that you can use to maximize performance, minimize latency, and schedule processing.

Remote partition

The data for the remote partition is stored on another instance of Microsoft SQL Server analysis Services and is not stored on the instance that contains the partition definition (metadata) and its parent cube.

Writable Partitions

Say that using a partition can improve query performance, in the end, how to improve performance?

9. Perspective

A perspective is a read-only view of a cube. Perspective controls the visibility of the objects contained in the cube. You can show or hide the following objects in perspective:

Dimensions, attributes, hierarchies, measure groups, measures, key performance indicators (KPIs), calculations (calculated members, named sets, and script commands), actions.

SSAS Series--"03" Multidimensional data (Cube objects)