User to build Semantic Model using table granted via OneLake Data Access Role

[stellahe102]

Hi, we have a use case titled.

We shared only 1 table (Employee) in a lakehouse (LH_source) via the OneLake Data Access Role with a group. The users in that group can see and access that table in their lakehouse (LH_user) which has a shortcut to the Employee table in LH_Source, but when they try to pull the table to a new semantic model (see below screenshot) from the LH_user, they get error saying that table (Employee) is not accessible. 

My understanding is the OneLake data access role should takes care of all the access whether use try to access it via lakehouse or SQL endpoint or semantic model, but what we observed conflicts this. Any idea why or we missed something from our end?

We also tried to share the whole lakehouse (LH_source) with the user group - via the route in below screenshot, and they can access table in a new semantic model, but this opens the whole lakehouse to the group, which is not what we wanted, as now they can pull any table in that LH_source via a shorcut in their own lakehouse. So anyway that we can allow the user to access only specific tables when building their own semantic model without having access to the whole source lakehouse?

[v-pagayam-msft]

Hi @stellahe102 ,
Can you please confirm whether you have resolved issue. If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.

[v-pagayam-msft]

Hi @stellahe102 ,
Thank you so much for explaining your scenario !
OneLake Data Access roles give users access to specific tables or folders for reading data, whether through a shortcut or directly. However, when it comes to creating a semantic model, it works a bit differently. To build a semantic model from a lakehouse, users need permissions at the lakehouse level, not just table level access. That’s why, when you shared the whole lakehouse it worked but it also gave them visibility to all tables in that lakehouse. Right now, there is no  way to restrict semantic model creation to just certain tables.
A common workaround is to create a separate lakehouse that only contains the Employee table, and give users build access there. That way, they can build their semantic model without seeing other data.
If like to see more granular permissions in the future, I would recommend posting your idea in the Fabric Ideas forum so the product team can take it into consideration. Hope this helps clarify things!
For more information,refer the below links:https://community.fabric.microsoft.com/t5/Data-Engineering/User-unable-to-access-semantic-model/m-p/...
https://powerbi.microsoft.com/en-in/blog/deep-dive-into-direct-lake-on-onelake-and-creating-direct-l...

Thank you.

Regards,
Pallavi.

[stellahe102]

Hi Pallavi. Thanks for the quick and helpful reply!

We appreciate you confirming that the build semantic model needs lakehouse-level access. We kind of feel that way, but it's good to get it confirmed. If you have any doc links around this, please share?

Context - our user case is a lakehouse without schema

Several follow-up questions:

1.  It seems that "build report" access on the lakehouse is not enough. We need to give the "Read All SQL Endpoint data" option, too, so the user can create a new semantic model. Can you confirm this is true?

2. This workaround you mentioned - "A common workaround is to create a separate lakehouse that only contains the Employee table, and give users build access there. " does the separate lakehouse need to contain the materiazlied tables, or just shorcut is enough? if later then source lakehouse to the shortcut needs to be shared as well?

3. we are thinking another workaround to have someone in the user's workspace who has lakehouse level access to create the semantic model with 1 table, and then share the semantic model with the user and no lakehouse-level access is needed for the user, will this work from microsoft's infrastuctre perspective? we can test this out, but the access takes up to 2 hours to reflect for us making it hard to run test with the users.

[v-pagayam-msft]

Hi @stellahe102 ,
Thanks for your detailed follow-up, your understanding is correct. To answer your questions: for Direct Lake semantic models, only Lakehouse Build permission is required to create a semantic model, users do not need the “Read All SQL Endpoint data” permission unless they are querying the SQL endpoint directly (Lakehouse Permissions - Microsoft Docs).
Regarding the second question, yes, you may create a shortcut in a separate Lakehouse that points to the Employee table. Just make sure users have Read permission on the source Lakehouse where the Employee table resides and Build permission on the new Lakehouse where they are creating the semantic model.
Finally, if someone else creates the semantic model and shares it, this also works. As per my knowledge, If the semantic model is in Direct Lake mode, users might still need Read permission on the Lakehouse at runtime to query the data; however, if the semantic model is in Import mode, no Lakehouse access is required after the data is imported (Dataset Permissions - Microsoft Docs, Direct Lake Deep Dive - Microsoft Blog).

Hope this helps.

Thank you.

[stellahe102]

Hi @v-pagayam-msft 

Only giving "Build" permission doesn't seem to work, the user can only create the semantic model and report when having "ReadAll" Access, we have tested this around. here is the full journey:

1. In the source lakehouse, create a Onelake Data Access Role for the users, and grant access only to one table

2. Create a shortcut in user's lakehouse pointing to the the shared table, let's call this the shortcut table.

3. User cannot create semantic model in their own workspace by selecting the shortcut table, we give all access at the lakehouse level:  

4. user now can select the shortcut table when creating the semantic model, but once we remove the "Read all SQL endpoint data" access, user has error when trying to refresh the semantic model which used to work, and also error in the report:

Two questions:

1. Can the screenshot in my original message be removed?

2. the use case here is essentially: user has an existing lakeshouse with some tables, and needs to build semantic model and report by using some existing table in their lakehouse and only one table of a lakehouse in another workspace, what would be the most simple and correct way to share that one table vs whole lakehouse from other workspace?

please let me know if ways we have tried are not the recommended and there is a better approach.

[v-pagayam-msft]

Hi @stellahe102 ,
Thank you for the followup. We are happy to assist you!
Users need both Build and "Read all SQL endpoint data" permissions to use shortcut tables in Direct Lake semantic models, as per my knowledge. Table level access alone may not enough. The best workaround is to create a separate lakehouse with just the needed table and grant access there. Alternatively, someone with access can create the semantic model and share it.
Refer the links below for more information:
https://learn.microsoft.com/en-us/fabric/fundamentals/direct-lake-develop
https://learn.microsoft.com/en-us/fabric/fundamentals/direct-lake-overview
For screenshot removal, please edit your post.
I hope this helps.
Thank you.

[stellahe102]

Hi @v-pagayam-msft Thanks for the patience here.

On the approach of creating a separate lakehouse, I can see 2 options:

1. A new lakehouse with a shortcut pointing to the source  - if this, then the whole source lakehouse needs to be shared again, so there is no point in creating a new shortcut lakehouse, right? 

2. A new lakehouse with materialized/real data for the needed tables, won't we create too many duplications of the same data if following this option? 

Please let me know which option you were referring to when suggested that approach, or I misundestand anything.

[v-pagayam-msft]

Hi @stellahe102 ,
Thank you again for the follow-up.
As per my understanding, creating a new lakehouse with just the needed tables materialized is the better approach. While it may lead to some data duplication, it is currently the only way to provide access to specific tables without exposing the entire source lakehouse. Option 1 might still require sharing the full source lakehouse, so it may not fully solve the access concern.
Hope this helps!