Business Intelligence

Interesting Open Source BI Blog

I’ve been interested in the idea of Open Source BI for quite a while and for certain organisations or requirement sets I believe that Open Source BI has a very bright future.  Open Source offerings may never oust the ‘big boys’ like Informatica and Oracle Data Integrator but they will become valid competitors in time, though vendors offering paid  ‘Enterprise’ editions need to be careful that their high-end offerings aren’t priced too high since people spending large sums are bound to tend towards proven solutions from stable companies. 

Anyway, to get to the point – today I stumbled across an intersting blog by Slawomir Chodnicki covering Open Source BI and ETL products from Pentaho, JasperSoft and SQL Power Group.  Much of the content is of the ‘how to’ variety but if you’re interested in the topic as a whole it’s worth stopping by Adventures With Open Source BI. 

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.

Quick Fix: DeskI Query Panel Appears Invisible / Off Screen

For the last couple of weeks I’ve had an extremely unusual problem in Business Objects Desktop Intelligence (DeskI), whenever I went to edit a Data Provider nothing would happen.  Initially it looked like DeskI had hung but hitting escape returned me back into the report, I figured that because the Query Panel is modal it must be launching but for some reason I couldn’t see it. 

After a little bit of searching I managed to find the solution on the excellent BOB Forum (original post here) and I just had to reproduce it since I found it quite tough to find but hats off to Marek Chladny who came up with the solution – if you’re ever in the UK let me know and I’ll buy you a beer! 

1. Go to Edit Data Provider. 
2. Press Alt + Space
3. Press M
4. Use the arrows to move the Query Panel back onto the screen. 

Marek mentions that this only works if the Query Panel isn’t maximised, also I assume that since this uses standard Windows keyboard shortcuts it might work for any other application where you have a similar issue.  As for how it came about, I have no idea – it may be something to do with occasionally using a single-screen PC to remote-desktop into my dual-screen desktop.

Set Instance Limits in Business Objects XI R2

In a previous post I mentioned previously How to Manage Instances in Business Objects XI R2, well – that’s only half of the story – rather than having to go and delete old instances yourself it’s far easier to have Business Objects tidy up after itself.  This is one of the few scheduler-related features that can be found in the CMC under “Settings” and “Limits” allows you to specify…

• The maximum number of instances to be maintained (anything above will be deleted). 
• How many instances should be kept for each user/group (I choose Everyone). 
• How many days these instance should be kept for each user/group (again, I choose Everyone). 

Please note that the excess instances do not get deleted immediately after the change, in my case it seemed to happen by the next day so do be patient!  This will really help to cut down the size of your FRS and may disaster recovery much easier. 

Microsoft’s UK Tech Days: SQL 2008 R2

I enjoy going to SQL Server community events, I usually find they provide a refreshing look at what other people are doing and provide inspiration and ideas of what I could be doing myself.  Vendor-run events are different so I attended Microsoft’s SQL Server 2008 R2 Tech Days event with mixed expectations, not sure if it was going to overly marketing-heavy or whether it really would be worth taking a day out of the office.

Thankfully I was in luck, Microsoft did a great job of treading the line between promotion and information and whilst the intro and the first 5-10 mins of each take were quite marketing oriented the majority of the content was realistic and provided honest demonstrations of the product.  Also throughout the talks presenters were offering to answer questions via SMS or via the Twitter hash-tag #uktechdays, this was a great touch and even though there wasn’t time to answer all of the questions it really added to the interactivity of the event.

First up was Power Pivot, as a product it looks to be immensely powerful and provides lightning fast analytical capabilities though I imagine it needs a decent amount of RAM and an up to date processor to achieve it – the most amazing part is that it’s a free add-in for Excel 2010!  Essentially PowerPivot allows you to extract up to a million records from a database and perform in-memory analysis with that set of data, including combining it with other data sets, combining it with data in your spreadsheet, performing calculations, making summaries, etc.  It’s well worth taking a look at the demos, PowerPivot is a massive leap forward in Excel’s capabilities but to me it seems like a step backwards in terms of the centralised BI ‘single version of the truth’ concept – allowing users to rip a million rows out of the Data Warehouse, mix them up with other data sources and then send them around via email or even publish them via SharePoint.  As it goes the Share Point integration was also pretty remarkable, allowing other users to use published reports not only for viewing but also as a data source on which to build new reports – pretty ground breaking stuff but I’d hate to be the guy debugging a report based on a report based on a report based on… (you get where I’m going).  Overall I’d give PowerPivot a 5* rating for innovation but it seems that Microsoft is using a common tactic from Formula One – trying to get ahead of the competition by taking a contrary strategy, but will it turn out like Jenson Button in Shanghai (he won) or like Lewis Hamilton in Australia (he didn’t)?

