Forum Discussion
Fabric Environment Design Advice
Hi All,
I work for a company that will be providing Fabric capacities managed by us on a per-client basis. Currently we have a mirrored Microsoft SQL Server DB connections in place acting as a bronze layer.
I am constantly finding hard limnits with literally everything i am tryingg to achieve. Our data is very poorly structured, a flawed designt hat has spanned over decades and nobody is going to be able to fix it anytime soon. Prior to Fabric, SQL views have often been utilised to pull together data, these views typically reference data from anything between 5 - 15 tables.
Originally, i hoped we could have our data that sits in the Bronze layer feed into semantic models that would be structured and act as a 'Gold' layer - however sometimes there is the need for calculated columns to be generated whitch is a hard limit within this metholodgy. Measures can be put in place 'sometimes' - but not all of the time. It is somethign that from time to time will be needed.
Due to this hard limit i have instead looked at using 'views' via the SQL endpoint being built into the Semantic model - however my concern here is that as i stated earlier, the views reference a minimum of 5 tables - due to the fall back to Direct Query mode i feel like the performance of reports would be dreadfull - this leads me to the next paragraph...
Matrerialised views! Seems great on paper, however in reality i reach two hard limits, 1) Optimal refresh cannot levgerage incremental loads due to the source tables being a Fabric mirrored SQL database - you cannot enable the pre-requisite of Change Data Feed on these tables, it is still being developed by microsoft, 2) Even when they have released the CDF feature for this rsource table type, a Full overwrite is still a requirement should there be a deletion or ammendment of row within the referenced tables, these operations do happen within 90% of our tables, resulting in constant full overwrites even if CDF was a released feature. This would absolutley drain my capacitys so once again does not seem a viable option.
All i have been able to do so far is create notebook activities to create a Silver layer, however this introduced data duplication and also has the potential for inconsistencies.
I am quite frankly pulling my teeth out over this, literally every attempt i have made results in either hard limitations or potential problems.
This is the first time i have tried to engineer data, so i could be missing something - io really hope that is the case. Does anyone have suggestions? I need to essentially meet the below requirements:
- Utilise Direct Lake and to not fall back to Direct Query. (In order to minimise the potential of reports becoming slow performance wise.
- Try and achieve as near real time as i can and keep layers to a minimum - we dont have any crazy transformations, we simply just need access to the data and the ability to ad calculated columns.
- Ensure the soloution remains stable and stands the test of time, we intend to utilise F2 capacities so minimising the CU useage due to ETL operations is imperative.
- Data: As stated already, nothing special, simply accessing the data whitch we can via the bronze mirroed DB and have the ability to create calculated columns somehow.
Does anybody have any ideas? Is there anything i am missing? I am becoming beyond frstrated trying to mould this tool into a working soloution that does not rely on daily notebook refreshes into a silver layer. Fabric is very expensive and i feel like for the cost of it there should be a way to achieve the above in a simple manor, i really dont think i am trying to do anything crazy here....
Thanks you for your time reading through this, i appreciate it is quite a long post...
6 Replies
Hi michaelgambling,
In an environment this complex, I would recommend having a more mature ETL process than relying on views.
This does mean some data duplication as you'd have a notebook that transforms the data, but then you have clean(ish) data for your semantic models to sit on.
It would be great if we could just layer views on top of views, but in reality that is never going to perform well even if it did work.
THis probably isn't the answer you wanted, but I hope it helps.
Hi michaelgambling ,
I agree with the point raised by Taylor Amy. In scenarios where the source data is highly fragmented across multiple tables and requires additional calculated columns or transformations, relying solely on views can introduce performance and maintainability challenges.
A more robust approach in Microsoft Fabric is to introduce a lightweight transformation layer (Silver) using notebooks or dataflows. While this does introduce some level of data duplication, it allows you to pre-join and structure the data once, enabling semantic models to operate efficiently with Direct Lake without falling back to DirectQuery.
This pattern generally provides better performance, improved stability for reporting workloads, and clearer separation between ingestion, transformation, and consumption layers. For complex legacy schemas, a small ETL layer often ends up being the most sustainable option.
Hope this helps.
Thank you.Hi michaelgambling ,
We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.
Thank you.Hi michaelgambling,
May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.
Thank you.
As Taylor Amy mentioned, don't rush to a solution. It's not one single word answer for your case. Here are some things I can point out.
1. Keep the Bronze layer.
2. Start setup the Silver layer & build the notebook to process the data in Joins. Keep Fact and Dim tables at transaction level and define retention policy.
3. Build Gold layer with aggregations and make sure no fallback to direct query. Some of the models can be import mode it I requires once or day refresh with set of tables.
Remeber you can't achieve one gold layer table for all reports requirements.
Just a thought, and I would classify this as a bandaid solution and would not recommend it unless you need quick ROI on Fabric reporting, but you could create the materialized view in your source SQL server and mirror those into Fabric. It would allow you to do the cleaning and incremental refresh leverage your server's computing power and prevent that from affecting your capacity's compute.
Again, I would not plan this as a long term fix and would aim towards fixing your underlying data structure that is preventing utilization of the raw tables.
Hope the suggestion helps!
