Month: April 2018

Secrets to Modifying Spotfire Information Designer SQL

  • Are you struggling to modify SQL in the Information Designer?
  • Do you need to concatenate two integer columns into a date but don’t know how to do it?

Read More

Guest Spotfire blogger residing in Whitefish, MT.  Working for SM Energy’s Advanced Analytics and Emerging Technology team!

How to set the Y-Axis in DCA Wrangler

To give you greater control when editing declines in the DCA Wrangler, we have added an option to lock the Y-Axis at a specific maximum. The ability to lock this axis gives us some neat follow on features.

  1. Fine control with the interactive decline curve editor in Single-Well mode.
  2. Ability to compare many declines with the same scale on the plot in Multi-Well mode.

Read More

Robert Henkhaus is the Product Manager and a Developer at Ruths.ai. He has 5 years of experience in the oil and gas industry and was previously with ConocoPhillips supporting BD and Land in high value decision spaces. Robert has a BS in Geography with emphasis in Earth Science from Texas A&M University. Before completing school, he also served 8 years in the Army as a sniper.

New Result Tables workflow in DCA Wrangler

We’ve added a slick new feature to help you manage all that Well Decline and Type Curve data in Spotfire. Result Tables in the DCA Wrangler represent the current working set of data produced by the DCA Wrangler. In this article, I will show you how to:

  1. Add multiple Result Tables of the same type and target each separately.
  2. Compare different decline assumptions for the same set of wells – with the ability to tweak parameters.

Read More

Robert Henkhaus is the Product Manager and a Developer at Ruths.ai. He has 5 years of experience in the oil and gas industry and was previously with ConocoPhillips supporting BD and Land in high value decision spaces. Robert has a BS in Geography with emphasis in Earth Science from Texas A&M University. Before completing school, he also served 8 years in the Army as a sniper.

Happy 100th Blog, Julie Schellberg/Sebby

Just wanted to put up a shout out to Julie on her 100th blog. When we started DataShopTalk in Fall 2015, we had no idea how much traction and support we’d get from the community. DataShopTalk was always intended to fill the void of technical Spotfire/BI work in the oil and gas space and we are so surprised how well it’s done.

Especially from out contributors, among them Julie Schellberg now Sebby, started writing in August 2018, her first article was something about OVER commands. A consistently mysterious feature of Spotfire, Julie has tackled it and many others over the past two years.

This post is dedicated to Julie’s commitment to the Spotfire and BI community and we wish her happy writing and may her Spotfire never crash.

Technical Director at Ruths.ai

How to Load MS Access Tables as Quickly as Possible

Last week’s post focused on the differences between tables sourced from MS Access versus SQL.  My current project is hitting the limits of MS Access, and we are discussing migrating to SQL.  For now, I’m stuck with MS Access.  I say “stuck with” because loading tables into Spotfire from MS Access can be incredibly slow.  Because many other users have the same problem, let’s talk about how to make the best of it.

Prelude

Before I begin, I want to mention one thing.  I write about Spotfire every week, and I realized recently I don’t ever specify which version of Spotfire I use.  That is kind of a big deal, so from this point forward, I will always include a closing notation to specify which version of Spotfire I use to develop content.

Connecting to MS Access

Now, as mentioned last week, there are two ways to connect to MS Access — Open from File or via a database connection (ODBC, OLEDB, etc).

Testing MS Access

I have historically used Open from File because it’s just easy.  However, you cannot edit the query, which is a big downside.  Also, load time critical in my current project.  Thus, I whipped out the iPhone stopwatch and tested the two methods.  Here are the conditions of my tests

  • Platform: Spotfire Analyst Client on my computer with 16GB of RAM
  • Location of the data: Database in Denver, and I am in NW Montana.
  • Conditions:
    • VPN to Denver.  The users of this project will be located in Midland with a marginally better internet connection on the company network.
    • Tests were performed one after another, and other tasks were minimized during load (ie. checking email and putzing on the internet).
  • Data Set: 541,350 records and 10 columns.

The results are shown below.  Now, the absolute time isn’t as important as the relative time.  This is not good.  This is slow.  Real slow.

Source Start Time End Time Total Load Time
ODBC 12:34:34 12:53:11 0:18:37
File 12:56:21 13:13:49 0:17:28

