# 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…

#### 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).

#### 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.

### 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.

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

# Helpful Tips for Editing Spotfire Tables via the Data Panel

• Have you had difficulty using the Data Panel to edit Transformation steps?
• Have you lost the ability to edit Transformations in the data table but do not known why?
• Does the Data Panel show you an error message that says — “No data can be shown for this step because the entire data table has been embedded.”

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

# 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.

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.

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

# Updating your Exchange.ai Desktop

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.

Technical Director at Ruths.ai

# HTML for Lists – Part 5

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:

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.

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

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.

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.

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

# Horizontal Line Limitations in Lines & Curves

• Are you struggling with the limitations of Horizontal and/or Vertical lines in Lines and Curves?
• Do you want a horizontal line to move with time but can’t figure out how?
• Are you trying to limit the horizontal line?