Important Concepts in Analysis Services mdx

Source: Internet
Author: User
Tags microsoft sql server 2005 month name

You can use a multi-dimensional expression (MDX) to query multi-dimensional data or create an MDX expression for a multi-dimensional dataset. However, you need to first understand the concept and terminology of Microsoft SQL Server 2005 Analysis Services (SSAs. The following section briefly introduces the basic concept and terminology of dimensional modeling. The subsequent sections explain how to apply these concepts.

For more information, see "Other Resources" on the SQL Server 2005-Analysis Services page of the Microsoft technet website. For more information about performance issues related to MDX query and computing, see the "compile a valid MDX" section in the SQL Server 2005 Analysis Services performance guide.

Terms and concepts of dimensional modeling
Microsoft SQL Server 2005 Analysis Services (SSAs) cubes are organized based on metric values, dimensions, and dimension attributes. The following table describes the terms and concepts of dimensional modeling required to use the MDX expression language.

Database dimension)
"Database dimension" is a set of dimension attributes related to a key attribute, which is related to fact data in the measurement value dimension.

Dimension attribute)
"Dimension attributes" are bound to one or more columns in the dimension table and contain members. Dimension attributes can contain the customer name, month name, and product name.

Member (member)
"Member" is the value of a dimension attribute (including a measurement value dimension. The members in the hierarchy can be leaf members, parent members, data members, or "(all)" members.

Measure)
A measurement value is a value from a fact data table, also known as "fact data ". The value of a measurement value dimension is also known as a "member ". The metric value is usually a numeric value, but it can also be a string value.

Measures dimension (Measures dimension)
A measurement value dimension is a dimension that contains all measurement values in A Multidimensional Dataset. A measurement value dimension is a special dimension. Its members are usually aggregated based on the current members of each dimension attribute (with a specified measurement value) (usually in sum or count mode.

Measurement Group)
A measurement value group is a set of correlation values in the SQL Server 2005 Analysis Services multi-dimensional dataset (usually measured values from the same fact data table ). In SQL Server 2005 analysis services, a cube can contain multiple measurement groups.

"(All)" member (all) Member)
"(All)" is the calculated value of all members in the Attribute Hierarchy or user-defined hierarchy.

Calculation member (calculated Member)
"Computing member" is a dimension member that is defined and calculated during query. You can define computing members in user queries or MDX computing scripts and store them on the server. A computing member corresponds to multiple dimension table rows that define their dimensions.

Data Member)
"Data member" is a child member associated with the parent member in the parent-child hierarchy. A data member contains the data value of its parent, rather than the aggregate value of its child.

Parent member (parent Member)
A "parent member" is a member in the parent-child hierarchy and contains its child-level aggregate values.

Leaf Member)
"Leaf member" is a level that does not contain child members.

Child Member)
A sub-member is a member in the hierarchy under the top layer.

Key Attribute)
The "Key Attribute" of the database dimension is the attribute linked to by all non-key attributes in the dimension (either directly or indirectly. Key attributes are also granularity attributes.

Granularity attribute)
A dimension attribute of A Multidimensional Dataset. It links a dimension to the fact data in the measurement value group in the measurement value dimension. If the granularity attribute and key attribute are different, the non-key attribute must be directly or indirectly linked to the granularity attribute. In a multi-dimensional dataset, the granularity attribute defines the dimension granularity.

Cube dimension)
"Multidimensional Dataset dimension" is a database dimension instance in A Multidimensional Dataset.

Attribute Hierarchy)
An Attribute Hierarchy includes the following levels of attribute member hierarchies:

The leaf level that contains all non-repeating members. Each member of the leaf level is also called a "leaf member ".

Intermediate Level (if the Attribute Hierarchy Is parent-child ).

The optional "(all)" level (isaggregatable = true) contains the aggregate values of leaf members of the Attribute Hierarchy. "(All)" members are also called "(all)" members.

By default, the attribute hierarchy is defined for each dimension attribute (attributehierarchyenabled = true ). The default Attribute Hierarchy is visible (attributehierarchyvisible = true ).

Balanced hierarchy)
A balanced hierarchy is a hierarchy with the same number of levels between top-level members and any leaf members.

Non-hierarchical structure (ragged hierarchy)
See "unbalanced hierarchy )".

Unbalanced hierarchy)
An unbalanced hierarchy is a hierarchy with different levels of numbers between the top level and the leaf level. The parent-child hierarchy is an example of an uneven hierarchy. Unbalanced hierarchies are also called "uneven hierarchies ".

Parent-Child hierarchy)
The parent-child hierarchy is a special attribute hierarchy that sets dimension attributes to the parent type. A parent-child hierarchy consists of child members and parent members. The parent-child hierarchy includes the following levels:

Contains the child level of the parent member. The child level of a parent member includes attribute members (including data members) aggregated to the parent member ).

Contains the intermediate level of the parent member.

Optional "(all)" level (isaggregatable = true), which contains the aggregate value of the parent-child hierarchy leaf member. "(all)" Level members are also called "(all) "member.

Each dimension can have only one parent-child hierarchy, which must be related to key attributes.

User-Defined hierarchy)
The "user-defined hierarchy" is a balanced hierarchy of attribute hierarchies. It aims to help users browse multi-dimensional dataset data. The user-defined hierarchy is not added to the Cube space. In some cases, the user-defined level can be hidden and displayed in unbalanced form.

Attribute relationship)
"Attribute relationship" is a one-to-multiple relationship between attributes, for example, the relationship between the state, province, autonomous region, and city dimension attributes.

Member Property)
A member attribute is a property of a feature member, such as the customer's gender or product color.

Cell)
The Unit in a cube is the space where the members of the measurement value dimension and members of each attribute hierarchy in the cube are located.

A Measurement Value Dimension member can be a leaf member (individual fact data) or an aggregate member (for example, the sales volume aggregated for a specific year ).

A Dimension member can be a leaf member, a data member, a parent member, or a (all) member.

Cube Space)
"Multi-dimensional dataset space" is the intersection of the members of the multi-dimensional dataset Attribute Hierarchy and the measurement values of the multi-dimensional dataset.

Subcube)
A subcube is a subset of multi-dimensional datasets in the filtering view of a multi-dimensional dataset. You can use the Scope statement in the MDX computing script or the nested SELECT statement in the MDX query to define a subcube.

Subcube with Subselect)
The child cube defined by the nested SELECT statement in the MDX query contains all the members that comply with the definition of the child cube. The result is as follows:

The results of "(all)" members that contain hierarchies are the same as those of each leaf member that contains hierarchies.

Including any member will include its ancestor and descendant.

Each member at a certain level in a hierarchies that contains a user-defined hierarchies will contain all the members in the user-defined hierarchies, however, it is possible to exclude members of other hierarchies that do not coexist with members of this level (for example, members of cities that do not include customers ).

Each "(all)" member in a cube always exists in a subcube created from the Cube.

Aggregate values in a subcube are directly summed.

Tuples
The "tuples" uniquely identifies a unit based on the combination of attribute members of an attribute in each Attribute Hierarchy of A Multidimensional Dataset. When defining tuples in mdx queries or expressions, you do not need to explicitly include attribute members in each attribute hierarchy. If a member in an Attribute Hierarchy is not explicitly included in a query or expression, the default member of this attribute hierarchy is an attribute member implicitly included in a tuples. Unless explicitly defined in a multidimensional dataset, the default members of each Attribute Hierarchy are "(all)" members (if "(all)" members exist ). If no "(all)" member exists in the Attribute Hierarchy, the default member is the top-level member in the Attribute Hierarchy. Unless the default measurement value is explicitly defined, the default measurement value is the first measurement value specified in the Multidimensional Dataset. For more information, see define default members and
Defamemmember (MDX ).

