How to Load MS Access Tables as Quickly as Possible

Julie Schellberg of Big Mountain Analytics, LLC Spotfire Leave a comment

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

Jason May Spotfire 1 Comment

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

Julie Schellberg of Big Mountain Analytics, LLC Spotfire Leave a comment

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

Julie Schellberg of Big Mountain Analytics, LLC Spotfire Leave a comment

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

Julie Schellberg of Big Mountain Analytics, LLC HTML, Spotfire Leave a comment

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.  As a Spotfire developer, you care about 2 of them.

  1. In a separate CSS file — only applicable to building web pages
  2. Inside an HTML tag using the style attribute (see post Containers and Style Attributes).
  3. Inside a style tag

Now, folks new to HTML will generally start with the style attribute, but using CSS inside a style tag is where you can achieve beauty and efficiency in Text Areas.  This method allows you 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.

In order to tackle this subject, it will be broken 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 that accompany the browser to inspect the underlying HTML and CSS that creates the DXP in the web player.  In Chrome, DevTools are accessible by pressing F12.  So, open a DXP in the web player, press F12, click the button that looks like this  and click on the Elements tab  .   Now, you can mouse around the page and hover over various pieces of the DXP.  When you hover, you will see the name of the element as shown in a few examples below.  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

 

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.

 

 

 

 

Updating your Exchange.ai Desktop

Lucas Wood Spotfire Leave a comment

If you having some trouble logging in, that’s a good problem! We’ve updated the Exchange.ai server for security and now you will need to log in using a new address. Follow along and we’ll get you set back up.

This is the first error you would have encountered; we’ll update the server name so that you can log in again. Press Manage Servers to update the server location.

Click Edit.

Change the server address to match above.

Once you login, you’ll update and then you should see this screen:

Congrats! You made it through.

HTML for Lists – Part 5

Julie Schellberg of Big Mountain Analytics, LLC HTML, Spotfire 1 Comment

We’re almost done folks!  Only two posts left in the HTML series.  This week, I’m going to focus on writing the HTML for lists because bullets and numbers are where the Spotfire GUI fails early and often.  In case you are new to the series, go back and check out the earlier posts covering these four topics:

  1. Intro to HTML — Basics of Tags
  2. Tag Attributes
  3. Using CSS
  4. Containers and Style Attributes

Before learning to write HTML, I had more trouble with lists than any other function in the Text Area, and I’m talking about “throw your computer out the window” kind of rage.  Fortunately, bullets and lists are one of the easiest things to write in HTML.  Master three tags — <ol>, <ul>, and <li> — and you are ready to rock and roll.

HTML for Lists

Lists are created with <ol> and <ul> tags.  Use <ol> for ordered lists (numbers) and <ul> for unordered lists (bullets) . Each line in the list will also use the <li> tag.

Of course, there are a few rules to abide by.

  1. Start the list with <ol> or <ul>.
  2. Use the <li> tag for each line in the list.
  3. Always close the <li> tag.  You’ll notice weird spacing if you don’t close each <li> tag.
  4. Add another <ol> or <ul> to indent a level.
  5. Close the <ol> or <ul> to go back up a level.
  6. Make sure to close the final <ol> or <ul> when the list is complete.

Here are two examples demonstrating the rules above.  The first example is fairly simple and just demonstrates the use of the <ol> and <li> tags.

HTML for Lists

In the second example, I have highlighted the <ul> tags used to move up and down levels in the list.

HTML for Lists

Now, you might be asking whether putting each tag on a new line is required, and the answer is no.  I do this to make tracing my start and end tags easier.  Add the tags in whatever manner is easiest for you to follow and understand.  These few simple rules are all you need to work around the GUI and correct the text area when it goes awry.

Attributes for <ol> and <ul>

Now, because I know you are going to Google it, I want to mention that the <ol> and <ul> tags do have HTML attributes.  You can read about them here  for <ol> and here for <ul>.  An example is provided below.  Just remember to use the attribute in the <ol> or <ul> tag, not the <li> tag, or it won’t work.  I definitely made that mistake.

HTML for Lists

 

 

In closing, I want to note that when I first started using HTML for lists, I printed out the syntax and kept it on my desk.  A week later, I threw that paper in the trash.  You’ll pick this up and have it memorized in no time.