MDAC 2.5 SDK - OLE DB Programmer's Reference
Chapter 24: Multidimensional Expressions

Set Value Expressions

For any expression examples in the following sections, SampleSet is defined as

{USA, Buffalo, France, NYC, London, California, LA, Nice, UK, Paris}

with sales data as indicated in the following table.

<dimension>.MEMBERS

The set of all members in <dimension>.

For example,

Geography.MEMBERS

is the set of all members in the Geography dimension.

<hierarchy>.MEMBERS

The set of all members in <hierarchy>.

For example,

Time.Quarterly.MEMBERS

is the set of all members in the Quarters hierarchy of the Time dimension.

<level>.MEMBERS

All members at a given level in a dimension.

<member>.CHILDREN

All children of <member>.

CROSSJOIN(<set1>, <set2>)

Generates the cross-product of the input sets. The order of tuples in the resulting set is as follows:

If <set1> = {x1, x2,…,xn} and <set2> = {y1, y2, …, yn}, then CROSSJOIN(<set1>, <set2>) is

{(x1, y1), (x1, y2),...,(x1, yn), (x2, y1), (x2, y2),..., 
(x2, yn),..., (xn, y1), (xn, y2),..., (xn, yn)}

DESCENDANTS(<member>, <level> [, <desc_flags>])

All descendants of <member> at <level>. By default, only members at the specified level will be included. This corresponds to a desc_flag value of SELF. By changing the value of desc_flag, the consumer can include or exclude descendants at <level>, the children before <level>, and the children after <level> (until the leaf).

For example,

DESCENDANTS(USA, Counties)

yields all counties in USA, and is the same as

DESCENDANTS(USA, Counties, SELF)

The statement

DESCENDANTS(USA, Counties, BEFORE)

yields all states in USA. If another level exists between Countries and States, members from that level will also be returned.

And the statement

DESCENDANTS(USA, Counties, AFTER)

yields all cities in USA. If another level exists after Cities, members from that level will also be returned.

The statement