After a relatively dry talk on virtualisation and Hyper-V Live Migration (impressive stuff but I’ve seen it before) the next talk was about Report Builder 3 and having never been a user of Reporting Services I thought I was just going to sit through it and twiddle my thumbs – I was wrong.  Having been knocking about in the BI world for about 8 years or so I can really say that this release of Report Builder really cements Microsoft’s position in Business Intelligence.  It’s still not very slick from a usability standpoint but the visualisations they’ve added are stunning and having been a long-time user of Business Objects the talk actually did make me think “how hard would it be to switch?” – since I have a mature installation the answer is very hard but it still made me think.  The most impressive visual elements were the Spark Lines, Data Bars and Indicators but the maps were also pretty good especially given that you can use ESRI shape files.

The next talk was “Maximising your existing hardware CPU, memory and disk” by Ramesh Meyyappan, I’ve seen Ramesh before at SQLBits and he’s always very good, very detailed and straight to the point.  It was a great talk, taking place mainly in Management Studio rather than PowerPoint and if you get the chance to see one of Ramesh’s talkes in the future you should definitely go (but have a cup of coffee first).  Following Ramesh’s rollercoaster of a talk was much more relaxing run-through of Microsoft’s ‘database in the cloud’ offering SQL Azure, a product I find extremely interesting but don’t have an immediate use for though I expect in time as the feature-set converges with SQL Server I will be changing my mind.  Next up was StreamInsight, R2′s Complex Event Processing (CEP) solution for analysing large data streams (10k rows/sec+) on the fly without touching the relational engine – it looks interesting but I don’t have those sorts of requirements at the moment so I don’t have much of a reaction.  The day was rounded off by a presentation by Andrew Fryer about Master Data Services, a difficult topic to present in a jazzy way but it looks very interesting and if it will integrate with the spaghetti-junction of systems floating around in most organisations it could do a lot to help us keep our data warehouses in line with corporate naming conventions, it sounds like a lot of fuss over a little issue but if you’ve ever actually tried to solve the problem yourself in a company with more than a couple of source systems you’ll understand how hard it can be.

All in all a good day, I’ll give a shout out to the staff at Jumbucks in Shepherd’s Bush where I had breakfast and bought a bagful of Australian confectionary and to the Vegetarian Chinese buffet over the road for providing me with much needed sustenance.

View Failed Scheduled Jobs in Business Objects XI R2

When I moved from old-school Business Objects (6.x) to XI Release 2 I was happy that the scheduler would now be available for all users directly in WebI, unfortunately along with the improvement came the down-side – no Broadcast Agent Console (BCA Console) to monitor jobs. The lack of an admin tool for the scheduler becomes a pain every now & again since without very careful control you’ll quickly lose track of which documents have been scheduled by whom and whether they’re still running, if they’re failing, etc. Additionally if you experience some sort of system failure (e.g. FRS goes down) you may need to see what failed in order to make sure it is re-run manually.

Well, despite the fact that the CMC doesn’t include a great deal of schedule management functionality, there is a tool bundled with the standard BO installation which can at least help with the issue. The tool is called the Instance Manager and it’s part of the SDK ‘use case’ examples provided and can be accessed via the Administration Launchpad URL as so…

http://bo_server_name:8080/businessobjects/enterprise115/adminlaunch/launchpad.html

… effectively this redirects you to here…

http://bo_server_name:8080/businessobjects/enterprise115/adminlaunch/instancesByStatus/main.jsp

After selecting the Instance Manager on the left-hand menu (see image above), log in with the relevant credentials…

Select the status you want to look for and a username for which you want to search (username optional for everything but ‘All Statuses’) and hit Go!

You then see a list of instances (note, this is Instances not Jobs)…

Please be careful since this screen will allow you to delete instances (up to 100 at a time) if required and whilst there is a message box asking “are you sure you want to delete…” I don’t know of any way to roll back the changes.