KB6149: How to break ranking ties in MicroStrategy SQL Generation Engine

• Strategy

This article describes how to break ties when ranking data in MicroStrategy

The ranking function in Strategy Engine assigns the same ranking place to numbers that share the same value. For example, given the following list of values:

EmployeeID form: EMPLOYEE_ID; source tables: LU_EMPLOYEES (lookup), LVW_EMPLOYEES, LVW_HOURS_BILLED DESC form: lookup table: LU_EMPLOYEESEngagementID form: ENGAGEMENT_ID; source tables: LU_ENGAGEMENTS (lookup) , LVW_ENGAGEMENTS, LVW_HOURS_BILLED DESC form: lookup table: LU_ENGAGEMENTSUserID form: USER; source tables: LU_USER (lookup), LVW_EMPLOYEES, LVW_ENGAGEMENTS, LVW_HOURS_BILLEDHoursHOURS_BILLED in table LVW_HOURS_BILLEDSalarySALARY in table LVW_EMPLOYEESDurationDaysBetween(START_DATE, END_DATE) in table LVW_ENGAGEMENTSHours BilledSum(Hours) {~+}SalarySum(Salary) {~+}DurationSum(Duration) {~+}User LoginUser@ID = ?ValueRank of Value (ascending order)7192153153205	EmployeeID form: EMPLOYEE_ID; source tables: LU_EMPLOYEES (lookup), LVW_EMPLOYEES, LVW_HOURS_BILLED DESC form: lookup table: LU_EMPLOYEESEngagementID form: ENGAGEMENT_ID; source tables: LU_ENGAGEMENTS (lookup) , LVW_ENGAGEMENTS, LVW_HOURS_BILLED DESC form: lookup table: LU_ENGAGEMENTSUserID form: USER; source tables: LU_USER (lookup), LVW_EMPLOYEES, LVW_ENGAGEMENTS, LVW_HOURS_BILLEDHoursHOURS_BILLED in table LVW_HOURS_BILLEDSalarySALARY in table LVW_EMPLOYEESDurationDaysBetween(START_DATE, END_DATE) in table LVW_ENGAGEMENTSHours BilledSum(Hours) {~+}SalarySum(Salary) {~+}DurationSum(Duration) {~+}User LoginUser@ID = ?ValueRank of Value (ascending order)7192153153205
EmployeeID form: EMPLOYEE_ID; source tables: LU_EMPLOYEES (lookup), LVW_EMPLOYEES, LVW_HOURS_BILLED DESC form: lookup table: LU_EMPLOYEESEngagementID form: ENGAGEMENT_ID; source tables: LU_ENGAGEMENTS (lookup) , LVW_ENGAGEMENTS, LVW_HOURS_BILLED DESC form: lookup table: LU_ENGAGEMENTSUserID form: USER; source tables: LU_USER (lookup), LVW_EMPLOYEES, LVW_ENGAGEMENTS, LVW_HOURS_BILLEDHoursHOURS_BILLED in table LVW_HOURS_BILLEDSalarySALARY in table LVW_EMPLOYEESDurationDaysBetween(START_DATE, END_DATE) in table LVW_ENGAGEMENTSHours BilledSum(Hours) {~+}SalarySum(Salary) {~+}DurationSum(Duration) {~+}User LoginUser@ID = ?ValueRank of Value (ascending order)7192153153205	EmployeeID form: EMPLOYEE_ID; source tables: LU_EMPLOYEES (lookup), LVW_EMPLOYEES, LVW_HOURS_BILLED DESC form: lookup table: LU_EMPLOYEESEngagementID form: ENGAGEMENT_ID; source tables: LU_ENGAGEMENTS (lookup) , LVW_ENGAGEMENTS, LVW_HOURS_BILLED DESC form: lookup table: LU_ENGAGEMENTSUserID form: USER; source tables: LU_USER (lookup), LVW_EMPLOYEES, LVW_ENGAGEMENTS, LVW_HOURS_BILLEDHoursHOURS_BILLED in table LVW_HOURS_BILLEDSalarySALARY in table LVW_EMPLOYEESDurationDaysBetween(START_DATE, END_DATE) in table LVW_ENGAGEMENTSHours BilledSum(Hours) {~+}SalarySum(Salary) {~+}DurationSum(Duration) {~+}User LoginUser@ID = ?ValueRank of Value (ascending order)7192153153205
EmployeeID form: EMPLOYEE_ID; source tables: LU_EMPLOYEES (lookup), LVW_EMPLOYEES, LVW_HOURS_BILLED DESC form: lookup table: LU_EMPLOYEESEngagementID form: ENGAGEMENT_ID; source tables: LU_ENGAGEMENTS (lookup) , LVW_ENGAGEMENTS, LVW_HOURS_BILLED DESC form: lookup table: LU_ENGAGEMENTSUserID form: USER; source tables: LU_USER (lookup), LVW_EMPLOYEES, LVW_ENGAGEMENTS, LVW_HOURS_BILLEDHoursHOURS_BILLED in table LVW_HOURS_BILLEDSalarySALARY in table LVW_EMPLOYEESDurationDaysBetween(START_DATE, END_DATE) in table LVW_ENGAGEMENTSHours BilledSum(Hours) {~+}SalarySum(Salary) {~+}DurationSum(Duration) {~+}User LoginUser@ID = ?ValueRank of Value (ascending order)7192153153205	EmployeeID form: EMPLOYEE_ID; source tables: LU_EMPLOYEES (lookup), LVW_EMPLOYEES, LVW_HOURS_BILLED DESC form: lookup table: LU_EMPLOYEESEngagementID form: ENGAGEMENT_ID; source tables: LU_ENGAGEMENTS (lookup) , LVW_ENGAGEMENTS, LVW_HOURS_BILLED DESC form: lookup table: LU_ENGAGEMENTSUserID form: USER; source tables: LU_USER (lookup), LVW_EMPLOYEES, LVW_ENGAGEMENTS, LVW_HOURS_BILLEDHoursHOURS_BILLED in table LVW_HOURS_BILLEDSalarySALARY in table LVW_EMPLOYEESDurationDaysBetween(START_DATE, END_DATE) in table LVW_ENGAGEMENTSHours BilledSum(Hours) {~+}SalarySum(Salary) {~+}DurationSum(Duration) {~+}User LoginUser@ID = ?ValueRank of Value (ascending order)7192153153205
EmployeeID form: EMPLOYEE_ID; source tables: LU_EMPLOYEES (lookup), LVW_EMPLOYEES, LVW_HOURS_BILLED DESC form: lookup table: LU_EMPLOYEESEngagementID form: ENGAGEMENT_ID; source tables: LU_ENGAGEMENTS (lookup) , LVW_ENGAGEMENTS, LVW_HOURS_BILLED DESC form: lookup table: LU_ENGAGEMENTSUserID form: USER; source tables: LU_USER (lookup), LVW_EMPLOYEES, LVW_ENGAGEMENTS, LVW_HOURS_BILLEDHoursHOURS_BILLED in table LVW_HOURS_BILLEDSalarySALARY in table LVW_EMPLOYEESDurationDaysBetween(START_DATE, END_DATE) in table LVW_ENGAGEMENTSHours BilledSum(Hours) {~+}SalarySum(Salary) {~+}DurationSum(Duration) {~+}User LoginUser@ID = ?ValueRank of Value (ascending order)7192153153205	EmployeeID form: EMPLOYEE_ID; source tables: LU_EMPLOYEES (lookup), LVW_EMPLOYEES, LVW_HOURS_BILLED DESC form: lookup table: LU_EMPLOYEESEngagementID form: ENGAGEMENT_ID; source tables: LU_ENGAGEMENTS (lookup) , LVW_ENGAGEMENTS, LVW_HOURS_BILLED DESC form: lookup table: LU_ENGAGEMENTSUserID form: USER; source tables: LU_USER (lookup), LVW_EMPLOYEES, LVW_ENGAGEMENTS, LVW_HOURS_BILLEDHoursHOURS_BILLED in table LVW_HOURS_BILLEDSalarySALARY in table LVW_EMPLOYEESDurationDaysBetween(START_DATE, END_DATE) in table LVW_ENGAGEMENTSHours BilledSum(Hours) {~+}SalarySum(Salary) {~+}DurationSum(Duration) {~+}User LoginUser@ID = ?ValueRank of Value (ascending order)7192153153205
EmployeeID form: EMPLOYEE_ID; source tables: LU_EMPLOYEES (lookup), LVW_EMPLOYEES, LVW_HOURS_BILLED DESC form: lookup table: LU_EMPLOYEESEngagementID form: ENGAGEMENT_ID; source tables: LU_ENGAGEMENTS (lookup) , LVW_ENGAGEMENTS, LVW_HOURS_BILLED DESC form: lookup table: LU_ENGAGEMENTSUserID form: USER; source tables: LU_USER (lookup), LVW_EMPLOYEES, LVW_ENGAGEMENTS, LVW_HOURS_BILLEDHoursHOURS_BILLED in table LVW_HOURS_BILLEDSalarySALARY in table LVW_EMPLOYEESDurationDaysBetween(START_DATE, END_DATE) in table LVW_ENGAGEMENTSHours BilledSum(Hours) {~+}SalarySum(Salary) {~+}DurationSum(Duration) {~+}User LoginUser@ID = ?ValueRank of Value (ascending order)7192153153205	EmployeeID form: EMPLOYEE_ID; source tables: LU_EMPLOYEES (lookup), LVW_EMPLOYEES, LVW_HOURS_BILLED DESC form: lookup table: LU_EMPLOYEESEngagementID form: ENGAGEMENT_ID; source tables: LU_ENGAGEMENTS (lookup) , LVW_ENGAGEMENTS, LVW_HOURS_BILLED DESC form: lookup table: LU_ENGAGEMENTSUserID form: USER; source tables: LU_USER (lookup), LVW_EMPLOYEES, LVW_ENGAGEMENTS, LVW_HOURS_BILLEDHoursHOURS_BILLED in table LVW_HOURS_BILLEDSalarySALARY in table LVW_EMPLOYEESDurationDaysBetween(START_DATE, END_DATE) in table LVW_ENGAGEMENTSHours BilledSum(Hours) {~+}SalarySum(Salary) {~+}DurationSum(Duration) {~+}User LoginUser@ID = ?ValueRank of Value (ascending order)7192153153205
EmployeeID form: EMPLOYEE_ID; source tables: LU_EMPLOYEES (lookup), LVW_EMPLOYEES, LVW_HOURS_BILLED DESC form: lookup table: LU_EMPLOYEESEngagementID form: ENGAGEMENT_ID; source tables: LU_ENGAGEMENTS (lookup) , LVW_ENGAGEMENTS, LVW_HOURS_BILLED DESC form: lookup table: LU_ENGAGEMENTSUserID form: USER; source tables: LU_USER (lookup), LVW_EMPLOYEES, LVW_ENGAGEMENTS, LVW_HOURS_BILLEDHoursHOURS_BILLED in table LVW_HOURS_BILLEDSalarySALARY in table LVW_EMPLOYEESDurationDaysBetween(START_DATE, END_DATE) in table LVW_ENGAGEMENTSHours BilledSum(Hours) {~+}SalarySum(Salary) {~+}DurationSum(Duration) {~+}User LoginUser@ID = ?ValueRank of Value (ascending order)7192153153205	EmployeeID form: EMPLOYEE_ID; source tables: LU_EMPLOYEES (lookup), LVW_EMPLOYEES, LVW_HOURS_BILLED DESC form: lookup table: LU_EMPLOYEESEngagementID form: ENGAGEMENT_ID; source tables: LU_ENGAGEMENTS (lookup) , LVW_ENGAGEMENTS, LVW_HOURS_BILLED DESC form: lookup table: LU_ENGAGEMENTSUserID form: USER; source tables: LU_USER (lookup), LVW_EMPLOYEES, LVW_ENGAGEMENTS, LVW_HOURS_BILLEDHoursHOURS_BILLED in table LVW_HOURS_BILLEDSalarySALARY in table LVW_EMPLOYEESDurationDaysBetween(START_DATE, END_DATE) in table LVW_ENGAGEMENTSHours BilledSum(Hours) {~+}SalarySum(Salary) {~+}DurationSum(Duration) {~+}User LoginUser@ID = ?ValueRank of Value (ascending order)7192153153205

