Ash Burton

Mobile | Web | Data

Mobile Payments, Android, iOS, Mobile Web, HTML5, Multichannel Customer Service, Web Services, IVR, Business Intelligence, Telecoms, Radio, Photography, Running, Gaming

iOS 7 Beta - First Impressions (WWDC 2013)

ios7_lock_screenIt's long been rumoured that iOS 7 would bring about a shift in Apple's design philosophy and with the keynote  of this year's World Wide Developers conference not only did we get to see the new user interface but within hours developers were able to download the first beta version and naturally I grabbed a copy immediately on release.  The install was painless, a simple IPSW restore via iTunes though this version does feel very much like a beta having experienced more than a dozen crashes already within the first couple of hours (mainly with multitasking). Having spent a little time with iOS7 I would say that visually it's different enough to keep interest in the platform going, it may not be revolutionary but it certainly feels a lot more modern than iOS6 which somehow seemed old fairly soon after launch with the successive releases of Jellybean and Windows Phone 8.  Putting aside the shiny-shiny UI changes the largest and most important steps are those that improve usability, making it easier to get to controls and settings and adding to Siri's repertoire. There are some minor bugbears that jump out at me immediately too such as Calendar's monthly view which no longer highlights busy days and Mail still throwing an error per-mailbox at you when it can't connect when the reality is that your internet connection is down, I'm sure there will be more over the next few weeks as I use iOS7 on a daily basis as well.  Now, on to the main features...

Death of a Thousand Flashlight Apps

Despite the inevitable focus on the UI changes iOS7 does bring about a number of new features, some might argue several are overdue however there's a good level of innovation in there and even where Apple have 'taken inspiration' from others they've generally done it quite well.  The highlights are...

Control Centre - swipe up from the bottom on any screen (including the lock screen) to see a new 'quick settings' screen providing access to music controls, screen brightness, wifi, bluetooth, torch, clock, calculators and the camera.  You'll notice that like much of iOS7 the translucent background creates quite a different feel depending on then wallpaper you're using, in one example I've used a photo of myself whilst the other is the stock (animated) background.

iOS7 Control Centre    iOS7 Control Centre

Notification Centre - an updated UI matching the rest of iOS7 but with instant access from the lock screen to today's calendar, missed calls/messages and the remainder of your notifications.

iOS7 Notifications - Calendar    iOS7 Notifications

Multitasking - a new full-screen preview (looking suspiciously like WebOS) enables you flip between apps easily and 'flick away' apps to terminate them (Android anyone?).

iOS7 Multitasking    iOS7 Multitasking

Camera & Photos -  a simplified interface allows users to swipe between Video, Photo, Square and Pano (yes, really - 'square' is now a picture type).  Additionally, photos are now automatically organised into collections and grouped by year with small thumbnails...

iOS7 Camera    iOS7 Collections    iOS7 Collections Years

Safari - possibly the largest (and most overdue) collection of improvements with the clutter and chrome gone entirely leaving substantially more space for content, the URL bar and search box are finally combined and coverflow is now being employed for changing tabs.

iOS7 Safari Page    iOS7 Safari Tab Switching

Siri - now includes Wikipedia & Twitter content and provides access to settings (e.g. brightness, Bluetooth)

iOS7 Siri - Twitter    iOS7 Siri - Wikipedia    iOS7 Siri - Settings


General Look and Feel - I've spoken of the simplicity above, here's a few screenshots to illustrate the new design ethos...

photo 3    ios7_keypad     calculator

There are other features I'm yet to play with like iTunes Radio (not available in the UK yet), Airdrop file transfers, audio-only Facetime calls, turn-by-turn walking directions, etc.  Apple also promises to be friendlier for business (which essentially means sysadmins), including data protection, license management, Mobile Device Management, wireless app configuration, enterprise single sign-on support - it will be interesting to see how useful the features are in practice.


How to Disable Animation in Microsoft Excel 2013 / Office 2013

Having just purchased a copy of Office 2013 (no, really) I've just started to use it for serious work and immediately found the new cell animations in Excel to be irritating.  What Microsoft don't seem to grasp is that the people really making Excel the powerhouse that it is are the hardcore users, you know the type - they're often management accountants, business intelligence developers, data warehousing types who handle large volumes of data constantly. Most businesses only have small number of these 'Excel Elite' who get called upon to solve problems, design spreadsheets and generally keep the whole Excel ecosystem afloat and what do they want?  They want simple, predictable behaviour, consistent across new versions and nothing that causes distraction or having to re-learn where to find features (Ribbon I'm looking at you). Excel 2013's latest addition to the 'irritation toolkit' is animated cell selection.  This feature genuinely benefits nobody and serves only to add lag to cell selection, even on new powerful new PCs since the animation needs time to complete otherwise you wouldn't see it.  So, how do you disable it?  Well, it's not as simple as a setting in Excel - this is a setting in Windows (I'm running Windows 8 by the way), the risk here is that you'll be disabling something else fancy that you do want but if you're a heavy Excel user this will be much more worthwhile I expect.

