Do you look at reports more than code?

It was my first job fresh out of university. I was excited and ready to rock and roll. They gave my a bunch of specification documents to read and a bunch of monthly reports. My primary task then was to make sure the monthly reports come out “normal”.

The definition of “normal” was to make sure the numbers in those reports don’t vary too much from last month’s reports. We’re talking 5 to 7 digits of debt value here.

As I continued working in the corporate environment, I found the production of reports to be extremely important. Inputs go in at one end, such as telephone call lengths, number of text messages, ringtones bought, Internet connection length via satellite, number of emails sent. In the middle, we process them according the price plans the company had set. At the end, we calculate how much we need to bill the customers, or how much profit we share with partners, or how much the customers of our customers had used.

The end users don’t want SQL statements. They want a report, usually a PDF or Excel spreadsheet.

At the start of my corporate programming career, I was proficient in generating PDF reports using Crystal Reports. That’s just a weird turn of events. My team manager had enough front-end programmers for web development work. He also had enough back-end programmers for the number crunching price/settlement plan calculations. But he needed someone who can turn the results of those calculations into beautiful reports. That’s where I come in.

Then I left the company for a startup. It was something to do with searching patent information. No reports there, but we still needed to present the results to the customer.

Then I left the startup (cough) for a small development firm. I was assigned to a team doing enterprise software for a company. I had to go through a fingerprint scanner every time I needed to go to the loo (I’m not kidding). Lots of reports too (no, not about toilet visiting frequencies).

Then I went back to the first company I worked for. I went to another team. This time, the users of that team wanted their reports in Excel spreadsheets instead of PDFs. Why? Because they can work with the numbers inside.

They can work with the numbers to generate their own charts if they want to. They can sort if they want to (you can’t sort data in PDFs). They can swap columns if they want to. They can highlight values if they want to.

And then I understood something. Data trapped in a database is useless, unless it can be meaningfully presented to someone who cares about it.

Does your brand new startup website/service/product allow your customers to get their information easily (and in a meaningful manner)?

A Twitter feed in RSS/XML format is not useful if your customers don’t know how (or want) to format it into something readable. A straightforward database dump might not be useful if there are multiple relationships between tables. Those high score values won’t be meaningful if they were hidden among heaps of columns of irrelevant data to your gamer customers.

Also, it’s currently tax season and the start of the financial year for some companies. Lots of reports needed. I’m currently running a promotion for a software library (that I wrote) that generates Excel reports. Check it out here if you want. Until 16 April only.

In the age of the Internet, information is aplenty. Making all that information meaningful is harder. Writing code to present data is just as important as writing code to receive and crunch and manipulate data.

Satellite and maritime data calls

Let’s talk maritime communication data. That’s what my users work hip-deep in.

“Wait! Didn’t you say you’re in the satellite business the last time?”

Oh yeah… so, to me (and my users), they’re effectively the same thing. You see, out at sea (ooh, alliteration), captains and sailors still need to talk with their loved ones back on terra firma. And surf the Internet (for you know, uh stuff…). And send (short message service) SMSs to their friends (and sweet phone messages to lovers). And report back to headquarters of course.

[actually they probably don’t frivolously send love notes. Because their company is paying for it, and paying close attention to their usage. See below…]

They do that by using a device, which sends data up to space to one of the supporting satellites (not our moon, one of those man-made ones), which then sends it back to Earth to be received by a satellite dish, which then relays that data out the normal way. Here’s a simple diagram to show how it works:

Basic land-sea data transfer via satellite

I totally suck at drawing diagrams…

The ships are equipped with a terminal (no it’s not installed at the fore of the ship like in the diagram. It’s probably in the command centre or communication centre or… you know, I have no idea where they install it…), which contains one (possibly more) subscriber identity module card or SIM card. These SIM cards aren’t the kind we use in our mobile phones though.

And each SIM card has one, possibly more, mobile numbers. And each mobile number is tied to a specific service, such as voice call, or SMS, or fax. These mobile numbers are used to track usage. What do I mean by that?

For the purposes of our discussion, the word “call” refers to any initiated communication. So an SMS is a call. Browsing to a particular website is a call. Faxing a piece of paper is a call.

And each call comes with associated data, and the entire thing is referred to as a call detail record, or CDR (or call record in short). There’s a lot of data in one call, so I’m going to tell you about the ones my users and customers care most about (because they track usage religiously. I know, because I wrote the web application for it.) You may encounter other similar terms.

  • Calling number – the mobile number making the call
  • Called number – the name’s a bit misleading. It can actually be an IP address or URL, other than a receiver’s mobile number or fax number. It’s also known as destination number.
  • Unit type – “messages” for SMS, bits/bytes/KB/MB (and other variants) for IP data, sec/min for voice data
  • Call duration – a value corresponding to the related unit type, such as 2.13 MB or 27 seconds
  • Call start date/time – when the call was made

The calling number is the unique identifier. The main thing it identifies is the company to whom the billed is sent (very important, this billing thing). The calling (mobile) number is tied to a billing account in the billing system.

The call duration is especially important, because the customer is billed accordingly. This is why I’m adamant about the use of fixed point database types such as numeric(12,4). The floating point errors will scale out of hand otherwise (millions of these call records are created every month). Which will cause the total billed amount to the customers to be out of hand. Money is a very serious topic… Do not underestimate the power of 2 numbers not tallying by a difference of 2 cents in the billing report…

That’s the basics of satellite data. Oh right, the terminals…

There’s this service called Broadband Global Area Network, or BGAN for short (pronounced bee-gan), and I even presented at a product launch hosted by my employer. The BGAN service is provided by Inmarsat, a satellite telecommunications company, and they provide me with most of the data I work with. And they (and their data servers) are based in London. Which is why I obsess over timezones and formatting dates and times.

So (customer) companies buy these BGAN terminals and install them on their ships. And the captains and crew members get to communicate with people on land, and even with other ships. All made possible by routing data through the satellites.

And my users sell these terminals, associated paraphernalia (such as the SIM cards), and data usage (where price plans come in). Inmarsat deals with telecommunications companies (like my employer), and the telecommunications companies take care of the customers.

There are other types of terminals of course. Which means different services, and different types of data. Which gives my team many headaches. Because my users will be telling the customers “We support this!”, and turn to my team to actually support the new data, new price plans, new billing calculations, new entries in bills, new everything. There are now more than 10 sources of call records which we handle every day…

I believe that’s enough information for now. Any questions?