Archive for December, 2010

SQL Server Backup Compression vs. Quest Litespeed Engine

As more and more functionality is built into products like SQL Server it’s always worthwhile reviewing third-party tools and utilities when you’re considering an upgrade to see (a) if they’re still required and (b) if the tools themselves need to be upgraded.  With the introduction of Backup Compression in SQL Server 2008 R2 Standard Edition you could begin to think that the future is grim for Quest’s backup compression software LiteSpeed so I thought I’d do some testing to see exactly how it stacks up against the native compression.

LiteSpeed Engine

I’ve been using LiteSpeed on and off for a few years now and it has always been a great tool but I’ve always found it a bit of a drag to have to use the GUI to administer and setup jobs however in January 2010 Quest launched the LiteSpeed Engine for SQL Server which allows you to administer jobs using the native SQL Server tools.  The LiteSpeed Engine acts as a driver and the configuration tool allows you to define a variety of configuration profiles based on file extension and from that point onwards you can use the Management Studio to setup backup jobs, maintenance plans, etc. and all you have to do is specify the file extension of the profile you wish to use.

The configuration tool allows you specify the compression level from 1 to 8, encryption level including various bit-length versions of RC2, RC4, 3DES and AES though as you’ll see later the overhead of adding the highest level (256-bit AES) isn’t that great so I’d always shoot for the maximum.

Benchmark Structure

The test is relatively unscientific since I used only one database but it was carried out systematically, the data comes from a transactional billing system which I chose as it has a mix of strucured tables and raw transactions and comes in at about 6.5GB so it wouldn’t take too long to test.  I used the following configurations…

Benchmark Results

On my test database the baseline SQL Server native compression reduced the 6.2GB database to 765MB (12.2% of the original size) and took less than half the time (43%), to achieve the same level of compression using LiteSpeed I had to use Level 2 which gave me 12.2% of the original size and 40% of the original duration.

At first this doesn’t look great for the third-party tool but the benefit of using a mature backup compression engine is the flexibility and LiteSpeed’s configurations allow you to tweak the performance to solve whatever problem you have in your environment whether that be the absolute size of the backup, the backup window time or a mixture of the two.

If it’s size you’re after then Level 8 really did seem to work wonders on my test DB bringing the size down to 5.6% of the original at only 352MB though it did take 2.6 times the original duration, if it’s the backup window you’re looking to reduce then the basic Level 1 did manage to improve on the native compression by taking 0nly 37% of the original duration whilst still compressing to 13% of the original size.  If like most people you’re looking to have your cake and eat it (i.e. reducing size and backup window) I’d suggest that Level 3 is the best compromise giving 10.9% of the original size at 77% of the original duration so you get some benefit in both areas, though Level 4 takes compression a bit further and still gave a slight time reduction.

Clearly, the real answer is testing and since I’m at the beginning of data warehousing project I’m not in the position to make any firm decisions but I think that even if you don’t run out and purchase it now LiteSpeed is a very valuable tool to have in your mental arsenal so that if you come up against backup size/window issues or you’re faced with older versions of SQL Server you’ve got a solution in mind already.  Quest have an odd policy of keeping pricing quite opaque but I believe that the full Enterprise version (including the LiteSpeed Engine) retails for around £1,800 ($2,800) which isn’t too bad if you need that level of flexibility.

SQL Server Native Compression

LiteSpeed Compression (No Encryption)

LiteSpeed Compression (With Encryption)

Quick Tip – MySQL Equivalent of ISNUMERIC()

In one of the source databases for my data warehouse I have a table of contact telephone numbers, stored as VARCHAR() values since where a number is not available the record might say “unknown”, or “N/A”.  So to extract a list of all valid telephone telephone numbers I need to perform a logical test and one of the simplest checks I can perform is to verify that the number is actually a number. 

This would be pretty straight-forward in Excel with the ISNUMBER() function, or in T-SQL with ISNUMERIC(), but neither work in MySQL so after a little searching around I came across this solution…

SELECT DISTINCT contact_number
FROM customers
WHERE contact_number REGEXP (‘[0-9]‘)

Effectively we’re processing a regular expression on the contents of the ‘contact_number’ field, it may seem like using a sledgehammer to crack a nut and I’ve no idea how performance would differ from a more simple approach but it worked and I guess that’s the point.

