La necesidad de adaptar formatos numéricos y monetarios en Analysis Services es muy común, así como las acciones de detalle o drillthrough, muy requeridas por los usuarios de negocio para analizar en detalle ciertas desviaciones o casuísticas que descubren a través de la exploración de un cubo de SSAS. En la gran mayoría de ocasiones, mezclar ambas funcionalidades no presenta ningún problema, pero recientemente nos hemos encontrado un caso en el que sí hemos tenido que trabajar un poco más para conseguir el resultado deseado.
Tenemos un cubo (en este caso muy sencillo, solo con propósitos de demo) de SSAS 2012 que estamos explorando con Excel 2013. Se trata de un cubo que analiza ventas de material informático, tanto hardware como software. Disponemos para ello de una dimensión Producto donde están los productos a la venta. Esta dimensión tiene dos propiedades a nivel de producto: su precio (Product Price) y su valoración de usuario (Consumer Rating), que mide (tras haber incorporado al cubo información de encuestas a usuarios) el precio que los usuarios estarían dispuestos a pagar en el mercado, y no solo el que efectivamente acaban abonando para conseguirlo. Así podemos tener información social agregada al sistema analítico, y comparar si el valor de mercado del producto se acerca al real. Ambas son de tipo Currency y su tipo de datos en la BD relacional es money (es importante elegir con cuidado el tipo de dato en nuestro data warehouse, como nos explica Rubén Pertusa en un artículo de muy recomendable lectura).
Vemos que al explorar la dimensión desde SQL Server Management Studio podemos ver las propiedades con un carácter coma como separador decimal, siguiendo el tipo de dato que hemos especificado en el modelo y el tipo de datos de origen.
Si mostramos las propiedades de la dimensión al navegar el cubo, obtenemos el mismo comportamiento
De la misma manera, tenemos las propiedades con coma como separador decimal si consultamos el cubo con una sentencia MDX
Este cubo tiene también una acción de detalle, donde mostramos las propiedades de la dimensión Product y tres métricas. Pero cuando en este caso las propiedades se visualizan en un formato diferente, con un punto como separador decimal
En este caso el usuario nos pedía que todos los campos monetarios, incluyendo estas propiedades, se mostrasen con la coma como separador decimal. Lo que sucede dentro de SSAS es que si se consulta la propiedad de la dimensión a través de una herramienta cliente, estamos interpretando también los metadatos (como el tipo de dato, Currency en este caso) de la propiedad (desde SSMS, desde Excel y lanzando una MDX directamente contra el cubo). Sin embargo, cuando ejecutamos la acción de drillthrough, lo que se ejecuta detrás de los focos es una sentencia como esta (que capturamos con la inestimable ayuda de SQL Server Profiler)
Es una consulta directamente contra las tablas internas de SSAS donde están los datos de la dimensión (de ahí la sintaxis usando el símbolo $ en los campos después de la cláusula RETURN), y aunque la localización del servidor esté en idioma español y su intercalación también esté establecida a Modern_Spanish, esta consulta siempre devuelve los datos de las dimensiones con un punto como separador decimal. Al mostrarlo, Excel pone directamente el mismo separador y de ahí que lo veamos con un punto al ejecutar la acción.
Sin embargo, las métricas sí las muestra respetando los formatos que tiene el modelo, y ahí es donde encontramos una posible solución para este problema.
Al estar hablando de una vista de detalle, podemos simplemente incluir esta información como una métrica invisible para las herramientas cliente y no agregable, es decir, sin función de agregación predefinida. De esta manera, SSAS interpretará correctamente el formato y usará la coma como separador decimal.
Podemos conseguir esto de dos maneras:
- Incluyendo las propiedades de la dimensión en el DSV donde tengamos el grupo de medidas, usando una consulta nombrada (named query) haciendo una unión entre la tabla de hechos y la dimensión. Esta opción es la más sencilla desde el punto de vista de desarrollo pero puede impactar en el tiempo de procesado de nuestro cubo si tenemos un gran volumen de datos y no podemos indexar con un índice de cobertura para la consulta por el coste que supone mantenerlo (operaciones de mantenimiento de la BD relacional, tiempo de inserción de datos en el proceso ETL con un índice extra, etc.)
- Incluyendo las propiedades de la dimensión como una métrica física durante el proceso ETL que carga la tabla de hechos. Así integramos completamente las propiedades en la tabla de hechos y minimizamos los perjuicios que puede tener la opción anterior. Sin embargo, en este caso necesitaremos algo más de tiempo de desarrollo y el tamaño de la tabla de hechos se incrementará. Si hablamos de cientos de millones de filas el hecho de incluir una nueva columna puede suponer un crecimiento considerable.
En este caso, implementaremos la opción de inclusión de las propiedades utilizando el DSV del proyecto de SSAS, pero es importante considerar la otra opción dependiendo de nuestro escenario concreto.
Para ello, sustituimos la tabla de hechos por una consulta nombrada que añada los dos campos de propiedades a la salida de la consulta
El código T-SQL de la named query quedaría algo parecido a esto:
SELECT S.IdSales, S.IdProduct, S.IdDate, S.Quantity, S.GrossSale, S.NetSale, P.ProductConsumerRating AS ConsumerRating, P.ProductPrice
FROM FACTS.Sales AS S
INNER JOIN
DIMENSIONS.Product AS P
ON S.IdProduct = P.IdProduct
Después, añadiremos dos nuevas métricas usando los campos recién añadidos en el DSV y estableceremos su propiedad Visible a False y las dejaremos sin función de agregación
Finalmente, añadiremos a la acción de drillthrough las métricas que hemos añadido y eliminaremos las propiedades de la dimensión.
Después de desplegar nuestro proyecto y de procesar el cubo, vemos que ahora sí se nos muestra la información con el formato deseado.
Esta solución nos da la oportunidad de poder mostrar este tipo de información de detalle con la coma como separador decimal, pero hay que tener en cuenta que aunque la métricas se encuentra ocultas podrían ser consultadas con MDX si le especificamos una función de agregación en la propia consulta, y esto puede llevar a interpretaciones erróneas del dato, ya que puede no tratarse de un dato agregable. Llegados a este punto, podría ser necesaria la aplicación de seguridad en nuestro cubo para evitar el acceso de ciertos usuarios a dicha métrica.
Espero que este enfoque os saque de algún apuro como lo hizo conmigo 🙂
¡Feliz análisis!
