Business Intelligence Tools

Spotfire Architecture — how to avoid losing columns when replacing data tables

  • Are you tired of rework in Spotfire?
  • Are you frustrated with inserting columns over and over again after you replace a table?

Once again you’ve had to replace a data table and lost all of your inserted columns.  How do you keep this from happening and avoid rework?

Solution Description: Rather  than having a DXP file with a single table with columns inserted from many other tables, architect the DXP to include all data tables that will be used in the analysis, reinsert one of the tables (the master table if you will) and then insert all necessary columns, as shown in the model below.

8-16-2016 9-38-13 AM

 

How/Why It Works:  If/when you have to replace a table, replace the component table(s) rather than the master, and the components will continue to feed the master.  The master table is where columns are inserted, and it is never replaced, which means no lost columns.

Pros: If you have to replace a table, you won’t lose any of the inserted columns, which eliminates or reduces rework.  It also makes troubleshooting errors easier.

Cons: Creates larger DXPs because you have more tables.  There will still be errors if you lose a column from the component table that is used to join to the master table.

Other Notes: You can see in the Edit menu, Data Table properties, Source Information tab (of any table that has inserted columns), that the way Spotfire builds tables with inserted columns is to specify which columns to ignore, rather than which columns to insert.  Therefore, if new columns are added to the tables you are inserting columns from, those will also be added to the master table.  You can always exclude them in a transformation when the table is replaced.  Lastly, when replacing tables, if a column name changes and it is used to join, you MUST match the old column to the new column when presented with the dialog showing the differences between the old table and the new table.

4 thoughts on “Spotfire Architecture — how to avoid losing columns when replacing data tables

  1. Stephen Thompson Reply

    If you replace the data source by going through the data source view you can replace a data table without loosing transformations or inserted columns. Detailed instructions are in Help. You still cannot preserve these if you use File>Replace Data Table.

    (I am using version 7.12)

    • Julie Sebby Post authorReply

      Oh man! You are digging deep into the archives! At some point in my blogging history, I started closing each post by noting the version of Spotfire I was using to write the article. I have been on 7.9 until just last week. I knew that they added the ability to replace a data table without losing transformations or inserted columns, but I had not actually tested it yet. So, thank you for letting me know where to go to get that function. I probably would have started with File > Replace Table and then wondered why I lost transformations. Much appreciated!

  2. Nick Parrillo Reply

    Once the master table is created, is there a need to set up the “data table relations” so that the master gets automatically updated when new data is added to the other tables? Could you elaborate a bit on this?

    • Julie Sebby Post authorReply

      The purpose of relations is to allow integrated filtering across tables. They aren’t used for updating data. The data will update in the “secondary table”. It used to be the case (a very long time ago) that if you setup tables this way, you also had to create a “refresh table” button using IronPython to make sure they stayed in sync. That is not an issue now. If one table updates the other will udpate.

Leave a Reply

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