Calcular clasificaciones es un problema clásico de MDX y, como suele serlo, Mosha tiene una gran entrada de blog sobre el tema que le animo a leer si aún no lo ha hecho:
http://sqljunkies.com/WebLog/mosha/archive/2006/03/14/mdx_ranking.aspx

Su consejo se puede resumir de manera bastante simple: si es posible, declare un conjunto con nombre que esté ordenado para usarse en la función de rango en lugar de intentar hacer ese orden cada vez que desee calcular un rango, ya que eso le dará un rendimiento mucho mejor. Eso es todo lo que necesita saber para el 99% de los cálculos de clasificación que escribe; sin embargo, pensé un poco en el 1% restante de los escenarios y esto es lo que se me ocurrió.

Primero, vale la pena señalar que la función Ordenar no siempre es la forma más eficiente de ordenar un conjunto. Como señala Mosha en esta entrada de blog:
http://sqljunkies.com/WebLog/mosha/archive/2007/04/19/stored_procs_best_practices.aspx
…hay un error de rendimiento en la función Ordenar que la hace muy lenta. Consulta de muestra de Mosha:

con miembro y como
para contar(
Pedido( [Customer].[Customer].[Customer].MIEMBROS
*[Product].[Category].[Category].MIEMBROS
*[Product].[Style].[Style].MIEMBROS
Las medidas.[Internet Sales Amount]BDESC))
seleccione y a 0 de [Adventure Works]

… se ejecuta en 40 segundos en mi computadora portátil y, si bien muestra cómo un sproc puede mejorar el rendimiento, no menciona que si reescribe para usar la función TopCount, obtendrá un rendimiento aún mejor. La siguiente consulta se ejecuta en 4 segundos en un caché frío en mi máquina:

con
miembro y como conteo (conteo superior (
{[Customer].[Customer].[Customer].MIEMBROS
*[Product].[Category].[Category].MIEMBROS
*[Product].[Style].[Style].MEMBERS} como myset
contar(miconjunto), Medidas.[Internet Sales Amount]))
seleccione y a 0
a partir de [Adventure Works]

La función TopCount utiliza un algoritmo diferente al de la función Order y está optimizada para devolver los primeros n miembros de un conjunto ordenado; para un conjunto relativamente pequeño como el de este ejemplo, funciona mejor que la implementación con errores existente de Order, pero puede que no siempre sea así. Y, por supuesto, es muy probable que la función Orden se corrija en un futuro paquete de servicio, por lo que en algún momento comenzará a funcionar mejor que TopCount. Por lo tanto, utilice este enfoque bajo su propio riesgo y pruébelo a fondo.

Pero volvamos al tema de la clasificación en sí. El problema obvio que Mosha no aborda en su artículo es ¿qué sucede cuando tienes que calcular un rango en más de un criterio? Para tomar el ejemplo de Mosha, ¿qué pasa con el cálculo de la clasificación de los empleados por el monto de las ventas del revendedor durante varios meses, donde esos meses deberían aparecer en las columnas? Si sabe de antemano cuáles serán esos meses, es bastante simple porque puede crear varios conjuntos con nombre para usar. el peor problema que vas a tener es que tu consulta va a ser bastante larga. Pero, ¿y si no sabes cuáles serán esos meses o cuántos hay? Por ejemplo, puede filtrar los meses según otro criterio o utilizar la función TopPercent. Es imposible crear los conjuntos con nombre que necesita para obtener un buen rendimiento si no sabe cuántos conjuntos necesitará, ¿verdad? Si tuviera control total sobre el código de la herramienta de su cliente, podría generar dinámicamente su cadena de consulta MDX para brindarle todos los conjuntos con nombre que necesita, pero eso sería una molestia incluso si fuera posible (y no lo sería con muchas herramientas listas para usar como Proclarity Desktop). Bueno, una solución a este problema es simplemente usar un conjunto con nombre para todos sus meses:

CON
SET MyMonths como TopPercent([Date].[Calendar].[Month].Miembros, 20, [Measures].[Reseller Sales Amount])
ESTABLECER MisEmpleados como [Employee].[Employee].[Employee].MIEMBROS
SET Empleados Pedidos COMO
GENERAR(MisMeses,
PEDIDO(
[Employee].[Employee].[Employee].miembros
([Measures].[Reseller Sales Amount],[Date].[Calendar].MiembroActual), BDESC)
TODO)
MIEMBRO [Measures].[Employee Rank] ME GUSTA
RANGO([Employee].[Employee].MiembroActual,
Subconjunto(
Empleados ordenados
,(RANGO([Date].[Calendar].MiembroActual, MisMeses)-1) * Recuento(MisEmpleados)
Contar(MisEmpleados)
))