DESCENDANTS(USA, Counties, BEFORE_AND_AFTER

yields all states and cities in USA.

DISTINCT(<set>)

Deletes duplicate tuples from a set. Duplicates are deleted from the tail.

EXCEPT(<set1>, <set2> [,[ALL]])

Finds the difference between two sets. Duplicates are eliminated from both sets prior to finding the difference. The optional ALL flag retains duplicates. Matching duplicates in <set1> are eliminated, and nonmatching duplicates are retained.

EXTRACT(<set>, <dimension>[, <dimension>...])

This is the opposite of the CROSSJOIN function. The resulting set consists of tuples from the extracted <dimension> elements. This function always removes the duplicates.

FILTER(<set>, <search_condition>)

Filters set based on <search_condition>.

For the expression

FILTER(SampleSet, (Sales,[1996]) < (Sales, [1995]))

the following result is returned:

{UK, Paris, Buffalo}

GENERATE(<set1>, <set2> [,[ALL]])

This is best explained with an example, as follows:

GENERATE({USA, France}, DESCENDANTS(Geography.CURRENTMEMBER, Cities))

The GENERATE function will repeatedly go through for each member of the set {USA, France} and apply the expression DESCENDANTS(Geography.CURRENTMEMBER, Cities). Each such application results in a set. (Application to USA will generate the set of all cities in USA; application to France will generate all cities in France.) These sets are joined by union to get the result of this function. In this example, all cities in USA and France will be the result. In general, GENERATE(<set1>, <set_expression>) will apply the set expression to each member of <set1> and join the results by union.

If <set1> is not related to <set_expression> by means of CURRENTMEMBER, GENERATE results in a simple replication of the set implied by <set_expression>, with as many replications as there are tuples in <set1>. If the optional ALL flag is specified, all duplicates are retained in the result. If ALL is not specified, duplicates are removed. For example, the statement

GENERATE({USA, FRANCE}, {SEATTLE, BOSTON}, [ALL])

will result in

{SEATTLE, BOSTON, SEATTLE, BOSTON}

However, if ALL was not specified, the result is

{SEATTLE, BOSTON}

HIERARCHIZE(<set>)

Hierarchizes the set. Members in a level are sorted in their natural order, the default ordering of the members along a dimension when no other sort conditions are specified. HIERARCHIZE will always retain duplicates. The statement

HIERARCHIZE(SampleSet)

returns the set in natural order. The hierarchized dataset follows (assuming that the natural order for the data store is alphabetical).

Note   Even when none of the ancestors of a member are present in the input set, the sorting is done as if the appropriate ancestor is present. For example, Buffalo and NYC appear after Los Angeles because California (the parent of Los Angeles) sorts before New York (the parent of Buffalo and NYC).

INTERSECT(<set1>, <set2> [,[ALL]])

Finds the intersection of two input sets. By default, duplicates are eliminated from both sets prior to intersection. The optional ALL retains duplicates. Because there are several ways for ALL to work, it is worth mentioning the algorithm: Nonduplicated elements are intersected as usual. For each duplicate in <set1>, match it with a duplicate in <set2>, if one exists, and keep matching duplicates in the intersected set.

ORDER(<set>, {<string_value_expression> | <numeric_value_expression>}
   [, ASC | DESC | BASC | BDESC])

There are two varieties of ORDER: hierarchized (ASC or DESC) and nonhierarchized (BASC or BDESC where "B" indicates "Break hierarchy"). The hierarchized ordering first arranges members according to their position in the hierarchy, and then it orders each level. The nonhierarchized ordering arranges members in the set without regard to the hierarchy. In the absence of an explicit specification, ASC is the default. The statement

ORDER(SampleSet, ([1995], Sales), DESC)

hierarchizes all members and sorts each level according to Sales. Sales are compared at the highest level when constructing the sorted list. Therefore, if the sum of Sales in all California cities is less than the sum of Sales in all New York cities, California and California.LA will appear below NYC in the sorted, descending list.

The result of

ORDER(SampleSet, ([1995], Sales), DESC)

is as shown in the following table.

The statement

ORDER(SampleSet, ([1995], Sales), BDESC)

sorts the members according to their values, without regard for their relative positions in the member hierarchy. In this example, numeric values are sorted by 1995 sales per city, including aggregate sales values by state and country.

The result of

ORDER(SampleSet, ([1995], Sales), BDESC)

is as shown in the following table.

Note   When the input set has 2 elements for which the <string_value_expression> or <numeric_value_expression> has the same value, the input order is preserved. For example, if the sales for USA and Europe is 300 each and the sales for Asia is 100, then the call

   ORDER({USA, Europe, Asia}, Sales, BASC)

returns the set {Asia, USA, Europe} and not the set {Asia, Europe, USA}.

TOPCOUNT(<set>, <index> [, <numeric_value_expression>])

Sorts on the <numeric_value_expression> (if any) and picks up the top <index> items. This function always breaks the hierarchy. The BOTTOMCOUNT function is similar.

For both these functions, the number of members returned is always equal to the specified <index>. Specifically, if there is a tie during the calculation of these members, the provider resolves the tie in a provider-dependent fashion and returns only the required number of members. For example, the statement

TOPCOUNT(Geography.Cities.MEMBERS, 5, Sales)

yields the following results:

• The top four cities in terms of sales are New York, Los Angeles, Chicago, and Philadelphia.
  
  
• The fifth city ties in terms of sales between Seattle and Boston.

The provider can do either of the following:

1. Return all six members: {New York, LA, Chicago, Philadelphia, Seattle, Boston}
   
   
2. Arbitrarily break the tie between Seattle and Boston and return only five cities: {New York, LA, Chicago, Philadelphia, <either Seattle or Boston>}

OLE DB for OLAP requires that the provider not choose option 1 but instead, to resolve a tie in a provider-dependent manner, choose option 2 and return only the specified number of members.

TOPPERCENT(<set>, <percentage>, <numeric_value_expression>)

Sorts on <numeric_value_expression> and picks up the top x (the smallest number possible) elements such that their percentage of the result of <numeric_value_expression> is at least <percentage>. This function always breaks the hierarchy. The BOTTOMPERCENT function is similar.

TOPSUM(<set>, <value>, <numeric_value_expression>)

Sorts on <numeric_value_expression> and picks up the top x (the smallest number possible) elements such that their sum is at least <value>. This function always breaks the hierarchy. The BOTTOMSUM function is similar.

UNION(<set1>, <set2> [, [ALL]])

Joins the two input sets by union, eliminating duplicates by default. The ALL flag keeps duplicates in the joined set. Duplicates are deleted from the tail. The expression (<set1>, <set2>) does not remove duplicates.

Another way of joining by union is to use a comma-separated list of sets within braces. For example,

{USA.CHILDREN, CANADA.CHILDREN}

is an implicit union and is equivalent to

UNION(USA.CHILDREN, CANADA.CHILDREN, [ALL])

Duplicated members are always retained in an implicit union.