Managing logical data unbundling between companies

[pmscorca]

Hi,

in a Fabric environment I've three workspace, the DataWS having a warehouse with all companies data, two Power BI reporting workspaces, the RepWS01 for the main company and the RepWS01 for the other companies.

I need to implement a logical data unbundling or separation between the main company and the other companies.

I don't want to duplicate warehouse tables in the DataWS workspace.

I think to create a lakehouse in the two reporting workspaces to allows the users to access and query the warehouse; each lakehouse could "see" the warehouse by a shortcut.

Moreover, I think to create in the warehouse specific views for the main company and for the other companies, simply filtering the companies. I could also implement the RLS in the warehouse.

Which could it be the right solution in a such scenario? Thanks

[stoic-harsh]

Hi @pmscorca,

I suggest not to reuse a Fabric Warehouse across workspaces for secured access (RLS/CLS) via Lakehouse shortcuts. That pattern is not supported and will behave inconsistently.

Keep one warehouse in DataWS, expose company-specific views/tables + RLS/CLS in the same workspace, and build separate semantic models on top of these views/tables, and finally share the reports or semantic models, not shortcuts.

[ssrithar]

Hi @pmscorca ,

In this scenario, the simplest and most maintainable solution is to keep one central Warehouse in the Data workspace and implement logical data separation using SQL views and security, rather than duplicating data or introducing extra Lakehouses.

One Warehouse → filtered SQL views → Warehouse RLS → Power BI semantic models per reporting workspace.

This approach provides clear data separation, strong security, and minimal operational complexity.

If this post helps, then please appreciate giving a Kudos or accepting as a Solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[pmscorca]

Hi, thanks for your reply.

I intend to have one warehouse in the DataWS workspace, where I would like to create some filtered SQL views, but the end users will be able to access to the two reporting workspaces, RepWS01 and RepWS01, where they will be able to use the reports and query by a SQL Analytics endpoint; for this reason, I think to create one lakehouse for each reporting workspace and one shortcut linked to the unique warehouse.

Thanks

[v-tejrama]

Hi @pmscorca ,

There is no need to introduce a Lakehouse or shortcuts in this scenario.

Warehouses can be accessed across workspaces, including via the SQL Analytics endpoint, with security consistently enforced at the Warehouse level rather than the workspace level.

By creating filtered SQL views in the central Warehouse and applying the necessary permissions or row-level security, users in either reporting workspace will only have access to the data they are permitted to see. Implementing a Lakehouse with shortcuts would not enhance isolation or security and would add unnecessary complexity. Maintaining a single Warehouse with logical separation through views and security is the most straightforward and sustainable solution for this setup.

Thank you.

[pmscorca]

Hi, thanks for your reply.

Which is the object to create in the reporting workspaces in order to query the views of the unique warehouse in the data workspace?
Is it possible to creare a SQL Analytics endpoint without a lakehouse or warehouse?

Thanks

[v-tejrama]

Hi @pmscorca ,

In the reporting workspaces, you do not need to create a Lakehouse or another Warehouse in order to query the central Warehouse. You also cannot create a SQL Analytics endpoint as a standalone object. A SQL Analytics endpoint is automatically provisioned when you create a Warehouse or a Lakehouse, and it is tied to that item. It does not exist independently and cannot be created on its own in another workspace.

What you should create in each reporting workspace is a semantic model that connects directly to the existing Warehouse in the data workspace. When you create a new semantic model, you can select the Warehouse from the other workspace as long as you have the appropriate permissions. From there, you can select only the specific views you created for the main company or for the other companies.

All security is enforced at the Warehouse level. If you define filtered views, apply schema level permissions, or configure row level security in the Warehouse, those rules will be respected regardless of which workspace the semantic model is created in. Workspaces are containers for organizing content, but they do not provide data isolation by themselves.

Because of that, introducing a Lakehouse with shortcuts would not add any additional isolation or security. It would simply introduce extra objects to manage without changing how access control is enforced. Keeping a single centralized Warehouse, defining the appropriate views, and managing permissions there is the cleanest and most maintainable approach for your scenario.

Thank you.

[pmscorca]

Hi,

• having an unique and centralized data warehouse in an unique dedicated data workspace is ok for me;
• creating schemas and views for specific companies in the unique data warehouse is ok for me;
• having user group for specific companies is ok for me;
• having a reporting workspace for the specific company, where will access the specific user group, is ok for me;
• having a semantic model and the related PBI report for each reporting workspace is ok for me.

Now, I need to have a place in each reporting workspace to allow to the end users to query the SQL views using T-SQL language not Power BI.

Thanks

[v-tejrama]

Hi @pmscorca ,

