Inside Data 57

by Graham Keitch

Now you can access your data from mobile devices. Graham Keitch checks out BI on the move.

HardCopy Issue: 57 | Published: September 1, 2012

For some time now, Business Intelligence (BI) has been labelled the ‘must have’ technology for companies seeking a competitive advantage. BI takes basic reporting to the next level, adding analytics and enhanced data visualisation to aid interpretation and decision making. It can be run against a local data source in a standalone environment but it is more common in larger organisations to adopt a client server model to facilitate multi-user, controlled and scheduled access to back end BI resources.

The reporting engine can be embedded in a thick client .NET or Java application. Alternatively, a thin client architecture can be adopted. This typically uses a browser or viewer to access server side results, and can be extended to mobile clients. Most mobile BI solutions merely provide Web access and limited functionality for on board data interrogation and presentation, but new technologies are permitting more to be done at the front end. The BlackBerry’s larger screen and keyboard removed some of the obstacles and paved the way for purpose-built mobile BI apps for platforms such as iOS, Android and Windows Phone 7. Let’s take a quick look at mobile BI as provided by three leading publishers in this field, namely Oracle, Microsoft and SAP.

Oracle is an industry leader in this area and their BI offering is a subset of Oracle Business Analytics which includes Enterprise Performance Management (EPM) tools. For extreme analytics performance, this includes an optimised hardware and software solution known as Oracle Exalytics that utilises in-memory database technology.

Embarcadero DB Optimizer screenshot

Embarcadero’s DB Optimizer graphically portrays information about bottlenecks and other parameters that affect database performance.

SQL Server 2012 uses columnstore indexes for column storage and users are reporting tenfold or more performance gains for some queries when used with improved compression and in-memory storage. BI and analytics are two areas that Microsoft has focused on to deliver dramatic performance enhancements, and their database line-up has been reconfigured to reflect this. There is now a dedicated BI edition, although the most advanced features such as proactive caching and xVelocity optimised columnstore indexes are reserved for the Enterprise Edition.

Oracle provides its high-end Enterprise Edition with a number of add-on products to improve the performance of data warehouse queries, including one for OLAP. Oracle Database 11g R2 introduced some advanced compression technologies to store related columns contiguously on data blocks and to remove duplicate values. This reduces the amount of disk space needed as well as I/O and network traffic, allowing smaller and more affordable high performance solid state disks to be deployed. The Partitioning Option for Oracle Database Enterprise allows a table or index to be subdivided into smaller portions. You might choose to ‘remove’ old orders from a table or create an optimised subset for a specific query or report. These leaner partitions can be managed individually or collectively to increase performance and availability.


Third party products

There are also a number of products from other companies that can maximise database performance. These include database drivers and tools for storage management and SQL code tuning.

Progress DataDirect has developed some high performance ODBC, JDBC and ADO.NET drivers for both Oracle Database 11g and SQL Server 2008. The company is also working on a JDBC driver for Microsoft SQL Server Parallel Data Warehouse which will improve runtime performance for Java and J2EE applications connecting to it.

DataCore’s SANsymphony-V is a virtualisation solution with advanced features that can also help deliver improved performance. It automatically promotes frequently accessed disk blocks to faster devices in the storage subsystem, and creates SAN-wide caches to speed up performance.

The SQL code itself is often the cause of poor performance. Tools such as Embarcadero’s DB Optimizer XE3 and Idera’s SQL doctor v3.0 can help identify and correct problem code and other factors that can compromise performance such as hardware configuration.

With the availability of more powerful multi-core CPUs, fewer processors need to be thrown at the task and this can potentially reduce the licensing overhead. Microsoft has adopted per core licensing for SQL Server 2012. Oracle also counts cores in the case of the Enterprise Edition. However Standard and Standard Edition One processor licensing is based on the number of occupied CPU sockets only. Performance is paramount for some applications, but usually this has to be balanced with overall cost which is where hardware, virtualisation and licensing considerations enter the equation.