0

I’m working with a client who wants to reduce the execution time of my application when making multiple DAX calls.

Currently, the flow is:

  • I use an LLM to generate a DAX query based on report descriptions.
  • Then, I use the Adomd packages in .NET to connect to Azure Analysis Services (AAS) and execute the DAX queries.

The application is hosted on Azure App Services, so I don't have a local environment where I can create a dummy model with empty data and just the measures. The Power BI data and models are managed entirely by the client — I only have access via a connection string.

The problem is:

  • Sometimes the queries fail due to syntax or semantic errors.

Now, the client wants us to validate the DAX before we hit AAS using a .NET-based Power BI package that can catch invalid DAX.

He wants to avoid hitting AAS altogether if the DAX is invalid, as that adds latency. The idea is to reduce time spent waiting on errors from the server.

I’ve searched for a package or API that can validate DAX offline (without requiring a connection to Power BI or AAS), but I haven’t found anything. The only semi-relevant resource I found was this:

https://learn.microsoft.com/en-us/power-bi/developer/embedded/embed-organization-app

However, it doesn’t seem applicable in my case, as I don’t have access to host or modify the dataset/model.

I'm also concerned that trying to validate DAX locally might increase overhead. I'd essentially be opening two connections per DAX query: one to validate, and another to execute — which might end up being slower than just failing fast on execution.

I've raised these concerns, but the client still insists on finding a way to pre-validate the DAX.

I'm stuck at this point and not sure how to proceed. Has anyone faced a similar requirement? Is there a way to validate DAX queries in .NET without opening a connection to AAS or Power BI?

Any suggestions or workarounds would be greatly appreciated.

Improve this question

1 Answer 1

Reset to default
0

You can validate the query by calling the AdomdCommand.Prepare method before actually running the query.

The method will throw an exception if the query is invalid (syntactically or semantically).

Other than that, am not aware of any other tools that you could use to verify semantics (without going to the server). Regarding local syntax validation, there is quite a lot of nuget packages that are available for that but I haven't used those so cannot recommend them.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

To supplement - you could consider wrapping the query in TOPN( 1, {your query} ) to reduce the number of rows returned.
Prepare is not returning any results, it just "verifies" the query so TOPN wouldn't (probably?) have any effect. But TOPN could be used instead of Prepare (though I would still prefer the Prepare).
Apologies - I was implying that if you were wanting to validate the model by querying it, then the TOPN could potentially help reduce the load.
I believe ExecuteReader does a Prepare internally before fetching the data. So if I already do ExecuteReader on my query, doing Prepare before that to just check the validity of the DAX would not be needed right? It would just be hitting the servers twice if the query is valid.
ExecuteReader is not calling Prepare before fetching the data. It's calling the same method (ExecuteStatement, if the CommandText was supplied) but with different execution command properties. Specifically, the Prepare method is internally setting the ExecutionMode to Prepare. I don't think you will be able to verify the query locally because you don't have access to schema locally, so your question now becomes "How do I minimize the resource utilization of the verification process."

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.