Optimizing Spotfire Over a Distributed Network

By | Analytics, Spotfire | No Comments

I’ve been working on a project to create an analytical tool using Spotfire. My IT colleagues did a nice job creating a data model for me, and we worked through the bugs on loading the data into SQL Server. So the next step was to create the visualizations in Spotfire.

To my dismay I discovered load times of 30+ minutes for my 11 million row (and growing) dataset. That’s a decent size data set but it isn’t big by Big Data standards. Spotfire should eat that stuff up. Running an identical query closer to the metal in SQL Server Management Studio ran in about 4 minutes. Something was wrong. Here’s what I learned:

  • My support team suggested Data-On-Demand. This reduced load time but because I need around 3-4 million rows minimum I was still looking at 20 minute load times. Not good enough.
  • Next I tried switching from an in-memory Information Link to a in-database Data Connection. In the Spotfire Windows client this worked great, but there are catches. First, you lose Spotfire features. This seems fine until you try that one bloody thing that always worked for you and you can no longer do it. Annoying. The second and bigger catch was that I need my app to work in the Spotfire WebPlayer and the in-database links did not play nicely with our security system. We eventually found a solution to make this happen but if you work in an environment with certain security standards, what seems simple may not be.
  • While waiting for IT to investigate how to make in-database connections work with WebPlayer, I tried to optimize my Information Links. There isn’t a ton of configuration in the Information Link itself, but I discovered that my database and Spotfire server were in different cities and that led me to explore the configuration of the underlying DBConnection. I’m no DBA, so I used the Google and concluded that Fetch Size might be a critical parameter for me. I decided to experiment to see what would happen.
Fetch Size  Rows Retrieved  Time Elapsed

0 (let database decide)

3,780,604

32:05

10,000 (Spotfire default)

3,780,604

11:39

100,000

3,780,604

2:59

1,000,000

3,780,604

2:02

2,000,000

3,780,604

1:55

3,000,000

3,780,604

1:54

1,000,000

11,986,073

5:45

2,000,000

11,986,073

5:52

3,000,000

11,986,073

5:51

11,000,000

11,986,073

5:47

A pretty amazing difference. It’s not clear to me how effective this is if your Spotfire server and database are co-located but it seems to make a big difference when you have some distance between sites. I settled on a Fetch Size of 1,000,000 vs. the 10,000 default value.

  • Finally, after reading an article from Bear on Spotfire I thought I could reduce the amount of data I transmitted across the wire. Instead of one Information Link that joined together four tables into one big wide table, I decided that I could make it work with three separate Information Links sending me tall/skinny tables and then manipulating client-side instead of on the server. With this I was able to cut load times in half again, which meant that for a typical 3-4 MM row load, load times were approximately 1 minute.

The joys of business technology.

The Falsehood of the Minimum Credit Card Payment

By | Personal Finance | No Comments

Over Christmas, my Dad was looking at his credit card statement and noticed that if he multiplied the Estimated Time to Pay Using Minimum Payment by the minimum payment he would actually have paid less than the balance. I thought he was being a bit cranky but I looked at my credit card and sure enough:

Balance: $5123 Estimated Time to Pay: 40 years, 9 months Minimum Payment: $10

Let’s do the math shall we?

489 months × $10 = $4890

So I can pay the minimum payment for the next 41 years and actually pay less than I owe? Sweet!

Of course, that’s a dream. It’s a much uglier situation. A 20% interest rate means that the interest per month is around $85/month. That’s more than the minimum payment, which means that the balance grows and the interest will grow with it. Which means that the card will never ever get paid off at the minimum payments. And I assure you that a risk control system will ratchet down the screws on an unsuspecting customer just at the wrong time.

I don’t have a problem with the product per se. If you’re going to use credit, then you need to take a bit of personal responsibility with it. But a flat out lie? We already have woeful financial literacy in this country – it isn’t right that a financial institution can deliberately mislead a customer.

I posted a query to the “TD Helps” website to ask about the arithmetic and Alex D. from TD Canada Trust replied to my note within 15 minutes (kudos for the blazingly fast response):

The purpose of the “Estimated Time To Pay” calculation is to explain to our customers why it’s important to make more than the minimum payment on the TD Credit Card. I agree that the estimate could indeed be more accurate, however whether a customer is led to believe that it will take 40 years and 9 months or 50 years and 8 months, the message is clear that more than the minimum monthly payment should be made in order to get ahead of the interest costs. I will follow up with your TD Credit Card department and determine how they calculate this estimate and update this thread when I have more information.

I’m still waiting for that follow-up more than a month later. But it’s still an unsatisfactory response in my opinion. The arithmetic is simple and there’s no justification to lie to the Canadian consumer.

Argentina Vacation — Part Two

