by Simon Bisson
Simon Bisson checks out the latest tools that can help developers present data in new and exciting ways.
HardCopy Issue: 55 | Published: February 1, 2012
We live in a world of big data: terabyte upon exabyte of information that increasingly includes geographic data as well as both structured and non-structured content – all of it important and all of it needed to help users make the right business decisions at the right time. Some end users can handle free-form analytic tools, such as those built into Microsoft’s Office productivity suite or Tableau’s graphical tools, but not all need that level of complexity, which is where purpose-built tools come in handy.
There are also security concerns. Letting users work with all the data you have may seem attractive, but there are many good reasons for controlling user access to raw data. Industry regulations may limit access, and there may be concerns over network bandwidth or CPU usage. Licensing concerns can also encourage organisations to centralise business intelligence and analysis tools, building them into and around portals and collaboration services, or integrating them into line-of-business applications and services.
Business Intelligence (BI) development tools originate from three different roots. Some come out of business analytics tools like Crystal, some come from databases and the OLAP world. Others aim to bring these all together, building on familiar development tools and platforms.
SAP Crystal Solutions
The tools available from SAP are among the best known, building on the familiar features of Crystal Reports. At the heart of Crystal Solutions is Crystal Server 2011 which allows you to deliver and manage information analysis and reporting across a business, while still managing licences and monitoring overall usage. Now firmly part of SAP, Crystal Reports continues to build on the work done by Crystal Decisions and Business Objects.
The tools bundled with Crystal Server allow you to set up a common semantic layer across your data sources. Instead of giving users direct access to the sources themselves, and the complex task of designing and optimising their own queries, it provides a one-stop shop for data for anyone who needs access, tying into search tools that help report designers find the right information sources. Integrated report design tools also simplify the creation of reports that can be shared with end users, and built into dashboards or accessed over the Web.
There’s support for embedded Flash objects that make reports more interactive. Reports can also pull in data from Web services, allowing you to use live data in your dashboards. You can use the Crystal Dashboard Design tool to create and share reports on corporate portals, giving users access to sophisticated analysis and including what-if scenarios. The Dashboard Design package also lets you create custom Flash elements for your applications, adding your own widgets to a dashboard.
Developers can build Crystal Reports into their applications, and there are tools for both Java and .NET. An Eclipse plug-in adds reporting to Java applications, with an embedded runtime engine. Similarly, Visual Studio tooling means that reports can be delivered either as WinForms desktop applications or through ASP.NET to the Web.
SAP also offers a runtime licence which lets you include reporting features in applications that are shared with partners or sold commercially. Embedded reports have all the features of standalone reports, including Flash components, while also letting your code interact with report contents. If you prefer, you can use the full Crystal Reports package to build dynamic user interfaces in Adobe Flex, using Crystal components in Adobe’s own Flash Builder design and development tools.
SQL Server 2008 R2
The current release of SQL Server comes with a wide selection of business intelligence features, including a set of Visual Studio extensions for BI developers. Microsoft’s Business Intelligence Development Studio is a powerful plug-in for the familiar Visual Studio IDE, with all the features you’d expect for building and managing business analytics. One thing to note is that, while BIDS does ship with the current version of SQL Server, it’s not compatible with Visual Studio 2010 (you’ll have to wait for SQL Server 2012 for that) so you’ll need to have Visual Studio 2008 to hand for any BIDS projects.
The heart of SQL Server 2008 R2’s business intelligence features is Report Server, and BIDS is where you design and develop dynamic reports. A project wizard will help you create new reports, linking them to data sources. A design view gives you tools for laying out reports, including a toolbox of visual components that includes charts and tables. You can see the source data and build the appropriate queries before previewing the report and debugging its output. While you can build reports from scratch, the wizard handles data source connection, helps design queries and sets you up with a basic report layout which you’re free to edit and refine. One option lets you create a report model that can be used by end users in the ad hoc Report Builder. You can add and edit entities, and manage the data sources available to users.
BIDS also handles the development of rules for SQL Server’s extraction, transformation and loading features, through its support for Integration Services. You can use the SSIS designer to create a series of tasks that handle the data flow through the ETL tools, managing transformations and handling data aggregation. You can also add event handlers to report on automated ETL operations, for example emailing an administrator when a job completes, or if a job fails. SSIS scripts are written using familiar tools and in familiar languages, allowing you to programmatically manage ETL tasks in C# or Visual Basic. More complex BI tasks are handled using SQL Server Analysis Services, and BIDS provides a selection of development tools to help design and build OLAP solutions to speed up queries and reports. You can use BIDS to combine data sources and views to create either an OLAP cube or a data mining model. BIDS doesn’t take a simplistic approach to OLAP design, and it’s capable of building large, multi-dimensional cubes with support for external functions written in familiar .NET languages. It’s a powerful tool that requires a lot of knowledge, but makes it easier for experienced BI developers to work with SQL Server 2008 R2’s analysis tools.
Microsoft’s enterprise content management tool, SharePoint, is also its enterprise portal product. While it doesn’t contain its own business intelligence tools, it serves as an effective wrapper and delivery mechanism for reports that have been created in other tools. You can use it as a host for SQL Server reports, as well as Excel and Visio-powered BI applications, while its Business Connection tools can link it to multiple information sources, including line of business applications.
Where SharePoint comes into its own is in conjunction with PerformancePoint 2010, Microsoft’s performance management tool. Building on top of Microsoft’s BI framework, PerformancePoint is designed to distil and display key business indicators on a dashboard. You can use it to deliver scorecards and benchmarks, as well as KPIs.
SQL Server 2012
The next release of SQL Server isn’t out just yet but a release candidate is available. SQL Server 2012 introduces a new edition, specifically focused on Business Intelligence. Along with a new version of BIDS, SQL Server 2012 introduces the Business Intelligence Semantic Model. BISM mixes multi-dimensional data with the familiar relational database in a new tabular model and is able to support everything from large scale corporate BI solutions to ad hoc queries by end users.
Other new features include tools to simplify mash-up design, as well as deeper integration with SharePoint. SQL Server 2012 improves information management, helping you lock down your data sources and ensure that the right people have access the right data. Increased regulation and new security models makes it essential to manage security at an information level, preventing sensitive information leaks.
The future of data is the cloud, and SQL Server 2012 takes advantage of the work done to deliver SQL Azure. This means the BI development tools that come with SQL Server 2012, like BIDS and Report Builder, will also work with the reporting tools built into SQL Azure. Taking BI to the cloud gives you a whole new way of delivering analytics to your users, supporting massive scale cloud data as well as your own internal business systems.
Alongside its display features there’s an element of analytics, letting users drill down into information sources to understand just why an indicator is in its current state. PerformancePoint’s decomposition tree lets you quickly drill down into data sets, using visual tools to display the contents of a multi-dimensional data set.
PerformancePoint scorecards and dashboards are powerful tools, and can be filtered by time as well as by content, letting end users focus on relevant information quickly. Similarly the various Web parts that make up a PerformancePoint report can be linked together to give more complex reporting and analytic capabilities with minimal programming effort. A Dashboard Designer is integrated with SharePoint’s Business Intelligence Center. Here you can create new dashboards, linking to data sources and creating and designing custom scorecards. The designer lets you configure the target values for your KPIs, as well as tying them to indicator icons. You can also select the filters available to users looking to drill down into data, for example adding tools that focus on a specific geography or date ranges.
Microsoft’s consumer search service may seem an unusual tool to reference in a piece on business intelligence, but its mapping service makes an ideal platform for building and delivering geographic reporting tools. Replacing Virtual Earth, MultiMap and MapPoint.NET, the latest versions of the Bing Maps SDK include tools for adding your own layers to a map, and connecting to SQL Server, but you will most likely need a commercial licence for Bing Maps if you’re using them as part of a BI solution.
The Bing Maps APIs include both AJAX and Silverlight controls, as well as a set of tools for embedding Bing Maps content into mobile applications for both iOS and Windows Phone. There’s even a WPF version of the map control for use in desktop .NET applications, with support for touch. You can use the geocoding features of the APIs to take textual geographic data and layer it over a map, and the APIs contain tools for drawing polygons and polylines onto the map, as well as layering tiles. A set of translucent polygon fills can be used to display heatmaps, for example, a useful tool for showing relative values across a wide area.
There’s a lot you can do with the Bing Maps APIs (and the similar Google APIs), and support for familiar programming models make them easy to incorporate into your BI solution, and easy to mash your data with public mapping data. The SQL Server data connector is an open source project hosted on Microsoft’s CodePlex site with a range of different display types, including vector and raster data. Like the Bing APIs, there are both AJAX and Silverlight versions of the connector.
Desktop users may want to take advantage of the geographic data tools built into MapPoint 2011, which is also available with a free SQL Server add-in that lets you add map layers to reports using the desktop MapPoint client. Maps are stored in SQL Server, along with shape and point data, and there’s support for spatial queries against data warehouses.
A regional alternative to such services is viaEuropa from Europa Technologies which incorporates Ordnance Survey data of the UK into its cloud-hosted mapping service, and includes a free service for non-commercial use. It is standards-based so you can use it with a wide selection of data sources, making it easy to build into your business analytics platform, and it works with Bing Maps. Products include a range of vector data sets with support for features like contours and over 850,000 named places around the world.
Currently viaEuropa is working to improve UK geocoding, using the Ordnance Survey AddressBase to link postal addresses with rooftop geocoding. Rooftop geocoding allows you to link data to specific buildings, making it easier to deliver detailed reports to field staff and sales teams.
An alternative to commercial BI developer tools comes in the shape of Open Intel. Designed to help build open data portals, this is a visual business intelligence tool which you can modify and customise in Visual Studio. It’s not just for your own internal servers either as it takes advantage of the storage and Web features of Microsoft’s Azure cloud platform, and uses the Bing Maps tools for geospatial reports.
While Open Intel isn’t designed for complex OLAP or data mining applications, it is able to work with most common databases and data sources. You can use the built-in mash- up builder to create and edit map-based reports with support for spatial queries and map annotations. As it’s designed to support interactive portals it supports the concept of apps, although these are essentially commonly used views and reports. There’s also support for the OData protocol, giving you access to public and commercial datasets alongside your own data and so increasing the options for designing and delivering mash-ups.
The development team describes Open Intel as an ‘accelerator’, and it’s certainly quick to get started – and as there’s an option to use Azure there’s no need to invest in infrastructure for public-facing analysis portals. The instructions include the scripts needed to set up the appropriate data sources. Open Intel is designed to be used with the MapDotNet tools for building and design geospatial data, with support for most common formats. You also use MapDotNet to set up the appropriate spatial indexed for your data, speeding up queries and display through Bing Maps.
Delivering effective business intelligence requires a lot of development work. Whether it’s ensuring that your information security policy is ready to support end user data exploration and analysis tools, or designing and building a report server, there’s a lot to be done to get your data, servers and code ready for BI. Business Intelligence will become increasingly important as it helps us manage and use the increasing amount of data that is becoming available – dragging it out of the warehouse and into the growing internal and external data marketplaces.