topic Re: Datasets of other users are not found when using the sempy fabric package in Data Engineering

Datasets of other users are not found when using the sempy fabric package

russelp — Thu, 13 Jun 2024 01:18:01 GMT

The original discussion with @Anonymous can be found here - https://community.fabric.microsoft.com/t5/Data-Engineering/Datasetnotfoundexception-even-though-the-dataset-exists-when/m-p/3984512#M2493 but since the issue is different from the original post, a new thread has been created.

List_datasets() or list_columns() return only the dataset owned by the user. Users have admin role in the workspace and has "Workspace admin, All permissions granted" permission in the dataset. I tried with a few coworkers of mine and we ran the same script in the same notebook and got different results. The script only returned the datasets owned by the user who ran the script.

import sempy.fabric as fabric text = fabric.list_datasets(workspace=workspace_id) display(text)

Permissions on the semantic model

Permissions on the SQL endpoint

Permissions on the Lakehouse

Is there any permission or any setting we are missing to be able to display all datasets within a workspace?

Re: Datasets of other users are not found when using the sempy fabric package

Anonymous — Thu, 13 Jun 2024 17:16:14 GMT

Hi @russelp
Thanks for using Fabric Community.
At this time, we are reaching out to the internal team to get some help on this. We will update you once we hear back from them.
Thanks

Re: Datasets of other users are not found when using the sempy fabric package

Anonymous — Fri, 14 Jun 2024 03:12:30 GMT

Hi @russelp
Can you please try with this code and let me know :

import sempy.fabric as fabric text = fabric.list_datasets(workspace=workspace_id, mode='rest') display(text)

Hope this helps.

Re: Datasets of other users are not found when using the sempy fabric package

russelp — Fri, 14 Jun 2024 05:21:12 GMT

Hi @Anonymous !

The list_datasets() is now working! However, the 2nd part of the problem is actually the list_columns part. After listing the datasets, we need to also do a list_columns() on certain datasets. The issue now is even though the list_datasets() is showing all datasets, list_columns() still couldn't find the dataset not owned by the user running the script.

import sempy.fabric as fabric df_columns = fabric.list_columns(dataset=dataset_id, workspace=workspace_id) df_columns = df_columns[['Table Name', 'Column Name', 'Data Type']] display(df_columns)

Error message:

DatasetNotFoundException: Dataset <dataset_id> not found in workspace <workspace>

I should probably ask again, should I create a new thread for this follow up question or no need?

Re: Datasets of other users are not found when using the sempy fabric package

Anonymous — Fri, 14 Jun 2024 05:26:59 GMT

Hi @russelp
We can continue on this thread itself. I will let you know once I get the details.
Thanks

Re: Datasets of other users are not found when using the sempy fabric package

Anonymous — Fri, 14 Jun 2024 06:02:39 GMT

Hi @russelp
Can you try
fabric.evaluate_dax(dataset, dax_string="SELECT * from $system.tmschema_columns", workspace=workspace)

This should show the columns of the dataset.

Hope this helps.

Re: Datasets of other users are not found when using the sempy fabric package

russelp — Fri, 14 Jun 2024 06:32:24 GMT

Hi @Anonymous

Thank you for the very quick reply! However, I'm still facing the same issue as the list_columns one. Somehow, I could not access the datasets created by other users.

AdomdErrorResponseException: User '<euii><user_id></euii>' needs to be an administrator to read the metadata of the database '<dataset_id>'.

I checked my permissions on the dataset -

I also have admin role in the workspace -

Re: Datasets of other users are not found when using the sempy fabric package

Anonymous — Fri, 14 Jun 2024 18:09:03 GMT

Hi @russelp
For datasets we have limited access. Hence try using this DMV query

fabric.evaluate_dax(dataset, dax_string="SELECT DISTINCT [DIMENSION_UNIQUE_NAME] as [Table Name], [LEVEL_NAME] as [Column Name] FROM $SYSTEM.MDSCHEMA_LEVELS", workspace= workspace)

Also try to update semantic link to the latest version.

%pip install -U semantic-link-sempy

To avoid access issues please use dataset id and workspace id in the dax query.

Hope this helps.

Re: Datasets of other users are not found when using the sempy fabric package

Anonymous — Mon, 17 Jun 2024 15:15:42 GMT

Hi @russelp
We haven’t heard from you on the last response and was just checking back to see if your query got resolved. Otherwise, will respond back with the more details and we will try to help.
Thanks

Re: Datasets of other users are not found when using the sempy fabric package

russelp — Mon, 17 Jun 2024 23:32:37 GMT

Hi @Anonymous,

Thanks for the reply! It is now working and now returning the table and column names. However, we also need the data type of those columns. When I tried the query below

fabric.evaluate_dax(dataset=dataset_id, dax_string="""SELECT DISTINCT TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM $SYSTEM.DBSCHEMA_COLUMNS""", workspace= workspace_id)

I got the following result:

I checked Data_type 130 and it looks like it's WCHAR. 'age' and 'is_human' are int and boolean from the lakehouse so this is not what we are looking for. Unless there is another way to get this datatype, then I guess we can close this.

Thank you!

Re: Datasets of other users are not found when using the sempy fabric package

russelp — Tue, 18 Jun 2024 07:18:56 GMT

Hi @Anonymous , we finally made it work. We did something similar to the link below to extract details from the information_schema. This should be good enough. Thank you for your help!

https://community.fabric.microsoft.com/t5/General-Discussion/authenticate-to-Fabric-Data-Warehouse-with-Service-Principal/td-p/3467512