Forum Discussion
Convert YYYYMMDD String to DateTime w/o breaking query folding
Thank you for the reply. Yes, I have already tried the Table.TransformColumnTypes approach along with Table.AddColumn. Both break query folding because I'm changing a text field to a date field. Right now, I've switched back to odbc.query and I am pushing as much into the SELECT query as possible to leverage the service on the initial step. It's not query folding but it may well improve performance, we'll see.
Thanks for the reply, alas my DB does not have a calander table - I created my calander table in PBI desktop.
MS please work on a solution to what must surely be a very common issue.
I guess you understand, but I mention it anyway since I know this is an important request. You can either ask the DBA to implement a simple DATE and DateInt/DateText referens table for you to use with all your joins.
If I remember correctly, this table can be in in any schema in the same DB, but it can't be a table in other DB, e.g. the temp DB. I Once tried using PQ code with Native SQL to create a global temporary table in the DB (using CREATE TABLE ##DateTimeTable...), but this didn't work if I remeber correctly. The reason had something to do with the posibilities to do PQ joins from different DB (since the temp DB is a DB of it's own)
But if you could get your DBA to add the Date reference table you are good to go
Alternativ you ask your DBA to add a view something like below (I'm not native SQL speeking) :
CREATE VIEW Table1_WithDateTime AS
SELECT
convert(...... datetime...) AS TimeColumn,
*
FROM Table1
And why shall the DBA do this to you.... Bacause the load/bandwithUsage of his SQL server will be less if you are able to do incremential refreshes.
And as a third alternative for the DBA. He might also be able to add a new column to the table. And if he uses cumputed column capabilities he will not allocate extra space, although indexes migth be a bigger issue to ashive