Forum Discussion
Cross reference table column with multiple values in Fabric Dataflow
Hello cpwebb,
Thank you for your reponse. There are 38 more columns that we had to deal with including cg and Cg_part We took an approach of creating views on our Database with required joins and then use views as source on dataflows to import data. We are not quite certain if this is an ideal way. but this approach seems to work for our needs.
I am not a DB guy, but here is what I've seen... If what I am about to say is wrong, someone please correct me.
It is indeed better to do some operations at the source, ie the DB, and sometimes not. It really depends on many factors, such as whether your DB can have materialized views (Oracle, but not SQL Server), or if you have a competent DBA that has done a top notch job at optimizing the DB performance, then I'd say do as much as possible in the DB. But if not, PQ in the cloud can save the day.
This business of views, if the tables keep growing and growing, and everytime you request the view, it has to be rebuilt. There could be performance issues one day. A solution would be to push the raw data into a Fabric lakehouse, then use the SQL Analytics endpoint to create your views in the cloud and feed these to your DFg2. Or instead of the SQL endpoint, use a Spark notebook, which can actually write and create new tables in the lakehouse, if I remember my webinar correctly. The SQL endpoint can't do this, only create views, functions, and procedures.