For the purpose of comparison, I asked a user in Denver and a user in Midland to Open from File.  The Denver user experienced significantly better performance.  Tables loaded in approximately 30 seconds (remember, the database is in Denver), but the Midland user (the users of the DXP) experienced load times similar to mine, between 13 and 15 minutes.

Options

Okay, so what can we do about this? The project contains at least two large MS Access tables that load in 13-18 minutes each, which isn’t acceptable.  Two options remain — Citrix and load the table as a data set to the library and connect to the data set.

  1. Load as a data set
    • Pros: Loads in 30s (Yay!)
    • Cons: Static, not ta live data connection (Boo!)
  2. Citrix
    • Pros: Loads in 30s (Yay!)
    • Cons: If using ODBC connection, must install ODBC and configure on Citrix.  Some users dislike Citrix and/or experience latency or connectivity issues with Citrix.  More steps to launch Spotfire. (meh, okay fine…).

If your company does not have Citrix, then loading as a data set is the only way to get the table to load faster.  For my purposes, I will use Citrix, even though I have latency and connectivity issues occasionally.  The load time is an order of magnitude faster, so I will suck it up.  However, if you want to try out loading to the library as a data set, follow the steps below.

Loading from Library as a Data Set

If you aren’t familiar with loading a data set, here is how it works.

  1. Load the MS Access table as you normally would (ODBC or Open from File).
  2. Go to File > Export > Data to Library to save the data set to the library.  Essentially, you export to import.  It’s counter-intuitive.
  3. Replace the table using the Data from Library option to connect the DXP to the data set instead of MS Access.

And those are all the options.  I hope this helps you get the most out of MS Access.

Spotfire Version

Spotfire version 7.9.1 was used to develop this content.

Guest Spotfire blogger residing in Whitefish, MT.  Working for SM Energy’s Advanced Analytics and Emerging Technology team!

MS Access vs SQL for Spotfire

Are you starting to question whether you need to move beyond MS Access into SQL or Oracle?

  • Are MS Access limitations ruining your projects?
  • Are you thinking about connecting to an SQL database via MS Access?
  • Are you running into limitations in Spotfire using MS Access?

Read More

Guest Spotfire blogger residing in Whitefish, MT.  Working for SM Energy’s Advanced Analytics and Emerging Technology team!

Spotfire Solution: Replacing Data with Different Named Columns while Using the Data Limiting Expression

In Spotfire, the filter panel allows one to easily remove ranges of values from your data.  We can gain even further granularity and control of what we hide from a dataset by applying the “Limit data using expression” window.  However, the “Limit data using expression window” doesn’t play nice when you want to replace a data table by matching columns with different names.

When we use replace data functionality and the limiting expression uses a matched column, the expression doesn’t update the column name (as it does with other expressions), which leads to unexpected results.  Call this one of those “endearing” Spotfire intricacies.

Fortunately, we can get around this issue by creating a Show/Hide calculated column and rerouting our limiting expression through a calculate column, which will update when you replace data.

Read More

Jason is a Junior Data Scientist at Ruths.ai with a Master’s degree in Predictive Analytics and Data Science from Northwestern University. He has experience with a multitude of machine learning techniques such as Random Forest, Neural Nets, and Hidden Markov Models. With a previous Master’s in Creative Writing, Jason is a fervent believer in the Oxford comma.

Updated Keyboard Shortcuts to Sort by Multiple Columns in a Table

This is just a quick little entry about Spotfire keyboard shortcuts.

Before Spotfire version 7.5, you could use the keyboard shortcut Shift+Left Click to sort a table visualization using multiple columns.  That was before TIBCO began introducing left-click menus in addition to all the right-click menus.  In 7.9, they added a left-click menu to table visualization column headers, which meant you could no longer Shift + Left-click to sort by multiple columns.  It appeared the only path to sorting on multiple columns was thru the Properties dialog (dislike).

In actuality, the keyboard shortcut just changed.  Now, use Ctrl + Shift + Left-click to sort by multiple columns in a table visualization.  Thanks to TIBCO for publishing that in a support article this week.  People do read them!

Guest Spotfire blogger residing in Whitefish, MT.  Working for SM Energy’s Advanced Analytics and Emerging Technology team!

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?

Read More

Guest Spotfire blogger residing in Whitefish, MT.  Working for SM Energy’s Advanced Analytics and Emerging Technology team!