Date Format Issue when exporting from Anaplan to BigQuery with CloudWorks
Anaplan is using an unique date format “1 Jan 22” and “Jan 22” as opposed to “2022-01-01” and “2022-01”, which are common in data science. Therefore, when exporting data from Anaplan to BigQuery through CloudWorks, the date column needs to be formatted as string, because BigQuery can’t read “1 Jan 22” as a date. There are a number of ways to manipulate the data either on the Anaplan side or later on BigQuery. However, all these fixes are cumbersome workarounds for the real problem, that Anaplan is not using a common date format in the exports. I was hoping to get some feedback from other users that are using CloudWorks to export Anaplan Data to BigQuery on this matter? How do you deal with this? Thank you.
Answers
-
I think a good way is to create a line item in the exported module and transform the dates/time periods into the format that you require and use Tabular Single Column or Tabular mMultiple column option as an export option.
Misbah
Miz Logix
2 -
Dear Misbah
Agreed, there are ways to map the data either on the Anaplan side or on BigQuery. However, when I re-map the output to a custom date format in Anaplan and export that, I still end up with string formatted dates in BigQuery. Then I need to run a script on BigQuery to convert the date-strings to date-formatted dates. It works, but it's cumbersome.
I find it surprising that Anaplan has developed a connector (CloudWorks), but it's not possible to map date fields from Anaplan to date fields in BigQuery. I assume that Anaplan stores all the data in a database and that dates and periods are stored as datatype "date". If that's the case then there are numerous ways to cast dates to different date formats for export purpose. A connector (like CloudWorks) should maximise compatibility and include such functionality.
Maybe I am just missing that there is a place to make this kind of settings, but I haven't found it yet and I am curious how other users facing the same issue deal with it.
0 -
Cloudworks does not have such capability/functionality as of today. People usually do all the transformations in Anaplan before spitting the data out. But I agree to your point that CW should have such capabilities, However I recommend you put your idea on idea exchange forum here on community. Will upvote for it.
https://community.anaplan.com/t5/forums/postpage/board-id/AnaplanIdeas/search-before-post-mode/true
Thanks,
Misbah
Miz Logix
0 -
Sure, just posted the idea here: https://community.anaplan.com/t5/Idea-Exchange/Maxmise-compatibility-for-exports-containing-dates-and-periods/idi-p/150772#M11153
Thanks for your vote!
Regards,
Marco
0
