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

[russelp]

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? 

[Anonymous]

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 

[Anonymous]

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. 

[russelp]

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? 

[Anonymous]

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

[Anonymous]

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.

[russelp]

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 - 

[Anonymous]

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.

[russelp]

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!

[russelp · Accepted solution]

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

[Anonymous]

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