By | Travel | No Comments

Iguazu Falls

The trip to Iguazu was easy. Cab ride to the airport, a dingy LAN Airlines check-in area at the domestic and much more convenient Aeroparque airport, sit in a gleamingly-new departure lounge, short flight, and arrive to a jungle sea of green.

We stayed at the Sheraton, the only hotel in the park proper, figuring we could maximize our time exploring. The location is undeniably nice but the hotel itself is pretty meh. They’ve got you cornered and they’re full so there’s not much attempt to impress. But whatever, we’re not here for the hotel.

We didn’t have much intel to go on as the concierge gave us a map and had little else to offer in way of advice (see above lack of attempt to impress) and our guidebook was short on details but we walked the lower and upper loop paths on day one, and then took the little tourist train out to the Devil’s Throat viewpoint and did the truck ride/naturalist tour/boat ride on day two. I think we guessed at a pretty good sequence but there’s really no wrong way to do it.

2015-11_Iguazu_Rainbow

Typical poor view of the falls

Read More

Argentina Vacation — Part One

By | Travel | No Comments

This is a bit of a note to self on our 2015 trip to Argentina.

The Trip Down

As always, a trip on a US airline (American in this case) is a good reminder that while whinging about Air Canada is good sport, it is actually one of the world’s most okay-ish airlines. When you really want to see people who hate their jobs, the US airlines take the cake. Despite this, I was pleased to fly in the new Boeing Dreamliner and the trip down was uneventful, which is to say, pretty good. Arrived 30 minutes early too.

Airports are an interesting first impression to a country and Buenos Aires’ Ezeiza is no exception. A modern affair right into the customs hall where we had a solid hour to ponder a government propaganda reel on flat screens. Once at the immigration desk, everybody gets their picture and a thumbprint taken. No questions asked. There are few things more frustrating than a slow queue for no apparent reason but no matter, we get through immigration and the bags still haven’t arrived. The airport instantly changes into a more third world affair, cramped and a layer of grit everywhere. We collect the bags and join a large queue that leads to the customs and X-ray hall. Upon rounding the corner there is a large empty corral obviously meant to tame a queue from filling the baggage hall and having everyone trip over each other. Best of intentions I guess. A scan of our bags is quick and we grab a cab. We struggle to communicate with the driver (our Spanish sucks) but he is friendly and tries to give us restaurant advice as we near our hotel.

Buenos Aires Part One

We bookended the trip with two days in Buenos Aires up front and four days on the end. So a quick intro now and we will have a bit more time to explore on our return.

Rose Garden

The Rose Garden at Tres de Febrero Park

Read More

The Solo

By | Aviation | No Comments

I did my first solo flight June 7, 2015. What can I say, I am not the quickest at this blogging thing. But they say everyone remembers their first solo flight and I wanted to document it.

The Transport Canada Flight Training Manual says the first solo is a “landmark in your flying career … but do not exaggerate its importance”. In hindsight, that’s an accurate statement. I was very much anticipating and pressuring myself about the solo, particularly since my training was delayed a bit. But now that it’s done, it feels like the heavy lifting is yet to come. Yes, I can go up in the airplane by myself on a calm-ish wind day but proficiency still seems a long ways off.

The solo started with some dual time to warm up. I did five laps around the circuit with my instructor Akshay, and on the fifth he said that if I didn’t crash the plane on this one, he’d send me up on my own. I remarked that his standards seemed a bit low.

But sure enough, he got out of the plane.

Read More

Last Bastion of the Fax Machine: The Canadian Medical System (Also: An Aviation Medical in Seven Months)

By | Aviation, Technology | No Comments

If you read about the process of learning to fly, you’re bound to come across advice to get your aviation medical examination early. I thought I was doing it right; I scheduled my medical as soon as I decided to pursue the license and as a healthy, athletically active person I thought it would be simple. Little did I know.

I don’t feel like publishing my medical history on the Internet—that just seems weird to me—but I’m happy to report that I now hold a Transport Canada medical and am fit to fly airplanes. To get it took approximately seven months, most of it waiting for the medically competent, taxpayer funded, but glacially paced Canadian medical system. Waiting for an appointment with a specialist; waiting to get an appointment for tests; waiting for doctors to interpret results; and waiting for administrative staff to fax documents.

Wait, faxing? Permit me a bit of a rant here.

Read More

For the Long Run

By | Business | No Comments

A couple of good articles by Jason Kottke “Asking ‘who’s the customer?'” and a follow on from John Gruber “On the Long-Term Viability of Apple’s Customer-First Strategy” that are worth reading. They both have interesting comments regarding the Church of Maximizing Shareholder Value and how that undermines serving customers well.