PARA SELECCIONAR
MisMeses
*
[Measures].[Employee Rank]
EN 0
,MisEmpleados EN 1
a partir de [Adventure Works]

El conjunto MyMonths contiene los meses que me interesan y, como dije, debido a que usa la función TopPercent, no sé de antemano cuántos meses contendrá. Sin embargo, sé que hay una cantidad estática de empleados que quiero solicitar, por lo que en mi conjunto OrderedEmployees, estoy usando la función Generar para crear una lista concatenada de empleados solicitados para cada mes (tenga en cuenta el uso de la bandera TODOS aquí para asegúrese de que Generate no haga una distinción en el conjunto que devuelve). En mi cálculo de clasificación de empleados, puedo usar la función Subconjunto para seleccionar la sección de este conjunto que devuelve la lista ordenada de empleados para el mes actual: este es el subconjunto que comienza en el índice (RANK([Date].[Calendar].CurrentMember, MyMonths)-1) * Count(MyEmployees) y es Count(MyEmployees) miembros de largo.

PERO… por supuesto, solo funciona porque sabemos que hay la misma cantidad de empleados cada mes. ¿Qué pasa si cambiamos el cálculo y preguntamos si el empleado actual se encuentra en el 75% superior de los empleados por cantidad de ventas de revendedor para cada mes? En este caso, 8 empleados constituyen el 75% superior para agosto de 2003, pero hay 10 para septiembre de 2003 y así sucesivamente. Por lo tanto, este enfoque es inútil.

La solución a este problema se me ocurrió mientras conducía a casa por la autopista desde la casa de mis suegros el sábado por la tarde, y cuando sucedió, mi esposa me preguntó por qué de repente había comenzado a sonreír tan ampliamente: es algo que encenderá todos. Fetichistas de MDX (los tres). Básicamente, es una forma de generar dinámicamente conjuntos con nombre en una consulta. Veamos primero la solución completa:

CON
SET MyMonths como TopPercent([Date].[Calendar].[Month].Miembros, 20, [Measures].[Reseller Sales Amount])
ESTABLECER MisEmpleados como [Employee].[Employee].[Employee].MIEMBROS
SET MyMonthsWithEmployeeSets como
Produce(
MisMeses
Unión(
{[Date].[Calendar].Miembro actual}
,
StrToSet(«
Cortar({},
{Porcentaje superior (Mis empleados, 75, ([Measures].[Reseller Sales Amount],[Date].[Calendar].Miembro actual))
as EmployeeSet» + Cstr(MyMonths.CurrentOrdinal) + «})»)
))

MIEMBRO [Measures].[TopEmployee] ME GUSTA
Si (
RANGO([Employee].[Employee].MiembroActual,
StrToSet(«ConfigurarEmpleados» + Cstr(Rango([Date].[Calendar].MiembroActual, MisMeses)))
)<>0, «Sí», «No»)

PARA SELECCIONAR
MisMesesConConjuntosEmpleados
*
[Measures].[TopEmployee]
EN 0
,MisEmpleados EN 1
a partir de [Adventure Works]

Esto se ejecuta en 2 segundos en un caché frío en mi computadora portátil, en comparación con los 52 segundos de la consulta equivalente que evalúa el TopPercent para cada celda, por lo que definitivamente es una gran mejora. Lo que estoy haciendo es en la declaración establecida para MyMonthsWithEmployeeSets usando una función Generar para recorrer el conjunto de meses que voy a mostrar en las columnas y devolver exactamente el mismo conjunto, pero al hacerlo, encuentre el 75 % de los empleados principales. para cada mes y almacenarlo en un conjunto con nombre declarado en línea. Para hacer esto, devuelvo un conjunto que contiene el mes actual de la iteración y lo uno con una expresión que devuelve un conjunto vacío. el conjunto superior del 75 % se evalúa y almacena en la expresión que devuelve el conjunto vacío. La parte divertida es que la expresión que devuelve el conjunto vacío está dentro de una cadena que se pasa en StrToSet y, por lo tanto, puedo generar dinámicamente los nombres de mis conjuntos con nombre usando una cadena estática más el resultado de la función ordinal actual. En el ejemplo anterior, termino creando cinco conjuntos con nombre llamados EmployeeSet1 a EmployeeSet5, uno para cada mes. Gracias al hecho de que puedo hacer referencia a estos conjuntos en otro miembro calculado siempre que se evalúe más abajo en el árbol de ejecución (ver http://www.sqlserveranalysisservices.com/OLAPPapers/ReverseRunningSum.htm), suponiendo que construya mi declaración SELECT de manera adecuada, puedo acceder al contenido de esos conjuntos en mi miembro calculado TopEmployee usando otra llamada a StrToSet y alguna manipulación de cadenas para determinar el nombre del conjunto que busco. ¿Cuan genial es eso?