Este tema fue discutido recientemente cable Estuve involucrado con microsoft.public.sqlserver.olap y, según mi experiencia, las consultas de bajo rendimiento que utilizan cálculos de estilo YTD son un problema común. Lorsque vous y réfléchissez, la raison en est assez claire : récupérer puis résumer jusqu’à 12 mois de données prendra toujours plus de temps que de récupérer un mois de données seul, surtout si la synthèse finit par se produire sur le client plutôt que sur el servidor. Cuando trabaja con grandes conjuntos de resultados o tiene otros cálculos involucrados, el efecto a menudo parece mucho peor. el hecho de que no haya agregaciones diseñadas en el nivel de mes (a menudo el nivel inferior de la dimensión de tiempo) en el cubo solo agrava este problema.

Entonces, ¿qué se puede hacer para lidiar con este tipo de consultas, además de las cosas habituales como la partición? Tengo dos recomendaciones principales:

1) Asegúrese de que las agregaciones se construyan a nivel de mes. Establecer la propiedad Uso de agregación en un nivel (seleccione la dimensión en el editor de cubos, luego busque en la pestaña avanzada) le dará a Analysis Services sugerencias sobre dónde desea que se construyan sus agregaciones, pero no hay garantía si lo configura en ‘ Solo las agregaciones de nivel inferior se construirán en el nivel inferior; es posible que rompan la regla de 1/3 (explicada en detalle en el Guía de rendimiento de los servicios de análisis) por ejemplo.

Desafortunadamente, Analysis Manager no le dirá qué agregaciones se crearon cuando recorre sus asistentes, por lo que tendrá que usar algo como la herramienta Partition Manager (disponible como parte del SQL 2K Resource Kit o BI Accelerator) para ver esto. . Si todas las agregaciones enumeradas están por encima del nivel de mes pero todas sus consultas están en el nivel de mes, ¡sus agregaciones simplemente no se están utilizando! Si no puede persuadir a Analysis Manager para que cree agregaciones donde las necesite configurando la propiedad Uso de agregación y cambiando la cantidad de miembros de nivel, intente como último recurso crear las agregaciones manualmente en la ayuda de Partition Manager. Sin embargo, saber qué agregaciones crear cuando las creas manualmente puede ser muy difícil e intentaré abordar este tema en una futura entrada de blog.

Una última cosa a tener en cuenta aquí es que si sus consultas están en el nivel de hoja de casi todas sus dimensiones, la creación de agregaciones probablemente no ayudará mucho al rendimiento de las consultas y probablemente ralentizará mucho el procesamiento del cubo. Si es así, entonces mi segunda recomendación podría ser más útil…

2) Use agregaciones en niveles más altos en su dimensión de tiempo para su ventaja. Considere la situación en la que está realizando un cálculo del año hasta la fecha para el mes de diciembre: si todas sus medidas son aditivas, el resultado debería ser el mismo que el valor del nivel del año. De manera similar, al realizar un cálculo YTD para el mes de julio, además de la suma de siete meses, podría expresarse como la suma del trimestre 1, el trimestre 2 y el mes de julio, una suma de tres valores en en lugar de 7. En un cubo sin agregaciones, obtener el valor a nivel de año y agregar dos trimestres y un mes probablemente arrojaría el mismo resultado que agregar los meses respectivos, pero si las agregaciones están presentes en el año y trimestralmente, entonces si puede ayudar a Analysis Services a evitar muchas adiciones en el momento de la consulta haciendo que su cálculo YTD aproveche estos totales supuestos.

El problema real aquí es cómo escribir el MDX para su cálculo YTD para hacer esto, y no es fácil. Aquí hay un ejemplo trabajado en Foodmart 2000 que muestra cómo puede modificar un conjunto que contiene meses como {[Time].[1997].[Q1].[1]:[Time].[1998].[Q2].[5]} que la función YTD regresaría en un conjunto donde tantos meses como sea posible son reemplazados por sus ancestros comunes, entonces {[Time].[1997], [Time].[1998].[Q1], [Time].[1998].[Q2].[4], [Time].[1998].[Q2].[5]}:

