Power BI – Próximas características

Durante el pasado evento “Microsoft Business Application Submit” se enumeraron capacidades a incorporar en Power BI, el detalle de estos anuncios fueron publicados en el documento “October ’18“, a continuación algunos puntos mas que interesantes que estarían disponible entre Octubre-2018 y Marzo-2019. Formato basado en expresiones, podremos configurar diferentes elementos de las visualizaciones utilizando expresiones […]

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 – Jerarquías Padre-Hijo (Parte 1)

Analysis Services Tabular y Power Pivot no poseen un soporte directo a relaciones padre-hijo, sino que exponen una serie de funciones que permiten naturalizar una jerarquía Padre-Hijo.

Vamos a trabajar con la siguiente tabla, Organization de AdventureWorksDW:

image

Lo primero es generar una columna calculada, con el camino de los nodos, desde el principal, para esto debemos utilizar la función PATH de DAX

=PATH([OrganizationKey];[ParentOrganizationKey])

Esto nos agrega una nueva columna a la tabla con los nodos separados por |

image

Ahora necesitamos armar tantas columnas como niveles de nuestra jerarquía, si esto nos obliga a definir una profundidad fija de navegación. Para estas columnas calculadas utilizaremos la función PATHITEM

=PATHITEM([Path];1;INTEGER)

=PATHITEM([Path];2;INTEGER)

=PATHITEM([Path];4;INTEGER)

Estas columnas tendrán el valor correspondiente a cada nivel para ese registro, es decir, nos devuelve el valor entre | para la posición indicada.

image

Ahora tenemos que utilizar una segunda función DAX para reemplazar en la misma o una nueva columna el valor del nivel obtenido por la descripción correspondiente. Utilizaremos la función LOOKUPVALUE.

=LOOKUPVALUE([OrganizationName];[OrganizationKey];PATHITEM([Path];1;INTEGER))

=LOOKUPVALUE([OrganizationName];[OrganizationKey];PATHITEM([Path];4;INTEGER))

image

Ahora resta configurar una jerarquía con los niveles definidos

image

xEvents con Analysis Services

Los Eventos Extendidos de SQL Server permiten capturar datos del mismo a través de estos y son el reemplazo a las trazas. Ofrecen un menor uso de recursos que su equivalente anterior. SSAS también permite la utilización de Eventos Extendidos para capturar la misma información que vemos a través del Profiler y las DMVs.

Iniciar un Evento Extendido

Para activar un evento debemos ejecutar un XMLA como el siguiente:

<Create xmlns=http://schemas.microsoft.com/analysisservices/2003/engine

xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance

xmlns:ddl2=http://schemas.microsoft.com/analysisservices/2003/engine/2

xmlns:ddl3=http://schemas.microsoft.com/analysisservices/2003/engine/3

xmlns:ddl100_100=http://schemas.microsoft.com/analysisservices/2008/engine/100/100

xmlns:ddl200_200=http://schemas.microsoft.com/analysisservices/2010/engine/200/200

xmlns:ddl300_300=http://schemas.microsoft.com/analysisservices/2011/engine/300/300>

    <ObjectDefinition>

          <Trace>

               <ID>xEvent_SSAS_Trace</ID>

               <Name>xEvent_SSAS_Trace</Name>

               <AutoRestart>true</AutoRestart>

               <ddl300_300:XEvent>

                     <event_session name=xeas dispatchLatency=1 maxEventSize=4 maxMemory=4

                      memoryPartitionMode=none eventRetentionMode=allowSingleEventLoss

                      trackCausality=true>

                           <event package=AS name=QueryCubeEnd />

                           <target package=Package0 name=event_file>

                               <parameter name=filename value=H:\Files\Trace\xEvent_SSAS_Trace.xel/>

                               <parameter name =max_file_size value=10/>

                               <parameter name =max_rollover_files value=15/>

                               <parameter name =increment value=1/>

                           </target>

                     </event_session>

               </ddl300_300:XEvent>

          </Trace>

    </ObjectDefinition>

</Create>

 

