Executing MDX queries in a fabric notebook (via pyadomd or sempy)

[dbeavon3]

This is a question for the python folks at Microsoft.  I'm hoping someone else is familiar with pyadomd and sempy.

In the past we had been running python notebooks locally (on windows), and using them to execute MDX queries.  This query technology (MDX) is still at the core of Microsoft's BI stack, and is used in Excel pivot tables, in the semantic-model-import operations from PQ, and in other places.  For the sake of this discussion, please assume that we have already evaluated the other query language (DAX) and would prefer to use MDX in certain cases (just as Microsoft does).

How do we make MDX queries from a fabric notebook?  I haven't tested yet, but I'm assuming pyadomd is unsupported in Fabric (because it is specific to the windows platform).   Will the new "sempy" be supported for this scenario?  

Here is the sempy project, and the docs:

https://pypi.org/project/semantic-link-sempy/

https://learn.microsoft.com/en-us/python/api/semantic-link-sempy/sempy?view=semantic-link-python

https://learn.microsoft.com/en-us/fabric/data-science/read-write-power-bi-python

Unfortunately I don't think this "sempy" (semantic link) is open source, but I believe it uses XMLA for much of its functionality, and I believe it has the potential to run query workloads , similar to what pyadomd could do in the past.

It was hard to find, but I found a place where someone appears to use sempy for MDX:

https://www.purplefrogsystems.com/wp-content/uploads/2024/10/Fabric-SemPy-Notebook.txt

https://www.purplefrogsystems.com/2024/10/how-to-use-t-sql-to-query-and-monitor-your-microsoft-fabri...

Other than this, I find very few google hits about "sempy" and "MDX".  I'm guessing that reflects the fact that some of this Fabric stuff is still fairly immature (sempy has not yet reached v.1.0, and is only v.0.8.4 as of now).  I'm not really a python guy myself; I am accustomed to using very mature .Net interfaces to connect to these OLAP datasets hosted on the PBI service (via MDX).   Given that I'm not a python guy, I'm nervous about how we might create a long-term solution that would be fully supported by Microsoft.  I'm assuming that, in python like in other languages, there is a big difference between finding an approach that works, and finding an approach that would be fully supported by Microsoft.  Getting some support from Microsoft is all the more important in this particular scenario, given the fact that the semantic-link/sempy stuff is NOT being made available to the community as an opensource project on git.

Any information would be greatly appreciated.  In the very least, I'm hoping that this new posting will start to appear in google searches about "sempy" and "MDX" and "pyadomd".

[v-pnaroju-msft]

Hi dbeavon3,

We are writing to kindly follow up and inquire whether you have received any response from the Ideas team. If so, we would appreciate it if you could share the details here, as it would be beneficial to other members of the community.

The thread has been open for a considerable period. In the event that we do not receive any response, and in accordance with community guidelines, we will proceed to close the thread. For any further discussions or queries, please feel free to start a new thread in the Microsoft Fabric Community Forum, where we will be glad to assist you.

Thank you for your cooperation and understanding.

[v-pnaroju-msft]

Hi dbeavon3,

We are writing to follow up and inquire if you have received any response from the Ideas team. If so, kindly share the details here, as it would benefit other community members.

In the event that we do not receive a response, we will proceed to close this thread. For any further discussions or queries, please feel free to initiate a new thread in the Microsoft Fabric Community Forum, where we will be happy to assist you.

Thank you.

[v-pnaroju-msft]

Hi dbeavon3,

Apologies for the delayed response.

As you have already shared the idea for the implementation of a new feature, we kindly request you to wait for some time while the backend team processes and enrolls it.

We also request you to monitor the official Microsoft documentation for updates.

Thank you for your patience and understanding.

[v-pnaroju-msft]

Hi dbeavon3,

We sincerely appreciate your inquiry on the Microsoft Fabric Community Forum.