For example, the following tuples identify a unit in the adventure works database by explicitly defining only one member of the Measures dimension.

Copy the code (measures. [reseller sales amount])
The preceding example uniquely identifies a unit consisting of reseller sales amount members in the Measures dimension and default members in each Attribute Hierarchy of the cube. The default member is the "(all)" member of each Attribute Hierarchy except the destination currency Attribute Hierarchy. The default members of the destination currency hierarchy are US dollar members (this default member is defined in the MDX script for the adventure works cube ).

Important:
The members of the Attribute Hierarchy in the tuples are also affected by the relationship between the attributes defined in the dimension. For more information, see attribute relationships and multi-dimensional dataset spaces below.

 

The following query returns the value of the unit referenced by the specified tuples in the previous example ($80,450.596 .98 ).

Copy code select
Measures. [reseller sales amount] On Columns
From [adventure works] Note:
If you specify an axis for a set in the query (which consists of a single tuple), you must specify a set for the column axis before specifying the set for the row axis. The column axis can also be called "axis (0)" or "0 ". For more information about MDX queries, see Basic MDX queries (MDX ).

 

As shown in the preceding example, you can use tuples in the query to return the value of the units referenced by the tuples. Alternatively, you can use a tuple in an expression to explicitly reference the specified member in the tuple. You can use a query or expression to return tuples or obtain functions of tuples. Tuples can be used to reference the value of the specified unit, or to specify a member combination (when tuples are used in functions ).

The dimension of a tuples indicates the sequence or sequence of the members in the tuples. Since implicit members always appear in the same order, the dimension is usually for explicitly defining members of tuples. The sequence of member groups is very important when the tuples are defined. In the following example, A tuples on the column axis contain two members.

Copy code select
([Measures]. [reseller sales amount], [date]. [calendar year]. [CY 2004]) On Columns
From [adventure works] Note:
When explicitly specifying a member in a tuples from multiple dimensions, the entire tuple must be included in parentheses. If you only specify one member in the tuples, parentheses are optional.

 

In the previous example, the tuples in the query specify to return the cube units at the intersection of the reseller sales amount measure in the Measures dimension and the Cy 2004 Member in the calendar year Attribute Hierarchy in the date dimension.

Note:
Attribute members can be referenced by Member names or member keys. In the previous example, you can replace a reference to [CY 2004] with a reference to & [2004.

 

Sets)
A set is an ordered tuples with the same dimension. The following is an example of a set.

Copy code select
{
([Measures]. [reseller sales amount],
[Date]. [calendar year]. [CY 2003]),
([Measures]. [reseller sales amount],
[Date]. [calendar year]. [CY 2004])
} On Columns
From [adventure works] Note:
Use braces {} to specify the set of tuples.

 

In the previous example, each tuple in the SET has the same dimension, because the first member of each tuple is a member of the Measures dimension, the second member of each tuples is a member of the calendar year Attribute Hierarchy. If the second member of any of the two tuples comes from different attribute hierarchies in the date dimension (such as calendar month), you will receive an error message with different dimensions.

Tip:
You can use an alias to create a set (named set "). When processing complex MDX expressions, using a nameset makes MDX queries easier to understand or reuse. To use a nameset, use "as" after the set identifier and follow the alias after the word.

 

Multi-dimensional dataset space and automatic coexistence
Previously, we defined "multi-dimensional dataset space" as the intersection of members of its attribute hierarchy. The concept of "automatic coexistence" limits the space of this cube to actual units. Members of attribute hierarchies in a dimension may not coexist with members of other attribute hierarchies in the same dimension.

For example, if a cube has a city Attribute Hierarchy, a country Attribute Hierarchy, and an Internet sales amount metric value, the cube space only contains the co-existent members. For example, if the city Attribute Hierarchy contains the city New York, London, Paris, Tokyo, and Melbourne, and the country Attribute Hierarchy contains the country (region) united States, United Kingdom, France, Japan, and Australia, the space of this cube does not include Paris and united
Space (unit) at the intersection of States ).

