Business Intelligence Tools

Administrating Spotfire – using unions in information links to merge tables

  • Do you build Spotfire information links?
  • Would you like to be able to join multiple sets of data with a SQL union statement in the information designer?

Quite frequently, we have the same data, just different versions of it, stored in separate locations.  Maybe an administrator has created multiple information links for users working in different basins or the engineers have several versions of a forecast in separate Access databases.  This works well until someone wants to combine them all together.  Fortunately, merging and blending data is one of the things Spotfire does brilliantly, and many tools are available to users and administrators to address this task.

Generally speaking, there are three options:

  1. Prompts
    • Pros: Super easy, meets most needs
    • Cons: Usually must be setup by an administrator; some users are irritated by prompts with info links they use frequently; for large data sets (like IHS data), multiple select prompts that generate a pick list can be prohibitively slow.
  2. Insert Rows
    • Pros: Fairly easy task; the only option when the data set is a spreadsheet or other file; performed by the user, not an administrator
    • Cons: Performed by the user, not an administrator; adds to the work of creating the file and poses a rework risk if something breaks
  3. Using Union in the information link build
    • Pros: Just a little bit of extra work in the info link build
    • Cons: Just a little bit of extra work in the info link build; won’t work for file (eg. Excel, Access) data sources

I’m going to focus on the Union because it is extremely useful, but not necessarily intuitive in terms of how to make it work.  For those who aren’t SQL gurus, the Union operator is used to combine the result of two or more select statements (ex. Combine the result of ‘select all Powder River’ and ‘select all ‘Marcellus’).  The Union is applied to customized SQL in the Information Designer when creating information links.  If you don’t what the Information Designer is, talk to your administrator.

Here are the things you need to know, and then I’ll show an example.

  1. When union-ing two information links, only add the column elements from one of the data sources.  This is the non-intuitive part.
  2. The data sources must contain the exact same number of columns in each of the data sources, although they don’t have to be named exactly the same.
  3. After adding the column elements from one of the data sources, add the Union All statement and then add the add the SQL that you would use to query the second data source all on its own. This is easiest when the column names are the same because you can copy and paste the first half of the SQL and then just change the from statement.
  4. You must include ‘WHERE <conditions>’ at the end of the SQL, as with any information link.

Here is an example:

 

SELECT

v1.”CostCenterKey” AS “COSTCENTERKEY”,

v1.”CostCenterName” AS “COSTCENTERNAME”,

v1.”CostCenterOilProd” AS “COSTCENTEROILPROD”,

v1.”ProductionMonth” AS “PRODUCTIONMONTH”,

v1.”ProductionMonthMonth” AS “PRODUCTIONMONTHMONTH”,

v1.”ProductionMonthYear” AS “PRODUCTIONMONTHYEAR”,

v1.”TotalOilProd” AS “TOTALOILPROD”

FROM

“dmFinance”.”dbo”.”vAllocatedTransBriscoe” v1

 

UNION ALL

 

SELECT

v1.”CostCenterKey” AS “COSTCENTERKEY”,

v1.”CostCenterName” AS “COSTCENTERNAME”,

v1.”CostCenterOilProd” AS “COSTCENTEROILPROD”,

v1.”ProductionMonth” AS “PRODUCTIONMONTH”,

v1.”ProductionMonthMonth” AS “PRODUCTIONMONTHMONTH”,

v1.”ProductionMonthYear” AS “PRODUCTIONMONTHYEAR”,

v1.”TotalOilProd” AS “TOTALOILPROD”

FROM

“dmFinance”.”dbo”.”vAllocatedTransGalvan” v1

 

WHERE

<conditions>

 

Please reply to the comments section if you have any questions about these instructions or encounter difficulties in the details.

 

1 thought on “Administrating Spotfire – using unions in information links to merge tables

  1. KarthikRaja Subbiah Reply

    But assigning Prompt variables will not have the values from the second table.

    In this case, If I assign column ‘COSTCENTERKEY’ as Prompt column, It will not have values from “dmFinance”.”dbo”.”vAllocatedTransGalvan”.

    Any idea on how to solve it?

Leave a Reply

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