Based on your query, we understand your concerns regarding finding a reliable, long-term solution for executing MDX queries within Microsoft Fabric notebooks.

1. The 'Sempy' library is primarily designed for semantic linking and may not fully support the complexities of MDX queries, especially when it comes to advanced features such as calculated members or specific functions critical in OLAP environments.
   As 'Sempy' is a relatively new library, there might be compatibility issues with existing OLAP systems or other libraries relied upon for data analysis and reporting.

2. The current version of 'Sempy' (0.8.4) indicates that it is still under development. Consequently, it may lack the stability and comprehensive features necessary for production-level MDX query execution. Early versions often come with bugs and incomplete functionalities, which may adversely impact performance and reliability.

As a workaround, we recommend using the officially supported Microsoft Fabric REST API through Power BI REST APIs. This approach offers the benefits like Microsoft support,Platform independence,Stable and versioned API and Direct access to the XMLA endpoint.

1.To implement the Power BI REST API with the XMLA endpoint, kindly refer to the following links:

Power BI REST APIs for embedded analytics and automation - Power BI REST API | Microsoft Learn

Semantic model connectivity and management with the XMLA endpoint in Power BI - Power BI | Microsoft...

2.To implement using Python

a) Use the 'requests' library to interact with the REST API.
b) Authenticate using Azure AD.
c) Execute MDX queries via the XMLA endpoint.

Below is a sample Python code snippet for reference:

import requests

from azure.identity import DefaultAzureCredential

def execute_mdx_query(workspace_id, dataset_id, mdx_query):

    credential = DefaultAzureCredential()

    access_token = credential.get_token("https://analysis.windows.net/powerbi/api/.default")

    headers = {

        'Authorization': f'Bearer {access_token.token}',

        'Content-Type': 'application/json'

    }

    endpoint = f"https://api.powerbi.com/v1.0/myorg/datasets/{dataset_id}/executeQueries"

    payload = {

        "queries": [

            {

                "query": mdx_query

            }

        ]

    }

    response = requests.post(endpoint, headers=headers, json=payload)

    return response.json()

If you find the response helpful, we kindly request you to mark it as the accepted solution and provide kudos. This will assist other community members who may have similar queries.

Best regards,
Pavan

[dbeavon3]

Hi @v-pnaroju-msft 
I am investigating your sample.  Did you generate all this content from an A.I., or did you create it yourself? 

Do you have much familiarity with the "sempy" library?  I cannot tell based on your response.  I'm trying to find a person who has first-hand experience and a deeper understanding of how that library was built.

Interestingly sempy appears to be a layer of python code, and it has .Net underneath.  Did you know this?  The connectivity from sempy seems to rely on the client libraries from the .Net ecosystem.  The sempy library was not built from scratch using python and xmla.  Here are more details I posted about that side of things:

https://community.fabric.microsoft.com/t5/Data-Engineering/A-bumpy-journey-with-Net-beneath-the-sema...

According to the docs, your proposed use of "executeQueries" for running MDX queries is unsupported.  Yet my primary goals were to find a long-term solution that worked and was simultaneously supported by Microsoft...

https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/execute-queries

I'm hoping to find a path forward soon.  I now have a support case open with Mindtree, but my guess is that it will take two or three weeks to find some sort of resolution.  If you have some familiarity with sempy, or any contact with the python developers who built it, then you are likely to be able to help me a lot sooner than that.  Here is the SR : TrackingID#2501010040001419.

Any help would be very much appreciated.

[dbeavon3]

@v-pnaroju-msft 

Hi again, despite my misgivings, and despite the docs that I shared earlier, the code snippet that you gave was able to retrieve the results from an MDX query!  Thanks! (image below).  

I'm still not totally convinced, but I'll assume you are not an A.I.... for now. 

Can you please share your thought process when proposing this solution?  You claimed this officially supported Microsoft Fabric REST API  "offers the benefits like Microsoft support".  However the documentation clearly says the exact opposite of that, where MDX is concerned.  I am hopeful that you know more than what is stated in the docs; perhaps the docs are out of date.  Can you please tell me why your solution might be supported, when the docs say otherwise?