Thank you for your follow up. In this case, it is not necessary to create a separate Lakehouse or additional Warehouse within the reporting workspaces. A SQL Analytics endpoint is automatically set up with a Warehouse and cannot be created separately, so the required endpoint already exists as part of the current Warehouse in the data workspace. To access it from a reporting workspace, you can create a semantic model and connect it directly to that Warehouse, as long as the necessary permissions are in place.

All views, schema level permissions, and row-level security defined in the Warehouse will be consistently enforced, regardless of where the semantic model or reports are located. Adding a Lakehouse with shortcuts would not provide additional isolation or security and would only increase complexity.

Thank you.

[pmscorca]

Hi,

I repeat that in the reporting worspaces the end users should use PBI reports and be able to make queries in T-SQL language against the unique warehouse in the centralized data workspace.
Using a semantic model is it possible to query the data warehouse in T-SQL language (writing SELECT ... FROM ... WHERE ...)?

[v-tejrama]

Hi @pmscorca ,

Thanks for taking the time to clarify the requirement. If your end users need not only to consume Power BI reports but also to write and execute T SQL queries directly against the centralized Warehouse, then a semantic model by itself will not meet that need.

A semantic model is designed for analytical consumption and is queried using DAX or through report interactions. It does not expose a T SQL interface and you cannot run statements such as SELECT FROM WHERE against it. This is consistent with Microsoft Fabric documentation, which describes the Warehouse as the item that provides a full T SQL surface area through its SQL analytics endpoint, while the semantic model sits on top as a reporting layer.

Every Fabric Warehouse automatically includes a SQL analytics endpoint. That endpoint supports T SQL connectivity from the Fabric portal query editor as well as external tools such as SQL Server Management Studio or Azure Data Studio, provided the user has the appropriate permissions. The endpoint is part of the Warehouse and does not need to be created separately.

Cross workspace access is supported. If users in the reporting workspace are granted permissions on the Warehouse in the data workspace, they can connect directly to its SQL analytics endpoint and run T SQL queries there. Any object level permissions, schema permissions, or row level security defined in the Warehouse will be enforced consistently, regardless of which workspace the user is coming from.

So in your case, the correct design is to keep the single centralized Warehouse, manage security at that level, allow reporting workspaces to build semantic models for Power BI reporting, and grant authorized users direct access to the Warehouse SQL analytics endpoint when they need to execute T SQL querie.

Thank you.

[pmscorca]

Hi, many thanks for your reply, but I think that my question is still not very clear to you.
The end users are business users not technical users: for them the warehouse views must be queryable from the reporting workspaces not from the data workspace.

Thanks

[v-tejrama]

Hi @pmscorca ,

Thank you for the clarification. I understand the users are business users expected to work within the reporting workspace while querying centralized Warehouse views using T SQL.

In Fabric, T SQL queries can only be executed against the SQL analytics endpoint of a Warehouse or Lakehouse, which resides within the data item itself and is not replicated in other workspaces. Workspaces serve as organizational and security containers but do not provide their own T SQL execution surface for Warehouses located elsewhere.

Therefore, even if users are part of the reporting workspace, T SQL queries must be executed against the centralized Warehouse’s SQL analytics endpoint. There is no supported way to make the Warehouse’s T SQL engine function as if it belongs to the reporting workspace without creating a separate Warehouse or Lakehouse there.

If the goal is to prevent users from navigating into the data workspace, this can be managed through access and governance by granting direct access to only the relevant Warehouse and its views, with permissions applied as needed. However, all T SQL activity will still occur against the centralized Warehouse endpoint. If all T SQL activity must remain within the reporting workspace, a new Warehouse or Lakehouse would need to be created there, which adds complexity and management overhead.

Ultimately, T SQL execution is always tied to the Warehouse that contains the data and cannot be moved independently to another workspace.

Thank you.

[pmscorca]

Hi, the requirement to be met is described in my first post. The end users will need to log in to the relative reporting workspace that represents the point of access in Fabric to consume the PBI reports and query the data in the unique data warehouse.

However, I need to grant the right permissions both the specific reporting workspace and the unique data workspace.

It seems very easy to create a lakehouse and, inside it, a shortcut to the warehouse for each workspace. In this manner I can meet the data unbundling requirement respect to the companies.

[v-tejrama · Accepted solution]

Hi @pmscorca ,

You can meet the data unbundling requirement by carefully managing workspace and data permissions. For each company, create a dedicated reporting workspace where the end users will access Power BI reports. Then, in a central lakehouse, create shortcuts to the unique data warehouse for each workspace.

This way, you don’t duplicate data but still provide controlled access. Make sure to grant the right permissions both on the reporting workspace and on the lakehouse,warehouse shortcuts so users can query the data without accessing other companies data. This approach keeps things simple, secure, and aligned with Fabric’s best practices for data access and separation.

Thank you.

[v-tejrama]

Hi @pmscorca ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[v-tejrama]

Hi @pmscorca ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[v-tejrama]

Hi @pmscorca ,

I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.

Thank you.