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

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.

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.

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

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!

Optimal Architecture for Combining Spotfire Tables

One of the most critical aspects of building Spotfire projects is choosing the optimal architecture.  I recently had the “opportunity” to rebuild the same Spotfire project 3x.  Let’s not worry about why that happened, but instead focus on the fact that I did arrive at the optimal architecture for the project.

In rebuilding the same thing three times, I came up with a few tips and tricks, especially as it relates to scalability.  Now, before I dive into the details, I need to brief you on the project.

The Project

The purpose of the project was to compare the same (allegedly) data from three different sources, and I needed to be prepared for the following:

  • Different wells in each data set
  • Different date ranges in each dataset (ex. Well 1 has production volumes reported for Jan 1, 2017 – Jan 1, 2018 in dataset 1 but only for Feb 1, 2017 thru Dec 1, 2017 in data set 2)
  • Comparing oil, gas, and water volumes across the three data sets
  • Comparing several dates across the three data sets (ex. spud date, completion date, first date of production)
  • The possibility of broadening the analysis to include more points of comparison at a later date (remember this, it will be important).

Furthermore, the analysis needed to be efficient and interactive.  One page per data point comparison was too many pages.  Instead, the design called for one page comparing volumes and one page for comparing dates, with property controls for the user to specify the comparison they would like to see.

Now, if you have been thru the discussion of insert rows versus insert columns, just skip to the end of the post where I have saved the best for last.  If you aren’t certain whether you would use insert rows or insert columns, read on.

Insert Rows versus Insert Columns

What is the optimal method to combine data tables in Spotfire?  Well, the answer is not always straightforward, and there are pros and cons to inserting rows versus inserting columns.  The basics are this…

Insert Rows

Pros

  • SUPER easy and fast.  The easiest of all options. Just match up the columns.
  • Changes? No problem!  Insert row operations are editable in the Data Panel.
  • Scaleable.  Just insert more rows.

Cons

  • Calculations are much more difficult.  Comparing Gas/Oil from one data set to another is not as simple as Column A – Column B.  You must use Over and Node Navigation.
  • Calculations get extra complicated with the scenarios described above (different wells, different periods, missing wells, etc).
  • Cannot remove an insert rows operation (yet).

Insert Columns

Pros

  • Calculations are much easier.

Cons

  • Cannot edit the insert columns operation (yet).
  • Scaleable, but with the risk that IF the underlying tables get more columns, all of a sudden you’ll have columns in your tables that you don’t need or want.
  • Joins can be messy or difficult.
  • The biggest con of this method (and the ultimate reason I didn’t use insert columns) is that you wind up with multiple columns for each value (ex. multiple well name columns).  If your dataset contains a lot of header data, this can be particularly frustrating and time-consuming.  The most common way to deal with it is to create calculated columns with IF logic (or case statements) that look at each column and return a value.  This was a deal breaker.

The Final Answer

So, which method did I choose? In the past, I have gone with insert columns.  However, in the end, I went with an insert rows operation with a pivot transformation.  The insert rows operation allowed me to easily combine the tables in a way that was editable and scalable.  Then, by adding a pivot operation, I was able to quickly create more columns so that the calculations were easier, and I didn’t have multiple columns for a single value.  WIN! If you aren’t familiar with pivot transformations, you can read about them here.  Here is what it all looks like in a very simple one well example.

Data Sets

Pivot

Final Table

(Remember, it’s one well, so there is one record)

Saved the Best for Last

Of course, what I really wanted to share with you I saved for the end.  One of the most critical elements of this build was the fact that it needed to be scaleable.  I needed to be able to add additional points of comparison at a later date.  This can be problematic with insert columns operations.  However, I found a great solution for scalability with insert columns. (Just ignore the fact that I ultimately went with insert rows.  This still good information).