To disable cell selection animations:

  1. Go to Control Panel. 
  2. Search for "Performance" (actually, just "Perf" will do).
  3. Click on "Adjust the appearance and performance of Windows".
  4. Un-tick the "Animate controls and elements inside windows" option and click "OK".

Screenshots of the main screens are below should you have trouble finding the options...

Step 3



Step 4


How to Connect to a Windows File Server on Mac OSX Mountain Lion

So after you connect to your work VPN I expect you'll need to get access to files, in this case we'll assume it's a Windows file server and you've been given a path to connect to that looks something like this:


Now, since Windows works a little differently to Mac OSX (and Linux / Unix) you'll need to change that a little so before we start the next steps get your file server path ready and looking like this:


Then follow these steps:

1. Open the Finder from the Dock (usually at the bottom of the screen), if you can't find this cmd-tab to it.  



2. From the Finder's "Go" menu choose Connect to Server (or just press cmd-K)


3. Type "smb://" followed by your reformatted file path as follows, then hit the "+" key...


4. Click on the newly added Favourite Server and then click Connect.


5. Enter your credentials when prompted then click Connect...


How to Setup a Cisco IPSec VPN on Mac OSX Mountain Lion

Setting up a Cisco IPSec VPN on Mountain Lion is pretty straight-forward however I always forget the steps when I need to run through it with someone else so I thought I'd document the steps.  These all assume you have an admin username/password on the Mac in question and that you have all of your VPN details provided by your IT team... 1. Go to System Preferences on the Apple menu (top-left of screen)


2. Choose the Network settings pane (you may need to back out of a previous pane first)


3. If you need to 'unlock' the pane, click the padlock and do so then/otherwise click the + icon


4. Choose Interface = VPN, Type = Cisco IPSec then choose a suitable name for Service Name.  Click Create.  


5.  Fill in the IP address and user name fields, then click Authentication Settings


6. Fill in your Shared Secret and Group Name, click OK.  Click, Apply and close System Preferences.  