Este XMLA posee algunas propiedades que debemos ajustar a nuestra necesidad:

  • AutoRestart: Indica al SSAS que el evento debe reiniciarse automáticamente al reiniciar el servicio.
  • Event: Debemos agregar tantos Event como tipos de eventos necesitemos capturar.

Debemos tener cuidado con la cantidad y tipos de eventos que pidamos capturar pues puede afectar la performance del SSAS y/o generar muchísimos datos 

    • Filename: podemos indicar la ruta y nombre de archivo a generar.
    • Max_File_Size: podemos indicar en MB el tamaño máximo del archivo generado. Cuando alcance ese valor generar un nuevo archivo.
    • Max_Rollover_Files: cantidad de archivos a mantener.

 

Detener un Evento Extendido

<Delete xmlns=http://schemas.microsoft.com/analysisservices/2003/engine>

    <Object>

          <TraceID>xEvent_SSAS_Trace</TraceID>

    </Object>

</Delete>

 

Consultar Eventos Extendidos

Para ver los eventos ejecutándose en el SSAS debemos consultar la siguiente DMV

SELECT

    *

FROM

    $system.discover_traces

 

Visualizar Eventos Extendidos

Los archivos XEL pueden abrirse desde el Management Studio para su revisión, es suficiente con hacer doble click sobre el archivo y visualizarlos. Ahora si necesitamos levantar estos debemos utilizar la funcion de SQL sys.fn_xe_file_target_read_file.

SELECT

    xe.TraceFileName,

    xe.TraceEvent,

    xe.EventDataXML.value(‘(/event/data[@name=”ServerName”]/value)[1]’,‘varchar(50)’) AS ServerName,

    xe.EventDataXML.value(‘(/event/data[@name=”DatabaseName”]/value)[1]’,‘varchar(50)’) AS DatabaseName,

    xe.EventDataXML.value(‘(/event/data[@name=”NTCanonicalUserName”]/value)[1]’,‘varchar(50)’) AS NTCanonicalUserName,

    xe.EventDataXML.value(‘(/event/data[@name=”ConnectionID”]/value)[1]’,‘int’) AS ConnectionID,

    xe.EventDataXML.value(‘(/event/data[@name=”RequestID”]/value)[1]’,‘varchar(50)’) AS RequestID,

    xe.EventDataXML.value(‘(/event/data[@name=”CurrentTime”]/value)[1]’,‘datetime’) AS CurrentTime,

    xe.EventDataXML.value(‘(/event/data[@name=”StartTime”]/value)[1]’,‘datetime’) AS StartTime,

    xe.EventDataXML.value(‘(/event/data[@name=”EndTime”]/value)[1]’,‘datetime’) AS EndTime,

    xe.EventDataXML.value(‘(/event/data[@name=”Duration”]/value)[1]’,‘bigint’) AS Duration,

    xe.EventDataXML.value(‘(/event/data[@name=”ObjectPath”]/value)[1]’,‘varchar(max)’) AS ObjectPath

FROM

    (

        SELECT

            [FILE_NAME] AS TraceFileName,

            OBJECT_NAME AS TraceEvent,

            CONVERT(XML,Event_data) AS EventDataXML

        FROM

            sys.fn_xe_file_target_read_file(‘H:\BCR\Files\Trace\*.xel’, NULL, NULL, NULL)

    ) xe

Olap PivotTable Extensions

Olap PivotTable Extensions como lo indica su nombre agrega algunas características interesantes a las tablas dinámicas de Excel, útiles cuando estas trabajan conectadas a Analysis Services.
OE1

Algunas características:

Visualizar MDX

Podemos visualizar el MDX que genera la tabla dinámica, y permite su formateo.

imageimage

Buscar

Permite buscar en la metadata como también en los datos de las dimensiones.

image

Filtrar

Permite filtrar valores por ingresar valores en lugar de buscar y seleccionar individualmente.

image

Limpiar Cache

Esta característica permite borrar los datos de la tabla dinámica, de manera que al distribuir la misma, el destinatario solo vea los datos que tiene permisos.

imageimage

Otras características

  • Ver el mensaje de error detrás de #Value!
  • Elegir campos a mostrar
  • Deshabilitar Auto Refresh en PowerPivot
  • Miembros calculados