TabuLynx.Query.Executor 0.7.0

TabuLynx.Query.Executor

A powerful .NET library for executing multiple types of queries against Tabular Models using Microsoft's ADOMD.NET client and XMLA (XML for Analysis) protocol. Execute DAX queries, DMV (Dynamic Management Views), MDX, and other Analysis Services queries seamlessly across Power BI, SQL Server Analysis Services (SSAS), and Microsoft Fabric.

✨ Features

• ADOMD.NET Integration: Leverages Microsoft's Analysis Services ADOMD.NET client library for robust connectivity
• XMLA Protocol Support: Execute queries via XMLA (XML for Analysis) endpoints with support for multiple query languages:
  • DAX (Data Analysis Expressions): Business logic calculations and data analysis
  • DMV (Dynamic Management Views): System metadata and schema information queries
  • MDX (Multidimensional Expressions): OLAP cube queries and calculations
  • TMSL (Tabular Model Scripting Language): Model management and administrative operations
• Multi-Platform Support: Connect to various Analysis Services platforms:
  • Local Power BI Desktop instances
  • SQL Server Analysis Services (SSAS) servers
  • Microsoft Fabric workspaces
  • Power BI Premium/PPU workspaces
• Dependency Injection Ready: Built-in support for .NET dependency injection
• Azure Authentication: Integrated Azure Active Directory authentication for cloud scenarios
• Flexible Configuration: Configuration-driven connection management

📦 Installation

dotnet add package TabuLynx.Query.Executor

🏗️ Dependencies

This package depends on the following NuGet packages:

• Microsoft.AnalysisServices.AdomdClient - Core ADOMD.NET client library
• Microsoft.AnalysisServices - Analysis Services management objects
• Azure.Identity - Azure authentication library
• Microsoft.Extensions.Options - Configuration options pattern
• TabuLynx.Core - Core TabuLynx functionality
• TabuLynx.Discovery.PowerBI - Power BI instance discovery

🚀 Usage Scenarios

1. Local Power BI Desktop

Execute DAX queries against a local Power BI Desktop instance while a report is open.

Configuration (appsettings.json):

{
  "TabuLynx": {
    "ConnectionString": "Provider=MSOLAP;Data Source=localhost:{port};"
  }
}

Console Application:

using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using TabuLynx.Core.Configuration;
using TabuLynx.Core.Interfaces;
using TabuLynx.Query.Executor;

var host = Host.CreateDefaultBuilder(args)
    .ConfigureAppConfiguration(config =>
    {
        config.AddJsonFile("appsettings.json", optional: false, reloadOnChange: true);
    })
    .ConfigureServices((context, services) =>
    {
        services.Configure<TabuLynxOptions>(context.Configuration.GetSection("TabuLynx"));
        services.AddAdomdQueryExecutorForLocalPowerBI();
    })
    .Build();

var queryExecutor = host.Services.GetRequiredService<IQueryExecutor>();
var result = await queryExecutor.ExecuteQueryAsync("EVALUATE ROW(\"Current Time\", NOW())");

Console.WriteLine(result);

ASP.NET Core Web Application:

using TabuLynx.Core.Configuration;
using TabuLynx.Core.Interfaces;
using TabuLynx.Query.Executor;

var builder = WebApplication.CreateBuilder(args);

// Configure TabuLynx options
builder.Services.Configure<TabuLynxOptions>(
    builder.Configuration.GetSection("TabuLynx"));

// Register query executor for local Power BI
builder.Services.AddAdomdQueryExecutorForLocalPowerBI();

var app = builder.Build();

app.MapGet("/query", async (IQueryExecutor queryExecutor) =>
{
    var result = await queryExecutor.ExecuteQueryAsync("EVALUATE ROW(\"Server Time\", NOW())");
    return Results.Ok(result);
});

app.Run();

2. SQL Server Analysis Services (SSAS)

Connect to an on-premises or cloud-hosted SSAS server.

Configuration (appsettings.json):

