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.

Accepted answers

All comments

Misbah

@marcowohlgemuth

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

marcowohlgemuth

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.

Misbah

@marcowohlgemuth

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

marcowohlgemuth

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

Quick Links

Can you help?

Unanswered questions

Batch Script
Hello team, I am logging into Anaplan through SSO. I would like to create a batch script to import a file into Anaplan. For the Spoke model, I would like to run an process via a batch script to import data from the Data Hub. Can anyone provide me script for both the condition.
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…