SSRS + Mobile Reports + DAX (parte 1)

Con SQL Server 2016, Microsoft integro la “Datazen” dentro de Reporting Services con la denominación de Reportes Móviles.

Si nuestra solución de DW/BI utiliza SSAS (Analysis Services), especialmente la versión Tabular, como capa semántica, es interesante evaluar a DAX como language de consulta, y evitar pensar en DAX en los modelos y MDX en las consultas.

También puede ser una opción interesante tener SSAS Tabular detrás de nuestros reportes móviles, para mejorar tiempos de respuestas o aprovechar el poder de cálculo de DAX, reduciendo la complejidad en los reportes.

Los reportes móviles requieren acceder a los datos a través de “Shared Datasets”, estos pueden normalmente construirse con Report Builder o SSDT.

Para trabajar con DAX, vamos a necesitar trabajar los “Shared Datasets” con SSDT.

Los pasos para un reporte sencillo serían:

1- Definir conexión

dax-ssrs-mobile_1

2- Crear un “Shared Dataset”

Para trabajar con DAX debemos configurar el Dataset para comandos DMX de manera de ingresar el comando DAX.

dax-ssrs-mobile_2

Esto nos permite ingresar la sentencia DAX, y si necesitamos trabajar con parámetros, para este ejemplo inicial, ingresamos un DAX sencillo sin parámetros.

EVALUATE
SUMMARIZECOLUMNS (
     Product[Color],
     Geography[Country Region Name],
     'Product Category'[Product Category Name],
     "Total Sales", [Internet Total Sales],
     "Total Units", [Internet Total Units]
 )

dax-ssrs-mobile_3

Creado el Dataset debemos desplegarlo en el servidor de reportes.

dax-ssrs-mobile_4

3- Crear Reporte móvil

Para crear el reporte debemos utilizar la aplicación “Mobile Report Publisher”, e incorporar el Dataset construido con la sentencia DAX

dax-ssrs-mobile_5

Luego procedemos a construir el reporte, pudiendo quedar algo asi:

DAX-SSRS-MOBILE_6.PNG

 

 

 

 

 

DAX – ALLSELECTED – Porcentaje de…

DAX es un language de funciones, como tal tenemos muchas funciones interesantes, y como son tantas algunas pueden pasar o muchas, sin que sepamos como podemos utilizarlas.

En este caso voy a mostrarles una utilidad sencilla de la function ALLSELECTED, si bien es un función que requiere mirar en detalle, si les interesa pueden leer el excelente articulo de Alberto Ferrari en el link.

Dada una métrica sencilla de cantidad de Clientes que posee la siguiente sintaxis:

Customers:=CALCULATE( DISTINCTCOUNT( [CustomerKey] ))

genero una segunda medida que la denomino Customers %, y que me muestra el % de Clientes… Ahora que %?, bien ahí está lo interesante…

Customers %:=
DIVIDE
( 
  [Customers], 
  CALCULATE([Customers], ALLSELECTED())
)

Utilizando la función ALLSELECTED estamos indicando que elimine el contexto de filtro de las filas y columnas pero manteniendo los demás contextos de filtros y filtros explícitos.

Si seleccionamos estas métricas por ejemplo en una tabla de PowerBI veríamos lo siguiente

dax-allselected-1

Es decir, el total de clientes y como % el valor 100. Si agregamos columnas o filas que modifiquen el contexto veremos como funciona este ejemplo

dax-allselected-2

DAX ALLSELECTED 3.PNG

 

 

 

 

Datazen + SSAS Multidimensional (DAX)

Para construir un dashboard en Datazen que acceda a datos en el servidor de SSAS Multidimensional debemos escribir sentencias MDX, con ciertas características como podemos ver en el articulo Datazen + SSAS Multidimensional (con MDX).

Otra alternativa, que en pruebas reales me ha sido muy ágil de implementar es escribir sentencias de consultas DAX en lugar de MDX.

SSAS Multidimensional acepta instrucciones DAX de consulta desde SQL 2012 SP1, pero en las ediciones Business Intelligence y Enterprise.

Primero debemos definir en Datasen un Data Source a la base de datos de SSAS.

image

Luego debemos agregar un o mas Data Views en la conexión para ser utilizados en nuestro Dashboard Para armar las sentencias DAX recomiendo utilizar DaxStudio.

image

EVALUATE
(
    SUMMARIZE (
        ‘Internet Sales’,
        ‘Date'[Date.Key0],
        ‘Date'[Date.Value],
        Product[Category.Key0],
        Product[Category],
        Product[Subcategory.Key0],
        Product[Subcategory],
        "Sales", ‘Internet Sales'[Internet Sales Amount]
    )
)
ORDER BY ‘Date'[Date.Key0]

Una vez que armamos la sentencia DAX que nos devuelva los campos requeridos para nuestro tablero, la copiamos a la configuración del Data View

