New Topic

Community Topic Idea Data Sources

David Registro

APAC SD Team

Mark McGuire

Yellowfin Support Team

Assigned

Nathan Schroeder

NA Business Team

Chris McCarthy

Yellowfin Support Team

Category Data Sources

Product Version 7.3

Ability to change field Data Types in View

Ashleigh Gray shared this idea ago

Not Planned

Hi,

I was trying to append a subquery to a report with two different views by joining on the date field, but Yellowfin said the data in both fields had to be of the same type. The date field in the second view was correctly identified as a 'date' data type, but in my first view it has been picked up as a varchar. Viewing the data in the database I can see that both fields have the 'date' data type, but I can't get Yellowfin to pick up the change in data type. I can only assume it was varchar when I first made the view, as creating a new view on the same table forces Yellowfin to correctly identify this field as a date. Although I could make the view again, I have about 40 calculated measures in it so would rather not rebuild it again. I've restarted Yellowfin and refreshed my table but can't seem to get the change to go through.

Any help would be greatly appreciated!

Ashleigh

Files:

I like this idea 0

Replies (4)

Nathan Schroeder ●

Hi Ashleigh,

Once a view has been setup, Yellowfin prefers that the underlying structure of the view remain unchanged, so that content can be created consistently on top of it. To adjust this field after the fact we have two options:

Create a new view and then import the old content on top of the new. (you have mentioned this is not ideal)
Adjust the underlying field's value within the configuration database.

For the second option, we will need to directly update this column's definition which can be found in the "reportfieldtemplate" table of the configuration database that you specified on initial installation. If you have not done a large amount of work with this field, then switching it should be relatively simple. However if this field is used in reports, or in advanced calculations, you will most likely receive errors within those reports after switching the value.

As with any changes to the configuration database, always back up your database prior to making changes so that you can roll back if something goes wrong. I have not had an opportunity to thoroughly test this yet, but will be able to in the next few hours.

Please let me know your thoughts on this.

Regards,

Nathan

Reply URL

Ashleigh Gray ●

Hi Nathan,

I managed to work around it by exporting the view, changing the field type to DATE, importing it as a new view, then importing the old reports to point to the new view. Not the neatest solution but as a one off to fix my problem it isn't too bad. Thanks for your help!

Ashleigh

Reply URL

Nathan Schroeder ●

Hi Ashleigh,

Glad to hear you found something that works, and no problem! I am going to set this ticket to closed for now, but if there is anything else I can do to help here, please just let me know and the case will be re-opened!

Regards,

Nathan

Reply URL

Zack Wilson ●

Nathan,

Why should I have to work around this..? Shouldn't I be able to change this? If not, it needs to be an idea. I have had to do this 3 times, just this morning in order for my sub-queries to join properly. AND, I am working in version 9 (by now, I would have thought this would be fixed).

I also tried to "convert" the field in the view to text and then to numeric. This changed the field type, but still would not join (my assumption is because it wasn't imported this way).

I should have the permission, at an admin level, to change the structure of the view.

Thanks,

Zack

Reply URL

David Registro ●

Hi Zack, just letting you know Nathan is no longer working in support, though I understand your request and we will get back to you on this.

Thanks,

David

URL

Mike Sheehan ●

Hi Zack,

I've just gone through some testing and can confirm that you cannot join mismatched data types in sub-queries and at this time, field types in views appear to be fixed. The view needs to be exported and rebuilt in order to have reports make use of changes to data types.

It is a little strange that even when using a converted data type, they cannot be joined with sub-queries. As referenced in Nathan's earlier response, one current possibility would be changing the data type from the config db, but this is of course problematic if this field is used in reports, or in advanced calculations, as you would most likely receive errors within those reports after switching the value.

What I've done is created an internal enhancement request for this and set it to Highest priority. I'll provide an update to you regarding this as soon as able. I should have something soon given the priority.

Regards,

Mike

URL

Zack Wilson ●

Mike,

Awesome! Thank you. I am glad to see it is not something specific to me! ;0)

-Zack

URL

Mike Sheehan ●

Hi Zack,

You're welcome. Indeed it's not. It's just one of those purposeful design choices that should be revisited and reconsidered!

Regards,

Mike

URL

Dean Flinter ●

Hi Mike,

Just wondering if there is an update on this?

I'm currently faced with a situation where some int fields will be changed to a varchar. I had just assumed that there was some sort of metadata refresh functionality but based on the above that is not the case. This pending change will break a lot of reports and it's not really feasible to manually edit them all

Thanks

Dean

URL

Mike Sheehan ●

Hi Dean,

Thanks for reaching out. I've updated the internal enhancement request to reflect that you're requesting this enhancement as well. I've also pinged the dev team for an update and to see if they can provide some way of updating these fields globally, rather than manually. I'll let you know as soon as I hear back.

Regards,

Mike

URL

Mike Sheehan ●

Hi Dean/Zack,

I've received feedback on this:

"Changing the datatype of a field, is going to have cascading affects to reports, especially if these fields are being used in charts, or are used as metrics in cross-tabs etc.. to do this properly we'd need to do a full dependency check on all affected reports, charts, source filters, cached filters, signals jobs and then prompt the user as to what to do with the column in each case."

In cases where the schema is so dynamic, it's recommended you use database views as a protective layer between Yellowfin and the raw tables.

Then another dev team member stated the following: "Most of the functionality is built with the idea in mind that fields have a specific data type. In order to get this to work properly, everything would have needed to be built on the premise that data types could change. Some things might work, but others might not, and the outcome could be very unpredictable. Even scoping a task like this would require a significant amount of time of investigation."

Based on this information, there is no simple way of doing this and given the scope of this enhancement it's unlikely this would be developed out any time soon, but please let me know if you have any follow up questions on this.

Regards,

Mike

URL

Dean Flinter ●

Hi Mike,

Sorry to resurrect this again

In the end we changed the data from int to a decimal

I did some testing before this and Yellowfin seemed ok with the change. Simply changing the decimal places value displayed the correct data

However, today we realised that if we use this field in a filter, the decimal values get stripped out, resulting in no rows returned

I completely understand the feedback from the devs but I was wondering if it would be so bad changing from one numeric type to another via updating the configdb?

Just want to get your thoughts before we attempt it

Thanks

Dean

URL

Dean Flinter ●

I forgot to mention that the fields we changed were ID fields so they are defined as dimensions and are not used in any calculations etc

URL

Mark McGuire ●

Hi Dean,

I hope all is well,

I have questioned this with our development team, hopefully I can get some feedback asap.

Regards,

Mark

URL

Mark McGuire ●

Hi Dean,

I have since had feedback although I am still awaiting additional feedback to further assist;

The change might be possible with SQL updates to the config DB, the problem is there is no guarantee the field will keep working across the application (hence supporting it as a feature would be very hard).

Going forward you could possible try making the attempt of changing it, and see if that works out for your content, but there is no guarantee from us it will work, and is likely to break things if it's widely used across reports, charts, filters, dashboards, signal jobs etc...

Let me know your thoughts on this Dean.

Regards,

Mark

URL

Dean Flinter ●

Thanks Mark

We tried it in our dev environment and it seems to have worked but there is a lot of reports I need to check before I'd feel comfortable doing it on production. I think we're lucky that the affected fields are primarily IDs so I can't think of a situation where they are used in a calculation or chart etc

Thanks

Dean

URL

Mark McGuire ●

Hi Dean,

Thank you for getting back to me and letting me know the results having tested this at your end. Going forward I will re-mark this as Idea Logged and await additional feedback from the wider team.

Regards,

Mark

URL