Provide interfaces for executing queries against Tabular Models.
$ dotnet add package TabuLynx.Query.ExecutorA 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.
dotnet add package TabuLynx.Query.Executor
This package depends on the following NuGet packages:
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();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])");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])");TabuLynx.Query.Executor supports multiple query languages through the XMLA protocol:
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)
)");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"]})");
}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]");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);The TabuLynxOptions class supports the following configuration properties:
| Property | Description | Required | Default |
|---|---|---|---|
ConnectionString | XMLA connection string | Yes | - |
TenantId | Azure AD tenant ID (for cloud scenarios) | For Fabric/Cloud | - |
ClientId | Azure AD application client ID | For Fabric/Cloud | - |
ClientSecret | Azure AD application client secret | For Fabric/Cloud | - |
Scope | OAuth scope for authentication | No | https://analysis.windows.net/powerbi/api/.default |
Provider=MSOLAP;Data Source=localhost:12345;
Provider=MSOLAP;Data Source=ssas-server;Initial Catalog=Adventure Works;Integrated Security=SSPI;
Provider=MSOLAP;Data Source=ssas-server;Initial Catalog=Adventure Works;User ID=username;Password=password;
Provider=MSOLAP;Data Source=powerbi://api.powerbi.com/v1.0/myorg/workspace-name;Initial Catalog=dataset-name;
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
};
}
}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
}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}");
}
}For Power BI workspaces, you can find the XMLA endpoint:
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:
Connection Timeouts: Increase the connection timeout in the connection string:
Provider=MSOLAP;Data Source=server;Connect Timeout=60;
DAX Query Errors:
EVALUATE for table expressionsDMV Access Denied:
ExecuteQueryWithDictionaryResultsAsync() for better DMV result handlingMDX Compatibility:
TMSL Permission Issues:
Contributions, issues, and feature requests are welcome! Please feel free to check the issues page.
This project is licensed under the terms specified in the LICENSE file.