Trends in Business Intelligence & 2010 Review

It’s the time of year when magazine editors can’t resist the urge to fill their glossy wares full of ‘thing of the year’ articles, the print equivalent of the mid-season “clip show” that has plagued many a TV series.  Well, if it’s good enough for them it’s good enough for me so here’s my rather unstructured and unscientific take on Business Intelligence and Data Warehousing in the year that was – 2010…

Market Trends

To start, I’ve taken a series of snapshots from the excellent Google Trends showing global search volumes for each of the Big Four offerings to measure the level of interest.  It’s reasonably clear to see from the graph below that interest in OBIEE shows a small but steady growth whilst Reporting Services shows a marked decline and the other two offerings remains roughly static (maybe a small decline?), this surprised me given that with the release of 2008 R2 I think that Reporting Services is really getting to the point where it offers a legitimate choice in the BI marketplace.  Perhaps the issue that Microsoft have fragmented their BI offering to include a mixture of terms with Excel, PowerPivot, SharePoint, Analysis Services and Reporting Services all making up the BI stack and nobody really knows what to call it?

This year has also brought an increased emphasis on Mobile BI with the iPad and iPhone fast becoming common executive playthings, Business Objects making it’s Explorer and Xcelsius products available on Android in addition to the iPhone (Explorer only).  MicroStrategy took the mobile emphasis a step further (perhaps to help stick their head above the crowd) by announcing a strong focus on the mobile BI market and offering a free 25-seat licence for their Mobile Suite.  Despite a strong focus on marketing Mobile BI I’m still not convinced that any of the vendors have really hit the nail on the head with their solutions in that whilst many offer pretty visualisations and slick interfaces most seem to lack the kind of simplicity that helps to present information quickly and succinctly, even the frankly beautiful independent product RoamBI just feels a little overdone when it comes to actually using it.

Major Product Releases

It’s been quite a year in the BI & Database world with the launch of Microsoft SQL Server 2008 R2, Oracle Business Intelligence Enterprise Edition (OBIEE) 11g and IBM’s Cognos 10…

Microsoft’s launch is effectively a moderate evolution of SQL Server 2008 in most areas with little change to the database engine, it’s ETL tool Integration Services and it’s OLAP engine Analysis Services.  That said, R2 did bring some handy incremental features which will be especially welcomed by the budget-conscious with an increase in the DB size of the free Express Edition from 4GB to 10GB and the addition of Backup Compression to Standard Edition.  There were some interesting additions with PowerPivot, Master Data Services and StreamInsight thought I’m not sure that either will find a natural home for a good year or so as busy DBAs and developers struggle to find the time to try these new features out.

Despite the major jump in the version number Oracle’s release too seems to be mainly an evolution and as a great fan of the product I’m quite considerably relieved since Oracle could quite easily have been over-zealous in integrating their ‘own’ tools like Discoverer and Warehouse Builder with bought-in technologies like Siebel Analytics (which became the bedrock of OBIEE), Hyperion’s Essbase and Sunopsis (now Oracle Data Integrator).  One of the less exciting but fundamentally important additions is that the semantic layer employed in OBIEE will be directly and immediately compatible with future releases of other Oracle products in the CRM, ERP and Finance application spaces.

I’m not as familiar with Cognos as the other two tools having only experimented with Cognos 8 for a couple of weeks but from everything I’ve read it seems that Cognos 10 was certainly a major milestone in the product’s lifecycle.  Aside from the shiny sounding features such as Social Networking and iPad support (actually a very serviceable looking mobile BI app) there are some very cutting-edge additions to the product including a statistical engine drawn from SPSS and Active Reports which allows users to explore and analyse offline data including interactive email reports.

The Future?

No good review and roundup article ends without a nod to the future and whilst I’m not keen on making absolute predictions there are a few developments I’ll be keeping my eye on for 2011 and beyond.