image

image

Ahora desde Datazen Publisher podemos armar el dashboard

image

image

image

DAX–Tabla de Parámetros para condicionar cálculos

A veces necesitamos que nuestras medidas calculadas varíen según parámetros seleccionados, como podrían ser diferentes tasas, representaciones numéricas, escenarios de análisis, conversiones de moneda.

Necesitamos armar una tabla con una única columna donde tendremos los valores de selección y aplicación, o una tabla con mas de un campo. Esta tabla no debe estar relacionada con otras tablas del modelo.

image   image

 

Creada e incorporada la tabla, debemos modificar nuestras medidas calculadas hacerlas sensibles a los valores seleccionados.

Tomando la siguiente expresión de ejemplo

Sales Amount:=SUM([SalesAmount])

 

Debemos modificar la expresión a la siguiente forma:

Sales Amount:= 
   
IF

       
HASONEVALUE
(Escala[Escala]); 
       
SUM([SalesAmount])/VALUES
(Escala[Escala]); 
       
SUM
([SalesAmount])
    )

 

Ahora si nuestra tabla de parámetros tiene mas de una columna la expresión debería tener la siguiente forma:

Sales Amount:= 
    IF
        HASONEVALUE(Escala[Factor]); 
        SUM([SalesAmount])*(1+VALUES(Escala[Factor]); 
        IF( NOT( FILTERED( Escala[Escala]) ),
           SUM([SalesAmountQuota]),
           BLANK()
        )
    )

Consideraciones para optimizar modelos Tabulares y PowerPivots

A continuación se enumeran algunos tips para “optimizar” el uso de memoria en los modelos tabulares o PowerPivot, en relación al uso de memoria RAM.

Importar solo las columnas útiles

Debido a que los datos son almacenados por columna, cada una de estas posee un costo, siendo mas caras aquellas que poseen una gran cantidad de valores diferentes, motivo por el cual es importante que al importar tablas en PowerPivot solo debemos incorporar aquellas columnas que utilizaremos en filtros, relaciones y cálculos.

Las columnas que podríamos excluir en la carga son:

  • Claves Primarias de tablas de hechos
  • Identificadores de transacciones en las tablas de hechos
  • GUIDs y columnas utilizadas para replicación o auditoria
  • Timestamp, Fechas de Creación y Modificación
  • Normalizar Columnas

    Evitar incorporar columnas que son cálculos de otras y armar medidas o campos calculados en el modelo. Si tenemos por ejemplo las columnas cantidad, precio, y monto total, esta ultima podríamos excluirla, incorporando una medida o campo calculado utilizando cantidad y precio de la forma

    Monto Total := SUMX( Ventas, Ventas[Precio] * Ventas[Cantidad])

    En este ejemplo Precio y Cantidad serian columnas con una mayor tasa de compresión o mayor probabilidad de repetición de valores que el producto de ambas, siendo el monto total calculado en el momento de la consulta.

    Dividir columnas de alta cardinalidad

    Si una columna posee demasiados valores diferentes, como podría ser un ID de transacción o  fecha y hora de un evento, si dividimos en la carga en varias columnas, por ejemplo fecha y hora en dos columnas, estas ocupan mucho menos memoria que el campo original conteniendo ambos valores.

    Reducir la precisión cuando es posible

    Reduciendo la precisión de una columna podríamos quizás reducir la cantidad de valores diferentes, por ende el uso de memoria.

    DAX – Jerarquías Padre-Hijo (Parte2)

    Al naturalizar una jerarquía Padre-Hijo con las funciones PATH, PATHITEM y LOOKUPVALUE debemos resolver la navegación, buscando evitar se muestren niveles vacíos cuando no hay mas descendientes.

    image

    Si estamos trabajando con Analysis Services Tabular podemos hacer uso del complemento BIDS Helper y configurar la propiedad HideMemberIf.

    Otra alternativa es agregar un poco de complejidad al campo calculado para lograr el efecto buscado. Esta opción es válida también para Power Pivot donde no tenemos la posibilidad de configurar la propiedad HideMemberIf.

    Primero debemos determinar el Nivel de cada elemento de la jerarquía con la función PATHLENGTH.

    =PATHLENGTH([Path])

    image

    Debemos crear dos campos calculados que serán utilizados por el campo calculado Monto.

    MaxNivel:=MAX(Organization[Nivel])

    BrowseNivel:=ISFILTERED(Organization[Level 0])+ISFILTERED(Organization[Level 1])+ISFILTERED(Organization[Level 2])+ISFILTERED(Organization[Level 3])

    Ahora debemos crear el campo calculado o medida condicionando cuando retornar un valor Nulo o un valor

    Monto:=IF( [BrowseNivel]> [MaxNivel];BLANK();SUM([Amount]))

     

    Antes
    image

    Después
    image