1.
It is not an error to put the same member on both sides of the colon; you will just get a range of one member.
在冒号（：）两边放置同一个成员不会出错，得到的仅仅是一个成员。
2.
In Analysis Services, regardless of whether the member on the left of the colon is before or after the member on the right in terms of database ordering, the set that results will have all members between the two in database ordering. That is, the following two return exactly the same set:
{[Time].[April-2001] : [Time].[January-2001]}
{[Time].[January-2001] : [Time].[April-2001]}
在AS中，无需考虑冒号两边的成员的在Database中的顺序，只需考虑冒号两边的成员在同一个level，返回的这两个Member之间包括这两个Member的集合。
3.
In Analysis Services, hierarchies behave like dimensions, and multiple
dimensions have different sets of members. As a result, in an Analysis Services
dimension that contains multiple hierarchies, you cannot simply request
[Dimension].Members. If you do, it will complain of an unknown dimension.
For example, given a logical [Time] dimension that contains two hierarchies,
[Time].[Fiscal] and [Time].[Calendar], a client taking metadata from
OLE DB for OLAP will see one time dimension. However, the expression
[Time].Members will result in an error rather than result in all members on
all time hierarchies. To obtain a set of members, the client must request either
[Time].[Fiscal].Members or [Time].[Calendar].Members. If the
dimension has only one hierarchy and that hierarchy does not have an explicit
name, then [Dimension].Members will work. For example, if time has only
one hierarchy, then [Time].Members will work.
4.
SELECT
{ [Product].[Tools], [Product].[Toys] } ON COLUMNS,
Descendants (
[Time].[2005],
[Time].[Month],
SELF
)
ON ROWS
FROM Sales
WHERE [Measures].[Dollar Sales]
SELECT
{ [Product].[Tools], [Product].[Toys] } ON COLUMNS,
Descendants (
[Time].[2005],
[Time].[Month],
SELF_AND_BEFORE
)
ON ROWS
FROM Sales
WHERE [Measures].[Dollar Sales]
5.
You can always put a single member within parentheses, but it’s not
required if the tuple is defined by just that member. However, the following is
not valid because it has two time members in it:
([Customer].[
6.
The “dimensionality” of a tuple refers to the set of dimensions whose members
compose it. The order in which dimensions appear in a tuple is an important
part of a tuple’s dimensionality. Any and all dimensions can be part of a
tuple, including members of the measures dimension.
7.
Depending on the context in which a set is
used, it either refers to that set of tuples or to the value(s) in the cell(s) that its
tuples specify
8.
Although a single member is by default a tuple of one dimension, a set that
has only one tuple is not equivalent to a tuple. As far as standard MDX is concerned,
the following two examples are quite different:
([Time].[2001 Week 1], [Product].[HyperGizmos])
{ ([Time].[2001 Week 1], [Product].[HyperGizmos]) }
The first of these is a tuple, and the second is a set containing that tuple. You
might think it reasonable that wherever a set is called for, you can use a single tuple and it will be interpreted as a set of one. Analysis Services 2005 can make use of tuples or members in some contexts that otherwise ask for a set. For other servers, you will need to wrap the tuple in curly braces as in the second sample just given. Hence, the following is a valid query only for Analysis Services 2005 (it needs braces around the tuple to be valid otherwise):
SELECT
([Time].[Jun, 2005], [Geography].[
FROM [Sales]
WHERE ([Measures].[Dollar Costs])
The following is valid across all MDX providers:
SELECT
{ ([Time].[Jun, 2005], [Geography].[
FROM [Sales]
WHERE ([Measures].[Dollar Costs])
9.
Every tuple in a set must have the same dimensionality (that is, the dimensions
represented and their order within each tuple). The following would
result in an error, because the order of the dimensions changes:
{ ( [Time].[2005], [Measures].[Dollar Sales] ),
( [Measures].[Unit Sales], [Time].[Feb, 2005] )
}
10.
The “dimensionality” of a tuple refers to the set of dimensions whose members
compose it. The order in which dimensions appear in a tuple is an important
part of a tuple’s dimensionality. Any and all dimensions can be part of a
tuple, including members of the measures dimension.
11.
You can specify any additional
dimensions’ members that you need to in either the Boolean condition or in
the set. For example, if you wanted to filter based on
Rouge, you would simply say:
Filter (
[Product].[Product Category].Members,
([Measures].[Dollar Sales], [Time].[2000],
[Store].[
)
Within the filtering operation, the cell value will be taken from the 2000
12.
SELECT { Descendants ([Organization].[All Organization],
[Organization].[Junior Staff], SELF_AND_ABOVE }
PROPERTIES
[Organization].[Executive Suites].[Manager],
[Organization].[Middle Managers].[Manager],
[Organization].[Junior Staff].[Manager] on columns
...
In this case, the property for each level at each level’s member will arrive
appropriately filled in (and be empty at members of the other levels). However,
when you access properties in member calculations, there won’t be any ambiguity.
Suppose, for example, that some calculated member referred to (in Microsoft
syntax) [Organization] .CurrentMember.Properties (“Manager”).
(Appendix Aprovides a detailed reference to this function, and we also use it in
the “Using Member Properties in MDX Expressions” section of Chapter 3.) The
lookup of this value is done on a cell-by-cell basis, and at each cell the particular
manager is unambiguous (though the level of manager to which it refers may
change). For this case, you can easily and simply reference member properties on
multiple levels that share the same name.
13.
If a query specifies no cell properties, then
three properties are returned by default: an ordinal number that represents the
index of the cell in the result set, the raw value for the cell in whatever data
type is appropriate, and the formatted textual value for the cell. If the query
specifies particular cell properties, then only those are returned to the client.
14.
[Measures].[Profit]
AS ‘[Measures].[Sale Amount] - [Measures].[Total Cost]’,
SOLVE_ORDER = 0
[Scenario].[Amount of Variance]
AS ‘[Scenario].[Actual] - [Scenario].[Planned]’,
SOLVE_ORDER = 1
[Measures].[Percentage Margin]
AS ‘[Measures].[Profit] / [Measures].[Sale Amount]’,
SOLVE_ORDER = 2
[Scenario].[Percentage Variance]
AS ‘[Scenario].[Amount of Variance] / [Scenario].[Planned]’,
SOLVE_ORDER = 3
15.
CREATE MEMBER [Sales Cube].[Scenario].[Amount of Variance] AS
‘[Scenario].[Actual] - [Scenario].[Planned]’, SOLVE_ORDER = 1
This calculated member will only be visible to queries on the [Sales
Cube] cube. Queries to other cubes, even if they also use the scenario dimension,
will not be able to see this calculated member, just like they cannot see
any other information about another cube. The CREATE MEMBER statement
defines a calculated member for a dimension that can be used by any query
(until the member is dropped or the client exits), and additionally in Analysis
Services will exist in the dimension’s metadata visible through OLE DB for
OLAP. (In Analysis Services, this metadata will only be visible on that client;
that metadata will not be visible at the server or at any other client attached to
that server.)
16.
META
DATA REFERENCING AND .CURRENTMEMBER
Many of the metadata reference functions take as an argument a member that
is the starting point for the reference. For example, the .Parent function takes
a member and returns the parent member of the starting member. All of these
functions can take a particular member as the input member. They can also take
the “current” member, which is identified by a function like .CurrentMember.
(We won’t cover the full detail about how the “current” member becomes
current here, but intuitively it’s set to the appropriate member for each cell in
which the calculation is invoked.) Metadata references that involve
.CurrentMember are probably the single most important thing (out of a large
number of important things) to master to become an expert in MDX.
17.
Ratio to ancestor:
[Measures].[Payroll Total] /
([Measures].[Payroll Total],
Ancestor ([Geography].CurrentMember, [Geography].[State]))
Ratio to [All] (in Analysis Services):
[Measures].[Payroll Total] /
([Measures].[Payroll Total], [Geography].[All Geography])
18.
WITH MEMBER [Measures].[State Payroll Proportion] AS
‘[Measures].[Payroll Total] / ([Measures].[Payroll Total],
Ancestor ([Geography].CurrentMember, [Geography].[State]))’,
SOLVE_ORDER = 5, FORMAT_STRING = ‘0%’
SELECT
{ [Measures].[Payroll Total], [Measures].[State Payroll Proportion] } on
columns,
{ Descendants ([Geography].[State].[TX], [Geography].[Store],
SELF_AND_BEFORE),
Descendants ([Geography].[State].[LA], [Geography].[Store],
SELF_AND_BEFORE)
} on rows
FROM [Overhead]
WHERE [Time].[Year].[2004]
This calculation could also be put into a calculated member defined at the
server, in which case the WITH MEMBER section of the query would be unnecessary.
Note that the calculated member uses a format string property that
causes the cells to be shown as percentages.
19.
Handling Division by Zero
One issue that almost always pops up when taking ratios is how to handle
division by zero. Different OLAP providers return different results for division
by zero. The typical approach is to compare the denominator to zero and
return NULL, if it is. For example, the preceding Payroll Proportion calculation
would be rephrased as the following to handle zeros:
‘iif (
([Measures].[Payroll Total],
Ancestor ([Geography].CurrentMember, [Geography].[State])) = 0,
NULL,
[Measures].[Payroll Total] / ([Measures].[Payroll Total],
Ancestor ([Geography].CurrentMember, [Geography].[State]))
)’
20.
Suppose that you need to allocate an advertising
budget to arrive at a calculated budget per store, given your anticipated sales
aggregated up a geography dimension. The following calculation would perform
the allocation of advertising budget based on sales:
([Measures].[Advertising], [Geography].[All Geography]) *
[Measures].[Sales] / ([Measures].[Sales], [All Geography])
At the [All Geography] member, the allocated advertising budget should
be equal to the total advertising budget.
杭州三汇数字信息技术有限公司
杭州市文二路385＃汽轮大厦7楼，310012
