Business Intelligence Tools

Administrating Spotfire — currency data types

Photo by Annie Spratt on Unsplash
  • 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.


7 thoughts on “Administrating Spotfire — currency data types

  1. Spotfire traine Reply

    This is a question for retaining precision during calculations.
    As the data format behind currency data type is decimal and the number of significant digits for currency is 28, if we use currency data type for a numeric column then during calculations (for example average) the loss of precision due to binary rounding off errors that happen with real data type will not happen and the precision will also be grater. Do you see any drawback of using this approach to retain the precision.

    • Julie Schellberg of Big Mountain Analytics, LLC Post authorReply

      If you work with Spotfire a lot, then I am sure at some point you have seen an error that says something like….wrong data type for calculation. This happens when, for example, you try to add together two strings. Currency does not work with all calculation. I can’t remember off the top of my head which calculations, but I’ve definitely run into cases where I had a currency data type and it wouldn’t work with Spotfire expressions that do work with real numbers. That is the risk. If I can think of which calculations those were, I will update.

      • Spotfire traine Reply

        Thanks for your answer.
        It would be great if you could give me a heads up on which calculations fail with a currency column.
        My use case was that i had a real column on which i was performing average. lets say column has 1.2 as distinct value for all the rows. When the row count increases to say ~7000 rows then spotfire started giving result as 1.1999990999909 or some other rounded off value. We found that this is “binary rounding off” error that gets introduced in floating points calculations.
        So when I found out that Currency is essentially a Decimal data format and uses base 10 for calculations I performed the same average for a currency column (Note that this is a casted column from a text column), and I am yet to get rounding off error for same 1.2 value for as many as 16 million records.

        • Julie Schellberg of Big Mountain Analytics, LLC Post authorReply

          Thank you for posting your experience with currency. It’s confusing to a lot of users. I just don’t know which ones fail off the top of my head, only that I have seen it before. I tried a couple like Rank, Standard Deviation, and a few others, but I haven’t been able to replicate what I saw previously.

    • Keith Arnold Reply

      I too have spent endless hours picking through every currency column and converting back to integer.
      Changing our Oracle DB Schema is not an option. Our DBA makes all integers NUMBER(38,), which is Int128.

      I tried recasting some NUMBER(38,0) data to INTEGER, BIGINT, NUMBER(16,0), DECIMAL … no matter what I do, all fixed length integers return in SF as Currency.

      Does anyone know if SF has fixed this, or has any plans to fix this.

      • Julie Sebby Post authorReply

        They have not fixed it. TIBCO hasn’t done anything with the information designer in years and doesn’t have any plans to. There is currently an ongoing discussion in the ideas portal. I’ll look up the idea and share the link on Monday. TIBCO is saying they are moving to connectors and aren’t going to do anything with the information designer. We need more admins and users to speak up against this. I’ll get you the link.

Leave a Reply

Your email address will not be published. Required fields are marked *