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.

[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]

@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)