topic MashupException.Error while trying to load incremental data by DateTime field in Dataflow

MashupException.Error while trying to load incremental data by DateTime field

Anonymous — Wed, 22 May 2024 10:47:26 GMT

I am currently trying to follow the suggested approach here: https://learn.microsoft.com/en-us/fabric/data-factory/tutorial-setup-incremental-refresh-with-dataflows-gen2

I did an intial load to my table, which now contains ~ 600 records; I set up another Dataflow Gen 2, as you can see in this screenshot:

In this first step, I calculate the value I want to use as "watermark", and since I do not have a constantly-incrementing-integer-ID, I opt to use a Date-Time column "CLVLMDATE";

As you can see, I get a value back in the preview - that's good.

I also get data back, in the step 2, which actually uses the output-value from before - that's also good

Using the "Append" method, I then "Publish" my Dataflow Gen2.

But then, I get this error result:

I suspect, that there is some syntactical issue when the Dataflow Steps are combined in that Mashup Syntax?

Maybe there is some CAST, or CONVERT which I could introduce, so the Dataflow executes successfully in the end?

Can somebody elaborate a little bit on that Mashup? I guess this has to do with combining different data locations, i.e. here this would be my on-premise-oracle-database and my lakehouse.

Isn't it unfortunate, that in the power query assistant preview, everything seems to run perfectly, but when published the dataflow runs in an error?

Here I have put in the error message in plain-text for your viewing convenience.

HALL_BI_CUSLOADPROFILEVALUES: Error Code: Mashup Exception Expression Error, Error Details: Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: Fehler beim Einfügen einer Tabelle., InnerException: Der Operator "<" kann nicht auf die Typen "Table" und "DateTime" angewendet werden., Zugrunde liegender Fehler: Der Operator "<" kann nicht auf die Typen "Table" und "DateTime" angewendet werden. Details: Reason = Expression.Error;Message = Der Operator "<" kann nicht auf die Typen "Table" und "DateTime" angewendet werden.;Detail = [Operator = "<", Left = error "Microsoft.Mashup.Engine1.Runtime.ValueException: [Expression.Error] Es wurde kein Wert angegeben.#(cr)#(lf) bei Microsoft.Mashup.Engine1.Language.ValueCreator.CreateValueForThrow(IThrowExpression throwExpr)#(cr)#(lf) bei Microsoft.Mashup.Engine1.Language.ValueCreator.<>c__DisplayClass23_0.<CreateValueForRecord>b__0(Int32 index)#(cr)#(lf) bei Microsoft.Mashup.Engine1.Runtime.RecordValue.DemandRecordValue.get_Item(Int32 index)#(cr)#(lf) bei Microsoft.Data.Mashup.ProviderCommon.MashupResource.TryGetValue(Func`1 getValue, IValue& value, String& errorMessage)#(cr)#(lf)Record", Right = #datetime(2006, 2, 21, 10, 1, 9)];Message.Format = Der Operator "#{0}" kann nicht auf die Typen "#{1}" und "#{2}" angewendet werden.;Message.Parameters = {"<", "Table", "DateTime"};Microsoft.Data.Mashup.Error.Context = User GatewayObjectId: 8337=========459a2e (Request ID: f92=========a87).

Re: MashupException.Error while trying to load incremental data by DateTime field

LuitwielerMSFT — Wed, 22 May 2024 14:03:35 GMT

Hey there! I am the PM that wrote the guide on how to do this. This is a great way to amass your data incrementally and using the date field makes sense!

What I noticed is that you still have the first query that retrieves the timestamp with staging enabled. Can you try to disable the staging on the query that retrieves the timestamp?

additionally I noticed you used the the first row from the output as getting the latest timestamp. Could you consider using this function instead? It is a bit simpler and cleaner.

Re: MashupException.Error while trying to load incremental data by DateTime field

Anonymous — Wed, 22 May 2024 15:24:28 GMT

Thank you @LuitwielerMSFT , your suggestion turns out to resolve the issue. Would you bother to briefly explain how "Mashup" and "Staging" are correlated? I guess with disabling "Staging" I circumvent the need for the Mashup to happen, which seem to not support that kind of operation?

Or has it been the way I retrieved the maximum Date for further processing?

Re: MashupException.Error while trying to load incremental data by DateTime field

LuitwielerMSFT — Wed, 22 May 2024 21:09:07 GMT

Awesome! Well what happens is that when staging is turned on, the dataflow engine is storing the results into the staging lakehouse/warehouse. When the dataflow engine then is asked to use the results of the query it retrieves it from the "staging table" and makes the result a "table variable". I'm taking this feedback with me to the engineers while we work on a better design for staging and how we work with variables stored in staging that are not in a table format. Thanks for highlighting this! And stay tuned for the incremental refresh feature that is on the roadmap 😉

Re: MashupException.Error while trying to load incremental data by DateTime field

Anonymous — Thu, 23 May 2024 08:08:46 GMT

Thanks for the details @LuitwielerMSFT
Glad that your query got resolved @Anonymous . Please continue using Fabric Community for any help regarding your queries.