Scripts de CI/CD para la CLI de Tabular Editor 2

La CLI de Tabular Editor 2 se usa a menudo en canalizaciones de CI/CD (Azure DevOps, GitHub Actions, etc.) para realizar determinadas acciones relacionadas con modelos tabulares, como ejecutar un análisis de prácticas recomendadas, realizar una comprobación de esquema o implementar el modelo en un servidor de destino. La CLI se diseñó en 2016, antes de que Power BI e incluso Azure Analysis Services se popularizaran, y esto, por desgracia, significa que la CLI se ha quedado algo desfasada en lo que respecta a funcionalidades de modelos tabulares incorporadas más adelante, especialmente en lo relativo a los Data sources implícitos introducidos por Power BI. La buena noticia es que la CLI de TE2 se puede usar para ejecutar C# Scripts personalizados, y esto convierte la CLI en una herramienta muy flexible que nos permite sortear algunas de las limitaciones de sus casos de uso nativos.

En este artículo, presentamos C# Scripts que puedes ejecutar como parte de la CLI de TE2 para realizar diversas comprobaciones y modificaciones de metadatos del modelo. Estos son:

• Comprobaciones de esquema: Usamos un C# Script para sortear las limitaciones de la opción nativa -SCHEMACHECK de la CLI de TE2.
• Actualizar referencias de Data sources: Usamos un C# Script para cambiar sobre la marcha la configuración de conexión de los Data sources antes de implementar.
• Añadir miembros del rol: Usamos un C# Script para añadir sobre la marcha miembros a roles de seguridad antes de implementar.

Comprobaciones de esquema

Una comprobación de esquema es útil durante la validación de compilación, por ejemplo, cuando se envía una Pull Request, para asegurarse de que las columnas importadas en el modelo tabular coinciden con el esquema (nombres de columna y tipos de datos) de la tabla o vista de origen. Si se eliminó o renombró una columna en el origen, pero la columna correspondiente no se actualiza en el modelo tabular, es probable que esto provoque un error cuando el modelo se actualice posteriormente.

En SQL Server 2016 Analysis Services, solo teníamos una opción para las particiones de tabla: heredado (SQL). Sin consultas M, orígenes de datos estructurados, Data sources implícitos, etc. Cada tabla tenía una o más particiones heredadas que contenían una consulta SQL que se ejecutaba contra el Data source para devolver el conjunto de datos que debía cargarse en esa partición. Esto significaba que podíamos realizar una comprobación de esquema en Tabular Editor simplemente abriendo una conexión al mismo origen, ejecutando la consulta SQL especificada en la partición (incluso podíamos establecer el indicador CommandBehavior.SchemaOnly para indicar que no nos importan los datos, solo el esquema) y, después, comparando el esquema resultante con las columnas ya importadas en el modelo.

Una comprobación de esquema en la CLI de Tabular Editor 2, con salida de registro al estilo de GitHub Actions.
Por desgracia, hoy en día la mayoría de los modelos tabulares no usan los Data sources heredados con particiones SQL para definir los datos que se cargan en el modelo. En su lugar, con la proliferación de Power BI, los orígenes de datos implícitos son ahora la norma, lo que significa que el Data source y la consulta se definen mediante una expresión M. Esto significa que, para realizar una comprobación de esquema, habría que analizar de algún modo la expresión M, extrayendo detalles sobre el Data source y también sobre el objeto del Data source (tabla/vista), y además averiguar las consecuencias de cualquier transformación de datos especificada en la expresión M. Y esto sin entrar en el hecho de que el lenguaje M y Power BI admiten varios cientos de Data sources distintos, no solo bases de datos relacionales. Naturalmente, esto queda muy fuera del alcance de Tabular Editor 2, que es un proyecto de código abierto desarrollado por afición. Tabular Editor 3, por otro lado, sí incluye un analizador de M, pero esto no ayuda a las canalizaciones de CI/CD que dependen de la CLI de TE2.

Para que quede claro, la funcionalidad nativa -SCHEMACHECK de la CLI de TE2 solo admite Data sources heredados con particiones que especifican una consulta SQL.

C# Scripts al rescate

Como se ha mencionado antes, hay una solución alternativa: podemos ejecutar C# Scripts como parte de la CLI de TE2. A continuación, vamos a usar un script de C# para convertir cualquier expresión M del modelo en particiones SQL heredadas, que sí son compatibles con la funcionalidad de comprobación de esquema integrada en TE2.