Now, the first step in any project is adding the data tables.  In this case, I used information links, and they were HUGE!  They contained far more columns than I was going to use and a lot of data I wasn’t familiar with.  Therefore, I added in an Exclude columns transformation to exclude all unused columns.   This proved to be incredibly helpful.  For the sake of expediency, I’m going to list out the steps so far….

  1. Add all data tables to the project (completion data table, header table, production summary table)
  2. Insert an Exclude columns transformation on each table to exclude the columns not being used
  3. Create a “master” table by duplicating one of the tables (File > Add Data Table > From Current analysis) — learn more about the master table and why you should exclude columns in this post.
  4. Insert columns from the other tables into the master table

At this point, I have a master table with all of my data.  Now, if you have ever looked closely at the Data Table Source Information tab, you have noticed that they way Spotfire inserts columns is by building a list of columns to ignore rather than columns to insert.  I have never understood why the application is built this way (surely just to frustrate users). In this case, it is EXTREMELY helpful!

Keep in mind, the objective to be scaleable.  If I want to add in data points, all I have to do is modify the Exclude columns transformation and the columns flow in.   Now, because Spotfire builds up a list of columns to ignore rather than a list of columns to add, the newly included columns are not in that list, and so they flow through step 4.  There is no need to perform additional insert columns operations!!!! (I just got a little sheepish when I realized how excited I am about this).  This made the analysis much more scaleable.  I was relieved to not have to perform another insert columns operation for each new data point.  Tragedy averted. DONE.

If you wondered what I was doing up in Step 3, just hang on until next week.  I’ll explain that bit of architecture as well.

CSS in the Style Tag — Part 6

Hi Everyone! Welcome to the 6th and final post in the HTML series.  If you have just stumbled onto this post, use the links below to catch up on the series.

Intro to HTML 

HTML Tag Attributes

Using CSS in HTML

Containers and Style Attributes

HTML for Lists

 CSS with Style

This post picks up where post 4 left off with CSS.  To review, CSS can be written in 3 ways  4 ways (updated 4/24/2018).

  1. Inside an HTML tag using the style attribute (see post Containers and Style Attributes).
  2. Inside a style tag.
  3. Inside JavaScript in a Text Area.
  4. In a separate CSS file.

Now, if you are new to HTML and CSS, I recommend starting with a style attribute and then moving up to CSS inside a style tag before graduating to JavaScript in external style sheets.  Style tags are easy to edit, copy and paste, and share.  You will also be able to create a reusable CSS template for all DXPs, and formatting will be a thing of the past.  It’s magical!  Furthermore, CSS opens up formatting for pieces of the application that have no GUI.  In other words, the only way to format it is with CSS.

Now, it is bound to happen that as you get better at CSS, you write more of it.  At that point, it might make sense to store the CSS in a separate file and reference the file in the Text Area, but not if you need to edit frequently.  I’ve always just kept my CSS in the DXP.  There is also the option of creating a CSS style sheet with JavaScript.  This is demonstrated in this Data Shop Talk post.  Scroll down to “Insert a Style Sheet”.

This blog post is going to focus on CSS in a style tag.  I will break it down into 4 parts.

  1. Example of CSS in a style tag
  2. Explanation of the basic syntax and rules
  3. Other syntax and rules
  4. Explanation of how to decipher the “Spotfire” elements

CSS Example

Let’s start off with an example so you have a frame of reference.  Download this Ruths.ai template NFL Expectancy Win Calculator.  (Note: The button says subscriber-only, but click it anyway.  It will take you to a different website.  You might have to create an account, but the template is free.).  Go to the Expected Wins Page.  In the Pythagorean Win Expectancy Text Area, right-click and select Edit HTML to see the code… A LOT of code.  Scroll down in the sample template to make sure you know where the end is.  I’ve taken a small screenshot below.  This was all written by Lucas Wood of Ruths.ai.

In this example, the code is controlling the appearance of the entire template.  It can be copied and pasted into other DXPs to achieve the same look and feel.  Next, let’s look at the rules and structure for writing this type of code.