CON
/*El intervalo de tiempo inicial expresado en meses*/
ESTABLECER MI RANGO COMO ‘{[Time].[1997].[Q1].[1]:[Time].[1998].[Q2].[5]}’
/*Averigua qué años completos están presentes*/
ESTABLECER EL AÑO ACTUAL COMO ‘
/* Recorre todos los años desde el antepasado del primer mes del rango hasta
el antepasado del último mes*/
PRODUCE(
{ANCESTOR(MARANGE.ITEM(0), [Time].[Year]): ANCESTOR(COLA(MYRANGE,
1).ARTÍCULO(0), [Time].[Year])}
/* Necesidad de usar funciones de cadena desagradables para que IIF devuelva miembros o conjuntos.
¡Yukón no tiene esta restricción!*/
, STRTOSET(
/*Si todos los descendientes a nivel de mes del año actual aparecen en el
rango, podemos reemplazarlos con el miembro del año*/
IIF(CUENTA(INTERSECCIÓN(MIRANGO, DESCENDIENTES(HORA.MIEMBROACTUAL,
[Time].[Month]))) = CUENTA(DESCENDIENTES(HORA.MIEMBROACTUAL, [Time].[Month]))
, «{» + MEMBERTOSTR(HORA.MIEMBROACTUAL) + «}»
, «{}»)
)
)’
/*Como paso intermedio, eliminar todos los meses del rango inicial
que será reemplazado por años enteros*/
ESTABLECER MESES QUE RESTAN COMO ‘EXCEPTO(MIRANGO, DESCENDIENTES(AÑO ACTUAL,
[Time].[Month]))’
/*Ahora revise todos los meses restantes y vea cuáles tienen
barrios actuales, de acuerdo con la misma lógica que la anterior*/
ESTABLECER TRIMESTRE PRESENTE COMO ‘GENERAR (
{ANCESTOR(MESES QUE QUEDAN.ARTÍCULO(0), [Time].[Quarter]) : ANCESTOR(QUEUE(MES RESTANTE,
1).ARTÍCULO(0), [Time].[Quarter])}
, STRTOSET(
IIF(CUENTA(INTERSECCIÓN(MES RESTANTE, DESCENDIENTES(TIEMPO.MIEMBROACTUAL,
[Time].[Month]))) = CUENTA(DESCENDIENTES(HORA.MIEMBROACTUAL, [Time].[Month]))
, «{» + MEMBERTOSTR(HORA.MIEMBROACTUAL) + «}»
, «{}»)
)
)’
/* Finalmente regresa el nuevo rango, con años y trimestres reemplazados por
mes si es posible*/
ESTABLECER MI NUEVO RANGO COMO ‘{AÑO PRESENTE, TRIMESTRE PRESENTE, EXCEPTO (MI RANGO,
DESCENDIENTES({AÑOS ACTUALES, TRIMESTRES ACTUALES}, [Time].[Month]))}’
/*Miembros calculados por Helper para mostrar pasos*/
MEDIDAS DE MIEMBROS.STEP1 COMO ‘SETTOSTR(MYRANGE)’
MEDIDAS DE MIEMBROS.STEP2 COMO ‘SETTOSTR(AÑO ACTUAL)’
MEDIDAS DE MIEMBROS.PASO 3 COMO ‘SETTOSTR(MES QUE QUEDA)’
MEDIDAS DE MIEMBROS.PASO 4 COMO ‘SETTOSTR(CUARTO ACTUAL)’
MEDIDAS DE MIEMBROS.STEP5 COMO ‘SETTOSTR(MYNEWRANGE)’
/*Operación de visualización*/
SELECCIONE { MEDIDAS.PASO1, MEDIDAS.PASO2, MEDIDAS.PASO3, MEDIDAS.PASO4,
MEDIDAS.PASO5} ON 0
VENTAS

De la respuesta que recibí en el hilo anterior donde sugerí esta técnica por primera vez, si tiene las agregaciones correctas en su lugar, puede marcar una gran diferencia en el rendimiento.