Sin embargo, para que este script funcione, necesitamos hacer algunas suposiciones:

• Un único origen SQL: el modelo tabular solo importa datos desde un único origen de datos de tipo SQL (por ejemplo, una base de datos de SQL Server en local, una Azure SQL Database o un Synapse SQL Pool). En teoría, el script debería funcionar con otros Data sources relacionales que admitan conexiones OLE DB, pero no está probado.
• Sin transformaciones: no hay transformaciones de datos en las expresiones M dentro del modelo. Los datos se importan 1:1 desde tablas/vistas en el origen SQL, tal y como recomiendan las prácticas habituales (se permiten filtros en las expresiones M, pero el esquema resultante de cualquier transformación en M debe corresponder al esquema sin procesar de la tabla/vista).
• Acceso y autenticación: el Data source SQL es físicamente accesible desde la máquina que ejecuta la CLI de TE2. Además, dado que el script se va a ejecutar como parte de una canalización automatizada, debe estar disponible un Service Principal o una cuenta de usuario/contraseña de SQL con los permisos de lectura necesarios sobre la base de datos SQL.

El script consta de tres partes:

1. Crear un Data source (heredado) con los detalles de conexión que Tabular Editor usará para conectarse al Data source.
2. Recorrer todas las tablas que definen su contenido mediante una expresión M (pueden ser tablas con una o más particiones M, o tablas que tengan una política de actualización incremental) y sustituir sus particiones por una partición heredada (SQL).
3. Realizar la comprobación de esquema.

En la parte 2, usamos una expresión regular (regex) para extraer el esquema y el nombre de la tabla/vista a partir de las expresiones M. Esto asume que una expresión M en una partición tiene este aspecto, que muy probablemente será el caso si se cumplen las suposiciones anteriores:

let
    Source = Sql.Databases(Server),
    AdventureWorksDW = Source{[Name="AdventureWorksDW"]}[Data],
    dbo_Customer = AdventureWorksDW{[Schema="dbo",Item="Customer"]}[Data]
in
    dbo_Customer

En concreto, usamos la regex para obtener los valores de Schema e Item de la línea 4: “dbo” y “Customer” en este caso (por cierto, gracias a ChatGPT por ayudarme a escribir la RegEx).

A continuación tienes el script completo. Después de indicar tus propios valores para las 4 variables al principio del script (servidor, base de datos, ID de la aplicación y secreto del service principal), puedes ejecutar el script directamente desde la interfaz de Tabular Editor 2, y el resultado de la comprobación de esquema se mostrará de forma similar a la captura anterior. Pulsa CTRL+Z después de ejecutar el script para deshacer las modificaciones de metadatos del modelo que haya realizado.

using System.Text.RegularExpressions;
 
var server = "te3synapsetest-ondemand.sql.azuresynapse.net";
var database = "AdventureWorksDW";
var applicationId = "e449df3a-957e-40ff-802f-e3fa75e43be1";
var secret = "<your-service-principal-secret-key>";

// Part 1: Create legacy data source
var source = Model.AddDataSource();
source.Provider = "System.Data.OleDb";
source.ConnectionString = string.Format("Provider=MSOLEDBSQL;Data Source={0};Initial Catalog={1};Authentication=ActiveDirectoryServicePrincipal;User ID={2};Password={3}",
    server, database, applicationId, secret);

// Part 2: Swap partitions
string mExpression;

foreach(var table in Model.Tables)
{
    // Loop through all tables that have at least one M partition, or an M Source Expression:
    var partition = table.Partitions.FirstOrDefault() as MPartition;
    if (partition != null) mExpression = partition.MExpression;
    else if (!string.IsNullOrEmpty(table.SourceExpression)) mExpression = table.SourceExpression;
    else continue;

    // Extract the schema and name of the source table/view from the M Expression:
    string pattern = @"\[Schema=""(?<schema>.*?)"",Item=""(?<item>.*?)""\]";
    Match match = Regex.Match(mExpression, pattern);
    var sourceObjectName = string.Format("[{0}].[{1}]", match.Groups["schema"], match.Groups["item"]);
    
    // Create a single legacy SQL partition with a "SELECT * FROM <sourceObjectName>" query:
    var legacyPartition = table.AddPartition(query: "SELECT * FROM " + sourceObjectName);
    legacyPartition.DataSource = source;
    
    // Delete all other partitions:
    foreach(var p in table.Partitions.Where(p => p != legacyPartition).ToList()) p.Delete();
}