7.  On the title bar, click the VPN icon (doesn't really resemble anything relevant), then choose your new VPN service.  


8. Fill in your username/password (special considerations may apply for RSA Tokens, etc.) and click OK.  You may receive a message at this point regarding security, read and click OK as appropriate.  


9. Counter appears on title bar whilst VPN is connected.  


10.  To disconnect open the menu from the VPN icon once more.  







BusObj: Object failed to run due to insufficient security privileges.

I had a situation recently where scheduled Business Objects reports began to fail with the error message: "Object failed to run due to insufficient security privileges. ".  Having spent some time googling the error there seem to be many potential causes if permissions have been changed but in this case it turned out that a user had been deleted.  In Business Objects (XI R3.1 at least), when a user is deleted their scheduler instances will be allocated to the Administrator who, despite having scheduler privileges, appears not to be able to execute scheduled reports. The only solution I could find was to manually reschedule all reports previously owned by the deleted user, if anyone has any better solutions or knows of a method to make a bulk change rather than individually I would love to hear it!

Win Tickets to Mobile World Congress 2013 (MWC 2013)

Despite the fact that the last event seems like only a few months ago it's nearly time again for Mobile World Congress, as usual there are a raft of competitions around offering tickets to lucky entrants so I thought I'd collate the ones I've come across here. For anyone unfamiliar with MWC, it's an annual coming together of the entire mobile industry from network operators (MNOs), manufacturers such as Samsung, LG, Nokia, HTC, software and services companies like Google, and a whole host of companies making apps, accessories, etc.  Having been once (see my MWC Impressions post), it's quite an experience and well worth going if you can make it.

Anyway, on to the list - if you spot any I've missed please leave a comment and I'll bump it up into the post.

It's not always obvious when the open/closed dates are so I apologise if any of the above are no longer valid, please leave a comment if you find that to be the case and I'll remove the link.


Ofcom Mobile Phone Usage Report 2013: App & Web Highlights

Today Ofcom released a report covering mobile phone usage in the UK, the report is largely aimed at determining how Quality of Service relates to consumer behaviour and how poor coverage affects the customer experience.  The report covers voice, SMS, email, internet and video calling but I'm most interested in the mobile web and apps, so I'll focus primarily on the Internet results.  The survey was conducted in November 2012 and sampled 2,136 adults aged 16 and over from England, Wales, Scotland and Northern Ireland.

The report shows that only 44% of consumers use the internet via their mobile phone and of those, this seems quite low based on personal experience but that could be down to bias from working in the technology sector.  Of those people that use the Internet on their phone 50% do so more than five times per day (with 33% reporting 10+ times per day).  The implication being that there is a demographic relying on their mobile for everyday tasks, especially when coupled with the result that 71% of people saying that it is important for them to use the internet whilst outside.

Naturally there is a skew towards the younger end of the market with 86% of respondents between 16 and 34 that use the internet doing so on a daily basis.   Additionally whilst 7% of all users rated the ability to use the internet as the most important factor whilst thinking about their mobile operator this rose to 14% for the 16-35 age group and drops to 2% for the 55+ group.

Reliability is a problem for internet users with 47% being satisfied with their operator, this is poor compared to voice and SMS usage which received a 74% satisfaction rate.  Furthermore 34% of consumers reported having experienced no signal/reception (10% frequently) and an additional 15% reported an inability to use the mobile internet.

In terms of non-Internet usage there weren't too many unexpected results, the only surprising outcome of the survey to me was that 12% of respondents have used video calling and 25% do so on a daily basis - making 3% of all people using video calling on a daily basis.   The full report (33 pages) can be found here: source.


iOS Device and Version OS Support Matrix

During a project there always comes time to determine what the minimum version of iOS to support is for a given project, last night I stumbled across a handy infographic for checking which devices support which OS versions as well as some other handy feature related info.

 The iOS Support Matrix 2012 was created by Empirical Magic and Pencil Studio...

Quick Tip: How to Take Snapshots of Data at Specified Intervals

A few weeks ago I was debugging something and needed to take snapshots of a table every five seconds, as a quick solution a colleague started running the following SQL and I thought I'd share it for anyone else stuck in a pinch...

WHILE (1=1)


	INSERT INTO tmp_snapshot
	SELECT field1,field2,field3
	FROM table1 WHERE field4 = 'value'

	WAITFOR DELAY '000:00:05'


Just change the time delay and the query and you're off, of course you could be a lot cleverer with your WHILE conditions and rework this principle for loads of things.  I thought it worth posting as many database folk forget the power of the humble while loop!

Quick Fix: Invalid Parameter Number (WIS 10901) in BOXIR3.1

I encountered this error whilst testing a Stored Procedure Universe in BO XI R3.1, given that it says "Invalid Parameter Number" you would naturally assume that it was something relating to the stored procedure itself, passing parameters from the front-end to the DB in SQL, etc. but in my case it was a classic red herring error message.

The issue turned out to be permissions problem, the user executing the stored procedure did not have permissions to execute it.  After running the following SQL statement everything ran perfectly...

GRANT EXECUTE ON my_dwh.dbo.usp_do_something_useful TO PUBLIC 

... though bear in mind that you may not want to grant permissions to everyone ('public'), I just use it as a simple example.


SQL Server Agent PowerShell Jobs Waiting for Worker Thread

My normal Data Warehouse load process runs daily in the early hours of the morning so I'm not used to sitting watching it - this morning however through lack of sleep I happened to be there and I noticed something odd: there seemed to be a queue. What seemed to be happening is that two jobs would run simultaneously whilst other jobs sat showing a status of "Waiting for Worker Thread".  After digging around I found that...

EXEC sp_configure;

... showed that the server was configured to automatically manage the max_worker_threads setting (value of 0), additionally...

SELECT max_workers_count FROM sys.dm_os_sys_info; ... showed that the value being used (based on the 32/64 bit status and number of cores) was indeed 512.  A quick check of... SELECT COUNT(*) FROM sys.dm_os_threads; SELECT COUNT(*) FROM sys.dm_os_workers;

... showed that the actual running values (54 & 46 respectively) were within expectations and nowhere near the 512 thread limit so what was happening?

Well, it turned out that whilst my jobs are a mix of OS admin tasks, legacy DTS jobs and SSIS packages most of them involve some form of PowerShell script to initialise them (usually moving files around, setting permissions, etc.).  The problem was down to an obscure setting that limits the max_worker_threads to just 2 for the PowerShell subsystem, the setting can be seen here:

SELECT subsystem,max_worker_threads FROM msdb.dbo.syssubsystems;

In my case I just upped the running value to 40 (the same value set for CmdExec) and restarted SQL Server Agent:

UPDATE msdb.dbo.syssubsystems SET max_worker_threads = 40 WHERE subsystem = 'PowerShell';

I'm yet to spot whether this has any other long term effect but things seem to be behaving fine, I will say that as part of my research I found other people saying that this setting did not persist through a restart on certain versions - I'm running 2008 R2 and that appears to be fine but it's worth double-checking if you try this.


Eurogamer Expo 2012: Wii-U Impressions & General Roundup

This year's big launch event at Eurogamer Expo came from Nintendo with UK gamers getting their first chance to play on the Wii-U and since I had early-entry tickets to opening day it was almost certainly the first thing I planned to do after making it past the huge entry queue. Thankfully queues at the Wii-U stand were short and I jumped straight into a demo of Nintendo Land, a series of mini-games themed around Ninty's main properties.  On show during EGXP were The Legend of Zelda: Battle Quest, Luigi's Ghost Mansion, Animal Crossing: Sweet Days and  Takamaru's Ninja Castle.

The new tablet-style GamePad is comfortable to hold, provides two analogue sticks, triggers and an array of buttons that would look at home on a PS3 controller as well as a 6.2-inch resistive touch-screen.  Despite being only 158ppi the resolution of 854 x 480 is more than adequate for casual use and active gameplay but the decision to opt for a resistive touch screen seems puzzling since compared to the capacitive touch screens found on modern smartphones it feels noticeably unresponsive and I suspect small children may struggle with the force required to operate it.

The use of the second screen really comes into its own when used to provide one player with a different view of the action to the others which I saw during a series of five-player sessions with one GamePad and five Wii-motes.  In Luigi's Ghost Mansion the player with the GamePad plays the ghost and can see all four players and himself whereas the other players can only see themselves and each-other on the television, a similar mechanic is also used in Animal Crossing: Sweet Days.  In these scenarios the four Wii-mote+TV players work together by shouting out tips and calling out the bad-guy's location whilst the bad-guy tries to use stealth and independence to get the upper hand and it works brilliantly - classic Nintendo fun.

Despite the runaway success of the original Wii, Nintendo may face a struggle trying to promote the Wii-U with stiff competition coming from Sony's PS3-Vita integration and the upcoming SmartGlass on the Xbox 360 (good article on PocketLint covering the three).  The other issues Nintendo may face are awareness and price, despite a release date of November 30th most of the techies in my office had heard of it but none of the 'normal folk' had and with a rumoured release price of £229 - £250 it's going up against the Xbox 360 + Kinect (£250 with game) or the PS3 + Move (£250 with game) both of which offer significantly enhanced features such as Blu Ray, Netflix, iPlayer, Video on Demand, etc.

What about the rest of EGXP2012?

Last year's queues wore me down a little two much and this year I just didn't have the appetite having spent an hour queuing to get in then spend half the day queueing inside so I missed out on playing Call of Duty Black Ops 2 and Halo 4 but a spectator's view showed more of the same so in both cases if you like the previous games you'll probably like the new ones.  That's not to disparage sequels though as I had a blast playing Tekken Tag Tournament 2 and was literally stunned by the graphics on show from Forza Horizon.

Thankfully the single most memorable game of the show was an original property: Tokyo Jungle,  starting as either a deer or a Pomeranian the player must to survive as long as possible in a post-apocalyptic world eating plants, fighting with Beagles and even mating (an act I performed to gasps from onlookers).  Along with the weirdness that Tokyo Jungle brought a fair slice of odd was on offer in the Indie Games Arcade, always one of the highlights of EG Expo this is where the one-man-band and small team developers get to show their wares which sometimes tend towards the bizarre like the first-person-ambler Proteus or one of my favourite indie games of all time shown at a previous Expo: VVVVVV.  Another highlight was the Retro Zone where I got the chance to play on and Amstrad GX4000 and a TurboGrafx-16, something you don't get to do every day and a treat for lovers of retro gaming.

At the end of the day I was disappointed that I didn't get to see Hideo Kojima's Developer Session but as I said earlier, queuing for an hour or more just to get in just wasn't worth it in my eyes and I think that Eurogamer ought to consider a pre-booking facility for the popular talks rather than making attendees waste time queueing.  All told though, it was a good day and well worth the trip - if you've not been I would definitely advise you to pop along to the 2013 show and if you can afford it I'd recommend going on opening day and buying early entry ticket as it'll increase your chances of seeing what you want to. Oh, and wear comfy shoes.

Update a Table Using a Join in MySQL

It's one of those occasional requirements that come up in a DBA or Data Warehouse architects's life, to update one table with values from another.  It's a fairly straight-forward syntax in MySQL but one that differs from my 'platform of choice' Microsoft SQL Server, the syntax goes...

UPDATE child c 
INNER JOIN parent p 
        ON p.id_parent = c.id_parent 
SET    c.parent_name = 
WHERE  c.parent_name IS NULL 
       AND p.legal_guardian = 1;

Now my example is fairly contrived but it does show not only updating the table over the join but also using criteria in the WHERE clause, there's also an advantage over T-SQL in that you can use the table alias in all cases.

Convert Seconds to a Time Format (HH:MM:SS) in Business Objects / Web Intelligence

Every now and again you'll have a value coming from a database in seconds, this is usually very handy since you can perform operations on a nice friendly numeric value but often I find that the user will want to see the outcome in a more familiar time format. I had this problem the other day and rather than write the formula from scratch I used Google and came across Christian Key's solution which mostly solved my problem.  Christian's solution returns a formatted string, however I needed to return a value that Business Objects understood as a time, in order to that all I had to do is wrap his solution in a ToDate() function as follows:

=ToDate(If((IsNull([TestSeconds])) Or([TestSeconds] = 0 )) Then ("00:00:00") 
Else FormatNumber(Floor([TestSeconds] /3600) ;"00") + ":" + 
FormatNumber(Floor(Mod([TestSeconds] ;3600)/60) ;"00") + ":" + 
FormatNumber(Mod(Mod([TestSeconds] ;3600) ;60) ;"00");"HH:mm:ss")

It is best to supply integer seconds into the above formula, feeding decimals can cause unusual rounding errors and provide times like 02:60 instead of 03:00.

Many thanks to Christian and I hope my little addition helps a fellow WebI user out there.


Change a Column's Data Type in MySQL (equivalent of ALTER COLUMN)

I just thought I'd post this little tip as the syntax difference between MySQL and Microsoft SQL Serverjust foxed me once again...

ALTER TABLE my_table 

You may wonder why the column name is repeated twice, that's because the same syntax also allows you to rename the column at the same time.  The syntax might be a little odd but it does makes sense if you think about it.


Mobile World Congress 2012 Impressions

I started the day at the App Planet exhibition in Hall 7 and whilst one or two of the stands were still busy setting up I started at RIM where they were demonstrating NFC and streaming media from Blackberry to PS3. I found the Blackberry guys to be typically bullish (always a little more so than you might expect) and their offering was quite slick but overall they lacked a 'message', especially when compared to their closes rival in the smartphone space: Nokia. Having kept everything under wraps until after the press conference had finished Nokia really had something to show and the message loud and clear was: "we're doing lots of crazy cool stuff". Innovation was a strong theme including high-definition call quality, nano-technology and indoor positioning as well as the superb tie-up with Dolby for digital audio and the frankly incredible 808 PureView boasting so many features you have to double-take when you find out that it's a Symbian phone. It was definitely good to see them back on form and if the enthusiasm of the staff is anything to go by thing are looking up and seriously, it's gotta be hard to stay chirpy in a blue Where's Wally outfit.

One interesting technology I saw was Clic2C, a print watermarking method that gives QR-code like functionality but without the ugly QR code despised by magazine layout artists the world over. Most impressive was the fact that it can work in newspapers which typically have a low dpi. The best individual app I saw was probably Runtastic, a fitness tracking app due to launch imminently which is available with a heard rate strap and receiver for around €60. Another app launching soon is Voice Over IP service Voxtrot, free of charge from handset to handset with PSTN calling coming later their USP compared to Skype is set to be call quality and address-book integration - interesting if it lives up to the spin.

I had an interesting chat with a guy from haptics company Immersion, if you've never heard of them you may still have used one of their products - they're responsible for that little buzz when you press the on-screen keys on your phone. Their idea is to provide a sense of physical action when interacting with touch screen devices and some of the uses demonstrated were quite compelling though hard to explain in writing. The advances are being made in terms of response times, sensitivity (very soft to quite aggressive) and resolution (i.e. how close to your finger does the effect feel), this is great news for gaming though I am convinced that all kinds of apps can benefit from improved and varied user feedback mechanisms.

Down in Hall 1 things were much more carrier oriented With LTE testing gear and a phenomenal focus on small cell and femtocell technologies. One unexpected highlight of the day was SpareOne, an emergency phone that can be powered by a single AA battery with a reported standby time of 15 years (basically, the life of the battery) and a talk time of three hours on a single cell. Sure, it has niche uses and isn't going to be supplanting the major handset manufacturers but it has the potential to make a massive impact on the niche it serves and will no doubt save hundreds of lives. Also down in Hall 1 were Opera, touting their Opera Mini browser - a great alternative to the stock Android browser and with the benefit that their proxy technology saves on bandwidth and makes content load considerably faster than other browsers.

Back to handsets, HTC were hanging with the carrier-grade boys but had a good showing with their new HTC One lineup and whilst I'd be hard pushed to explain the differences between the V, the S and the X some of the features in the range were impressive. As a photographer the burst mode shooting caught my eye, allowing you to take 5 photos per second which will be great for taking photos of moving subjects - parents taking photos of kids will definitely appreciate that as kids and animals rarely stay still. The Beats Audio addition is interesting and it adds a bit more "welly" but under the hood I'm not sure it's anything more clever than the "BASS" button you used to get on old portable tape decks.

Well, that's the bulk of my floor-walking for the day - time for a bite to eat and some shut-eye to prepare for tomorrow's sights.

The Database Countryside Code: Best Practices for BI & SQL Users

Those 'City Folk' among you may not be aware but in Rural England we have what is called The Countryside Code, it's a set of guidelines that everyone should follow in order to keep the countryside clean, tidy and a nice place to visit.  You may be asking - what does this have to do with Business Intelligence and Database Administration?  Well, I think it's vital - if we all follow a fairly simple but broad set of guidelines then all classes of database user will have a better experience from Developers to DBAs and Analysts to CIOs.  This isn't really about making your databases perform better, it's about working better with each-other and taking other people's perspectives on board.  Having been in most of the related roles over the years this is what I'd put into The Database Countryside Code...

1. Enjoy the countryside and respect its life and work
Whether your application is an 'out of the box' software suite, a Business Intelligence package that can be tweaked on implementation or a hand-crafted bespoke solution if you're running against a database maintained by someone else or shared with other applications you need to take heed of this point.  Remember that cooperation is key and if you build a good relationship with the DBA and the other key users of the database you'll have a much better time of things and if there are any critical issues you'll be included in the remediation process and may even be able to help your own users get back online faster.  It's easy to see DBAs as grouchy, narrowly focused sorts who tend to view all user activity as bothersome (I can say that as I've been one myself) but generally speaking if the DBA is aware of user activity at all the chances are that there's already a problem as it's the long running, resource intensive activity that will stand out in alerts and performance reports.  Before your application goes live you should do some testing, run your designs and SQL statements / stored procedures past the DBA for some advice (but remember, you don't have to take it) and establish some sort of procedure for reporting issues, and remember that an SLA can work both ways as you may need the DBA's help as much as they might need yours.


2. Guard against all risk of fire
Security is a huge issue and as exploit frameworks and toolkits become more and more prevalent and feature-rich the likelihood of vulnerabilities being discovered in our applications should be treated more like a certainty.  If you're developing bespoke applications and especially web apps you'll need to pay close attention to the OWASP Top 10 application security risks but from a database perspective the most notable threat is SQL Injection - the art of passing SQL into an application so that it might be executed by the database (as a good starting point check out OWASP's SQL Injection Prevention Cheat Sheet).  If you're deploying packaged apps or BI tools don't think that you've gotten away with it, the primary responsibility may be on software developers to avoid exploits but if they're baked into an application you're implementing it will affect your users and your business, so...


3. Protect wildlife, plants and trees
The most important security contribution we as implementers can bring to the table is to review and limit the privileges required by our applications.  Many install guides and expensive external consultants ask for a 'dbo' (database owner) level user and some even ask for 'sa' (system administrator) or 'root' level privileges but don't hand these out like candy on halloween.  In most cases these high-level privileges are only required during setup and install and can be removed afterwards but often basic read/write access is all that is required (and for BI tools often read-only), it may only be achievable through a few frustrating rounds of trial and error but if you assign your applications the lowest possible permissions you will significantly reduce the risk of compromise in the future.  Another important step during implementation is to make sure that your permissions are segregated, where possible have a separate user for each service and an entirely separate user for accessing each database not shared by any other application.  Whilst it may seem excessive this setup will allow you to audit any security issues and identify which user was compromised and exactly what they had access to.


4. Fasten all gates
Many Business Intelligence tools include some degree of control over connection management and if you're developing your own application you'll have complete control over all database connections, the decision to be made is whether connections are 'pinned' open, closed after x minutes or closed at the end of each transaction.  The preference will vary depending on the load and the usage, in most Business Intelligence use cases there tend to be a large number of users, not always connecting concurrently and issuing fairly large queries against the database followed by periods of quiet whilst a report is read - in this case there is usually no need to keep the connection open for long.  On the other hand if you have users issuing a constant stream of small transactions (e.g. a Point of Sale system) the overhead of creating and dropping connections might actually add load to the database so it would be more effective in this scenario to maintain the connection.


5. Keep your dogs under close control
This applies more to developers and BI architects where your dogs are your users, if you are deploying an application that creates load on somebody else's database you should do whatever you can to limit each user's ability to cause long running queries - in some BI tools you are handed an option to let a query time out after x minutes and perhaps limiting the number of rows returned.  If you are developing your own application you should include both of these options but make sure that you kill the query at the database level rather than just killing the thread in your application that made the request otherwise it's equally bad if not worse since the user may simply re-issue the offending query.  The actual limits are bound to vary from database to database but that's where the first point comes in, discuss this with both your users and the DBA.


6. Keep to public paths across farmland / Use gates and stiles to cross fences, hedges and walls
When it comes to solving problems try to stick within the basic and simple boundaries of an ordinary user, avoid using undocumented stored procedures, excessive use of user defined functions, custom data types, plugins and extended stored procedures or anything else that strays too far from a standard install of the database platform.  Obviously you've got an app to deploy and you want to solve your problems in whatever way is best for your users but the further you are from a standard deployment the more issues you're likely to encounter, both you and the DBA might be fully aware of this amazing new setting you tweaked to make things run better but a couple of years down the line during a disaster recovery will it all come flooding back quite as easily?  What if one or both of you that setup the application have moved on to other roles?  Thinking outside the box is great but be conscious of introducing risk and if you do feel that it is necessary then make sure that it's well documented in the Run Book or the corporate wiki.


7. Leave livestock, crops and machinery alone
Since you may already have elevated privileges on your own database, a shared database or even the server you may be tempted from time to time to perform maintenance tasks or make minor 'improvements' to indexes or configuration settings - do not do so without the DBA's blessing.  If you're following the rules above you'll probably have a fairly good rapport with the DBA already so it's likely that you'll be granted some level of trust not to mess things up but be careful not to overreach, the DBA will be 'in the loop' of many changes and other requirements (e.g. critical deadlines, disaster recovery tests, unplanned maintenance) whereas you may not be aware of them so before you make any changes run them past the DBA - just in case.


8. Take your litter home / Help to keep all water clean
If you've ever been a DBA you'll have seen, on more than one occasion, tables popping up called tmpSomethingorOther, tblToBeDeleted or TableName_bak but when it comes to the key questions (How long have these been around?  Are they still required?) nobody seems to have a straight answer.  I know myself that whilst I've been developing data warehouses I've created these sorts of tables and subsequently forgotten what they were used for, not too much of a problem if you're 'the guy' but in a large team or with personnel changes over time it can be hard to know what is required and what isn't - I came to a database once with temporary tables over five years old which had not been deleted out of fear that they were important.  The moral here is an obvious one, clean up after yourself or if the table must exist for some short period of time put a note in your diary to come back and cull it.


9. Make no unnecessary noise
Be mindful of what errors you raise and what you write to public logs, if your application causes a large amount of data to be written to database or other centrally collated logs you may inadvertently make it harder to detect genuine issues which will hurt both you and and other users of the database.  If you do occasionally need exhaustive logs consider adding a 'debug mode' into your application which can be turned on or off via a configuration setting, that way you can turn it on whilst you're tracing a fault and need more verbose logging then turn it off when you're done.


10. Take special care on country roads
There can be plenty of unexpected hazards on country roads so don't always rush around everywhere at 60mph, acknowledge that whist you might want everything to go as fast as possible you could be causing some other critical process to slow or stop.  Driving at night can be treacherous too as you might come across an unexpected backup window or import/export process, talk to your DBA and coordinate the major tasks.  If it's a shared server make sure you have access to the task list so that you know where to slot in your jobs and that those jobs get put back into the master list.

Really it comes down to one thing, as the great and wise Jerry Springer oft said, "take care of yourselves, and each other".

Mobile Web Design Best Practices, Tips and Tricks

Everyone knows the key mantra for designing mobile web sites - "keep it simple" but there are some tips and tricks that will help to create a great user experience for mobile visitors...

  • Capture mobile users from the full site - if your full site isn't rendering well on mobile devices how are people going to find the link to your mobile site?  Put in place a redirect to a mobile optimised layout though it's worth remembering that redirects could also be annoying to users that wanted to see your main site so...
  • Provide a link back to your full site - this could be in the footer or as a landing page but in some cases the user may be trying to achieve something not possible on a slimmed-down mobile site or they may be on a tablet that is incorrectly being recognised as a mobile device.
  • Remember the bad old days - there are still a large number of mobile devices out there that do not fully support CSS and JavaScript, including older Blackberry models which are common in corporate environments.  If non-smartphone users are a target audience for your site it should be designed with these older phones in mind and progressively enhanced to support more modern design features and input validation.
  • Consider multiple mobile layouts - you could have a theme that optimises content specifically for iPhone and Android, leaving the other mobile users with a plainer but still small-screen optimised site.  Figure out what your audience is likely to be using and target that but don't forget to tweak and customise the site after you've gone live based on the type of devices your users are actually using which will change over time.
  • Use appropriate input types - if you are asking the user to provide email address or usernames via a form it can be difficult for them to enter correctly if autocomplete is turned on, similarly it would be better to provide the numeric keypad if you are asking for a telephone number and you usually would not want .  You can provide this functionality with a mix of the <input> tag and the autocapitalize property, there are a whole host of other possibilities including length checking and regular expressions but bear in mind not every device will respect these features.
  • Avoid scrolling - pagination vs. scrolling has long been a debate in web design circles but if you want to provide your users with a more 'app-like' experience the key elements to your site should fit adequately on the page without the need for scrolling.  This may not apply to content but if the user is being asked to follow through a process or provide a series of inputs it will be much clearer to the user what they have to do if it fits on one page, equally...
  • Avoid clutter - if you have pages with little content it may be worth ensuring that any non-essential (but for whatever reason required) footer information sits below the bottom of the screen to avoid clutter, at the very least you should consider a little trailing white space followed by a dividing line to clearly separate the content from the footer.
  • Consider the user's goal - you might be falling over yourself to provide content or services to your mobile users but is that what they really want?  Consider whether or not the user might have other goals in visiting your site and show how they can be achieved, even if that is not via your mobile site.  For example, it may be helpful to include a 'contact us' or a telephone/email link on at least the first page if not every page.
  • Don't be annoying - it's the little things that tend to irritate users and on a mobile device this is magnified since they are already compromising on screen size and input capability.  For example, pre-fillling forms with help text may mean that the user is going to have to delete that text to enter their own - irritating enough on a desktop and even more so on a mobile device.
  • Device testing is essential - there are dozens of emulators and simulators for mobile devices but nothing will ever match testing on devices, it is very tempting as a developer to test primarily on a desktop but it really isn't the same as holding a small device at arm's length and using a tiny keyboard to provide input.  During your testing phase have someone with a very critical eye run through your site to check for any minor irritations, make sure to tell them to be ruthless in their criticism.

I hope that provides some useful information to those of you starting out with the mobile web and of course, much of this is up for debate so do get in touch if you disagree or have content to add.  The list is not intended to be exhaustive and over the next few months I'll add posts on testing and more technical aspects of the process.

How to bring OSX Lion's Natural Scrolling to Windows 7

If, like me, you move between Windows and Mac on a daily basis you may have found yourself finding it a little hard to figure out which way to scroll the mouse.  With OS-X Lion Apple introduced ‘natural’ scrolling which means that when you scroll the wheel on the mouse an upwards push sends the scroll bar down, that might sound weird but in essence your upward movement of the wheel actually pushes the screen upwards – very much like a touch gesture on a smartphone or tablet. Whether you love it out loath it, getting used to switching between the two is difficult and you could either turn it off on the Mac or if you like it you could bring the same feature to Windows.  As it happens the feature is already there, to enable it you need to edit a registry key and if you’re not familiar with this process I would advise caution since a mistake in the Registry can make your machine quite unstable but if you’re comfortable with RegEdit you’ll need to modify the following key:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Enum\HID\????\????\Device Parameters\FlipFlopWheel

Set the value from 0 (default) to 1 where the ????\???? section are whatever device IDs you can see.  I changed the FlipFlopWheel property for all of the devices I could see, unplugged and re-plugged the mouse and the then it worked – natural scrolling on Windows.

Credits go to darkfader on the NeoSmart forums for the original solution.