A bumpy journey with .Net (beneath the semantic-link/sempy library for python)

[dbeavon3]

Background: I'm trying to get a formatted string with the help of an MDX statement in sempy.  It is not as easy as I expected.  All of a sudden I'm discovering that there is some very unusual stuff going on beneath the surface of the "sempy" library.

Consider the following query that is intended to get the "Measures.Sales" as a formatted string

This doesn't work.  The custom member ("PleaseGetTheString") will not be easily transferred over to the python environment.  The solution seems like it should be simple, yet I have been struggling with this for quite a while and it is still out of my reach.  See more about that here:
https://community.fabric.microsoft.com/t5/Data-Engineering/Executing-MDX-queries-in-a-fabric-noteboo...

After several different attempts to work around the errors, I have accidentally discovered that this sempy library has a massive amount of .Net code (and other unexpected components) running under the covers.  For proof, here is an exception from one of my query attempts via  fabric.evaluate_dax.

It is easy to see that the core implementation of fabric.evaluate_dax, involves a dependency on XMLA, and parquet, and it also depends on .Net (probably 6 or 8)!

If someone has more experience with sempy than I do, can you please explain how this "semantic link" makes use of .Net in Fabric?  I would like to see the implementation of this if it is available.  I have not yet found any of the source code in github, but perhaps I need to adjust my google-fu technique.

There are a number of things that I really want to know about this exception.  Most importantly, how might a customer build their own python wrappers for .Net code.  It seems like a double standard if Microsoft is able to execute some of their own .Net code from these spark drivers yet, where customers are concerned, they have denied our ability to do anything like it.   (In the past I had many uncomfortable discussions with the underlying PG when they removed their support for ".net for spark" in synapse.  They had explained to customers that this sort of functionality was dead.  Yet here in "Fabric", we see that .Net is still very much alive and still kicking!)

Other things that interest me are the fact that xmla libraries, and parquet libraries seem to be used by this .Net implementation.  I'm guessing the parquet (rather than apache arrow) is used for the sake of serialization/deserialization when data is transferred from the .Net runtime to the python runtime.

Can someone please point me to any of this sempy code for .Net interop?  Or point me to other examples that might show how customers can perform some comparable interop with the underlying .Net runtime? It is very frustrating to rely 100% on python in for our entire solution, when it is obvious that there is a full .Net runtime that is hidden beneath the surface.

[v-prasare]

We are following up once again regarding your query. Could you please confirm if the idea has raised with Microsoft?

If the issue has been resolved, we kindly request you to share the resolution or key insights here to help others in the community. If we don’t hear back, we’ll go ahead and close this thread.

Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you for your understanding and participation

[dbeavon3]

I was able to find one of the engineers (in a github community) It sounds like there is in fact a dependency on .net under the covers.  In addition they use the AdomdClient from .Net.  And I believe they said pythonnet for the interop.

For the AdomdClient, they use the public nuget variety of the package, and it is not a home-grown integration with datasets.  (That is a relief.)

Oddly the sempy library isn't fully supported from vscode (local development) or from other compute platforms like databricks. 