CSS Syntax


I’ve mentioned them before, and I’ll mention them again — W3Schools is a great resource for learning HTML, CSS, and JavaScript.  The image below is from their site that demonstrates the basic syntax.

It’s very simple syntax that consists of a selector and a declaration block.  The declaration block uses property-value pairs where the property and value are separated by a colon and multiple property-value pairs are separated by semi-colons.  The declaration block itself is wrapped in squiggly braces.

Now, selectors are important.  They find HTML elements and format all elements of that type on a page (or in our case, in a Text Area or DXP file).  This is the efficiency I have been talking about.  CSS formats everything with one block of code so you don’t have to format individual elements (like buttons) or add attributes to all HTML tags to format them.

There are many different types of CSS selectors, and you should definitely review them at  W3Schools .  Here is a simple example of an element selector.  The “p” references the <p> tag, and the selector is selecting all of the <p> tagged content and formatting it.

 

Here is an example of a class selector from the Ruths.ai template.  The selector is looking for the .sf-element-page-tab class.  It will select all page tabs and change the border and padding accordingly.

 

Now that you know the basic syntax, let’s look at some specific code in the template that might not yet make sense.  For example, you may be wondering what @import and !important are doing.

Other Rules & Syntax

@import

CSS also utilizes “at-rules”.  The “@import” is an example of an “at-rule”.  At-rules are special instructions that control how styles are applied.  In the case of this example, @import is pulling in a Google font.  Other examples include @media, @font-face.

!important

Each line of CSS in the template has “!important” at the end of it.  “!important” establishes priority.  It is telling SPotfire this code takes precedence over any other formatting that might be in the template, such as formatting in HTML tags or formatting from the set theme.  The exclamation mark is called a delimiter token, and “important” is a keyword.

Commenting

You may also have noticed that a line can be commented out with a forward slash and an asterisk.

Okay, that was a lot of talk about syntax.  However, there is still more to learn.  Next, we are going to talk about how to decipher the classes used by Spotfire. For example, if we want to change the active page tab color, how do we know to write “.sf-element-page-tab.sfpc-active”?

CSS for Spotfire

To figure this out, you can use the Developer Tools native to the application.  Developer Tools are NOT turned on by default, so to even see the menu, go to Tools > Options > Application > Scroll to the bottom and check the box for Show Development menu.  Now, you will see this menu in Tools.

Select Developer Tools, and a popup window appears. Click on the button that looks like this  to inspect page elements.  Now, move the mouse around the DXP and hover over elements to get the element names. I’ve taken some screenshots below for example.  I realize these screenshots aren’t the best.  I actually tried to make a video, but when you move and hover, stuff is constantly flashing on the screen.  It was painful to watch.

.sf-element-page-tab

.sf-element-text-box

.sfc-text-area

.sf-element-visual-title

Now, take note that an element can have multiple classes.  Each class is denoted by a period, such as “.sf-element” and “.sf-element-visual-title”.  The element “.sf-element” is a referring to something less specific than “.sf-element.sf-element-visual-title”, and “sf.element.sf-element-visual-title.sfpc-top” is even more specific.

For example, for the element shown below, to format the text box, use “sf-element-text-area”.  You don’t need to dig all the way into “sfpc-first.row”.

sf-element.sf-element-visual.sf-element-text-area.sfpc-first.row

You’ll get better identifying the right element and class with practice and trial and error.

Final Notes

I know this is turning out to be quite a long post, but just hold on.  I’m about to wrap it up.  There are just a few more things to note.  First, in order to have CSS apply to an entire DXP, you need to have the <style> tag and accompanying CSS in at least one Text Area on each page. With that said, be careful when you edit.  You might drive yourself crazy trying to figure out why the CSS isn’t doing what you want when there are conflicting pieces of code on different pages.

Well, that wraps it up.  Thanks for sticking with me to the end.  Clearly, there is much more to learn about CSS than one blog post can handle, but this will get you started.