Forum Discussion
Helper VDataflow Gen 2 - how to join tables and load results, without loading initial tables?
Hi,
I understand you are trying to load the tables from on-prem to a lakehouse/data warehouse on Fabric.
The option to disable the load we had before was very useful because the dataflow was a starting point to the modeling. Once created, the datasets created over the dataflow would "see" the loaded tables and many times we wouldn't like that. But this doesn't mean that during the refresh processing the tables were not loaded into memory to produce the new one you "modelled".The Dataflow Gen 2, on the other hand, is used to load the data into the lake, it's not a modelling starting point. In this way, you don't need to bother about "loading" the table. For each table you would like to load into the data lake, you need to set a destination.
So, after merging the tables the way you would like, just set the destination on the resulting table, ignoring the others, and only the resulting table will be loaded into the lake, where in fact the modelling will happen. The "work tables" created in the middle of the ETL will not be on the lake.
But nothing changes in relation to how heavy the processing is. If your concern is about the processing, maybe you would like to consider incremental load options.
Kind Regards,Dennes
Power ParticipantHi, Scott,
It took me a while to understand what you mean.
In summary, you are referring to the fact that sometimes, when you disable the load of some dimensions, the query for the fact had the opportunity to fold, becoming a single query sent to the source.
I will put a lot of guess on this, but I think the folding opportunity was a side effect of the disable load. The disable load was mainly intended to not save the table on the result, if it could fold the query or not, was a side effect.
The problem is that you are right: It was a very nice side effect and we are missing it.
I suggest registering this as an issue on https://community.fabric.microsoft.com/t5/Issues/idb-p/Issues . But this is a complex one. You will need a very good example and step by step to illustrate this as an issue.
Kind Regards,
Dennes
Helper VHi DennesTorres , I don't think in my case it was a side effect - it's literally critical for anyone who wants or needs to load only slices of data instead of entire tables. Note I'm documenting below a very simple example, mostly so I can link to this post when I enter the issue as you suggested (which is the proper way to go - thanks!)
Below is a very simple example of a dataset (I work for a hospital system). The steps I go through to build any dataset like this is (note - see picture at bottom for final result):
Step 1: Bring in SQL Server table definitions for all tables that will be in use (our DW is on-prem SQL server). Set all of those tables to DISABLE LOAD and store them in a "sources" group
Step 2: Create a "fact driver" staging table that focuses on the set of data I'm interested in. In this case I start with the DateDim src table, filter it to the set of dates needed, and then join this to the hospital admissions fact source table. This allows me to subset the original fact table - which is functionality we seem to have lost. This also completely folds to SQL server - so ONLY the records I want are pulled across. Note that the staging tables also have load disabled (to support step 3c below)
Step 3a: Create the "final model" date dim - by using the original Date Dim and applying the same filtering logic used in the staging fact table.
Step 3b: Create the final model fact table - really just a reference to the staging fact table, but with load ENABLED
Step 3c: Create the remaining "non-driver" dimension tables - but only containing values that actually show up in the staging fact table. For example with PatientDim, I start with the staging fact table, remove all columns except the PatientKey column, deduplicate it (because a patient may have > 1 hospital admission), then join it to the source PatientDim table. This results in a final model PatientDim that only has the subset of patients that actually had a hospital admission. This also cleanly folds back to SQL Server so it's very fast. In our case, the source patient dim has 14.6 million rows (type 2 dim), but the final Patient Dim has only about 600,000 rows. We also seem to have lost this capability.
Interested in whether this seems clear or if there's a better way I could phrase this to make it easier for the issues team to understand.
Thank you!
Scott
Picture of final dataset: