- Do you wonder why some columns from SQL come across as currency data types and why others come across as real data types?
- Are you tired of manually changing currency data types to real data types?
- Are you tired of using transformations to change currency data types to real data types?
One of my many roles is that of Spotfire administrator, and as the administrator, my most disliked task is manually changing currency data types to real data types, column by column. Not to mention that despite my diligent reviews of info links prior to publishing, sometimes a currency column will slip past me and make it into the production environment.
Until recently, I had no idea why Spotfire interpreted some numbers as currency data types and others as real data types. However, after recently changing approx. 50 columns from currency to real data types one by one, I went to talk to “the database guy”, and he explained that in the SQL database, these particular columns were decimal, not numeric. It appears that Spotfire takes any decimal data types (any number with a fixed number of decimals) and treats them as currency.
Now, you could speak with your database administrator about NOT using decimal data types in the database itself. However, other data types like float/double/real may be desirable for the Spotfire administrator but not so desireable for other tools, like SSIS. The database administrator may not want to change them in raw tables and risk breaking SSIS jobs. It may be an option to change in tables or views going forward.
I highly suggest using the TIBCO idea portal and asking them to make changes to the Information Designer that would facilitate changing the data type of multiple columns of data in a faster and more efficient way. https://spotfireideas.tibco.com/ideas/TS-I-5612
Guest Spotfire blogger residing in Whitefish, MT. Working for SM Energy’s Advanced Analytics and Emerging Technology team!