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:
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.
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.
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.
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).
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
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.
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.
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.
If you aren’t familiar with loading a data set, here is how it works.
And those are all the options. I hope this helps you get the most out of MS Access.
Spotfire version 7.9.1 was used to develop this content.
Are you starting to question whether you need to move beyond MS Access into SQL or Oracle?
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.
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!
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 purpose of the project was to compare the same (allegedly) data from three different sources, and I needed to be prepared for the following:
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.
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…
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.
(Remember, it’s one well, so there is one record)
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….
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.
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.
Containers and Style Attributes
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).
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.
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.
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.
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.
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.
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”?
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.
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.