The third ranking value is shared by two identical values (15). The next ranking value is not 4 because its place is already taken by one of the previously repeated value.

This can be proven using the Strategy Tutorial project against a SQL Server warehouse. A report requires to show the bottom 10 unit prices per item. With the following objects:

Metrics:
[UNIT PRICE]: Sum([Unit Price]) {~}
[UNIT COST]: Sum([Unit Cost]) {~}

Even though the filter explicitly requires to show only the bottom 10 values, it returns 15 rows of data:

This happens because the value '72' is repeated six times and all the values '72' tie in the tenth position.

A new metric can be created to clarify the algorithm:
[RANK OF UNIT PRICE]: Rank([UNIT PRICE])

This metric will show the rank position of the metric shown on the report that the Strategy SQL Generation Engine uses on ranking filters. The results are the following:

Even though the filter explicitly requires to show only the bottom 10 values, it returns 15 rows of data:

Note that the behavior explained above is proved, all the equal values share the same rank:

The 'Chinese Survival Tool' shares the same ranking position with 'Dragonfly Pin' because their unit price is the same. The next ranking value for the 'Hedley's Blend Coffee' is '10' because the ninth space was previously taken by a tying value.

At the end, all the items that have a unit price of '72' share the rank position of '10'. This is why the report returns 15 values.