... Given that the building blocks seem to be fairly standard ones, it seems like the team should be able to support this sempy library elsewhere.  But I'm guessing there are only some minor reasons why they don't want to support it everywhere (eg. dependencies on fabric's "notebookutils" for getting access token).  Or maybe they aren't prepared to keep up a regular cadence of changes to the pypi public library.

[dbeavon3]

Interestingly, the docs from Microsoft are not afraid to say that there is an underlying "bridge" from python to .Net:

Source:

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

Please let me know how I might create a similar library for python (a lightweight wrapper around the "real code" that lives in .Net).

I'm assuming the bridge is entirely local, on the spark driver, and it is not a client connection via TCP to a different compute node.  Else the verbage here would be considerably different than it is (and the callstacks would be a lot more complex, in the underlying exception details).

[lbendlin]

I would hazard a guess that MDX is entirely unsupported by sempy as it talks to SSAS Tabular models, in DAX.

[dbeavon3]

Probably.  But it works for all the columns on dimension tables.  It only fails for calculated measures on fact tables.  If unsupported, you might think they would generate a failure, rather than teasing us by returning some columns and not others. 

... Or maybe they would add some documentation that explains more clearly that this method ("evaluate_dax") should only be used for evaluating dax. 😉

After digging, it appears taht they seem to be mishandling the data on the python side.  If I can get around that, and use the underlying .Net components then I should be fine.  The underlying components are probably equally capable of handling results from DAX or MDX queries.

[v-prasare]

@dbeavon3, Thanks for actively participating in MS fabric community

The inconsistency you're facing highlights a gap in sempy's maturity. Your best path forward is likely to bypass sempy's Python layer for calculated measures and instead use the .Net components directly. This approach not only gives you flexibility but also ensures you're leveraging the full power of the underlying XMLA and .Net infrastructure

for contributing ideas, feedabck, features and development please write here. Your feedback is important to us, Looking forward to your response.

Thanks,

Prashanth Are

MS Fabric community support.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos"

[dbeavon3]

@v-prasare 

You said, 

I really, really love that answer.  But I'm a bit nervous that it was copy/pasted something from chat-GPT.  Is this a real suggestion?

I'd guess that it is probably the very last thing that Microsoft would be telling their customers.  They are hardly able to support "sempy" in the current form, over at the Mindtree organization.  Anything I build with .Net will be even more difficult to support. 

Are you proposing I just unzip the python "whl" and then write a similar wrapper of my own, fo the sake of MDX queries:

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

.. the download is semantic_link_sempy-0.8.5-py3-none-any.whl

[v-prasare · Accepted solution]

@dbeavon3, Thank you for your continued engagement and feedback.

we want to provide you the best possible guidance to help you achieve your goals. Microsoft has not provided any documentation or best practices for modifying or extending this bridge and we do not recommend modifying the sempy library itself or creating a custom bridge unless you have extensive experience in both Python and .NET

As above mentioned, Microsoft's official support for such functionality is limited we recommend to submit your ideas here to Fabric team: https://ideas.fabric.microsoft.com/ideas/search-ideas/

Thanks,

Prashanth Are

MS Fabric community support.

[dbeavon3]

@v-prasare Assuming that a customer DOES have extensive experience in both Python and .Net, does the platform allow customers to host similar software architecture as what Microsoft created in this case? 

Is Fabric intended to be a "flexible" Linux platform for custom solutions? Where do we get support at the OS level, if there are problems hosing our solutions in Fabric?  Are we free to host any type of underlying custom libraries via python?  Is there any Fabric support team that would provide OS-level support if the Linux environment itself is buggy? 

I have had incidents where a .net or python library will work fine in a custom on-prem environment but it fails after deploying to one of the proprietary flavors of Spark in Synapse (now Fabric).

Eg.  I had an issue with the Linux configuration in the Synapse platform wherein the OS team had used a really non-standard networking configuration.  (they had disabled the negative cache via "no-negcache" and this configuration created a conflict with the IP v6 traffic that was being attempted in the SQL libraries.  We struggled with a tremendous amount of errors due to port exhaustion and it took many, many, many months for the Synapse support team to help with that.) 

Please note that there are common libraries out there already that easily enable interoperation with .net.  Eg see "pythonnet".  I'd guess that Microsoft wouldn't use this in sempy, but I haven't dug down to that level yet.  The main callenge I see with modeling software after "sempy" is that Microsoft has not shared the code yet, and the license is probably closed.  If you can direct me to a Microsoft contact for that knows anything about "sempy", then I would be able to ask follow-up questions.  Barring that, I simply want to know if customers of Fabric would be discouraged from using pythonnet, because it would have no OS-level support from Microsoft (nobody to take OS-level tickets) 

[v-prasare]

@dbeavon3, Thank you for sharing your experience with the issues you've encountered while working with MDX statements in sempy.

The details you’ve provided, including the link to the other user’s discussion, will undoubtedly contribute to a better understanding of these challenges. Your insights into the .Net interop and the XMLA/parquet libraries being used within Fabric will be helpful for further exploration and potential improvements.

By bringing this to our attention, you’re not only helping to enhance our own knowledge but also contributing to improving the overall support and experience for our Fabric community.

We truly appreciate you taking the time to share your experience, and we’ll keep your observations in mind as we continue working on better solutions.

Thanks,

Prashanth Are

MS Fabric Community Support