{
  "TabuLynx": {
    "ConnectionString": "Provider=MSOLAP;Data Source=your-ssas-server;Initial Catalog=your-database;Integrated Security=SSPI;"
  }
}

Dependency Injection Setup:

services.Configure<TabuLynxOptions>(configuration.GetSection("TabuLynx"));
services.AddAdomdQueryExecutorForSsasServer();

// Usage
var queryExecutor = serviceProvider.GetRequiredService<IQueryExecutor>();
var result = await queryExecutor.ExecuteQueryAsync("EVALUATE SUMMARIZE(Sales, Sales[ProductCategory])");

3. Microsoft Fabric / Power BI Premium

Connect to Microsoft Fabric workspaces or Power BI Premium using Azure AD authentication.

Configuration (appsettings.json):

{
  "TabuLynx": {
    "ConnectionString": "Provider=MSOLAP;Data Source=powerbi://api.powerbi.com/v1.0/myorg/your-workspace;Initial Catalog=your-dataset;",
    "TenantId": "your-tenant-id",
    "ClientId": "your-app-client-id",
    "ClientSecret": "your-app-client-secret",
    "Scope": "https://analysis.windows.net/powerbi/api/.default"
  }
}

Service Registration:

services.Configure<TabuLynxOptions>(configuration.GetSection("TabuLynx"));
services.AddAdomdQueryExecutorForFabric();

// Usage
var queryExecutor = serviceProvider.GetRequiredService<IQueryExecutor>();
var result = await queryExecutor.ExecuteQueryAsync("EVALUATE TOPN(10, Sales, Sales[Amount])");

📊 Supported Query Types

TabuLynx.Query.Executor supports multiple query languages through the XMLA protocol:

DAX Queries (Data Analysis Expressions)

Execute business logic and analytical queries:

// Simple DAX evaluation
var result = await queryExecutor.ExecuteQueryAsync("EVALUATE ROW(\"Current Time\", NOW())");

