#tabular editor

Lo primero que necesitamos para realizar esta práctica es la external tool tabular editor. Podemos conseguir la version gratuita en el siguiente enlace: https://github.com/TabularEditor/TabularEditor/releases

A partir de ese momento la tendremos disponible para hacer muchas cosas contra nuestro modelo, pero en este post solo veremos el tema del análisis de buenas prácticas.

Para proceder a chequear nuestro modelo, simplemente vamos a la tab de External Tools y abrimos Tabular Editor.

Nota: Puede que aparezca un mensaje que las ediciones del modelo que hagamos aquí corren por nuestra propia responsabilidad. Pueden tener la tranquilidad que el producto lleva años de estabilidad y testing sin fallas críticas.

En el menú de herramientas o tools veremos el analizador que también puede ser accedido con F10

Una vez abierto el menú administraremos las reglas agregando por URL las del GitHub de la plataforma https://github.com/microsoft/Analysis-Services/tree/master/BestPracticeRules

NOTA: Recordemos usar la información cruda en modo RAW ingresando al archivo BPARules y dando click en raw para obtener la URL

En el menú que se incorpora ingresamos la URL antes mencionada

Esto incorporará un item a la administración de reglas. Al seleccionarla podemos ver cada detalle de reglas que analizará por si queremos desactivar alguna.

Del mismo modo podemos incorporar las reglas en español. Si vemos bien el Github de reglas hay una carpeta spanish. Entonces repetimos el proceso con la URL de la RAW data del archivo BPARules.json y obtendremos el análisis en español:

De este modo podremos analizar más detalladamente que las mejores prácticas de modelado estén aplicadas en nuestro modelo y aquellas advertencias o errores por no completarlos, sean expresados en español. Al dar “Ok”, podremos apreciar las reglas que se expanden como una matriz con +/- para reflejar la columna, tabla o item que rompe la regla. 

A partir de ese punto queda en cada persona leer el informe y tomar decisiones sobre la modificación de cada hallazgo encontrado por el analizador.

Tabular Editor

Tabular Editor es una herramienta que le permite manipular y administrar fácilmente medidas, columnas calculadas, carpetas de visualización, perspectivas y traducciones en los modelos de Analysis Services Tabular y Power BI XMLA (desde el nivel de compatibilidad 1200 en adelante). Esto quiere decir que podemos hacer mucho más que lo que mostraremos en este post con la herramienta.

Scripts en C#

La herramienta nos brinda la posibilidad de ejecutar código C# que podemos utilizar de distintos modos para ayudarnos en la eficiencia de operaciones de DAX, como por ejemplo: Crear muchas medidas con un mismo patrón, modificar más de una medida al mismo tiempo, etc.

El script que hacemos puede correr de distintos modos según lo deseamos. Entonces lo primero que necesitamos entender son dos sentencias:

Model: representa el modelo actual conectado. Corre nuestro script contra todo el modelo.

Selected: refleja la operación para la selección actual que tengamos sobre los componentes de la UI en tabular editor. Necesitamos seleccionar por lo menos un elemento de la lista de la izquierda para que funcione (podemos seleccionar varios con Ctrl)

En redundancia, podemos armar scripts que ejecuten contra todo el modelo o simplemente seleccionar medidas o columnas que nos ayuden a ejecutar cierto código.

Ahora veamos unas funciones comunes de interés:

Output() la función para retornar lo deseado, es ideal para debugging

CustomAction() corre otro script que fue grabado previamente como acción personalizada

FormatDax llama a DAXFormatter.com para formatear el código de respuesta

ConvertDax() convierte separadores/decimales entre UK/US

ExportProperties() exporta propiedades de objetos a una tab separada

ImportProperties() importa propiedades de objetos a una tab separada

Éstos métodos nos ayudarán a modo informativo o para complementar nuestro código. Lo último que necesitamos saber antes de comenzar a escribir nuestro código es que lo que vamos a correr esta muy relacionado con linq. Para aquellos familiarizados con este modo de querear una BD dentro de C# puede resultarles más sencillo.

Manos a la obra

Iniciaremos nuestro camino de práctica haciendo una query que nos traiga un resultado, no vamos a hacer cambios o creación la primera vez, siempre es bueno ir familiarizándose.

Veamos como hacer una consulta para saber que medidas tiene un nombre semejante al texto parametrizado (color) > Model.AllMeasures.Where(m => m.Name.Contains("Color")).Output();

Veamos que vamos a ejecutar sobre Todo el modelo apuntando a Todas las Medidas donde el nombre tenga la palabra Color. Al final agregamos Output() para conocer esas medidas que podrían ser aquellas a editar o simple análisis.

La respuesta incluye una descripción de propiedades de la medida para conocer más detalladamente su información. Esto también nos enseña que podríamos buscar otra cosa además del nombre de la medida como por ejemplo si la Expresión de DAX contiene una palabra

Consultar las medidas cuya expresión tenga la palabra calculate: > Model.AllMeasures.Where(m => m.Expression.Contains("CALCULATE")).Output();

De ese modo pudimos encontrar todas las medidas que tienen ciertos patrones lo cual sería muy importante si necesitamos cambiar masivamente contra todo el modelo una determinada línea.

También podemos hacer otras operaciones sencillas como:

Renombrar la selección > Selected.Measures.Rename("Amount", "Value");

Prestemos atención a esta operación que inició de otro modo. No vamos a correrlo contra todo el modelo sino la selección. Entonces definimos Selected.Measures.[Operación]. De ese modo cambiamos el texto de una medida por otro. Muy util si tenemos muchas bajo un patron como YTD que necesitamos representar como YearToDate. Veamos como la foto tiene seleccionada la medida al ejecutar.

