The structure of the cube in SSAS

The structure of the cube in SSAS

When building the cube and writing MDX in SSAS (SQL Server Analysis Services), it is easy to get confused by some nouns, such as Dimension (dimensions), Measures Dimension (metric dimensions), Measure (Metric), hierarchy (hierarchy), Attribute hierarchy (property hierarchy), Level (rank), cell (unit), member (members), member properties (Members property), Set (set), Turple (tuples), and so on. To find out these nouns, you must understand the structure of the cube.

The explanation for the above nouns is as follows: Http://msdn2.microsoft.com/en-us/library/ms144884.aspx

Cube, dimension, and measure The
Cube is like a coordinate system, where each dimension represents an axis, and to get a point, one value must be taken on each axis, and this point is the cell in the cube. See the figure below (from http://msdn2.microsoft.com/zh-cn/library/ms144884.aspx):
 

The diagram above illustrates the cube, Dimension, The relationship between the measure. Here is the note: In fact, Measure also belong to a dimension, that is, measures Dimension. All the measure constitute the measures Dimension, the dimension has only one hierarchy, and this hierarchy has only one level.

hierarchy, level, and Memeber
in the diagram in the previous section, each dimension has only one hierarchy, and in the actual environment, A dimension often has a lot of hierarchy. Therefore, in the previous section about "Cube is like a coordinate system, each dimension represents an axis" is actually not accurate enough to say that every hierarchy represents an axis, Each member of the hierarchy represents a value on the axis. The following figure shows the internal structure of the dimension, taking the time dimension as an example.


In addition, we need to note that:

1 The above illustration is the general dimension structure, in the actual model, actually can do many customization work. For example, we can modify the default member of the hierarchy.

2 in general, the default member for hierarchy in SSAS is all (in your model, you might call it a different name). In other words, in MDX [TIME]. [Fiscal] is equivalent to [time]. [Financial]. [all],[time]. [Financial]. Children is equivalent to [time]. [Financial]. [All]. Children.

3) Dimension_Name.Hierarchy_Name.Level_Name equivalent to Dimension_Name.Hierarchy_Name.Level_Name.Members. For example: [Time]. [Financial]. [Half year] is equivalent to [time]. [Financial]. [Half year]. Members. The members of the level are all elements of the levels (for [time]). [Financial]. [Half year]. members={[first half],[second half],[first half],[]}, the first two are 2001 years, the latter two belong to 2002, and the hierarchy members contain all the content of the hierarchy.

4 Dimension_name is equivalent to Dimension_name.hierarchy_name latitude when and only if there is only one hierarchy under a dimension. For example: The time dimension has only one financial hierarchy, then [time] is equivalent [TIME]. [Finance].

5 The level of the members in Attribute hierarchy is two layers (MSDN is more accurate, here is Simplified): The first layer: all, the second layer: leaf node. In other words, it is the same as the multilayer hierarchy, which is the embodiment of the unified dimension model (Unified dimensional).

Note: Using attribute hierarchy can make it easier to write MDX, but it also increases the capacity of the cube, enlarges the number of cells, and has a negative effect on performance. So, in modeling, we can put some of the attribute hierarchyattributehierarchyenabledproperty settingsinto False,At the same time, when writing MDX, it is referenced in the way of member property, which can improve performance while satisfying requirements.

6) Measures dimension is a special dimension whose members do not have all this member, and its default membership can be specified when modeling.

7 for a general dimension, the default of the first level is "(All)".

Turple and set
If the cube is like a coordinate system, then the relationship between Turple and set is like point and surface. The turple consists of a member of each hierarchy in the cube. Because there are so many hierarchy, it is not generally possible to specify all member in Turple expressions, so to simplify development, all hierarchy that do not explicitly specify member are replaced with the default member of the hierarchy. In other words: ([Time].[ Finance]. [2001]. [First half]) equivalent to ([TIME]. [Financial]. [2001]. [First half],[time]. [Calendar]. [All]). Another thing we need to be aware of:

1 Some say that: Turple is "a subset of the cube (constantly open the Sub cube), this view is inaccurate, because Turple is just a point, not a face, it is only a member of each hierarchy."

2 the expression outside () is not necessarily turple. For example: ([Time]. [Financial]. [Half year]. members,[time]. [Calendar]. [2001]. [first half]) is not a turple, but a set, because Turple is a point that consists of only one member of each hierarchy, and if there are two members on any hierarchy, then it becomes set.

Note: ([TIME]. [Financial]. [Half year]. members,[time]. [Calendar]. [2001]. [first half]) is equivalent to Crossjoin ([time]). [Financial]. [Half year]. members,[time]. [Calendar]. [2001]. [first half]) or {[Time].[ Finance]. [Half year]. members}*{[Time].[ Calendar]. [2001]. [First half]}, in the MDX of SSAS, we can define multiple expressions separated by commas in (), and the compiler will parse and, if found to be set, convert it into multiple set multiplication forms.

3 The Turple in set can be repeated. For example: {[Time].[ Calendar]. [2001]. [First half],[time]. [Calendar]. [2001]. [First half]} is not equal to {[Time].[ Calendar]. [2001]. [First half]}, because the former has two turple, the latter only one.

4 SSAS can automatically turn turple into set according to the needs of the context, a single member becomes turple, and more than one member becomes set. This is why we often confuse turple and set. The detailed examples are as follows:

A) When the context requires a set, ([Time]. [Calendar]. [2001]. [first half]) automatically converted to {[Time].[ Calendar]. [2001]. [First half]}. &NBSP
B) When the context requires turple, [Time].[ Calendar]. [2001]. [first half] automatically converted into ([time]. [Calendar]. [2001]. [first half]).
C) When the context requires a set, [time]. [Calendar]. [2001]. Children automatically into {[Time].[ Calendar]. [2001]. Children}. Summary
Overall, the internal structure of the cube in SSAS is very clear, and in actual development it is easy to use as much attention as the default transformation.