Developing Analytic Applications With IBM DB2 Alphablox

A technical white paper January 2005 Developing analytic applications with IBM DB2 Alphablox Developing analytic applications with IBM DB2 Alphablox Page 2 Contents Executive summary 2 Executive summary 3 Introduction 3 Custom analytic applications 4 Key characteristics of DB2 Alphablox-enabled applications 6 Overview of IBM DB2 Alphablox 8 Delivering applications with a fastforward framework 9 DB2 Alphablox components 9 Data access Blox 10 User interface Blox 12 Form Blox 13 Business logic Blox 14 Analytical infrastructure Blox 15 DB2 Alphablox programming model 17 Development tools 19 IBM DB2 Alphabox tools for analytic applications This white paper provides an introduction to developing custom analytic applications with the IBM® DB2® Alphablox® platform. It is intended for IT application developers who are interested in an overview of the DB2 Alphablox analytic application development process. Those interested in the technical details and inner workings of the DB2 Alphablox architecture and its associated components should refer to the white paper entitled “Inside IBM DB2 Alphablox.” DB2 Alphablox enables a wide range of developers—from professional application programmers to business analysts—to rapidly deliver custom analytic applications. Non-programmer analysts can use a fast-forward feature to create and publish analytics, while professional application developers can leverage modular, reusable components to quickly assemble a broad range of personalized, customized analytic applications. Topics discussed in this white paper include: • The need for flexible custom analytic applications in today’s business environments • Characteristics of custom analytic applications • Reporting and analysis applications delivered using a fast-forward framework • Custom analytic applications built with DB2 Alphablox components, programming models and tools Readers with knowledge of standard Web technologies, application design and development, Java™ 2, Enterprise Edition (J2EE™) application servers and JavaServer Pages™ (JSP™) technology will gain maximum value from this paper. Developing analytic applications with IBM DB2 Alphablox Page 3 Introduction The amount of data generated today by enterprise resource planning (ERP), customer relationship management (CRM), supply chain management (SCM) and other operational systems is staggering. Businesses can gain powerful insights by analyzing the wealth of data residing in their corporate databases and can become more profitable by acting on those insights. Unlike query and reporting tools that merely report on raw data from data warehouses and other sources, analytic applications present the data in a rich business context. In addition, analytic applications can provide users the additional functionality to take action. The use of query and reporting tools is limited to only a few specialized analysts within a company. The large number of decision makers such as line-of-business (LOB) managers, product managers and sales representatives do not use these tools because they are not only complex to learn, they also are hard to deploy. Moreover, businesses need to be flexible to succeed in rapidly changing conditions. By design, tools and prepackaged analytic applications are inflexible, and therefore cannot meet the speed at which a business may be changing DB2 Alphablox enables rapid development of custom analytic applications ranging from financial reporting and analysis applications to budgeting and forecasting applications. Thus, custom analytic applications that provide the right information at the right time to all decision makers are more critical than ever in the current competitive business environment. These applications are intuitive, easy-touse, personalized to individual decision makers and flexible to meet changing business conditions. In addition, decision makers can have the capability to act on the insights they gain through the use of write-back to data sources and collaboration with other decision makers. Custom analytic applications Custom analytic applications can enable organizations to measure and manage their businesses. DB2 Alphablox enables rapid development of custom analytic applications ranging from financial reporting and analysis applications to budgeting and forecasting applications. DB2 Alphablox also makes it easy to deploy these applications to all the decision makers inside and outside the organization. Some examples of custom analytic applications (see Figure 1) include: • Self-service reporting and analysis applications • Operational analysis applications — Sales (revenue) analysis Developing analytic applications with IBM DB2 Alphablox Page 4 — Expense variance analysis (profit analysis) — HR headcount analysis • Financial reporting and analysis applications — Multidimensional balance sheets — Multidimensional cash-flow statements — Multidimensional income statements • Planning applications — Budgeting — Forecasting • Business performance and key performance indicator (KPI) dashboards • LOB/industry-specific analytic applications — Pricing analysis and optimization — Credit and loan risk analysis — Currency risk analysis — Customer profitability analysis — Product quality analysis — Telecom churn analysis Figure 1: Example of a custom analytical application Key characteristics of DB2 Alphablox-enabled applications Typical characteristics of a custom analytic application include: Web-based format. Applications deployed over the Web provide two major benefits. First, because the applications can appear as a series of interactive Web pages, anyone comfortable using a Web browser can navigate through the application and understand the available functionality with minimal training. Second, there is no need to install special software on each user’s computer; in addition, it can be made accessible from both inside and outside the company. Developing analytic applications with IBM DB2 Alphablox Page 5 Interactive and guided analysis. This feature enables users to interact with real-time data via grids, charts and other components such as drop-down lists, check boxes, radio buttons and more. Data analysis actions such as drilling, pivoting, sorting and selecting can be performed in several different ways: directly on the grid and chart, through toolbar buttons, through right-click menu options or via HTML-based controls and components created by the application developer. Real-time access and analysis. Custom analytic applications can drive data analysis from multiple data sources, including both relational (RDB) and multidimensional (MDB) databases. Users should be able to leverage capabilities such as ranking, derived calculations, ordering, filtering, percentiles, variances, standard deviations, correlations, trending, statistical functions and other sophisticated calculations while performing data analysis. Customization and personalization. Since each user has different data and business needs, DB2 Alphablox analytic-enabled solutions can be personalized to meet the needs of each individual user. For example, the first screen seen by users after logging in can be customized according to their role in the organization. Users in a sales group may see the top five best-selling products or the most profitable regions for the month to date. Users in finance may be more interested in monthly summary figures for sales, cost of goods, marketing, payroll and profit. In addition, users can further personalize the application to their specific needs by setting custom preferences. Sharing and collaboration. Users need the ability to leverage existing messaging and workflow systems to save and collaborate their analyses and associated insights. The ability for one user to save analysis as a bookmark and allow it to be shared with other users in the department would save time for all users in the department. Similarly, if users can attach their insights as an application or cell-level comment, all other users can read those comments and also add their own comments. Real-time planning through write-back. Custom analytic applications may extend from historical analysis to forward-looking forecasting and proactive resource allocation. Write-back capabilities can be leveraged to build real-time planning applications such as budgeting, sales forecasting, “what-if” modeling and collaborative demand planning. Developing analytic applications with IBM DB2 Alphablox Page 6 DB2 Alphablox can enable easy development and deployment of applications with all these characteristics and more. Overview of IBM DB2 Alphablox The IBM DB2 Alphablox platform can help organizations rapidly assemble and deploy custom analytic applications. It has an open, extensible architecture based on J2EE standards, an industry standard for developing Web-based enterprise applications. It simplifies enterprise application development by automatically handling many details of application behavior without the need for complex programming. DB2 Alphablox provides various Blox® components, an application framework, a powerful programming model and a variety of development tools for assembling analytic applications (see Figure 2). For its run-time environment, DB2 Alphablox leverages standard J2EE application servers—DB2 Alphablox can be installed on leading commercial J2EE application servers such as IBM WebSphere® or BEA WebLogic. Application builders who are developing applications with DB2 Alphablox can take advantage of many features such as enhanced performance, security and personalization that are offered by the underlying J2EE application servers. ��� ��� ��������� �������� ���� Figure 2: DB2 Alphablox architecture �������� ������������� ����������� ��������������� ������������ Developing analytic applications with IBM DB2 Alphablox Page 7 Integration with the application server environment enables application builders to leverage DB2 Alphablox for its core capabilities of building, deploying and executing analytic applications while relying on the application server to provide robust management and deployment services. The process for developing analytic applications is similar to any other dynamic Web application development. The first step is to clearly identify the users, understand their application and data requirements and scope the application accordingly. Then all the data sources need to be ready (see Figure 3). The next step involves identifying whether the provided fast-forward application framework meets the application needs. DB2 Alphablox offers a self-service fast-forward feature that makes it easy to develop reporting and analysis applications. This feature ships with a set of report templates that can enable application developers to easily build additional templates and plug them into the fast-forward framework. For building other custom analytic applications without the advantage of the fast-forward framework, simply leverage the full depth of DB2 Alphablox functionality including its rich set of components, programming model, developer tools and examples. ���������������������� ���������������������� ����������������������������� ������������������������� ������������������������������� ������������������������ ������������������� ����������������������� ����������������� ��� ����������������������������� �������������������������� �� ����������������������� �������������������������� ��������������������������������� ��������������������������������� ��������������������������� ����������� �������������������������������� ���������������������������� Figure 3: A model of the process for developing analytic applications ��������������� ���������������������� Developing analytic applications with IBM DB2 Alphablox Page 8 Delivering applications with a fast-forward framework A common type of analytic application is a self-service reporting and analysis application. This application typically needs to deliver different views of data to various users in the enterprise. Each user should see the list of all available views personalized to that particular user. Moreover, users should be able to interact with these views and share or save their custom analysis views for later use. The fast-forward feature makes it easy to develop and deploy self-service reporting and analysis applications (see Figure 4). The template-based fast-forward framework automatically handles the navigation, provides commonly used toolbar functionality and enforces security. Business analysts can go to administration Web pages for the fastforward feature to easily create, organize, secure and publish analytics to other users. Figure 4: Example application based on the fastforward framework Creating an analytic application based on the fast-forward framework is as simple as configuring and administering the application: no coding is required. As the application requirements change, application developers can simply create new analysis templates using DB2 Alphablox tag libraries to further extend the functionality of applications built on the fast-forward framework. A template for the fast-forward framework typically consists of two JSP Web pages and one XML configuration page. It is much quicker to develop this template than a complete application. Installing a new template for the fastforward framework is as easy as dropping a zipped file into the templates folder of the fast-forward framework (see Figure 5). Developing analytic applications with IBM DB2 Alphablox Page 9 Figure 5: Configuring a new report in the admin pages of the fast-forward framework IBM DB2 Alphablox components IBM DB2 Alphablox provides an extensive library of modular, reusable components called Blox to help meet all analytic application design requirements for maximum usability. These components include the data access Blox, user interface Blox, form elements Blox, business logic Blox and analytic infrastructure Blox. Data access Blox Data access is managed through the Data Blox, the Stored Procedures Blox and the MDB Query Blox. The Data Blox manages the connection between the user interface Blox and the appropriate data source. It also is responsible for submitting queries and retrieving result sets from a database. The syntax used for queries will vary depending on the data source that is being accessed. DB2 Alphablox supports the following types of query strings: • Essbase report scripts for DB2 OLAP and Hyperion Essbase data sources • Multidimensional Expressions (MDX) for Microsoft® SQL Server analysis services and DB2 Alphablox cubes (a multidimensional representation of relational data created with the DB2 Alphablox cube server) • SQL statements for relational data sources The Stored Procedures Blox enables programming support to use stored procedures in relational data sources. Developing analytic applications with IBM DB2 Alphablox Page 10 The MDB Query Blox is useful for specifying data queries based on axes, dimensions and members without having to use data-specific query language. This Blox also makes it easy to replace query fragments using the program. ���� ���������� ���� ���� ���� ������������� ���� ����� ����� ���������� ������� ��� ���� �������������� ��������������������� ��� ���� Figure 6: Data access architecture �������������������������������������� Because DB2 Alphablox directly accesses data from the databases, DB2 Alphablox-enabled applications will abide by any security features or constraints built into the databases. DB2 Alphablox exposes all the analytic functionality supplied by the multidimensional database engines (such as ranking, derived calculations, ordering, sophisticated filtering, percentiles, deciles, variances, standard deviations, correlations, trending, statistical functions and other sophisticated calculations). In addition, DB2 Alphablox allows end users and application developers to create custom calculated members (see Figure 6). The Data Blox also offers application programming interfaces (APIs) to return data in an XML format. This feature opens the door to extensibility, enabling DB2 Alphablox applications to be integrated with other applications. It also enables delivery of data to XML-enabled clients including cell phones, pagers and PDAs. Application developers also can expose the data in a Web service and/or build custom user interfaces. User interface Blox User interface (UI) is a crucial factor in application usability. The user interface Blox provided by DB2 Alphablox is highly functional, interactive and completely customizable. The user interface Blox in a DB2 Alphablox-enabled application includes several components: Developing analytic applications with IBM DB2 Alphablox Page 11 • Grid Blox provides a table of data and all the UI functions to manipulate the data in a multidimensional way. Users can drill up, drill down, sort, pivot, swap axes or choose to view the top N or bottom N members based on the values of a given data column. • Chart Blox, which is used for advanced visualization of data, supports a large number of chart types including bar, line, pie, scatter and bubble charts as well as bipolar and dual-axis charts. • Data Layout Blox makes it easy for users to move and reorder dimensions across axes. • Page Blox provides drop-down lists to manage the setting of slicer dimensions. • Toolbar Blox offers easy access to common data analysis functionality through the click of a button. • Present Blox combines the Grid Blox, the Chart Blox, the Data Layout Blox, the Toolbar Blox and the Page Blox into a single, well-orchestrated, interconnected user interface along with user toolbars and menus (see Figure 7). The Present Blox also implements additional logic to interconnect the various underlying Blox. For example, in a Present Blox, drilling down in the grid automatically updates both grid and chart with the new data. These user interface elements employ cutting-edge Dynamic HTML (DHTML) technology to provide a rich user experience including menu bars, right-click menus and custom layouts in a thin client (no need for Java, Microsoft ActiveX® or other browser plug-ins). • Report Blox is the core collection of Blox for building relational reports with other componentized Blox that handle specific report formatting and data sorting, grouping, filtering and calculating functionality for relational data sources (see Figure 8). Figure 7: Example of the Present Blox Developing analytic applications with IBM DB2 Alphablox Page 12 Figure 8: An example of relational reporting Blox Form Blox DB2 Alphablox provides several form Blox that can be very useful in developing custom analytic applications. Developers who are familiar with using native HTML form elements know that when the HTML page is refreshed, the user’s current form element selections get reset unless they write additional code to maintain state across page refreshes. All the form Blox not only maintain the form elements’ current state, freeing the developer from writing the extra code, but they also hook up with other components, such as JavaBeans™, including the data access Blox and user interface Blox to provide the most commonly required functionality with minimal coding. For example (see Figure 9), it is easy to link a Checkbox Form Blox to enable or disable the alternate row banding property in the Grid Blox and to create a Select Form Blox that controls the chart-type property in the Chart Blox. Figure 9: Some example form Blox Developing analytic applications with IBM DB2 Alphablox Page 13 MDB database–specific form Blox are specialized HTML form elements that know how to work with MDB metadata. For example, Data Source Select Form Blox shows a list of all the available data sources in a drop-down list and Dimension Select Form Blox shows a list of available dimensions for a given data source. The various form Blox provided by DB2 Alphablox include: • For Basic HTML form elements — Checkbox Form Blox — Edit Form Blox — Radio Button Form Blox — Select Form Blox • For MDB-specific HTML form elements — Data Source Select Form Blox — Cube Select Form Blox — Dimension Select Form Blox — Member Select Form Blox — Time Period Select Form Blox — Time Unit Select Form Blox • Tree navigation element — Tree Form Blox Figure 10: An example of Tree Form Blox Business logic Blox DB2 Alphablox provides the following business logic Blox to facilitate the incorporation of dynamic, complex business logic into analytic applications: Developing analytic applications with IBM DB2 Alphablox Page 14 Member Security Blox: Limits the visibility to members for which the users have no access. Even though the underlying databases provide data security data, some databases do not provide security on the outlines (that is, the member names themselves). The Member Security Blox could be used to provide outline security in those cases. Time Schema Blox: Helps create a time table to support dynamic time series based on the defined time schema. For example, once the time schema for the data source is defined, it is easy to access data for any supported time periods, such as the last six months, the last two quarters, the month to date or the current week. Analytical infrastructure Blox DB2 Alphablox provides a tremendous amount of customization, personalization and collaboration functionality through the following components: Repository Blox: For accessing and manipulating user and application properties stored in the repository; key to personalization Bookmarks Blox: For accessing and manipulating bookmark-related properties and information stored in the repository; includes extensive APIs for programmatic manipulation of bookmarks Comments Blox: For managing and storing comments at cell, page or application level. Application developers can create customized line-ofbusiness applications and personalize the interfaces to each individual user through Blox properties, Blox JavaScript™/Java API and application and user custom properties (see Figure 11) Figure 11: Comments Blox in action Developing analytic applications with IBM DB2 Alphablox Page 15 IBM DB2 Alphablox programming model IBM DB2 Alphablox supports a standard J2EE application development model, offering a complete development paradigm for application delivery. This paradigm typically contains HTML pages used for static content, JavaServer Pages for dynamic content, cascading style sheets (CSS) for formatting and JavaBeans for application/business logic. By conforming to this paradigm, a typical DB2 Alphablox-enabled application consists of a collection of JSP pages, with each page containing a combination of Blox, HTML, graphics and cascading style sheets. JavaScript and Java can be optionally used to enhance the application’s capabilities. DB2 Alphablox programming model consists of the following facilities: • An extensive set of JSP custom tag libraries • Server-side APIs to the JavaBeans beneath the Blox • Server-side access to the Blox UI model • Ability to remotely invoke server-side APIs from the browser through JavaScript Figure 12 shows an example of a JSP page with DB2 Alphablox tags. In this page, tags are used to embed a Present Blox and a Data Blox on the Web page. Those lines using tags are highlighted. Figure 12: Example JSP page using DB2 Alphablox tag libraries and the generated Web page Developing analytic applications with IBM DB2 Alphablox Page 16 These few lines of code will produce the Web page shown in Figure 12. The end user of this simple Web page has automatic access to a great amount of analytic functionality, including these capabilities: • Drill down, drill up, pivot, swap axes, show, hide and sort data either through the toolbar, menu bars or the right-click menus • Move dimensions across row, column, page and other axes • Customize member selection on each axis • Show or hide grid, chart and the data layout panels • Reallocate the space occupied by the chart and grid components • Undo and redo data navigation operations • Change the chart types and other chart options • Set up traffic lights (conditional formatting) • Perform 80/20 analysis with Pareto charts • Enable regression analysis and predictive analytics by adding trend lines and forecasting to the charts • Render the complete grid data and chart to PDF format or export to Microsoft Excel for further analysis—all with the single click of a button • Use the bookmark facility to save views of the data as bookmarks that can be shared with other end users DB2 Alphablox provides application developers the ultimate flexibility in customizing the user interface and adding their own business/ application logic. With the DB2 Alphablox tag libraries, the JSP author does not have to know the low-level technical details behind the Blox components, but simply needs to know the syntax and functionality for each Blox. This process enables page authors with no Java experience to incorporate analytics seamlessly on an intranet and/or extranet using best-of-breed authoring tools. Each Blox has a comprehensive set of properties that by using the tags could be set easily to custom values in the JSP pages. For example, setting the width property in the blox:present tag to 50 percent will make this Present Blox occupy only half of the available horizontal space in the browser. DB2 Alphablox also provides application developers the ultimate flexibility in customizing the user interface and adding their own business/application logic by exposing every Blox as a JavaBean and allowing programmatic access to those beans through a rich set of Java APIs. Moreover, DB2 Alphablox provides the industry’s pioneering Web UI model—the Blox UI model—where application developers can register server-side handlers to handle user interaction events in the browser, such as mouse clicks and right-click actions. This Blox UI Developing analytic applications with IBM DB2 Alphablox Page 17 model offers developers the ability to customize the user interface behavior. For example, application developers can provide a different right-click menu depending on whether the user clicks on the row header, on the column header or on a data cell, and then apply any additional logic based on the current user’s role, time of day or session state. Application developers also can add their own HTML, CSS and JavaScript to the JSP pages to achieve the exact look and feel they desire. DB2 Alphablox also provides developers a unique and powerful ability to remotely invoke serverside Java APIs through JavaScript. DB2 Alphablox provides developers a unique and powerful ability to remotely invoke server-side Java APIs through JavaScript. Development tools In addition to the various Blox components and the DB2 Alphablox programming model, DB2 Alphablox also provides a library of development tools and examples to accelerate the application development process. DB2 Alphablox examples are reusable and modifiable and include prebuilt sample queries for many of the data sources that DB2 Alphablox supports. The Blox sampler is a rich set of real-world examples that illustrate essential Blox configuration and common Blox manipulation techniques. These examples will help guide developers through building DB2 Alphablox-enabled applications. The primary goals of the Blox sampler are to: • Help application developers understand the various user interface Blox and their behavior • Provide tips and techniques for solving real-world issues that application developers are likely to encounter • Provide sample code for use in building applications The Blox sampler sections and other examples include: • Presenting data—Using techniques such as numeric properties and cell formatting for better presentation of data • Converting to PDF—Generating a PDF from different user interface Blox • Interacting with data—Providing user selections to interact with the data using various HTML elements, asymmetric reports and event filters • Filtering data—Using different techniques to dynamically filter data Developing analytic applications with IBM DB2 Alphablox Page 18 • Highlighting data—Calling the user’s attention to certain data using techniques such as cell alerts, traffic lighting and cell links • Commenting on data—Support for cell- and page-level commenting using Comments Blox • Persisting data and bookmarking—Custom user properties, personalization dialog, saving and restoring application states • Inputting and modifying data—Write-back examples • Providing relational reporting—Examples that illustrate essential steps and tips for creating relational reports; includes an overview of the set of Blox supporting the relational reporting feature • Offering the Quality Chocolate Company (QCC) databases—These databases, used in many DB2 Alphablox examples, contain a significant amount of data and are designed for showing realistic business scenarios in the context of a ficticious company. Multidimensional and relational versions of this sample data are shipped with DB2 Alphablox. The QCC sample data can be used when building a prototype with DB2 Alphablox when data is not ready. The DB2 Alphablox Workbench includes the Alphablox Query Builder (see Figure 13), which can be used to manipulate the grid options, layout, queries and chart type. Once application developers get to the view they like, they can generate the JSP tags for the Present Blox by clicking the “Generate Blox Tag” button. Those tags can then be easily copied and pasted into their application. Figure 13: Query Builder tool Developing analytic applications with IBM DB2 Alphablox Page 19 A full set of documentation including the Javadoc, the reference guides and the developer’s guides are all available online in HTML and PDF formats. These documents step developers through the process of defining the application, accessing data, creating the application home page and building their analytic views. First-time users of JSP technology will learn that the steps needed to build analytic solutions are relatively easy and build upon general Web technologies and skills. Because DB2 Alphablox-enabled applications are created using open standards–based technologies such as JavaServer Pages, DB2 Alphablox customers and partners can use any of the extensive selection of Web design tools and source code control systems available on the market today to develop and manage DB2 Alphablox application code. Thus, a wide variety of developer tools such as Macromedia Dreamweaver MX, Adobe GoLive, Borland JBuilder, IntelliJ IDEA, BEA WebLogic Workshop, IBM WebSphere Studio Application Developer and Eclipse can be used in the DB2 Alphablox development process. IBM DB2 Alphablox tools for analytic applications More than ever before, custom analytic applications are critical for businesses today. With IBM DB2 Alphablox, customers and partners do not have to choose between deploying inflexible query and reporting tools or packaged applications and developing custom analytic applications from scratch. DB2 Alphablox provides the components, application framework, programming model and tools needed to significantly reduce development time while offering the ultimate flexibility in developing and deploying custom analytic applications. © Copyright IBM Corporation 2005 IBM Corporation IBM Software Group Route 100 Somers, NY 10589 Printed in the United States of America All Rights Reserved January 2005 IBM, the IBM logo, DB2 and WebSphere are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries or both. Alphablox and Blox are registered trademarks of Alphablox Corporation in the United States, other countries or both. Microsoft and ActiveX are registered trademarks of Microsoft Corporation in the United States, other countries or both. Java and all Java-based trademarks are trademarks of Sun Microsystems, Inc. in the United States, other countries or both. Other company, product and service names may be trademarks or service marks of others. All statements regarding IBM future direction or intent are subject to change or withdrawal without notice and represent goals and objectives only. ALL INFORMATION IS PROVIDED ON AN “AS-IS” BASIS, WITHOUT ANY WARRANTY OF ANY KIND. References in this publication to IBM products or services do not imply that IBM intends to make them available in all countries in which IBM operates. The IBM home page on the Internet can be found at ibm.com G507-1010-01