Other follow-up questions, if you know the answer:

- why would sempy not be supported with MDX?  The docs don't say anything about that mdx, one way or the other.

- is the sempy code available somewhere on the internet so that customers can review the implementation when it misbehaves?

- are you familiar with the technique that is used to run .Net logic underneath the sempy python library?  Can customers accomplish a similar thing if we have our own .Net code that we want to host in Fabric?

- why do Microsoft product teams not check for unsupported scenarios and throw explicit errors? It would avoid a tremendous amount of discussion and confusion.  Unsupported feature should simply be disallowed.  Otherwise the customers of this Fabric SaaS will inevitably build complex software without knowing that some large part of it is a house of cards that is liable to fall over at any time, with no sympathy or support from Microsoft. (On Synapse, Microsoft wasn't even standing behind their "supported" features, so I'm certain they won't stand behind unsupported features either)

[dbeavon3]

Hi @v-pnaroju-msft 

Can you tell me more about your proposed use of "executeQueries" for running MDX queries?

Was this recommendation generated by chat GPT?  For some reason that AI likes to skip over the most critical things in the Microsoft docs (like that "not supported" sentence).

I don't blame chat GPT; I would also like to think MDX queries would be fully supported in a python notebook.   I'm glad that chat GPT and I are in agreement on that.  I'm sure Microsoft relies heavily on MDX as well (in Excel, in import PQ, and in other places). 

... chat GPT is probably giving me a solution that it found while sifting thru Microsoft's own internal code libraries. 😉

[dbeavon3 · Accepted solution]

Hi @v-pnaroju-msft 

Interestingly, I reached out to Microsoft/Mindtree support and they did not turn me away from using "executeQueries" for running MDX.

Regarding your query about why the REST API works in the notebook despite the limitation stating that MDX queries are not supported, we reached out to PG for clarification.
 
PG responded that while the limitation indicates MDX is not supported, it doesn't necessarily block all scenarios. This limitation applies primarily when the REST API is running independently.

Since it works within the notebook, it should continue to work in the future.

[dbeavon3]

@v-pnaroju-msft 

Here is a shameless plug for my "idea".

https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=ca5b047e-d6d2-ef11-95f5-000d3a0442cc

Given that sempy is newer than the REST API then, of the two options discussed, I'm guessing Microsoft is more likely to extend its functionality sooner than they would extend the REST API.

[dbeavon3]

Just correcting myself...  The query example that I posted is NOT demonstrating the use of an MDX.  It is actually a DMV/SQL query.

As such, I still have found no Microsoft-approved solutions for running an MDX query from a python notebook in Fabric.

[dbeavon3]

FYI, I did some minimal testing, and it appears that MDX queries work to a limited degree...  image...

... however it seems likely that there is no official support for this scenario. Especially given that (1) I'm running a method named "evaluate_dax", and (2) the data type of a simple measure is not detected and generates the error that I highlighted.  Perhaps I may need to explicitly convert outputs to intermediate strings as a workaround or something 

If there is a python developer who can provide feedback, do you see any red flags that should prevent me from pursuing this?  Is my experience pretty typical in the world of a python developer?  Does the version (v.0.8.4) of the library mean anything in terms of supportability?   If this is not the right path, can you share tips on how to reverse-engineer the underlying implementation of "evaluate_dax" to find out what internal dependencies are used to send the MDX and retrieve the outputs?  I'd guess that those underlying dependencies are better suited to my MDX scenario than the sempy library itself.   They probably place a more direct dependency on the XMLA endpoint, although they may be a bit more technical, and I may need to do more work than I would with sempy.

Any guidance would be appreciated.  My responsibilities don't normally involve python development.  So I'm a bit nervous about pursuing an approach that might work at the moment, yet fall over at some point in the future.