// Part 3: Perform schema check
SchemaCheck(Model);

Si prefieres usar autenticación de usuario/contraseña de SQL, sustituye las primeras 12 líneas del script por lo siguiente:

using System.Text.RegularExpressions;

var server = "te3synapsetest-ondemand.sql.azuresynapse.net,1433";
var database = "AdventureWorksDW";
var userId = "<your-sql-username>";
var password = "<your-sql-password>";

// Part 1: Create legacy data source
var source = Model.AddDataSource();
source.Provider = "System.Data.SqlClient";
source.ConnectionString = string.Format("Server={0};Initial Catalog={1};Persist Security Info=False;User ID={2};Password={3};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;",
    server, database, userId, password);

...

También es posible usar un token de acceso para la autenticación:

using System.Text.RegularExpressions;

var server = "te3synapsetest-ondemand.sql.azuresynapse.net";
var database = "AdventureWorksDW";
var accessToken = "<your-access-token>";

// Part 1: Create legacy data source
var source = Model.AddDataSource();
source.Provider = "System.Data.OleDb";
source.ConnectionString = string.Format("Provider=MSOLEDBSQL;Data Source={0};Initial Catalog={1};Access Token={2}",
    server, database, accessToken);

...

Si ejecutas el script como parte de una canalización de CI/CD, quizá te convenga leer los detalles de conexión desde variables de entorno, en lugar de dejarlos codificados en el script. Por suerte, en C# es bastante sencillo:

using System.Text.RegularExpressions;

var server = Environment.GetEnvironmentVariable("server");
var database = Environment.GetEnvironmentVariable("database");
var applicationId = Environment.GetEnvironmentVariable("applicationid");
var secret = Environment.GetEnvironmentVariable("secret");

// Part 1: Create legacy data source
...

Para ejecutar un C# Script desde la línea de comandos, usa la sintaxis que se muestra a continuación. Esto asume que los metadatos del modelo están en “c:\path\to\model.bim” y que el script que quieres ejecutar está en “c:\path\to\schemacheck.cs”. Ten en cuenta que puedes especificar varios archivos de script en la CLI para que se ejecuten uno detrás de otro.

start /wait TabularEditor.exe "c:\path\to\model.bim" -S "c:\path\to\schemacheck.cs" -G

start /wait TabularEditor.exe "c:\path\to\model.bim" -S "c:\path\to\schemacheck.cs" -V

Como se puede ver en la captura siguiente, la comprobación de esquema se realiza a través del script, igual que si se hubiera especificado el conmutador -SC / -SCHEMACHECK, pero con la ventaja de que funciona con Data sources implícitos. La misma técnica debería funcionar para modelos que usan orígenes de datos estructurados, con las mismas suposiciones que antes.

Actualizar referencias de Data sources

Al implementar un modelo de forma automatizada, es bastante habitual que sea necesario actualizar las referencias de Data sources dentro del modelo. Por ejemplo, los metadatos del modelo en el control de código fuente pueden apuntar a un Data source de desarrollo o pruebas, mientras que al implementar el modelo en producción también debemos asegurarnos de que el modelo referencia el Data source de producción.

Para Data sources heredados (de proveedor), la CLI de TE2 tiene una funcionalidad nativa para actualizar la cadena de conexión del Data source directamente desde la CLI. Sin embargo, esto no es suficiente para Data sources estructurados o implícitos, que no especifican explícitamente una cadena de conexión.

Por suerte, esto es fácil de resolver con un C# Script. Además, es un enfoque muy flexible, ya que el C# Script tiene acceso a todo el Tabular Object Model. Por ejemplo, si usas una expresión M compartida (también conocida como parámetro en Power BI Desktop) para guardar el nombre o la dirección de tu servidor, cambiar esa expresión M es tan sencillo como esta línea:

var server = "te3synapsetest-ondemand.sql.azuresynapse.net";

