OLAP MDX Learning Notes

MDX is mainly used in OLAP query language, full name: Multi-dimensional Expression. Syntax is similar to the Structured Query language SQL for OLTP, but there is a big difference. Look at the Sql-server online Help, combined with the company's existing OLAP implementation, a simple study.

1: The Concept: Hierarchical structure
For a few other concepts: dimensions, levels, members, attributes are better understood, only the concept of hierarchy, the beginning of a little fuzzy.
In fact, hierarchies are between dimensions and levels, reflecting multiple level scenarios that a single dimension can evolve. For example, product dimensions, it can have large classification >> detail classification >> product name One level scenario, there can also be a classification >> product name another level of program. Both of these schemes constitute two levels of hierarchy.
In the application, generally only a level of solution, such as our company's OLAP implementation products provided by the model definition, does not reflect the concept of hierarchy, that is, the system provides only

A hierarchical structure. Think about one dimension in an analysis of a topic in OLAP, and there is usually only one intrinsic level scenario.

2: Grammar:
The most basic syntax: Select Axis 1, Axis 2, ... from cube model name where slice
As you can see, very SQL-like syntax: Select column 1, column 2 ... from table 1, table 2 ... where condition clause.
First, you need to understand the concept of axes:
Axis: Equivalent to the column in SQL, imagine the axis, need a bit of stereo concept, or, we simplify the problem, for the flat table display, there will be only two axes, that is, column axis and row axis. Most of the existing OLAP display, but also only reflect the two axes, our company's products are no exception. To be specific, an axis corresponds to a set.
Set: formed by tuples,
Tuples: You can include members from multiple dimensions, or you can include multiple members from the same dimension.
Members: Items in a dimension where data occurs once or more times.

And then look at the cube model: This is well understood, which is the model scenario that is formed when the theme is analyzed, and MDX only supports a single model.

Finally, take a look at the slices: this is the same as filtering in SQL. However, because the OLAP data is three-dimensional, multidimensional, to become a plane, you need to cut ah, cut ah, some do not need to show the dimension set fixed value (that is, cut). Note: When you do not specify any slices, the general implementation is to make the default member value of the dimension that does not appear as a slice value.

3: Calculate Member Columns
A calculated member column is typically generated with a member as ' as ', and then referenced in a select.
An expression in as, you can use an MDX function.

4:mdx function
Many functions, in addition to the common values, logic, string processing, unique is: dimension/level/level functions, member functions, aggregate functions.
Some functions are highly business-like bottomsum: Take out several rows at the bottom of the specified and value. This type of function needs to be realized in practical applications.

5:solve_order
This is used to specify the calculation pass order in the calculated member, because there is a problem of calculating the pass and order between the calculated members.

6: Member properties
attribute as an extension, that is to say, some columns are added as dimensions. It is generally determined when customizing the cube model. In our implementation, attributes are also used as a configuration scenario, such as the Caption property used as the display value for the dimension, and the Name property as the identity value. This depends on how the front end display is interpreted.

   7: Cell Properties
   The main completion of cell style settings. Styles include: Font, string format, foreground, background ...
   Cell properties are more often just the views that are displayed, so they can be handled at the front end, such as the property settings of the grid itself. For example, in our existing implementation, we also provide a set of conditions that can be distinguished by conditional determination to meet specific display requirements.