The main event I’m anticipating is the release of Business Objects XI Release 4, I’ve not seen too many concrete details about functionality but over the last few years Business Objects have seen themselves distracted by the Crystal acquisition (including the shoe-horning of their core product into Crystal Enterprise) and in turn their acquisition by SAP.  As a regular and long-term user of Business Objects I’m really hoping that they’ll blow away some of the cobwebs and deliver some new functionality as well as rounding off some of the edges that in previous versions feel a little unfinished, it would be great too if they finally included the key functionality from the legacy desktop client (which many long-term customer still rely on) in their core Web Intelligence product (Freehand-SQL & Grouping – I’m looking at you).

Another area to watch in Business Intelligence and Data Warehousing as well as the wider enterprise market is cloud computing, Informatica’s ETL in the Cloud offering has seen improvements and adoption throughout 2010 and it’s almost a given that Microsoft will be adding some degree of ETL capability to their SQL Azure platform. I’d expect an announcement if not a release along these lines in the coming year, though it’s possible that ETL comes behind providing cloud based analytics (something SSIS guru Jamie Thomson suggests).

In a broader sense I’m expecting to see a little more interest and pickup in the open source BI market, I’ve been saying this for a while (“this time next year, Rodders…“) and I might be wrong for some time to come but I always keep an eye on companies using an Open Source model such as the ETL vendor Talend who recently acquired Sopera (a middleware and SOA vendor), BI vendor Jaspersoft and all-rounder Pentaho.  With the global economy still suffering a hangover from the sub-prime mortgage crisis and banking collapse people have been looking for cheaper alternatives and open source companies provide a great way to achieve that, though some of Talend’s high-end offerings are almost comparable in price with other commercial products.

Another possible area to watch out for is the area of Personal Intelligence, essentially Business Intelligence for the individual.  A colleague and I spoke about this back in 2008 and we could both see that as people increasingly become data-aware they’ll start to look inwards and aim to measure things about themselves, one obvious starting point is fitness and we already have sites to log and chart your weight and calorie intake as well as the brilliant Nike+ product that measures your pace, time and distance during a run using either a sensor in your shoe or GPS (iPhone app), see the sidebar of this blog or below (one of my runs on the Nike+ site) for examples of the output.

Reset Default Database from Command Line in SQL Server

During a recent consolidation exercise I was migrating one database at a time and then taking the old copy offline, unfortunately one of the DBs had (un-knowingly) been my default database and next time I tried to log in I received the dreaded “Cannot open user default database. Login Failed.” message. 

I tried one of the workarounds proffered by Microsoft for this exact problem only to receive exactly the same error message, thankfully I then came across this excellent post on SQL Server Nation which explains the entire issue from start to end and offers a couple of fixes based on your particular scenario. 

In my case it was to execute the sp_defaultdb stored procedure using the OSQL utility, for example…

osql -Sservername -E -dmaster -Q”sp_defaultdb ‘username’,'master’”

Sincere thanks to Brandon Galderisi from SQL Server Nation for the fix.

How I Built my First iPhone App with JQTouch + PhoneGap

I registered as an iPhone developer earlier this year primarily to get my hands on the pre-release iOS upgrades but recently I was asked to help design the user interface for a BI-related smartphone application and it started my brain ticking.  When it comes down to it I’m a BI + SQL guy and it’s been a long time since I’ve actually gotten my hands dirty with real coding (remember the BBC Micro anyone?) but I have spent time on-and-off dabbling with PHP, HTML, CSS and Visual Basic as well as having managed a couple of development projects so whilst I’m rusty I’ve not been totally out of the loop.

Still, given that I’m hardly a candidate for Developer of the Year I figured that learning Objective-C and Cocoa Touch would be a little heavy going but thankfully it’s possible to build apps for the iPhone using only JavaScript, HTML5 and CSS3 which puts me in much more familiar territory.  Now that I knew I at least had a good starting point I could really start to think about the big issues in front of me:

1. Content – what is the app for?
2. Styling – how can I achieve the iPhone look-and-feel?
3. Compilation – how to turn a website into an executable binary?
4. Publication – what do I need to do to release my creation in the wild?

