Excel-based BI using BIRT Spreadsheet

Microsoft Excel is not traditionally considered a business intelligence tool. However it is one of the most popular reporting and analysis tool used by the business community. Thanks to the flexibility and features offered by Excel, business users find it difficult to adopt any other tool when it comes to business analysis. This poses a huge problem (read ‘headache’) for the IT team since there is no straight-forward way to integrate MS Excel with traditional BI systems.

All conventional BI solutions offer a Web-based platform for reporting. A majority of them also have Excel and PDF adaptors to convert the Web-based reporting formats into Excel or PDF documents. These adaptors however transform the final output (the view) of the report into an Excel document. The end result is not necessarily what the end user desires since the transformation does not make use of the native Excel formatting and analysis features. As a result, the user will get his/her report in Excel without the ability to do anything further with it. This is not exactly what the end user wants. To circumvent this problem business users ‘cut-and-paste’ the relevant data from Web-reports or other data sources into their preferred Excel templates (created and managed individually by the user) making use of the native Excel features that assist them in their decision making requirements. The end result is a set of isolated Excel-based data marts, also referred as spreadmarts, propagated throughout the organization. These spreadmarts are uncontrolled, unverified, and prone to error due to manual intervention.

There is a strong need for a robust solution that addresses the business user need for Excel interactivity and IT requirement for control and manageability. One of the very few such tools that offer this functionality is BIRT Spreadsheet from Actuate. A report author uses the BIRT Spreadsheet Designer (that looks and feels like Excel) to build a report design. The report design acts as a blueprint for how the resultant spreadsheet report is to be generated by the server. The design defines:

• Where the data should be coming from – which data sources, datasets, queries, and parameters to use to filter the incoming data
• How the incoming data is to be presented in the report – how it is to be laid out and which charts, formulas, formatting, macros and other logic are to be used.

BIRT Spreadsheet Designer

Once the report design is complete, the report author can publish the produced report executable to the Actuate Reporting Server, called as iServer. The iServer manages all deployment aspects such as security, scheduling, versioning, presentation, etc. Once the report executable is published to the iServer, users with the right privileges can schedule the report; execute it on-demand to produce a fresh spreadsheet report document containing the latest data; or, elect to view a previously generated spreadsheet report document that is cached on the iServer. In all cases the user will receive an Excel file that is downloaded to the user’s desktop and is automatically opened within Microsoft Excel.

Sample Excel produced by BIRT Spreadsheet

Sample Excel produced by BIRT Spreadsheet

Sample Excel produced by BIRT Spreadsheet

Sample Excel produced by BIRT Spreadsheet