WORKAROUND:

If only 10 values must be displayed, a second metric can be used to break the tie between the tying values. In this case the second metric shown on the template ([UNIT COST]) will be used to break the tie.
A third metric that performs a running count of the items, ordered by [UNIT PRICE] and [UNIT COST] will be used to assign unique ranking values:

1. Create a tie-breaker / unique-ranking metric using the [running count] analytical function:
[TIE BREAKER]: RunningCount([UNIT PRICE])

Right-click on the RunningCount function name and choose 'parameters':

Set the sorting order to be [UNIT PRICE] and [UNIT COST]. The metric will assign sequential numbers to the rows using the defined order.

2. Put the [TIE BREAKER] metric on the report:

Note that the [TIE BREAKER] metric uses sequential and unique numbers for each [Item]. For cases in which even the combination of two values produce a tie, the Strategy SQL Generation Engine will assign the next consecutive row count value to the first value introduced into the Analytical Engine (returned by the SQL). Notice how the last two rows share the exact same combination of [UNIT PRICE] and [UNIT COST] but they actually have different value for the [TIE BREAKER] metric.

3. Now a metric limit on the [TIE BREAKER] metric can be applied to show only the bottom 10 results.

With a metric limit with the following definition:

[TIE BREAKER] <= 10

The report will return the first 10 values of the ranked bottom 10 items according to their unit price and unit cost.

Comment

0 comments

Details

Knowledge Article

Published:

May 4, 2017

Last Updated:

May 4, 2017