Business Intelligence Tools

Optimal Architecture to Avoid Rebuilding Tables

  • Are you frustrated with having to rebuild Spotfire tables when you need to modify insert columns or make a major change?
  • Would you like to build Spotfire projects so that making modifications to tables is easier?
  • Do your tables have duplicate columns and you don’t know why?

If I had to list the most frustrating thing about Spotfire, it would be the fact that you cannot edit insert columns operations.  Users have been begging for the ability to edit columns for years, and TIBCO has yet to make this change in the software.  Granted, I hear it’s coming.  However, not all architecture is created equal, and there are things that you can do to minimize rework.  I’ll show you this architecture in a simple three table example.

Basic Architecture and Build

This is how it starts…a user will add one or more tables to a DXP.

Then, they will insert columns from one table into another.

Then, they proceed to build into that table with calculations, transformations, hierarchies, etc!

At some point, something breaks, and the entire table must be rebuilt.  Rework! Ugh! And you thought you had weekend plans.

A Better Architecture

However, there is a different way to architect the DXP.  Instead, add each table to the DXP.  Then, use an Exclude columns Transformation to exclude columns you don’t need.  It’s okay if you might need them later.  Exclude them now.

Then, use the Add table from Current Analysis option to duplicate one of the tables.  This will become the master table.  Insert columns from the other table into the master table and build away.  If/when something breaks or changes, you only have to replace the table that is broken and after you replace it, it will flow thru to the master.  The master table is safe and sound as long as the column names don’t change in the component tables.


Additionally, if you need more data (such as one of the columns you excluded), simply edit the Exclude columns transformation, and it will flow thru the join.  You will not need to perform another join because Spotfire builds a list of columns to ignore, not a list of columns to insert.  Because it was excluded originally, it’s not part of the ignore list (shown below).  This will keep your tables nice and tidy.  Who doesn’t like a tidy table?!?!?  (I really just liked typing it).

Just to Be Sure…

Now, I really want to make sure you understand the last part about excluding columns, so here is a simple two-table example.

After adding both tables to a DXP, I will exclude the State column (via transformation) because I don’t think I need it (it’s the same for all wells).  I will insert the County, Operator, and Play columns into the production table.

This is what my source information looks like.  My ignored columns list says (None) because I excluded State and inserted all the other columns.  Nothing was ignored.  If it turns out I do need the State column, I will edit the exclude columns transformation.  Because State was not part of the ignore list, it will be inserted as soon as I edit the exclude columns transformation.

This architecture will save you tons of time in rework, especially the little bit about excluding columns at the start.  I’ve seen so many files that join to the same table over and over as the user realized they needed more data, and ultimately that architecture will create a mess.




4 thoughts on “Optimal Architecture to Avoid Rebuilding Tables

    • Julie Schellberg of Big Mountain Analytics, LLC Post authorReply

      It will increase the number of tables, but it shouldn’t increase the load time materially because you are only hitting the data source once. Once the “original” table is loaded, it flows thru to the others. Glad you found it useful!

  1. Kyle Reply

    Thank you Julie, this has saved me more times than once! Have you changed your project architecture since updating to Spotfire X and having more options to edit and add columns in the data canvas? I am starting a new project and am trying to decide how to set it up. I want to make sure I understand why I had been using this architecture and if it is still the best practice.

    • Julie Sebby Post authorReply

      I have changed my architecture since updating to Spotfire X. Basically you don’t have to double up on the tables and add from current analysis anymore. If something breaks, you don’t have to rebuild, you can just edit.

Leave a Reply

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