Forum Discussion

fabricpribeiro's avatar
fabricpribeiro
Post Patron
8 months ago
Solved

Two DWHs in same layer but different workspaces

Dears,

 

I am designing a architecture model

 

I am now in the gold layer this layer will have tables in dimensionsal model (facts and dims)

 

And because we will be using a data mesh aproach , we are thinking to have each DWH in its own workspace 

 

So that one team is entirely responsible for everything in that ws while other team is responsible for another

 

Each workspace will be considered a data domain (master data, supply chain, finance, etc..) and each domain owner team will be able to create their own dimensions and tables there

 

We can also add the different workspaces to different capacities according to the needs

 

But I was thinking, we need to be able to share objects (data products)

 

For example, imagine that you have a MasterData Domain table like Customer

 

We will want to promote re-usability and hence, the fact tables on other domains whould be able to use the surrogate keys or even entire tables from the other domains.

 

As this are datawarehouses, not lakehouses, and because they will exist in different workspaces, Can I use views like : ws.dwh.schema.table.column to share information? or this is only possible if the two DWHs reside on the same workspace?

 

Thanks,

 

Pedro

 

 

 

 

 

 

 

and I will have several DWHs, each one in its own Workspace

 

 

 

 

workspaces

9 Replies

  • Pragati11's avatar
    Pragati11
    Super User
    8 months ago

    Hi fabricpribeiro 

    It is absolutely fine to create views and give users access to these views in the domain specific workspaces they are working.

    I am guessing you will be creating these views in the Gold layer workspaces that have a DWH.


    Another approach could be you keep your Facts & Dims in one Global workspace. 
    Give users relevant access to this global workspace and they can pull whatever they want in their domain specific workspaces. In this way you can also have a promited tables or semantic models that they can re-use.

    So, basically your Globak workspace becomes your main place for Dims & Facts with proper endorsed content.

     

     

  • tayloramy's avatar
    tayloramy
    Super User
    8 months ago

    Hi fabricpribeiro, 

     

    I don't think views can go across different workspaces, but shortcuts can. 

    You would probably want to make a shortcut to the fact tables you want to reference, and then make the views on top of your shortcuts. Same end result, but a few more steps. 

     

    If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution. 

  • tayloramy's avatar
    tayloramy
    Super User
    8 months ago

    Hi fabricpribeiro, 

     

    Yes you are correct. My apologies. 

     

    I'm not sure there's a good way to design this without duplicating data. 
    You can probably make lakehouses in the workspaces, and then use ADLS shortcuts to the warehouse tables, and then in your warehouses use a cross database view to select from the lakehouse, but that seems quite convoluted. 

     

    If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

  • v-veshwara-msft's avatar
    v-veshwara-msft
    Community Support
    8 months ago

    Hi fabricpribeiro ,

    Thanks for reaching out to Microsoft Fabric Community.

    Thanks to Pragati11 and tayloramy  for sharing your valuable inputs.

     

    Just to clarify how this maps to current Fabric behavior: cross-warehouse SQL queries and views are supported when the warehouses are in the same workspace. Direct SQL references across warehouses that live in different workspaces are not supported in Fabric Data Warehouse.

    References:
    Query the SQL Analytics Endpoint or Warehouse - Microsoft Fabric | Microsoft Learn
    Data Warehouse Tutorial: Create a Cross-Warehouse Query in a Warehouse - Microsoft Fabric | Microsoft Learn

     

    As mentioned by tayloramy , a supported way to work around this is to use OneLake shortcuts to bring data from another workspace into a lakehouse in your workspace, and then use that lakehouse as the source for cross-database queries within the same workspace.

    Similar threads for reference:

    Solved: Cross workspace lakehouse/warehouse queries do not... - Microsoft Fabric Community

    Solved: Cross Workspace access - Microsoft Fabric Community

    Solved: Creating Warehouse Shortcuts - Microsoft Fabric Community

     

    If the reuse requirement is mainly at the reporting layer, the option mentioned by Pragati11  around centralized or endorsed semantic models also fits within what Fabric supports today.

     

    Hope this helps clarify.

    Please reach out for further assistance,
    Thank you.

  • v-veshwara-msft's avatar
    v-veshwara-msft
    Community Support
    7 months ago

    Hi fabricpribeiro ,

    Just checking in to see if you query is resolved and if any responses were helpful.
    Otherwise, feel free to reach out for further assistance.

    Thank you.