When the query unit does not exist, the nonexistent unit returns NULL, that is, they cannot contain the calculation results, and you cannot define the computation that is written into this space. For example, the following statement contains a nonexistent unit.

Copy the code select [Customer]. [gender]. [gender]. Members on columns,
{[Customer]. [Customer]. [Aaron A. Allen]
, [Customer]. [Customer]. [Abigail Clark]} on rows
From [adventure works]
Where measures. [Internet sales amount] Note:
This query uses the members (SET) (MDX) function to return the member set of the Gender Attribute Hierarchy on the column axis, this set is also associated with the specified member set of the customer Attribute Hierarchy on the line axis.

 

When you run the preceding query, the unit at the intersection of Aaron A. Allen and female is empty. Similarly, the unit at the intersection of Abigail Clark and male is empty. These cells do not exist and cannot contain values, but they cannot exist in the returned results of the query.

If you use the crossjoin (MDX) function to return the cross product of the members of the Attribute Hierarchy in the same dimension attribute hierarchy, automatic coexistence only returns the actually existing tuples, instead of returning the entire Cartesian product. For example, run the following query and check its results.

Copy code select crossjoin
(
{[Customer]. [country]. [United States]},
[Customer]. [state-province]. Members
) On 0
From [adventure works]
Where measures. [Internet sales amount] Note:
Note that 0 indicates the name of the column axis, which is short for Axis (0.

 

The preceding query only returns the Unit for coexistence members of each attribute hierarchy in the query. The preceding query can also be written using the * variable in the * (crossjoin) (MDX) function.

Copy code select
[Customer]. [country]. [United States] *
[Customer]. [state-province]. Members
On 0
From [adventure works]
Where measures. [Internet sales amount]
You can also write the preceding query as follows:

Copy the code select [Customer]. [state-province]. Members
On 0
From [adventure works]
Where (measures. [Internet sales amount],
[Customer]. [country]. [United States])
Although the metadata in the result set will be different, the returned unit values will be the same. For example, in the previous query, the country hierarchy has been moved to the slice axis (in the WHERE clause), so it is not explicitly displayed in the result set.

The preceding three queries explain the impact of automatic coexistence in SQL Server 2005 Analysis Services.

User-Defined hierarchy and multi-dimensional dataset Space
In the previous example of this topic, attribute hierarchies are used to define the positions in the cube space. However, you can also use user-defined hierarchies defined by the attribute hierarchies in a dimension to define the positions in the cube space. A user-defined hierarchy is an Attribute Hierarchy designed to help users browse multi-dimensional dataset data.

For example, the crossjoin query in the previous section can also be written as follows:

Copy code select crossjoin
(
{[Customer]. [country]. [United States]},
[Customer]. [Customer geography]. [state-province]. Members
)
On 0
From [adventure works]
Where measures. [Internet sales amount]
In the previous query, the customer geography User-Defined hierarchy in the customer dimension is used to define the location in the cube space previously defined using the Attribute Hierarchy. You can use Attribute hierarchies or user-defined hierarchies to define the same position in a cube.

Attribute relationship and multi-dimensional dataset Space
Defining the attribute relationships between relevant attributes (by promoting the creation of corresponding aggregation) improves query performance and affects the members of the relevant attribute hierarchies displayed together with the attribute hierarchies. For example, if you define a tuples that contain members of the city Attribute Hierarchy and the tuples do not explicitly define the members of the Country Attribute Hierarchy, you may want the default country Attribute Hierarchy member to be a member of the country Attribute Hierarchy. However, the above expected results only appear when the attribute relationship between the city Attribute Hierarchy and the country Attribute Hierarchy is defined.

The following example returns a member that is not explicitly included in the query's relevant property hierarchy.

Copy the Code with member measures. X
Customer. Country. currentmember. Name
Select measures. X on 0,
Customer. City. members on 1
From [adventure works] Note:
Note that the With keyword is used together with the currentmember (MDX) and name (MDX) functions to create computing members used in queries. For more information, see Basic MDX query (MDX ).

 

In the previous query, the name of the country Attribute Hierarchy associated with members of the state attribute hierarchy is returned. Expected country members are displayed because the property relationship between the city and country attributes is defined ). However, if no attribute relationship is defined between attribute hierarchies in the same dimension, the "(all)" member is returned, as shown in the following query.