Since I knew I’d be putting some time into it I didn’t want to build an application that was just a glorified tech demo, I wanted to build an app that serves a valid purpose and that doesn’t exist already which was quite tricky.  Fortune was clearly smiling on me since I started working on this project in the same week that I went for a short break to Bratislava (it’s lovely by the way, you should go), being a vegetarian one of my pre-holiday tasks is to find out how to say “I am vegetarian” in the local language and for that I use the International Vegetarian Union’s Vegetarian Phrases in World Languages.  It’s a great resource and I usually just print the phrases to take them with me but then I had the lightbulb moment – wouldn’t it be brilliant if I had an app that did this?  Thankfully the manager of the IVU agreed with me and so I had my concept – a veggie phrasebook.

Next came the styling issue, if you’ve been an iPhone user for a while you’ll be used to the default look-and-feel that comes with most iOS applications and I had no idea how I was going to recreate that. Thankfully I’m not the first person to have hit this brick wall and developers with skills that are orders of magnitude better than mine have built a number of solutions, the best one out there in my opinion is JQTouch, a JQuery plugin that mimics the native iPhone styling as well as providing excellent navigation animations. A demo of JQTouch is available here: JQTouch Demo and is will only work if viewed on an iPhone or Android handset or in a WebKit browser (e.g. Safari or Chrome).

My general approach to learning new technologies tends to be very simple: just start - sure, you’ll get it wrong and it might be frustrating for a while but you’ll be learning all the way and come out the other end battle-scarred but victorious.  Thankfully JQTouch fits my methodology perfectly, there’s no installation to speak of, just download and unzip the package and dive in there with your favourite text editor (I’m fond of TextWrangler on Mac and Notepad++ on Windows).  JQTouch essentially uses one giant HTML file with divs for each ‘page’ of the application, the file includes the JQTouch libraries and a couple of CSS theme files and image sets – one in black (as per the demo) and one titled ‘Apple’ which looks very much like the settings page of the iPhone.

Having assembled the content, built the base HTML pages and customised the theme my next concern was how to turn it into a compiled app.  It’s quite nice being able to run the entire app in a browser window but I wouldn’t quite feel like I’d regained my developer strips without a bona-fide compiled app and there are a few toolkits out there that will help achieve that.  The most interesting toolkit to me right now is Appcelerator Titanium which looks powerful but might take some ‘discovery’ time and since I wanted to do the best possible job but in the shortest possible timescale I opted for PhoneGap, a cross-platform toolkit that allowed me to literally copy and paste my web root folder and make a compilable Xcode project almost immediately (seriously, read their Getting Started).

If you’re new to Apple development in general, Xcode is Apple’s development environment for both Mac and iOS applications and comes bundled with the OS as standard – it’s a little like Visual Studio in that it’s an IDE but the similarities end pretty quickly after the obvious.  Personally I find Xcode to be a little fiddly and not as intuitive as Visual Studio but that could be a little bias from having spent a lot more time in the Microsoft camp when it comes to development.  Nonetheless, Xcode is a great IDE and other than spending the best part of three hours trying to nail down my digital certificate signing chain (what happened to “it just works”?) it became surprisingly easy to debug the app on my iPhone and build the final version of the app.

My secret weapon in this whole process was (believe it or not) the data professional’s Swiss Army Knife – Excel.  I’ve often said that if you could teach even half of the world’s office workers how to use Excel properly you could change the world and I believe that the same is true even for the classically technical professions who eschew the GUI over scripting methods.  Excel’s blend of spacial referencing and a comprehensive function library make it an ideal code generator and I regularly use it to write large batches of SQL – this time I simply copied the IVU phrases into a spreadsheet, sorted and categorised them and used formulae to generate the best part of over 4,500 lines of HTML – even the menus came from Pivot Tables.

A little bit of tweaking and I was ready to submit the binary to Apple for approval, but that’s enough for now so I’ll talk a little more about the submission process in a future post.  Sure, there might be a few kinks round the edges but I think it’s a reasonably good first app and I’ve gained a stack of good experience building it which over the coming months I intend to share in a series of posts.

If you’d like to take a look please check out my Veggie Phrases app page or you can try it out (it’s free) here…

IVU Veggie Phrases iPhone App Released

Today Apple have approved my first iPhone application - Veggie Phrases, based on the IVU’s Vegetarian Phrases in World Languages with their kind permission.  I’ve already spotted a dozen spelling and other errors which I’ll correct in the next update but at least it’s out there now. 

You can read the full description here or download the app from iTunes by clicking on the button below…