Transformation flow time value adds decimals

Ticout Support Team shared this problem 7 months ago
Awaiting Reply

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:


/fdb2f069e2f81589f81f7e48d8d28174/43bf3f4caede06657cfc584fc5b6eb1c


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

Comments (36)

photo
1

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

photo
1

Hello Chris, the thing is that the table is created from the transformation flow, I'll check with them which version of mysql they are using.

photo
1

No worries! Do let me know. Also Happy New Year! I hope you had a pleasant break.

photo
1

Hello Chris,


the DB is MySQL version 5.6.51

photo
photo
1

Hello Chris,

the DB is MySQL version 5.6.51

sorry for the delay, Ive got Covid the past week.


Kind regatrds.

photo
1

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.

ALTER TABLE yourtable MODIFY opened DATETIME;
And then do it again for the field 'closed'.This document should help: https://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html 

Kind regards,

Chris

photo
1

Hello Chris,


I'll ask our client if the problem persists.

photo
1

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.

photo
1

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

photo
1

Hello,

the user is support@ticout.com

photo
1

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

photo
1

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.

photo
1

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

photo
1

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

photo
1

Hello Chris, yes, thanks.


Got in contact with our client and will try to update their installation as soon as possible.

photo
1

Great to hear! I'll close this one off for now.

Kind regards,

Chris

photo
1

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?

photo
1

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

photo
1

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.

photo
1

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

photo
1

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,

photo
1

No problem :) Do let me know.

Kind regards,

Chris

photo
1

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

photo
1

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

photo
1

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?

photo
1

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

photo
1

Hi Gonzalo,

Meeting invite sent through. Hopefully you've received it.

Kind regards,

Chris

photo
1

Hello Chris,


can we change it to friday instead? same time. Revo can't assist on thrusday.


Sorry for any inconvenience.

photo
1

Hi Gonzalo,

That's no problem, that actually works better.

See you on Friday!

Kind regards,

Chris

photo
1

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

photo
1

Hello Chris,

sorry but its impossible for us to join today, it should be tomorrow, at the same time.


Kind regards.

photo
1

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

photo
1

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"

photo
1

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

photo
1

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

photo
1

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

photo
1

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.

/19b333c6a1a36d29ecb7b5d154817be9

Kind regards,

Chris

photo
1

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

photo
1

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