// Table evaluation with filters
var salesData = await queryExecutor.ExecuteQueryAsync(@"
    EVALUATE 
    TOPN(100, 
        FILTER(Sales, Sales[Amount] > 1000),
        Sales[Amount], DESC
    )");

// Measures and calculations
var kpiResult = await queryExecutor.ExecuteQueryAsync(@"
    EVALUATE 
    ROW(
        ""Total Sales"", SUM(Sales[Amount]),
        ""Average Sale"", AVERAGE(Sales[Amount]),
        ""Sales Count"", COUNTROWS(Sales)
    )");

DMV Queries (Dynamic Management Views)

Extract metadata and system information:

// Get all tables in the model
var tablesResult = await queryExecutor.ExecuteQueryWithDictionaryResultsAsync(
    "SELECT * FROM $SYSTEM.TMSCHEMA_TABLES");

// Get column information
var columnsResult = await queryExecutor.ExecuteQueryWithDictionaryResultsAsync(
    "SELECT * FROM $SYSTEM.TMSCHEMA_COLUMNS WHERE TableID = 1");

// Get measure definitions
var measuresResult = await queryExecutor.ExecuteQueryWithDictionaryResultsAsync(
    "SELECT Name, Expression, Description FROM $SYSTEM.TMSCHEMA_MEASURES");

// Get relationship information
var relationshipsResult = await queryExecutor.ExecuteQueryWithDictionaryResultsAsync(
    "SELECT * FROM $SYSTEM.TMSCHEMA_RELATIONSHIPS");

foreach (var table in tablesResult)
{
    Console.WriteLine($"Table: {table["Name"]} (ID: {table["ID"]})");
}

MDX Queries (Multidimensional Expressions)

For OLAP cube operations and multidimensional analysis:

// Basic MDX query (when connecting to multidimensional models)
var mdxResult = await queryExecutor.ExecuteQueryAsync(@"
    SELECT 
        [Measures].[Sales Amount] ON COLUMNS,
        [Product].[Category].MEMBERS ON ROWS
    FROM [Adventure Works]");

TMSL Commands (Tabular Model Scripting Language)

Administrative and model management operations:

// Refresh table (requires admin permissions)
var tmslCommand = @"{
    ""refresh"": {
        ""type"": ""full"",
        ""objects"": [
            {
                ""database"": ""YourDatabase"",
                ""table"": ""Sales""
            }
        ]
    }
}";

var refreshResult = await queryExecutor.ExecuteQueryAsync(tmslCommand);

⚙️ Configuration Options

The TabuLynxOptions class supports the following configuration properties:

📋 Connection String Examples

Local Power BI Desktop

Provider=MSOLAP;Data Source=localhost:12345;

SSAS with Windows Authentication

Provider=MSOLAP;Data Source=ssas-server;Initial Catalog=Adventure Works;Integrated Security=SSPI;

SSAS with SQL Authentication

Provider=MSOLAP;Data Source=ssas-server;Initial Catalog=Adventure Works;User ID=username;Password=password;

Power BI Premium/Fabric

Provider=MSOLAP;Data Source=powerbi://api.powerbi.com/v1.0/myorg/workspace-name;Initial Catalog=dataset-name;

🔧 Advanced Usage

Multi-Query Service Example

public class AnalyticsService
{
    private readonly IQueryExecutor _queryExecutor;

    public AnalyticsService(IQueryExecutor queryExecutor)
    {
        _queryExecutor = queryExecutor;
    }

    // DAX Query: Business Analytics
    public async Task<string> GetSalesAnalytics(string productCategory)
    {
        var daxQuery = $@"
            EVALUATE 
            FILTER(
                SUMMARIZE(
                    Sales,
                    Sales[ProductCategory],
                    ""Total Sales"", SUM(Sales[Amount]),
                    ""Avg Sale"", AVERAGE(Sales[Amount]),
                    ""Transaction Count"", COUNTROWS(Sales)
                ),
                Sales[ProductCategory] = ""{productCategory}""
            )";

        return await _queryExecutor.ExecuteQueryAsync(daxQuery);
    }

    // DMV Query: Model Metadata
    public async Task<List<Dictionary<string, object>>> GetModelSchema()
    {
        var dmvQuery = @"
            SELECT 
                t.Name AS TableName,
                t.Description AS TableDescription,
                c.Name AS ColumnName,
                c.DataType,
                c.IsHidden
            FROM $SYSTEM.TMSCHEMA_TABLES t
            INNER JOIN $SYSTEM.TMSCHEMA_COLUMNS c ON t.ID = c.TableID
            ORDER BY t.Name, c.Name";

        return await _queryExecutor.ExecuteQueryWithDictionaryResultsAsync(dmvQuery);
    }

    // Combined Analysis: Data + Metadata
    public async Task<object> GetCompleteTableInfo(string tableName)
    {
        // Get table metadata via DMV
        var metadataQuery = $@"
            SELECT 
                Name, Description, IsHidden, RowCount
            FROM $SYSTEM.TMSCHEMA_TABLES 
            WHERE Name = '{tableName}'";
        
        var metadata = await _queryExecutor.ExecuteQueryWithDictionaryResultsAsync(metadataQuery);

        // Get sample data via DAX
        var dataQuery = $"EVALUATE TOPN(10, {tableName})";
        var sampleData = await _queryExecutor.ExecuteQueryAsync(dataQuery);

        return new
        {
            Metadata = metadata.FirstOrDefault(),
            SampleData = sampleData
        };
    }
}

Query Type Detection and Routing

public class SmartQueryExecutor
{
    private readonly IQueryExecutor _queryExecutor;

    public SmartQueryExecutor(IQueryExecutor queryExecutor)
    {
        _queryExecutor = queryExecutor;
    }

    public async Task<object> ExecuteSmartQuery(string query)
    {
        var queryType = DetectQueryType(query);
        
        return queryType switch
        {
            QueryType.DAX => await ExecuteDaxQuery(query),
            QueryType.DMV => await ExecuteDmvQuery(query),
            QueryType.MDX => await ExecuteMdxQuery(query),
            QueryType.TMSL => await ExecuteTmslCommand(query),
            _ => throw new NotSupportedException($"Query type {queryType} not supported")
        };
    }

    private QueryType DetectQueryType(string query)
    {
        var upperQuery = query.ToUpper().Trim();
        
        if (upperQuery.StartsWith("EVALUATE") || upperQuery.StartsWith("DEFINE"))
            return QueryType.DAX;
            
        if (upperQuery.Contains("$SYSTEM.") || upperQuery.StartsWith("SELECT") && upperQuery.Contains("TMSCHEMA"))
            return QueryType.DMV;
            
        if (upperQuery.StartsWith("SELECT") && upperQuery.Contains("ON COLUMNS"))
            return QueryType.MDX;
            
        if (upperQuery.StartsWith("{") && (upperQuery.Contains("\"refresh\"") || upperQuery.Contains("\"create\"")))
            return QueryType.TMSL;
            
        return QueryType.Unknown;
    }

    private async Task<string> ExecuteDaxQuery(string query)
    {
        return await _queryExecutor.ExecuteQueryAsync(query);
    }

    private async Task<List<Dictionary<string, object>>> ExecuteDmvQuery(string query)
    {
        return await _queryExecutor.ExecuteQueryWithDictionaryResultsAsync(query);
    }

    private async Task<string> ExecuteMdxQuery(string query)
    {
        return await _queryExecutor.ExecuteQueryAsync(query);
    }

    private async Task<string> ExecuteTmslCommand(string query)
    {
        return await _queryExecutor.ExecuteQueryAsync(query);
    }
}

public enum QueryType
{
    DAX,
    DMV,
    MDX,
    TMSL,
    Unknown
}

Handling Multiple Environments

public void ConfigureServices(IServiceCollection services, IConfiguration configuration)
{
    var environment = configuration["Environment"];
    
    services.Configure<TabuLynxOptions>(configuration.GetSection("TabuLynx"));
    
    switch (environment?.ToLower())
    {
        case "development":
            services.AddAdomdQueryExecutorForLocalPowerBI();
            break;
        case "staging":
            services.AddAdomdQueryExecutorForSsasServer();
            break;
        case "production":
            services.AddAdomdQueryExecutorForFabric();
            break;
        default:
            throw new InvalidOperationException($"Unknown environment: {environment}");
    }
}

🔍 XMLA Endpoint Discovery

For Power BI workspaces, you can find the XMLA endpoint:

1. Power BI Service: Go to workspace settings → Advanced → XMLA Endpoint
2. Power BI Desktop: The local port is automatically discovered by the library
3. Fabric: Use the workspace connection string format

🛠️ Troubleshooting

Common Issues

Port Discovery for Local Power BI: Ensure Power BI Desktop is running and a report is open. The library automatically discovers the local XMLA port.

Authentication Failures: For cloud scenarios, verify that:

• The Azure AD application has the required permissions
• The service principal has access to the Power BI workspace
• The tenant ID, client ID, and client secret are correct

Connection Timeouts: Increase the connection timeout in the connection string:

Provider=MSOLAP;Data Source=server;Connect Timeout=60;

Query-Specific Issues

DAX Query Errors:

• Ensure table and column names are correctly spelled and exist in the model
• Use proper DAX syntax with EVALUATE for table expressions
• Check that calculated columns and measures are properly defined

DMV Access Denied:

• Verify permissions to query system catalog views
• Some DMV queries require administrative access to the Analysis Services instance
• Use ExecuteQueryWithDictionaryResultsAsync() for better DMV result handling

MDX Compatibility:

• MDX queries work best with multidimensional models (SSAS OLAP)
• For tabular models, prefer DAX over MDX when possible
• Check model compatibility mode for MDX support

TMSL Permission Issues:

• TMSL commands require database administrator permissions
• Refresh operations need appropriate data source credentials
• Model modification commands may not be supported in all environments (e.g., Power BI Service)

🤝 Contributing

Contributions, issues, and feature requests are welcome! Please feel free to check the issues page.

📄 License

This project is licensed under the terms specified in the LICENSE file.