New Fabric Set up - Considerations

[michaelgambling]

Hi All,

Firstly, i am VERY new to Fabric and data analytics in general.

Here is my scenario:

1) I work for a software company, we have hundreds of clients and many are screaming for a Fabric soloution to be implemented.

2) The companies are completley seperate, and data isolation is imperetive.

3) Each company has a SQL database, hosted within our on-premesis envionment, housed on Microsoft SQL servers (4 in total accross our environment)

4) I *believe, the clients will want to try and access there respective Fabric data to potentially create there own reports / dashboards, as well as the ones we create for them inhouse.

I have begun exploring Fabric and so far i have done the following:

1) I have signed up for a 60-Day Fabric trial.

2) I have set in place an on-prem (virtual) datagateway. (will be testing the performance as i am aware physical server is preffered by MS due to potential virtual performance issues)

3) I have configured the data gateway within the Fabric instance as well as set up a connection to one of our on-prem SQL servers.

This is my basic foundation, from here i am trying to determine the best way to continue setting up the Fabric environment, some key things i need to ensure:

1) Performance - I need to ensure our on-prem servers do not have massive performance degradation as a result of Fabric processes.

2) Data security - I need to ensure data security is acceptable to uphold ISO standards.

With basic infrastructure in place, i am trying to determine the best way to set up dataflows. I have questions surrounding:

1) Warehouse vs Lakehouse - Our internal team is prodominantly SQL based, and our data is structured. From my reading and basic understanding, this means we are technically more suited for a Warehouse scenario, however i understand Warehouses are unsupportive of DirectLake - whitch i would like to use ideally..... Therefore would you suggest i try and work with a Lakehouse set up instead? Or simply look at a combo of import mode / Warehouse instead?

2) Import mode vs Direct Lake - Sort of leading on from the previous query, from my basic understanding, Direct Lake seems to be a perfect middle man for Direct Query benefits and Import mode benefits. My primary desire for DirectLake comes from the intention to put as least stress on our on-prem resources as possible.

3) Dataflow set up - Probably my most critical area that i am struggling with. We are looking to do the following regarding dataflows:

- Create a series of views in our on-prem SQL databases.

- Point the data source, of our dataflows to these views.

- Point the destination to a warehouse or lakehouse. With the intention to of course have a lake/warehouse per client, and to use a single sementic model in each, to push these views to.

- WIth the views in place, our SQL team will utilise PowerBI Desktop to open a connection to the lake/warehouse's symantic model and create report dashboards based off of these views.

Regarding this, i am very unsure of how best to structure this:

- Should i have a single ware/lakehouse per client > whitch has a handful? of dataflows  > that point to a single semantic model in said ware/lakehouse? I am under the pressumption that nothing will be overwritten by utilising a single semantic model, per client for their reporting dashboards as the data is solely read-only?

- Dataflows: As i have mentioned, our SQL team will be creating alot of reports that will be referencing various views, there will be alot of mixture of these views in different reports - as our data structure, historically, is pretty ugly. I am unsure what is best in terms of structuring my dataflows based on this fact. Obviously if i simply create a dataflow per view, it will become ridiculously unmanagable, but i am quite unsure how to structure them instead, as requirements will likely always be changing in terms of what views people will need to access for reports.

I do have more questions but will leave it at this for now as i am quite aware this is quite a lengthy post already...

Please forgive me if some points are not making sense, i am very new to Fabric and data analytics, this is a huge learning curve and i really want to produce a solid end soloution. Thank you for your time in reading this and please let me know if i can further expand / clarify anything!

Mike

[v-hashadapu]

Hi @michaelgambling , I wanted to check with you and see if the provided information was useful. If any of it helped resolve your question, consider marking it as "Accept as Solution" to make it easier for others to find. Let me know if there's anything else I can assist with!

[v-hashadapu]

Hi @michaelgambling , Thank you for reaching out to the Microsoft Community Forum.

Since your team is SQL-focused and the data is structured, a Warehouse might seem like a good fit, but only Lakehouses support DirectLake, which is critical if you want to minimize strain on your on-prem servers. DirectLake loads data directly from Delta tables in a Lakehouse into Power BI without scheduled refreshes or live queries back to SQL. This makes Lakehouse the better option. It still supports SQL through the SQL Endpoint, so your team won’t lose familiarity. If advanced SQL features are needed later, you can attach a Warehouse to the Lakehouse using shortcuts without duplicating data.

