BottomPercent (DMX)

Applies to: SQL Server Analysis Services

Returns, in order of increasing rank, the bottom-most rows of a table whose cumulative total is at least a specified percentage.

Syntax

  
BottomPercent(<table expression>, <rank expression>, <percent>)  

Arguments

<Table expression>
The name of a nested table column or table-valued expression.

<rank expression>
A column in the nested table, or expression that evaluates to a column.

<percent>
A double that indicates the total target percentage.

Result Type

A table.

Remarks

The BottomPercent function returns the bottom-most rows in increasing order of rank. The rank is based on the evaluated value of the <rank expression> argument for each row, such that the sum of the <rank expression> values is at least the given percentage that is specified by the <percent> argument. BottomPercent returns the smallest number of elements possible while still meeting the specified percent value.

Examples

The following example creates a prediction query against the Association model that you built in the Basic Data Mining Tutorial.

To understand how BottomPercent works, it may be helpful to first execute a prediction query that returns only the nested table.

SELECT Predict ([Association].[v Assoc Seq Line Items], INCLUDE_STATISTICS, 10)  
FROM   
     [Association]  
NATURAL PREDICTION JOIN  
SELECT (SELECT 'Women''s Mountain Shorts' as [Model]) AS [v Assoc Seq Line Items]) AS t  

Example results:

The BottomPercent function takes the results of this query and returns the smallest-valued rows that sum to the specified percentage.

SELECT   
BottomPercent  
    (  
    Predict ([Association].[v Assoc Seq Line Items],INCLUDE_STATISTICS,10),  
    $SUPPORT,  
    50)  
FROM   
     [Association]  
NATURAL PREDICTION JOIN  
(SELECT (SELECT 'Women''s Mountain Shorts' as [Model]) AS [v Assoc Seq Line Items]) AS t  

The first argument to the BottomPercent function is the name of a table column. In this example, the nested table is returned by calling the Predict function and using the INCLUDE_STATISTICS argument.

The second argument to the BottomPercent function is the column in the nested table that you use to order the results. In this example, the INCLUDE_STATISTICS option returns the columns $SUPPORT, $PROBABILTY, and $ADJUSTED PROBABILITY. This example uses $SUPPORT because support values are not fractional and therefore are easier to verify.

The third argument to the BottomPercent function specifies the percentage, as a double. To get the rows that represent the bottom 50 percent of the support, you type 50.

Example results:

Note This example is provided only to illustrate the usage of BottomPercent. Depending on the size of your data set, this query might take a long time to run.

See Also

Data Mining Extensions (DMX) Function Reference
Functions (DMX)