De sobras es conocido que cuando queremos algo bien hecho, hemos de hacerlo por nosotros mismos. Eso es una realidad cuando tenemos que utilizar ciertas herramientas que automatizan cierto tipo de tareas engorrososas y onerosas. Sobre todo cuando dichas herramientas tienen limitada su funcionalidad y ésta se agota pronto. Cualquiera que se hay enfrentado al Query Designer de los proyectos de Reporting Services para hacer consultas a un cubo, entenderá por donde está yendo el sentido de estas líneas.
A resultas de un proyecto en el que he tenido que abordar unas 35 consultas contra un cubo, pude comprobar lo que los bloggers comentan acerca de la compatibilidad Reporting-Analysis services. Microsoft en ese sentido tuvo que sacrificar buena parte de la potencia y complejidad del lenguaje MDX (multidimensional query expressions) para adaptar la utilización de dataset y tablix a Analysis Services. Como se puede comprobar tanto en MSDN como en otros sitios que hablan de la materia, las consultas para reporting devuelve un cubo «aplanado», es decir, con estructura tabular basada en columnas, «desdimensionando» la presentación de la información y deshaciendo las relaciones de nivel entre las jerarquías y las relaciones de uso entre dimensiones. De esta forma se vuelcan al dataset registros cuyas columnas son la medida y las dimensiones de la consulta. Además, de las modalidades de «eje» disponibles en análisis services, Reporting nos limita a dos para los campos QUE mostrarán información, «COLUMNS» y «ROWS»; y una para el «filtrado» de los datos, «WHERE». Si a esto le sumanos la obligatoriedad de uso de la medida en los dos primeros ejes (nunca en WHERE), que el uso de parámetros requiere de funciones STRTOMEMBER y STRTOSET, que perjudican el rendimiento de la consulta, podemos concluir que posiblemente nos tocará algún día encontrar modos de cumplir nuestros requerimientos por encima de estas limitaciones. Mientras pruebo las mejoras que pueda haber en SQL Server 2012, os enseño algunos consejos acerca de cómo mejorar un poco el rendimiento.
En primer lugar veamos una consulta típica del query designer:
SELECT
{
[Measures].[DVR Event Count]
}
ON COLUMNS
,
{
([Datetime].[Calendar].[Hour].ALLMEMBERS
* [Datetime].[Month].[Month].ALLMEMBERS
* [Datetime].[Day Of Month].[Day Of Month].ALLMEMBERS
* [Account Type].[Account Type].[Account Type].ALLMEMBERS
* [Device Type].[Device Type].[Device Type].ALLMEMBERS
* [Asset].[Asset Type].[Asset Type].ALLMEMBERS
* [Asset].[Assets by Asset Type].[Asset].ALLMEMBERS
* [Product].[Products].[Product].ALLMEMBERS
* [DVR Event Status].[DVR Event Status].[DVR Event Status].ALLMEMBERS )
}
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME
ON ROWS
FROM
( SELECT (
STRTOMEMBER(@FromDatetimeCalendar, CONSTRAINED)
:
STRTOMEMBER(@ToDatetimeCalendar, CONSTRAINED) )
ON COLUMNS
FROM
( SELECT ( STRTOSET(@DVREventStatusDVREventStatus, CONSTRAINED) )
ON COLUMNS
FROM
( SELECT ( STRTOSET(@ProductProducts, CONSTRAINED) )
ON COLUMNS
FROM
( SELECT ( STRTOSET(@AssetAssetsbyAssetType, CONSTRAINED) )
ON COLUMNS
FROM
( SELECT ( STRTOSET(@AssetAssetType, CONSTRAINED) )
ON COLUMNS
FROM
( SELECT ( { [DVR Event Type].[DVR Event Type].&[1] } )
ON COLUMNS
FROM
( SELECT ( STRTOSET(@DeviceTypeDeviceType, CONSTRAINED) )
ON COLUMNS
FROM
( SELECT ( STRTOSET(@AccountTypeAccountType, CONSTRAINED) )
ON COLUMNS
FROM
( SELECT ( STRTOSET(@OperatingBusinessOperatingBusiness, CONSTRAINED) )
ON COLUMNS
FROM
[GVP]
)))))))))
WHERE (
IIF( STRTOSET(@OperatingBusinessOperatingBusiness, CONSTRAINED).Count = 1,
STRTOSET(@OperatingBusinessOperatingBusiness, CONSTRAINED)
, [Operating Business].[Operating Business].currentmember)
, [DVR Event Type].[DVR Event Type].&[1])
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE
, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
Los hechos relevantes son el modo de gestionar los parámetros (mediante subconsultas para reducir el cubo) con funciones STRTOMEMBER y STRTOSET, la comprobación en el eje WHERE con la función IIF y El uso de CROSSJOIN (abreviado por *). Como no podemos evitar el uso de las medidas en el eje COLUMNS, la idea es intentar reducir la expresión de subcubos y el uso de funciones de conversión de cadena. Asímismo, es posible optimizar la consulta estableciendo dinámicamente la expresión del parámetro que se está utilizando. Para empezar vamos a suponer que tenemos que hacer una nueva query como la de antes. Empezaríamos por crearla con el designer de manera habitual. El resultado sería una expresión como la que tenemos en la imagen anterior. Lo importante de este paso es que al salir del designer pulsemos Refresh Fields para que la definición del dataset referencie los campos que la consulta devolverá para utilizarlos en el tablix que usemos.
Ahora es cuando hay que empezar a hacer cambios importantes. La mejor manera es establerciendo la query como una expression de código que devuelva una cadena comprensible por el Data Provider de OLAP. En la ventana de propiedades del dataset pulsamos el botón «fx» que nos permite abrir la query como expresión de código. La gran diferencia con el entorno que ofrece el query designer es que, por un lado, perdemos todas las ayudas visuales para modificar la query, pero por otro, adquirimos las oportunidad de usar código de VB NET y establecer cambios en la query antes de que se ejecute eligiendo la mejor manera según la expresión MDX que nos convenga. Lo primero es colocar «=» al comienzo de la query y envolviéndola con comillas «. Recordad que si queremos hacer saltos de líneas deberemos finalizar cada línea cerrando comillas y abriendo con «&» en la línea siguiente. La query anterior querdaría así:
="SELECT "
&"{"
&" [Measures].[DVR Event Count] "
&"} "
&"ON COLUMNS"
&", "
&"{ "
&" ([Datetime].[Calendar].[Hour].ALLMEMBERS "
&" * [Datetime].[Month].[Month].ALLMEMBERS "
&" * [Datetime].[Day Of Month].[Day Of Month].ALLMEMBERS "
&" * [Account Type].[Account Type].[Account Type].ALLMEMBERS "
&" * [Device Type].[Device Type].[Device Type].ALLMEMBERS "
&" * [Asset].[Asset Type].[Asset Type].ALLMEMBERS "
&" * [Asset].[Assets by Asset Type].[Asset].ALLMEMBERS "
&" * [Product].[Products].[Product].ALLMEMBERS "
&" * [DVR Event Status].[DVR Event Status].[DVR Event Status].ALLMEMBERS ) "
&" } "
&" DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME "
&" ON ROWS "
&" FROM "
&" ( SELECT ( STRTOMEMBER(@FromDatetimeCalendar, CONSTRAINED)
&" : STRTOMEMBER(@ToDatetimeCalendar, CONSTRAINED) ) "
&" ON COLUMNS "
&" FROM "
&" ( SELECT ( STRTOSET(@DVREventStatusDVREventStatus, CONSTRAINED) ) "
&" ON COLUMNS "
&" FROM "
&" ( SELECT ( STRTOSET(@ProductProducts, CONSTRAINED) ) "
&" ON COLUMNS "
&" FROM "
&" ( SELECT ( STRTOSET(@AssetAssetsbyAssetType, CONSTRAINED) ) "
&" ON COLUMNS "
&" FROM "
&" ( SELECT ( STRTOSET(@AssetAssetType, CONSTRAINED) ) "
&" ON COLUMNS "
&" FROM "
&" ( SELECT ( { [DVR Event Type].[DVR Event Type].&[1] } ) "
&" ON COLUMNS "
&" FROM "
&"( SELECT ( STRTOSET(@DeviceTypeDeviceType, CONSTRAINED) ) "
&" ON COLUMNS "
&" FROM "
&" ( SELECT ( STRTOSET(@AccountTypeAccountType, CONSTRAINED) ) "
&" ON COLUMNS "
&" FROM "
&" ( SELECT ( STRTOSET(@OperatingBusinessOperatingBusiness, CONSTRAINED) ) "
&" ON COLUMNS "
&" FROM "
&"[GVP]"
&")))))))))"
&"WHERE ("
&" IIF( STRTOSET(@OperatingBusinessOperatingBusiness, CONSTRAINED).Count = 1,
&" STRTOSET(@OperatingBusinessOperatingBusiness, CONSTRAINED)
&" ,[Operating Business].[Operating Business].currentmember)"
&",[DVR Event Type].[DVR Event Type].&[1] "
&") "
&"CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE
&", FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"
Empezemos las optimizaciones. Aunque tengamos los parámetros declarados y perfectamente establecidos en la query, lo mejor es que substituyamos cada expresión con STRTOMEMBER o STRTOSET con la expresión de Visual Basic .Net Parameters!NombredeParametro.Value, de tal manera que podemos ahorrar todo el código de la función de esta manera:
(…)
&" FROM "
&"( SELECT ( " & Parameters!FromDatetimeCalendar.Value & " : "
& Parameters!ToDatetimeCalendar.Value & " ) "
&"ON COLUMNS "
&"FROM "
&" ( SELECT ( " & Parameters!DVREventStatus.Value & " ) "
&"ON COLUMNS "
&"FROM "
&" ( SELECT ( " & Parameters!Products.Value & " ) "
&"ON COLUMNS "
&"FROM "
&" ( SELECT ( "& Parameters!Asset.Value & " ) "
&"ON COLUMNS "
&"FROM "
&" ( SELECT ( " & Parameters!AssetType.Value & ") "
&"ON COLUMNS "
&"FROM "
&" ( SELECT ( { [DVR Event Type].[DVR Event Type].&[1] } ) "
&"ON COLUMNS "
&"FROM "
&" ( SELECT " & Parameters!DeviceType.Value & ") "
&"ON COLUMNS "
&"FROM "
&"( SELECT ( " & Parameters!AccountType.Value & ") "
&"ON COLUMNS "
&"FROM "
&"( SELECT ( " & Parameters!OB.Value & " ) "
&"ON COLUMNS "
&"FROM "
&"[GVP]"
&")))))))))"
&"WHERE ("
&" IIF( " & Parameters!OB.Value & " & .Count = 1, "
& Parameters!OB.Value
&" ,[Operating Business].[Operating Business].currentmember )"
&" , [DVR Event Type].[DVR Event Type].&[1] "
&" ) "
&" CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR
&", FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"
Recuerda que por cada expresión en Visual Basic .Net hay que cerrar la cadena de texto y concatenar la expresión del lenguaje para que el resultado final sea como deseamos.
Con este cambio ya hemos mejodaro el rendimiento de la consulta un tanto. Ahora viene el trabajo fino. En primer lugar la clausula WHERE agrega una comprobación IIF que trata de evitar vacios en el parámetro que está evaluando. Si tenemos la certeza de que el parámetro siempre tendrá un valor representando un miembro de la dimensión, ya sea por establecer un valor por defecto o porque el usuario lo elija, podemos cambiar el uso de la función por la misma expresión de obtención del valor del parámetro (Parameters!NombredelParametro.Value). Llegados a este punto, hay que tener en cuenta el tratamiento del valor de los parámetros multivalor. Cuando los utilicemos en la concatenación deberemos utilizar los «{«, «}» y la función de Visual basic JOIN, ya que el valor del objeto parámetro es un array de tipo Object.
&"( SELECT ( " & Join(Parameters!Product.Value,",") & " )"
Siguiendo con la mejora de rendimiento, hay que atender al modo en que se genera la clausula FROM. Vemos que las mismas dimensiones que se utilizan en el eje ROWS,se utilizan para generar un subcubo de modo que genere un filtro. Esto permite que el eje ROWS utilice la función crossjoin solo con los valores devueltos de la subconsulta generando un filtro al modo de las clausulas WHERE de T-SQL. La cuestión es que la subconsulta y la función CROSSJOIN suponen un tiempo que podríamos ahorrar teniendo en cuenta la naturaleza de la consulta. Si colocamos los parámetros en el eje Rows ya no tendremos que utilizar subcubos para filtrarlos. Este cambio viene con una desventaja. Si utilizamos un miembro All en el eje ROWS y esperamos que el agregado resultante sea un cálculo basado en un cubo filtrado, perderemos esa capacidad al «subir» los parámetros filtro al segundo eje, porque el motor de Análisis realizará los cálculos de los miembros All teniendo en cuenta el cubo resultante de la clausula FROM que podría incluir todos los miembros de una dimensión aunque solo visualicemos 2 de ellos y queramos mostrar además un total calculado por nuestra consulta. Además, tenemos que tener en cuenta que si el filtro utilizado y el campo que queremos mostrar en la tabla del informe son el mismo, introducir «Parameters!NombredelParametro.Value» en ROWS si utilizamos el miembro All de la jerarquía no devolvería cada uno de los valores dependientes de esa jerarquía desglosados, sino directamente el miembro All y su agregado (Véase el ejemplo siguiente):
="SELECT "
&"{"
&" [Measures].[DVR Event Count] "
&"} "
&"ON COLUMNS"
&", "
&"{ "
&" ("
&" [Datetime].[Calendar].[Hour].ALLMEMBERS "
&" * [Datetime].[Month].[Month].ALLMEMBERS "
&" * [Datetime].[Day Of Month].[Day Of Month].ALLMEMBERS "
&" * " & Parameters!AccountType.Value
&" * " & Parameters!DeviceType.Value
&" * " & Parameters!AssetType.Value
&" * [Asset].[Assets by Asset Type].[Asset].ALLMEMBERS "
&" * [Product].[Products].[Product].ALLMEMBERS "
&" * [DVR Event Status].[DVR Event Status].[DVR Event Status].ALLMEMBERS ) "
&" } "
&" DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME "
&" ON ROWS "
&" FROM "
&"( SELECT ( " & Parameters!FromDatetimeCalendar.Value & " : "
& Parameters!ToDatetimeCalendar.Value & " ) "
&"ON COLUMNS "
&"FROM "
&" ( SELECT ( " & Parameters!DVREventStatus.Value & " ) "
&"ON COLUMNS "
&"FROM "
&"( SELECT ( " & Join(Parameters!Product.Value,",") & " ) "
&"ON COLUMNS "
&"FROM "
&" ( SELECT ( "& Parameters!Asset.Value & " ) "
&"ON COLUMNS "
&"FROM "
&" ( SELECT ( " & Parameters!AssetType.Value & ") "
&"ON COLUMNS "
&"FROM "
&" ( SELECT ( { [DVR Event Type].[DVR Event Type].&[1] } ) "
&"ON COLUMNS "
&"FROM "
&" ( SELECT " & Parameters!DeviceType.Value & ") "
&"ON COLUMNS "
&"FROM "
&"( SELECT ( " & Parameters!AccountType.Value & ") "
&"ON COLUMNS "
&"FROM "
&"( SELECT ( " & Parameters!OB.Value & " ) "
&"ON COLUMNS "
&"FROM "
&"[GVP]"
&")))))))))"
&"WHERE ("
&" IIF( " & Parameters!OB.Value & " & .Count = 1, "
& Parameters!OB.Value
&" ,[Operating Business].[Operating Business].currentmember )"
&" , [DVR Event Type].[DVR Event Type].&[1] "
&" ) "
&" CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR
&", FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"
Por esta razón, desplazar el uso de los parámetros de FROM a ROWS debe hacerse de forma juiciosa, y siempre que el parámetro no vaya a incluir el valor All entre las opciones a elegir por el usuario. En cuyo caso estaremos obligados a filtrar con subcubo.
Estos cambios ya suponen un avance importante en cuanto a la flexibilidad de la que partimos. Pero hay una parte que podría resultar inadvertidamente dañina para el rendimiento ¿Qué pasa si el usuario puede elegir de un parámetro multivalor más de mil opciones (es decir, dejar seleccionados todos los valores del combo box por defecto)? Pues que la query que nos podríamos encontrar implicaría un set con todos los valores concatenados así:
(…) FROM (SELECT (
{[Product].[Products].[Product].&[31],[Product].[Products].[Product].&[65]
,[Product].[Products].[Product].&[72]
, [Product].[Products].[Product].&[77],[Product].[Products].[Product].[146]
,[Product].[Products].[Product].&[147]))
ON COLUMNS FROM (...)
Si hablamos de 1000 resultados, es posible que el procesamiento solo por lectura de la cadena se vea menoscabado. Para ello, es interesante utilizar operadores mdx como «:» que establecen un cierto rango entre un miembro de la dimensión y otro en un intervalo dando opciones de mejora a la ejecución de la consulta:
(...) FROM (SELECT ( [Product].[Products].[Product].&[31] : [Product].[Products].[Product].&[147] ) ON COLUMNS FROM (...)
Desgraciadamente reporting no detecta cuando los elementos son suficientemente numerosos para tener que mejorar la expresión que se deriva del designer. Por ello la opción que he encontrado es el uso de una función personalizada en el informe que a partir de unos parámetros calcule la cadena que le conviene más a la consulta. En el caso que me encontré utilice el siguiente código (que se agrega como código en las propiedades del Report):
Function GetOptimizedMDXClause(ByVal Products As Object()
, ByVal TotalItems As Integer, ByVal AllMemberName As String) As Object()
Dim RetValue As Object()
Dim ProductsToEval As String = System.String.Empty
Dim IsRange As Boolean = False
If Products.length = TotalItems Then
ProductsToEval = Products(0).ToString() & ","
& Products(Products.Length - 1).ToString()
IsRange = True
Else
For i As Integer = 0 To Products.Length - 1
ProductsToEval &= Products(i).ToString() & ","
Next
ProductsToEval =
ProductsToEval.Substring(0, ProductsToEval.Length - 1)
End If
RetValue = New Object() {ProductsToEval, IsRange}
Return RetValue
End Function
Function GetSubCubeByParameter(ByVal Parameter As String
, ByVal Parameter2 As String, ByVal WithRange As Boolean
, ByVal IsSet As Boolean) As String
Dim Ret As String = ""
Ret = "( SELECT ( "
If WithRange Then
Ret &= Parameter
Ret &= " : " & Parameter2
Else
If IsSet Then
Ret &= "{"
End If
Ret &= Parameter
If IsSet Then
Ret &= "}"
End If
End If
Ret &= " ) ON COLUMNS FROM "
Return Ret
End Function
Function GetFromClause(ByVal CubeName As String
, ByVal NumberOfSubCubesClauses As Integer) As String
Dim Ret As String = CubeName
For n As Integer = 0 To NumberOfSubCubesClauses - 1
Ret &= ")"
Next
Return Ret
End Function
Function GetSelectFilterClause(ByVal ParameterList As Object()
, ByVal CubeName As String) As String
Dim Ret As String = ""
Dim ParenthesisCount As Integer = 0
For Each parameter As Object() In ParameterList
' If item in index 1 is True, then I know the parameters
'must be in a range MDX expression
Dim strParams As String() = parameter(0).Split(",")
Dim param1 As String = parameter(0) 'Only for readability purpose.
Dim param2 As String = Nothing
Dim IsRange As Boolean = parameter(1) ' if
'When Range, parameter is splitable and index 1
'is second member of Range and first splitted item is first item in Range.
If IsRange Then
param1 = strParams(0)
param2 = strParams(1)
End If
Dim IsSet As Boolean = strParams.length > 1 And Not IsRange
Ret &= GetSubCubeByParameter(param1, param2, IsRange, IsSet)
ParenthesisCount += 1
Next
' Clause Closing with the cube's name and subcube closing ')'
Ret &= GetFromClause(CubeName, ParenthesisCount)
Return Ret
End Function
Así, podía optimizar partes del código y elegir la expresión más apropiada a la consulta en cuestión. Un ejemplo de cómo podría resultar es el siguiente:
="SELECT "
&"{"
&" [Measures].[DVR Event Count] "
&"} "
&"ON COLUMNS"
&", "
&"{ "
&" ("
&" [Datetime].[Calendar].[Hour].ALLMEMBERS "
&" * [Datetime].[Month].[Month].ALLMEMBERS "
&" * [Datetime].[Day Of Month].[Day Of Month].ALLMEMBERS "
&" * " & Parameters!AccountType.Value
&" * " & Parameters!DeviceType.Value
&" * " & Parameters!AssetType.Value
&" * [Asset].[Assets by Asset Type].[Asset].ALLMEMBERS "
&" * [Product].[Products].[Product].ALLMEMBERS "
&" * [DVR Event Status].[DVR Event Status].[DVR Event Status].ALLMEMBERS "
&" ) "
&" } "
&" DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME "
&" ON ROWS FROM"
& Code.GetSelectFilterClause(
new Object(){
Code.GetOptimizedMDXClause(Parameters!Product.Value
, Parameters!TotalProductRows.Count, "[Product].[Products].[All]") �
,new Object(){Join(Parameters!DVREventStatus.Value,","), False}
,new Object(){Parameters!FromDatetimeCalendar.Value & ","
& Parameters!ToDatetimeCalendar.Value, True}
,new Object(){Join(Parameters!DeviceType.Value,","), False}
,new Object(){Parameters!AccountType.Value, False}
,new Object(){Parameters!OB.Value, False}
,new Object(){Parameters!Asset.Value, False}
, new Object(){"[DVR Event Type].[DVR Event Type].&[1]", False}
}
, "[GVP]")
&"WHERE "
&" ("
& Parameters!OB.Value
&", [DVR Event Type].[DVR Event Type].&[1] "
&" ) "
&" CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING
&" , FONT_NAME, FONT_SIZE, FONT_FLAGS"
Por supuesto, no hay que dejar pasar el hecho de que agregar código al informe también perjudica el rendimiento. Por este motivo, hay que valorar dónde penaliza más la consulta el uso de una técnica u otra.
Seguramente haya más trucos por ahí escondidos, como el uso de MEMBERS y SETs calculados, pero dejo a vuestro libre albedrío el descubrimiento de tales habida cuenta que la autentica optimización se encuentra cuando podemos generar el código adaptándonos al contexto de cada situación. La automatización es una cosa, nuestra capacidad natural de raciocinio el poder sobre las demás.