To isolate each client’s data and stay ISO-compliant, set up a separate Fabric workspace per client. Each workspace should contain one Lakehouse, a semantic model and their reports. The semantic model connects to the Lakehouse using DirectLake and is read-only, so reports won’t overwrite anything. Clients can build their own dashboards by connecting to this model in Power BI. If different departments need restricted views, apply Row-Level Security. For your internal SQL team, Power BI Desktop can be used to build and test reports before publishing.

Your plan to use SQL views as sources is sound. Instead of a dataflow per view, group views by business domain, like “Sales,” “Finance,” or “Operations” in a single dataflow. This keeps things manageable and scalable. Use parameters in your dataflows to make them reusable and flexible across different scenarios. If your views support it, enable incremental refresh so only new or changed data gets loaded, reducing impact on your SQL servers. Use clear naming conventions and replicate dataflows across workspaces with deployment pipelines to simplify onboarding new clients.

To protect your SQL servers, index and filter your views to avoid large unoptimized queries. Schedule dataflow refreshes during off-peak hours and monitor gateway performance. Your virtual gateway is fine for testing, but if you notice performance issues at scale, move to a physical server or increase VM resources. Security-wise, Fabric encrypts data in transit and at rest. Use Microsoft Entra ID for authentication, restrict workspace access to the right roles and enable auditing for traceability. Make sure your SQL servers use encryption like TDE for consistency.

During your trial, simulate a few clients end-to-end to validate this setup. Test Lakehouse ingestion, DirectLake performance, dataflow structure and workspace isolation. Your architecture will be scalable, secure, and efficient, with minimal load on your on-prem systems.

For more information on fabric, refer:

Microsoft Fabric documentation

If this helped solve the issue, please consider marking it “Accept as Solution” so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[mabdollahi]

Hey @michaelgambling ,
You're doing great — especially for someone new to Fabric and data analytics! It’s a lot to take in, but you’re clearly thinking about all the right things.

Just a few quick thoughts:

1. Lakehouse vs Warehouse: Since your team knows SQL and your data’s structured, Warehouse makes sense. But if you want to avoid putting pressure on your on-prem servers, Lakehouse with Direct Lake is a solid option. You can also use both — load into a Lakehouse, then connect a Warehouse if you want that familiar SQL feel.

2. Direct Lake vs Import: You’re spot on — Direct Lake gives you good performance without constantly pulling from your SQL servers. Definitely better than Direct Query for your case.

3. One Lakehouse/Warehouse per client: Yep, that’s a good approach. Keeps data isolated and secure. Using one semantic model per client is fine — nothing gets overwritten as long as your table names are unique.

4. Dataflows: Instead of one dataflow per view (which gets messy fast), try grouping related views — like a “Sales” dataflow or “Operations” one. You can always tweak later as needs change.

Honestly, you’re on the right track. Don’t worry if it feels like a lot — Fabric takes time to get comfortable with, and you’re already laying a solid foundation.

Happy to chat more if you have other questions!

Regards,
Mehrdad Abdollahi

[michaelgambling]

Thanks so much for the feedback, glad i am going in the right direction. Great shout regarding the dataflows!

Quick question on Direct Lake, obviously the tables i am creating are coming from SQL views, am i correct in saying that they will not function in DirectLake mode? And instead fall back to DirectQuery? Not quite sure i am getting the wrong end of the stick, i have go this impression from this MS blog post (see also the image attached referencing the particular bullet point).

Thanks again for all your input!!

Mike

[mabdollahi]

Hey @michaelgambling 
Totally fair question, and you’re not getting the wrong end of the stick at all. You're right; if you're pulling data directly from SQL views, that won’t work with Direct Lake. It would fall back to either Import or Direct Query depending on how it's set up.

To take advantage of Direct Lake, the data needs to already be in the Lakehouse. So if you load your view data into a Lakehouse first, then you're all set to use Direct Lake with your semantic model.

You're asking exactly the right things — it’s a bit of a shift in thinking, but you’re definitely on the right path!

Regards,
Mehrdad Abdollahi