Copy the Code with member measures. X
Customer. Education. currentmember. Name
Select measures. X on 0,
Customer. City. members on 1
From [adventure works]
In the previous query, the "(all)" member ("All MERs") is returned because there is no relation between education and city. Therefore, the "(all)" member of the education Attribute Hierarchy will be any one that involves the city Attribute Hierarchy (Education members are not explicitly provided in it) the default members of the education Attribute Hierarchy used in the tuples.

Computing Context
Each set, Member, tuples, or numeric function is executed in the context of the entire MDX expression or statement. When a parameter (such as a tuple) is passed to a function, only several coordinates in the cube space are explicitly provided. Other coordinates are obtained based on the current calculation context. Determine the computing context of unspecified Unit coordinates and attribute members in the following order:

From clause (if applicable)-This clause specifies the entire cube or specifies the subcube in the form of a SELECT statement.

Where clause (if applicable)-This clause, also known as the "slice axis", allows you to specify a set, tuples, or members on it to restrict query of returned Members on the column or row axis. Conceptually, the default members of each Attribute Hierarchy not explicitly specified on the column axis or the row axis are part of the slice axis.
Note:
When the Unit coordinates of a specific attribute are specified on the slicer axis and other axes, the coordinates specified in the function may give priority to the members of the set on the axis. The filter (MDX) and Order (MDX) functions are examples of such functions-you can exclude the select statements in the WHERE clause or from clause from the computing context by attribute members) filter or sort the results.

 

The name set and computing member defined in the query or expression.

Specify the tuples and set on the row axis and column axis. use default members for attributes that are not displayed on the row axis, column axis, or slice axis.

Multi-dimensional or sub-dimensional datasets on each axis eliminate null tuples on the axis and apply the having clause.

For more information, see create a multi-dimensional dataset context (MDX) in the query ).

In the following query, the country attribute member and the calendar year attribute member specified in the WHERE clause limit the computing context of the row axis.

Copy the code select customer. City. City. Members on 0
From [adventure works]
Where (customer. Country. France, [date]. [calendar]. [calendar year]. [CY 2004],
Measures. [Internet sales amount])
If you specify the filter function on the axis to modify this query and use the calendar year Attribute Hierarchy member in the filter function, you can modify the attribute members in the calendar year Attribute Hierarchy to provide the computing context for the members of the set on the column axis ).

Copy code select Filter
(
Customer. City. City. Members,
([Date]. [calendar]. [calendar year]. [CY 2003],
Measures. [Internet order quantity])> 75
) On 0
From [adventure works]
Where (customer. Country. France,
[Date]. [calendar]. [calendar year]. [CY 2004],
Measures. [Internet sales amount])
In the previous query, the computing context of the unit of the tuples displayed on the axis of the filtering column by the CY 2003 Member of the calendar year Attribute Hierarchy, even if the computing context of the calendar year Attribute Hierarchy Is Cy 2004 in name. In addition, it is filtered by the Internet order quantity metric value. However, if you have set an integrator on the column axis, the where clause determines the computing context of the member values displayed on the column axis again.

Important:
To improve query performance, members and metadata should be removed as early as possible during the resolution process. In this way, the complex query time calculation for the final member set involves the least units.

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/mstop/archive/2008/10/15/3072295.aspx

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.