APAC SD Team
Category
Data Transformation
Product Version
9.0
Transformation flow time value adds decimals
Defect Logged
Hello,
a client has the following setup for a transformation flow: from a view in the database the data is taken to the transformation flow, there is used to create new fields.
The problem araises on a date/time field that has no decimals on the origin and suddenly show on the destiny table, images from the database:
That field isn't transformed on any step and the client is worried about the cause of this. Is there any way to rectify this or explanation on the cause?
Yellowfin version is 9.6
Thanks
The same problem 
Hi there,
Thanks for contacting Yellowfin with this issue. This is likely down to the timestamp formatting choice on the destination database. What DBMS version is your client using here?
Kind regards,
Chris
Hi there,
Thanks for contacting Yellowfin with this issue. This is likely down to the timestamp formatting choice on the destination database. What DBMS version is your client using here?
Kind regards,
Chris
Hello Chris,
the DB is MySQL version 5.6.51
sorry for the delay, Ive got Covid the past week.
Kind regatrds.
Hello Chris,
the DB is MySQL version 5.6.51
sorry for the delay, Ive got Covid the past week.
Kind regatrds.
Thanks for letting me know. It looks like this has just been updated as this version was released earlier last week. Are you able to confirm this is still an issue?If so, it's likely to be the chosen datetime format for the destination table/database which is including a fractional value for the datetime, hence the milliseconds on the end.You should be able to change the format of the datetime field in the database with:Simply change the data type of the column to exclude the fractional part.
And then do it again for the field 'closed'.This document should help: https://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.htmlKind regards,
Chris
Thanks for letting me know. It looks like this has just been updated as this version was released earlier last week. Are you able to confirm this is still an issue?If so, it's likely to be the chosen datetime format for the destination table/database which is including a fractional value for the datetime, hence the milliseconds on the end.You should be able to change the format of the datetime field in the database with:Simply change the data type of the column to exclude the fractional part.
And then do it again for the field 'closed'.This document should help: https://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.htmlKind regards,
Chris
Hello Chris,
I'll ask our client if the problem persists.
Hello Chris,
I'll ask our client if the problem persists.
Hello Chris, I can't download the latest updater (or any installer for that matter) since my user its not recognized in portal.yellowfinbi.com , I've had access before the new changes.
Hello Chris, I can't download the latest updater (or any installer for that matter) since my user its not recognized in portal.yellowfinbi.com , I've had access before the new changes.
Hello,
Sorry to hear you're having issues with the new portal. It may be that you just need adding to a user group. Can I ask what username or email address you're using to log in with?
Kind regards,
Chris
Hello,
Sorry to hear you're having issues with the new portal. It may be that you just need adding to a user group. Can I ask what username or email address you're using to log in with?
Kind regards,
Chris
Hello,
the user is support@ticout.com
Hello,
the user is support@ticout.com
Hi there,
Do you have a name for the account - it doesn't look like there's a support@ticout.com present in our Okta portal and you'll need a portal account to download builds. If you need me to set you one up, let me know.
Kind regards,
Chris
Hi there,
Do you have a name for the account - it doesn't look like there's a support@ticout.com present in our Okta portal and you'll need a portal account to download builds. If you need me to set you one up, let me know.
Kind regards,
Chris
Hello,
we used to have access to the old section for version downloads, I don't understand what has changed, but we need this account to have access to the resources we used to, specially to download updaters.
Kind regards.
Hello,
we used to have access to the old section for version downloads, I don't understand what has changed, but we need this account to have access to the resources we used to, specially to download updaters.
Kind regards.
Hey Ticout, your account has been created which will give you access to yf product downloads +more.Please check your email for you activation details.
In terms of the portal changes, an email was sent to all users (I suspect your email was not on the list) along with an announcement here.
Hopefully this clears everything up and allows you to move forward. Of course, please let us know how it all goes.
Regards,
David
Hey Ticout, your account has been created which will give you access to yf product downloads +more.Please check your email for you activation details.
In terms of the portal changes, an email was sent to all users (I suspect your email was not on the list) along with an announcement here.
Hopefully this clears everything up and allows you to move forward. Of course, please let us know how it all goes.
Regards,
David
Hi Ticout,
Just checking in with you to see if you were able to activate your account and access downloads.
Let me know.
Kind regards,
Chris
Hi Ticout,
Just checking in with you to see if you were able to activate your account and access downloads.
Let me know.
Kind regards,
Chris
Hello Chris, yes, thanks.
Got in contact with our client and will try to update their installation as soon as possible.
Hello Chris, yes, thanks.
Got in contact with our client and will try to update their installation as soon as possible.
Great to hear! I'll close this one off for now.
Kind regards,
Chris
Great to hear! I'll close this one off for now.
Kind regards,
Chris
Hello Chris,
the client updated the installation, but the problem persists.
What would you need to deep into this problem further?
We got another issue with transformation flow with that client, would you prefer we open another ticket?
Hello Chris,
the client updated the installation, but the problem persists.
What would you need to deep into this problem further?
We got another issue with transformation flow with that client, would you prefer we open another ticket?
Hello,
That's unfortunate that it hasn't fixed the issue. Can I ask which version they updated to?
Did you also try my tip mentioned below regarding changing the datetime format for the destination table?
And yes, please could you log another ticket for the other issue, it makes things easier to track.
Kind regards,
Chris
Hello,
That's unfortunate that it hasn't fixed the issue. Can I ask which version they updated to?
Did you also try my tip mentioned below regarding changing the datetime format for the destination table?
And yes, please could you log another ticket for the other issue, it makes things easier to track.
Kind regards,
Chris
Hello,
they updated to yellowfin-9.7.0.3-20211215-full
the workaround isn't an option for the client, the transformation flow creates the table wrongly, its not something made manually.
Everything is thought to be automated, when one of their clients join the BI platform everything will be set up automated. Cant be done by hand for each table.
Hello,
they updated to yellowfin-9.7.0.3-20211215-full
the workaround isn't an option for the client, the transformation flow creates the table wrongly, its not something made manually.
Everything is thought to be automated, when one of their clients join the BI platform everything will be set up automated. Cant be done by hand for each table.
Hello,
It's probably best to get on a call to take a look at what's happening. It could be a number of things creating the table with fractional seconds. If the transformation flow is taking the timestamp and creating another table in the same data source then it could be the driver, how the transformation is set up or Yellowfin itself. It seems that one of those three is creating the table with a DateTime(6) value or it's not truncated properly.
Let me know your availability.
Quick side question, are you using the MySQL connector 5.1.49?
Kind regards,
Chris
Hello,
It's probably best to get on a call to take a look at what's happening. It could be a number of things creating the table with fractional seconds. If the transformation flow is taking the timestamp and creating another table in the same data source then it could be the driver, how the transformation is set up or Yellowfin itself. It seems that one of those three is creating the table with a DateTime(6) value or it's not truncated properly.
Let me know your availability.
Quick side question, are you using the MySQL connector 5.1.49?
Kind regards,
Chris
Hello Chris,
I'll get in touch with our client so we can schedule a meeting, when I have their time disponibility I'll get back to you.
Kind regards,
Hello Chris,
I'll get in touch with our client so we can schedule a meeting, when I have their time disponibility I'll get back to you.
Kind regards,
No problem :) Do let me know.
Kind regards,
Chris
No problem :) Do let me know.
Kind regards,
Chris
Hi Gonzalo,
Were you able to hear back from your client regarding this issue?
Let me know if it's still an issue for them.
Kind regards,
Chris
Hi Gonzalo,
Were you able to hear back from your client regarding this issue?
Let me know if it's still an issue for them.
Kind regards,
Chris
Hi Gonzalo,
I hope things are going well over there.
Just wanted to let you know I'll be closing this request due to inactivity. However, if you ever wanted to re-visit this or have anything else I can help you with, please let me know.
Regards,
Chris
Hi Gonzalo,
I hope things are going well over there.
Just wanted to let you know I'll be closing this request due to inactivity. However, if you ever wanted to re-visit this or have anything else I can help you with, please let me know.
Regards,
Chris
Hello Chris, sorry for the delay, we have been very busy.
would if be possible to have a 3 way call between us, Revo and you about this issue on Thursday 28 at 15:30 ( CET )?
Also, Revo has two other issues with transformation flows:
https://community.yellowfinbi.com/ticket/24640
https://community.yellowfinbi.com/ticket/25014
Could we review them on the same call?
Hello Chris, sorry for the delay, we have been very busy.
would if be possible to have a 3 way call between us, Revo and you about this issue on Thursday 28 at 15:30 ( CET )?
Also, Revo has two other issues with transformation flows:
https://community.yellowfinbi.com/ticket/24640
https://community.yellowfinbi.com/ticket/25014
Could we review them on the same call?
Hi Gonzalo,
Yes that's fine. I see Lesley's got the other two tickets. I'll have a chat with her to see where we're at. She might join us on the call.
Kind regards,
Chris
Hi Gonzalo,
Yes that's fine. I see Lesley's got the other two tickets. I'll have a chat with her to see where we're at. She might join us on the call.
Kind regards,
Chris
Hi Gonzalo,
Meeting invite sent through. Hopefully you've received it.
Kind regards,
Chris
Hi Gonzalo,
Meeting invite sent through. Hopefully you've received it.
Kind regards,
Chris
Hello Chris,
can we change it to friday instead? same time. Revo can't assist on thrusday.
Sorry for any inconvenience.
Hello Chris,
can we change it to friday instead? same time. Revo can't assist on thrusday.
Sorry for any inconvenience.
Hi Gonzalo,
That's no problem, that actually works better.
See you on Friday!
Kind regards,
Chris
Hi Gonzalo,
That's no problem, that actually works better.
See you on Friday!
Kind regards,
Chris
Hi Gonzalo,
Apologies, but Lesley has been taken ill today, we may need to postpone to next week, I hope that's okay. Does Tuesday work for you? (Monday is a bank holiday here)
Kind regards,
Chris
Hi Gonzalo,
Apologies, but Lesley has been taken ill today, we may need to postpone to next week, I hope that's okay. Does Tuesday work for you? (Monday is a bank holiday here)
Kind regards,
Chris
Hello Chris,
sorry but its impossible for us to join today, it should be tomorrow, at the same time.
Kind regards.
Hello Chris,
sorry but its impossible for us to join today, it should be tomorrow, at the same time.
Kind regards.
Hello Chris,
I've written a comment but it doesn't show, today is impossible for Revo or us, it should be tomorrow 4/05/2022 at 15:30 CET
also we didnt get any email with any invite, no sure you sent it to the correct adress. It should be de one on our profile.
Thanks and regards
Hello Chris,
I've written a comment but it doesn't show, today is impossible for Revo or us, it should be tomorrow 4/05/2022 at 15:30 CET
also we didnt get any email with any invite, no sure you sent it to the correct adress. It should be de one on our profile.
Thanks and regards
Hi Chris,
Can we hold this meeting this afternoon?
Please tell us something about it, our client does not understand this situation and neither do I, of course.
Saying nothing about it doesn't help anyone.
Pere
De: Yellowfin Support <support@yellowfin.bi>
Enviado el: viernes, 29 de abril de 2022 15:09
Para: Ticout Support Team <support@ticout.com>
Asunto: New Comment in "Transformation flow time value adds decimals"
Hi Chris,
Can we hold this meeting this afternoon?
Please tell us something about it, our client does not understand this situation and neither do I, of course.
Saying nothing about it doesn't help anyone.
Pere
De: Yellowfin Support <support@yellowfin.bi>
Enviado el: viernes, 29 de abril de 2022 15:09
Para: Ticout Support Team <support@ticout.com>
Asunto: New Comment in "Transformation flow time value adds decimals"
Hi Pere,
Yes, today is fine at 15:30 CET.
Here's the meeting invite.
---
Meeting to discuss:
https://community.yellowfinbi.com/ticket/23773
https://community.yellowfinbi.com/ticket/24640
https://community.yellowfinbi.com/ticket/25014
________________________________________________________________________________
Microsoft Teams meeting
Join on your computer or mobile app
Click here to join the meeting
Learn more | Meeting options
________________________________________________________________________________
Lesley will be on the call but she has tonsillitis, so I'll do the speaking on her behalf!
Kind regards,
Chris
Hi Pere,
Yes, today is fine at 15:30 CET.
Here's the meeting invite.
---
Meeting to discuss:
https://community.yellowfinbi.com/ticket/23773
https://community.yellowfinbi.com/ticket/24640
https://community.yellowfinbi.com/ticket/25014
________________________________________________________________________________
Microsoft Teams meeting
Join on your computer or mobile app
Click here to join the meeting
Learn more | Meeting options
________________________________________________________________________________
Lesley will be on the call but she has tonsillitis, so I'll do the speaking on her behalf!
Kind regards,
Chris
Hi guys,
Thanks for joining me on the call earlier where you showed me the issue.
I believe that on the call Jordi told us that the source database and the destination database are using two different MySQL versions, is that correct? Can I just ask to double check what they are - I've just seen in the MySQL documentation that since version 5.6.4, fractional seconds are being included as part of the datetime format.
Kind regards,
Chris
Hi guys,
Thanks for joining me on the call earlier where you showed me the issue.
I believe that on the call Jordi told us that the source database and the destination database are using two different MySQL versions, is that correct? Can I just ask to double check what they are - I've just seen in the MySQL documentation that since version 5.6.4, fractional seconds are being included as part of the datetime format.
Kind regards,
Chris
Just to add, the decimals precision problem and the datetime decimals may be two different issues. The first being solved by specifying the number of desired decimals in the initial step of the ETL. I can query this with our developer team as to why it would change from the source to the destination by default, however.
Kind regards,
Chris
Just to add, the decimals precision problem and the datetime decimals may be two different issues. The first being solved by specifying the number of desired decimals in the initial step of the ETL. I can query this with our developer team as to why it would change from the source to the destination by default, however.
Kind regards,
Chris
Hi guys,
Further to the below, I've been testing on my end, pulling the HSQL Ski Team data and creating a table in MySQL 5.7 and can see that it is also adding 4 decimals, even if I set the decimal precision to 2. I'll get a bug logged.
Kind regards,
Chris
Hi guys,
Further to the below, I've been testing on my end, pulling the HSQL Ski Team data and creating a table in MySQL 5.7 and can see that it is also adding 4 decimals, even if I set the decimal precision to 2. I'll get a bug logged.
Kind regards,
Chris
Hi Chris,
Do you know any version of MySQL that doesn't happen this error? If your answer is yes, do you recommend Revo upgrade to that version?
Kind regards
Hi Chris,
Do you know any version of MySQL that doesn't happen this error? If your answer is yes, do you recommend Revo upgrade to that version?
Kind regards
Hi there,
I've also tried this in MySQL 8.0.20 with the version 8.0.26 driver and ran into the same problem. Following that I also tried with the same MySQL version and the older 5.1.49 driver and still there are extra decimals being added. So I'm afraid that upgrading to a new MySQL is unlikely to solve the issue for Revo.
Kind regards,
Chris
Hi there,
I've also tried this in MySQL 8.0.20 with the version 8.0.26 driver and ran into the same problem. Following that I also tried with the same MySQL version and the older 5.1.49 driver and still there are extra decimals being added. So I'm afraid that upgrading to a new MySQL is unlikely to solve the issue for Revo.
Kind regards,
Chris
Replies have been locked on this page!