De este modo vamos viendo como escribir sobre nuestro modelo. Ahora si nos gusta esto podríamos inclusive crear medidas aquí directamente sin usar el motor de Power Bi Desktop de la siguiente manera:

Crear una medida con sus detalles más importantes > var nuevita = Model.Tables["InternetSales"].AddMeasure("TabMedida", "SUM(InternetSales[Unit Cost])"); > nuevita.FormatString ="$#0.0"; > nuevita.Description = "Nuevita creada por tabular editor";

En esta oportunidad vemos que Model no solo ejecuta contra la totalidad sino que también podemos enfocarlo sobre una tabla en particular como ejemplo “InternetSales”. Esta sería la mejor práctica en un desarrollo de Power Bi donde anotamos no solo el código DAX sino su formato y descripción correspondiente.

Ahora vamos a saltar a algo más complejo y muy atractivo. Sabiendo que podemos consultar el modelo para obtener un determinado conjunto de medidas, nos vamos a aprovechar de eso para ejecutar una transformación solo en esa lista que se determinó con código para ser más eficiente y no estar ejecutando contra todas las medidas del modelo.

Change text where text like (adding comment) foreach ( var medida in Model.AllMeasures.Where(m => m.Expression.Contains("SWITCH(_Units")) ) {    medida.Expression = medida.Expression.Replace("SWITCH(_Units","--Changing units \nSWITCH(_Units"); }

Esta función nos ayudaría a hacer un reemplazo masivo en la Expresión DAX de las medidas cambiando el texto “SWITCH(_Units” por “--Changing units \nSWITCH(_Units". De ese modo agregaríamos un comentario en todas las expresiones que tengan un switch de la variable __units dentro de su expresión DAX. Todo esto con ayuda de un for que recorra cada medida devuelta por nuestra consulta a la cual le asignamos una Expression que sea su Expression.Replace. Esto nos muestra que también podemos ejecutar contra un único item especifico además del modelo y lo seleccionado pero de un modo un poco más completo. Así mismo vemos una de las funciones más potentes e importantes que es Replace.

No todo se trata de modificar y reemplazar sino también de crear. La creación de medidas nuevas puede ser muy atractivo cuando comenzamos un modelo nuevo y sobre todo si estamos en la industria de Retail. Podemos usar código para crear muchas medidas SUM ( Tabla[Columna] ) seleccionando las columnas que nos gustaría que tengan medidas simples como esa. Una vez que todas esas tienen medida podríamos hacerlo mismo para generar medidas como Año Anterior (LY) o YTD. Vamos a ver esta ultima opción en el ejemplo porque es más completa pero podríamos hacerlo con la selección de columnas para los SUM sencillos.

Crear medidas de Time Intelligence para LY y YTD. Aprovechamos para formatear el nuevo código DAX. Obviamente necesitamos tabla fecha. var dateColumn = "'TablaFecha'[Fecha]" foreach (var m in Selected.Measures) {     //YTD     m.Table.AddMeasure(         m.Name + " YTD",         "TOTALYTD(" + m.DaxObjectName + ", " + dateColumn + ")"     );     //LY     m.Table.AddMeasure(     m.Name + " LY",     FormatDax( "CALCULATE(" + m.DaxObjectName + "," +          "SAMEPERIODLASTYEAR(" + dateColumn + "))" )     ); }

En este caso vamos a recorrer las medidas seleccionadas para agregar dos medidas a cada una de ellas. Fijense que volvemos a usar AddMeasure como vimos antes pero de este modo se crearía en más de una oportunidad. Nos aprovechamos de DaxObjectName y Name para parametrizar el nombre de la medida y el objeto interno de la expresión que daría vida a las nuevas medidas bien ejecutadas para cada uno. Notese que la segunda medida incorpora FormatDax para que nuestra medida salga formateada como DAXFormatter lo haría. Si bien nosotros podríamos agregar \n para saltos de línea o \t para tabulaciones, es más simple llamar al formateador.

Al terminar nuestro trabajo y todas las ejecuciones debemos dar en el siguiente botón para que nuestros scripts se hagan reales en nuestro modelo de Power Bi Desktop, AAS, etc.

En esta oportunidad vamos a valernos de una herramienta que se conecta al modelo de Power Bi y puede salir en el menú de external tools como explique en un post anterior. Tabular Model, entre varias características, puede ser muy cómodo para explorar y escribir código DAX. Ésta sencilla herramienta nos ayudará con la magia. Si no tiene la tienen configurada como external tool es probable que necesiten generar un archivo pbit template de su desktop para conectarse. Recomiendo configurar su Power Bi Desktop para permitir las external tools.

A partir de ese momento podremos utilizar Tabular Editor. Dentro del mismo vamos a la tabla deseada y la medida que necesitamos analizar. Podemos seleccionarla y tocar el shortcut F3 o click derecho para el menú de dependencias:

Esta opción nos devuelve una pequeña ventana con dos opciones disponibles. 

1- Show objects on which [Medida] depend: con esta opción activa vamos a ver todas las medidas que se calcularon para llegar a la que estamos viendo. Por ejemplo, la siguiente imagen muestra que para calcular Venta promedio, se utilizaron las medidas monto venta y count of sales. Dentro de ellas las columnas involucradas para sus cálculos. Así sucesivamente todas las que se hayan utilizado.

2- Show objects that depend on [Medida]: ésta otra opción hará el camino contrario. Nos va a mostrar todas las medidas que utilizan la medida en la que nos posicionamos. Por ejemplo la medida Monto de Venta luego es utilizada por dos medidas, rentabilidad y venta promedio.

Gracias a la vista de dependencias de Tabular editor podremos seguir el hilo de las medidas creadas. La herramienta es de suma utilidad para dar soporte a medidas en grandes modelos como así también para comprender cálculos de un modelo que no hayamos creados.