Jason Kottke seems to characterise the “big investment banks, mutual funds, and hedge funds who buy their stock” as something completely removed from customers. But he doesn’t go quite enough with his logic. Those entities have their own customers, be they individual retail customers buying mutual funds or institutional customers like pension funds that are paying out to their customers, like the average retired teacher, civil servant, or factory worker (if the pensions are properly funded that is, but that’s another story…). So it goes full circle and perversely this customer pressures the mutual fund (by voting with their money) or institutional investor to perform, and they in turn push on companies to maximize shareholder value by making changes that aren’t necessarily in the interest of  the person who started the whole thing.

Read More

Rebranding the Fuel Surcharge

By | Pricing | No Comments

I was shopping for a flight to London Heathrow in December and noticed that with the rapid drop in crude and fuel prices last Fall, Air Canada quietly rebranded their Fuel Surcharge to “Carrier Surcharge”.

From Air Canada’s site under International (emphasis mine):

Carrier Surcharges: Carrier surcharges are included in the Air Transportation Charges and are collected by airlines to partially offset certain volatile, unpredictable or fluctuating operating costs and fees, and certain fare Premiums linked to peak travel periods. These carrier surcharges can be used to offset some (among others) of the following costs: fuel, navigational charges, or select peak travel dates to/from certain destinations.

It’s not just about fuel anymore, you see? It’s about managing “fare Premiums linked to peak travel periods”! Which sounds a lot like increasing our prices when demand is high. Didn’t that used to happen by changing fares?

Read More

Learning to Fly — This is Going to Require Some Work

By | Aviation | No Comments

[This post was written back in October and I’m just getting around to posting it]

Three flying lessons in and two things are clear: it is very fun and it is challenging. I am not sure how many people get into airplanes and are “naturals” but I don’t feel like I’m among them them!  Clearly, developing this skill is going to require some work. And it doesn’t appear to have any shortcuts.

Each lesson has the same structure: A fifteen minute ground briefing in the classroom where we review some aeronautical principles and how we are going to demonstrate them in the air. I inspect the aircraft and run through various checklists with Nico observing. The 45–60 minute air lesson itself. And then a quick ground debriefing of what went by in a blur. It goes by fast.

Say what you will about the cost of flight training (and my credit card bill will say plenty) but there is something great about a one-to-one learning environment. When my instructor asks a question in the briefing or in the air, there is no hiding place. You better at least take a stab at it. I have probably been doing a better job pre-reading materials than in any degree courses but it is still challenging to recall a fact on the spot with the instructor waiting. And of course, sometimes I’m just wildly wrong. I have a patient teacher in Nico so far. The whole thing forces you to learn concepts quickly and then get in the airplane and use them. It’s great fun.

Learning to Fly — (Finally) Going For It

By | Aviation | No Comments

[Note: This was written in September 2014, just prior to my first flying lesson]

I have decided that I am going to learn to fly.

I have been fascinated with the thought of flying for as long as I can remember. As a child, one of my Dad’s friends had a plane and we went for a flight where he let me take the controls. I have always looked forward to the opportunity to fly in small airplanes when they have come up. I used to love following United Airlines “Channel 9” cockpit communications back when it was available. And I have long been an internet voyeur of aviation trying to exhaust YouTube (note to self: not possible) and reading pretty much whatever I could find on Avweb.com and the like even though I only had a partial understanding of it as a non-pilot.

Last year, Timberly bought me an introductory flight with a local charter operation in Squamish. It was an incredibly beautiful flight on a crisp December day, and we cruised around North of Squamish with clear views of Mt. Baker to the South and Vancouver Island to the West. I was able to take the controls, do some turns, check out crevasses in the mountains below, and generally cruise around. My pilot for the day, Carlos, explained to me some of the basics of mountain flying and reading terrain, and we experienced some light and moderate turbulence as we cruised around the leeward side of some mountains.

And then — bang! — we experienced a sudden downward movement that put my head into the roof along with the loose contents of the airplane. I’m not too proud to acknowledge that it gave me a pretty good fright. Carlos told me that it was enough of a disturbance to be classified as “severe turbulence”. We kept flying and Carlos took us in for the landing. He remarked that he would have been “worried if you weren’t a little freaked out by that”. I’m glad I passed that test. I left with an odd mixture of exhilaration and trepidation. Do I really want to do this? Because those couple of seconds were genuinely unpleasant. I was planning on doing this for fun —  aren’t hobbies are supposed to be enjoyable!

Well, a great spring and summer of 2014 passed by but flying has continued to be on my brain. A lot. I cannot explain what has been attracting me all these years but I have decided that it’s now-or-never to get started. I figure I will either love it for the rest of my life or I will have some sense beaten into me.

If Warren Miller ski movies have taught me one thing, it’s that if you don’t do it this year, you’ll be one year older when you do. So here we go….