Return m expression text for all queries in semantic model

[ebeery11]

I feel like this must already be possible with semantic link, but I am struggling to find any mention in the documentation of a way to return the m expression text for all queries within a semantic model.

The closest I've gotten so far is using sempy fabric.get_tmsl but parsing this from the entire TMSL seems like overkill.

I'm also not seeing anything in the sempy_labs.tom that seems to do exactly this.

[ebeery11]

Whoops, I think I answered my own question.

Looks like the 'Query' column of fabric.list_partitions was what I was looking for.

I'm guessing sempy_labs.tom all_partitions() will return something similar.

[m-kovalsky]

This is the easiest way to access that.

import sempy.fabric as fabric
fabric.list_partitions(dataset='', workspace='')

[ebeery11]

Thanks @m-kovalsky. One more question to clarify that I'm getting myself confused on:

Do basically all of these sempy functions require that the dataset reside in a Premium workspace? I'm getting a "user does not have permission to call the Discover method" error when attempting to use functions like fabric.list_datasets() or fabric.list_partitions() on non-Premium workspaces, but it's a bit confusing to me because fabric.list_workspaces() returns all workspaces, including those not on dedicated capacities.

[m-kovalsky]

Premium is not a requirement. What is your permission on the workspace? You must be be either admin/member/contributor.

[ebeery11]

Hmm. I am admin on all of the ones that I've been trying. Maybe I am missing something else...

[m-kovalsky]

Can you check if this tenant setting is disabled?

[ebeery11]

I'm not a tenant admin, but checking with them now to see. Will report back. Thanks!

[ebeery11]

@m-kovalsky I've confirmed that this setting is disabled in our tenant.

[m-kovalsky]

Do you have xmla r/w enabled on the capacity?

You can probably run list_datasets if you set the parameter mode=‘rest’ because it doesn’t use xmla (that forces the function to call an api, not xmla).

[ebeery11]

Thanks. If by "the capacity" you mean the Premium capacity, I think the answer is yes, because I'm able to do things like trigger refreshes, etc. using XMLA through Tabular Editor (on models residing in Premium).

I confirmed that I could run list_datasets with mode='rest' on a model in a Pro workspace. But I'm assuming list_partitions doesn't work the same way because there's no REST equivalent?

[m-kovalsky]

Correct. The list functions for the semantic model (list_tables, list_columns, list_partitions etc) use the Tabular Object Model API (TOM) to get the metadata. The odd thing is that I’m not aware of any calls to ‘discover’ objects.

[ebeery11]

@m-kovalsky just following up on this as I had another use case. I hacked together a solution(?) using your get_semantic_model_definition function, decoding the base64 file output into json, and unpacking the json to return just the query expressions.

Probably there is a better way, but it seems to work(ish) for now on both Premium and Pro models. Unfortunately, I frequently hit the 8000-character limit for storing in a Lakehouse table.

[m-kovalsky]

You’re much better off finding a way to use fabric.list_partitions() as otherwise you will have to reinvent the wheel several times over.

[ebeery11]

@m-kovalsky definitely agree, but the fact that fabric.list_partitions() doesn't work for non-Premium models seems to be a hard limitation for now

[m-kovalsky]

Are you storing a single M expression in a given row of the lakehouse table? 8000 characters seems plenty if that’s the case so sounds like the M code is overdone.

[ebeery11]

Yes I am. Agree, 8000 is plenty for most cases, but not all. In my initial screening, about ~1% of our queries (11/1246) had >8000 characters and so were truncated.

In this case even with truncation, it's still helpful, since what I'm mostly after is tracing SQL table/view dependency, and those are typically in the first few lines of the m expression.