Forum Discussion
Query Folding and Access Native Query for Incremental Refresh
>> some of my queries have some pretty complex transformations<<
How complex? I don't know about Teradata, but SQL Server can handle a lot of the transformations listed, even GROUPING and PIVOT/UNPIVOT. I chokes on things like splitting a field based on the instance of a delimiting character, or things like FILL DOWN.
If you absolutely MUST have your custom transformations and they preclude query folding in Power BI, then the best bet is to wrap all that in a view in the source database and simply reference the view instead of a base table.
But Power BI needs A) Query Folding and B) appropriate date column in order to support Incremental Refresh.
Hi, I was reading this and I wonder In case of DB like AS400/DB2 which doesnt support Value.NativeQuery, what if we pass Parameters (Start & End) as strings to native sql (complex) to be used in where clause. For sure the the model will refresh, but would it be able to create proper partition and proper incrmenatal setup?
RS = "'" & DateTime.ToText(RangeStart, "yyyy-MM-dd HH:mm:ss") & "'",
RE = "'" & DateTime.ToText(RangeEnd, "yyyy-MM-dd HH:mm:ss") & "'",
and inside native query we apply:
WHERE T.SNAP_DT >= " & RS & " AND T.SNAP_DT < " & RE & "