// This assumes the existence of a Shared M Expression with the name "Server":
Model.Expressions["Server"].Expression = string.Format("\"{0}\" meta [IsParameterQuery=true, Type=\"Text\", IsParameterQueryRequired=true]", server);

Como alternativa, si cada una de tus particiones M referencia un determinado nombre de servidor, puedes escribir un bucle corto para sustituir el nombre en todas:

foreach(var partition in Model.AllPartitions.OfType<MPartition>())
{
    partition.Expression = partition.Expression.Replace("\"old-server-name\"", "\"new-server-name\"");
}

O bien, para Data sources heredados u orígenes de datos estructurados, simplemente edita directamente las propiedades del Data source:

(Model.DataSources["MyDataSource"] as StructuredDataSource).Server = "new-server-address";

Igual que antes, podemos usar Environment.GetEnvironmentVariable("<environment-variable-name>") en cualquier parte del script para leer una cadena de texto desde una variable de entorno, en lugar de codificar valores directamente en el propio script.

Para ejecutar un script al vuelo antes de implementar el modelo, usa la siguiente sintaxis de comando:

start /wait TabularEditor.exe "c:\path\to\model.bim" -S "c:\path\to\script.cs" -D <target-server> <target-database> -O -C

<target-server> es el nombre del servidor SSAS o Azure AS, o el punto de conexión XMLA de Power BI. También puede ser una cadena de conexión MSOLAP completa, incluidas las credenciales del Service Principal, lo cual sería necesario para ejecuciones desatendidas de la CLI.

<target-database> es el nombre de la base de datos (Dataset) que se va a implementar. Usa -O para indicar explícitamente que está bien sobrescribir una base de datos ya existente.

Para Data sources de proveedor (heredados) u orígenes de datos estructurados, es importante especificar el conmutador -C como parte del comando de implementación, tal y como se muestra arriba. De lo contrario, el Data source con sus propiedades (actualizadas) no se implementará. Consulta la documentación de la línea de comandos para ver una descripción más detallada de los distintos conmutadores utilizados.

Añadir miembros del rol

Otro requisito habitual es asignar distintos miembros a roles de seguridad en el modelo, en función del entorno en el que se implemente. Una opción es no especificar los conmutadores -R / -ROLES y -M / -MEMBERS en el comando que implementa el modelo, lo que dejaría todos los roles/miembros de rol tal y como están. Pero esto asume que ya existe en el destino de implementación una base de datos implementada con los roles/miembros de rol correctamente configurados. A veces, es más práctico asignar miembros de rol como parte de la canalización de implementación.

Una vez más, esto se hace fácilmente con un C# Script. A continuación se muestra cómo añadir un grupo de Entra ID (antes conocido como “grupo de AD”) a un rol:

var roleName = "Reader";
var groupId = "2a73590e-cf03-4db8-a8b3-7e639c0ca4de";
var tenantId = "ddec87fe-da10-4d12-990a-770ca3eb6226";

var memberName = string.Format("obj:{0}@{1}", groupId, tenantId);
Model.Roles[roleName].AddExternalMember(memberName);

Para añadir un usuario concreto, proporciona su UserPrincipalName en el script:

var memberName = "daniel@test.com"
Model.Roles[roleName].AddExternalMember(memberName);

Esta técnica, por supuesto, se puede personalizar de muchas formas; por ejemplo, usando variables de entorno o consultando una lista de usuarios desde un archivo, una base de datos, etc. El límite lo pones tú.

Al realizar una implementación mediante la CLI y, al mismo tiempo, ejecutar este script, asegúrate de especificar tanto los conmutadores -R como -M para garantizar que se implementen los miembros de rol recién añadidos.

start /wait TabularEditor.exe "c:\path\to\model.bim" -S "c:\path\to\script.cs" -D <target-server> <target-database> -O -R -M

Conclusión

Como hemos visto en este artículo, la CLI de TE2 se puede usar para ejecutar C# Scripts, ya sea de forma puntual o como parte de una implementación. Esto ofrece una gran flexibilidad para configurar canalizaciones automatizadas que validen o implementen metadatos del modelo tabular con pequeñas modificaciones, algo que a menudo se necesita en escenarios empresariales, donde hay que trabajar con múltiples entornos, etc.

¡Esperamos que te haya gustado este artículo! No dudes en hacer preguntas o proponer ideas de scripts adicionales en la sección de comentarios de abajo.