Knosys ProClarity is an environment for developing analytic applications using Microsoft Analysis Services as the underlying multidimensional database. It comprises an extendable, component-based development platform, a general-purpose, customizable OLAP client tool, and the Microsoft Excel 2000 Add-In.

A COM Base

Its foundation is the ProClarity Analytical Platform, which is designed around the Microsoft ActiveX/COM architecture. The Analytical Platform uses its own object orchestration system to permit extensibility. COM is an interoperability standard that lets applications share functions and is a hallmark technology of the Windows family of operating systems. ActiveX controls are among the many types of components that use COM to interoperate with other types of components and services.

The platform's Data Source Hub mediates all communications between the platform's other components: selection controls, ActiveX plug-ins, and other ActiveX controls and COM components. Because these components communicate with each other only through the Data Source Hub, each time you add a new component that also communicates with the Data Source Hub, all other relevant components will automatically be aware of how to interact with it. Figure 2 shows the product architecture.

Developers can use Microsoft Visual Basic for Applications (VBA, which ships with ProClarity) to extend the functionality of the core product or build custom applications using ProClarity components.

The starting point for ProClarity users is an Analysis Services cube, which can be remote (connected to Analysis Services) or local (disconnected from Analysis Services). This choice lets users either access data stored centrally or work locally on their laptop. For example, sales reps could have local cubes containing data for just their accounts. Immediately after they are processed, cubes are available to the OLAP client and the Excel Add-In. Knosys built both the OLAP client and the Excel Add-In using ProClarity components, which means developers can extend their core functionality.

ProClarity OLAP Client

The intuitive browser-style interface and wizards make this tool very easy to use. In common with most other OLAP client tools, ProClarity lets you view data in both grid (tabular) and chart (graph) formats.

Grids have all the standard features that you would expect in a robust, general-purpose OLAP tool, including pivot, drill-down, sorting, extensive formatting, and dynamic color coding. You can isolate or eliminate rows that meet your filter criteria. There are nine different filter types from which to choose. For example, if the grid rows contain customers, you can apply a filter to view only customers who make up the top 10 percent of sales or the top customers whose combined purchases exceed a specified amount. In this way you can easily identify the most important customers.

Users can enter data, providing write-back is enabled in the Analysis Services cube. Users can modify individual cells by value or percentage and can then specify how to allocate this change to the values of all the descendants. However, if the cell being modified is at a high level in the dimension hierarchies and has many descendants, changes to one cell could trigger changes to many cells, which could dampen performance. Custom development effort would be necessary to deploy a multiuser planning and forecasting application.

You can display a grid alone or along with another view type. Changes made to one view are automatically updated in the other.

Charts provide standard business graphics capabilities using six different chart types - area, bar, horizontal bar, line, pie, and point - each with extensive formatting options.

Advanced Analysis

What differentiates the ProClarity OLAP client tool is its ability to perform advanced analysis using two additional view types known as Decomposition Tree and Perspective.

Decomposition Tree is what you use to drill down into a number and see the relative importance of each of its constituent parts. (See Figure 3) When you drill down into any dimension at any node in the tree, the OLAP client ranks the next level of detail from left to right by value and percentage of the total. In this way the user can readily grasp the relative importance of any group of products, sales regions, customers, or any other dimension of the cube.

Figure 3 shows an example of a Decomposition Tree created by first selecting the profit measure and the item (Product dimension) to decompose and then drilling into the top level of Product to view the members at the line level - in the example, PCs and peripherals. Drilling into PCs breaks this line into its family members. Drilling into the Business PCs family along the Sales Region dimension displays the contribution of each sales region. Finally, to determine the breakdown of business PCs in North America by individual customer, you drill into North America along the Customer dimension. The top eight customers are ranked left to right and the remaining 127 are shown in a box on the right with their total and percentage. Clicking this box would show the next set of customers.

A Perspective is a scatter plot between two measures that can help determine patterns or identify outliers. For example, in a Perspective view of the measures sales and profit, you can assign a different color to each channel (such as OEM, retailers, and distributors), so you can quickly visually identify an unprofitable channel.

When you plot all individual customers on a sales and profit Perspective view, by default it displays each customer as a red circle. You can then designate data point attributes to the customers you want to analyze (such as customers in one of three indirect channels) so that a unique color and symbol is assigned for each indirect customer channel (attribute).

In this view, you can move horizontal and vertical statistical sliders to view the quadrant contributions. Zoom in to view detail where there is a tight cluster of data points.

The OLAP client supports further analysis by letting business users create their own measures. The ProClarity client stores these calculated measures, which a wizard helps create. The wizard gives you a choice of measure types: Growth (percentage change or delta compared to prior year or prior period), Time Series (year-to-date, quarter-to-date, and so on), and arithmetic relationships such as Ratio and Difference.

If the available types do not accommodate the measure you wish to calculate, you can use Multi-Dimensional Expressions (MDX). MDX is the language for manipulating multidimensional information in Analysis Services. It is unlikely the average business user will have a working knowledge of MDX.

A big benefit of the browser interface is its ability to navigate backward and forward through the different views you create. You can modify views at any time and save them in your "favorites" folder or to a briefing book. Favorites are stored on the PC for future reference. Briefing books contain compilations of views (grids, charts, decompositions, or perspectives) that can be organized into folders. Briefing books can be saved, reopened at a later date, and easily shared with other users via email.

Web Capabilities

One very useful feature is the ability to publish a briefing book to a Web server. ProClarity translates the briefing book into an HTML document known as an Active Book. A wizard steps you through publishing the briefing book and you can set properties to determine the level of interactivity available to other users. For example, some of the features - such as drill to detail - can be turned off. With no additional work on the part of the publisher, ProClarity copies the required files to a location on the server, which other people can then access from a browser without needing to install ProClarity locally.

The components required for ProClarity ActiveX controls are bundled into a CAB file (for Internet Explorer) and a JAR file (for Netscape). The ActiveX controls required to render the views and provide the navigation capabilities are automatically downloaded (if not already downloaded in a prior session) when you open an Active Book. You now have the full analytical capability of the full ProClarity client with no compromise in functionality.

Office 2000 Integration

ProClarity is very tightly integrated with Microsoft Office 2000. The OLAP Client can export the briefing book and build interactive analytics directly into a PowerPoint presentation, Outlook folder, or as a Web part that can be used within a Digital Dashboard. For example, a PowerPoint presentation can be developed to convey business information and during the presentation additional queries can be generated directly from PowerPoint rather than having to move back and forth between multiple programs.

A view (or page of a briefing book) can also be saved into an Excel workbook. The information can then be manipulated using the the Excel 2000 Add-In.

Excel 2000 Add-In

Rate This Article Rating: 1 (best) 2 3 4 5 (worst) Comments: Optional e-mail address:

Users familiar will Excel can remain in Excel and benefit from their existing skills. An extensive query wizard can be used to create queries and multiple queries can be created on the same sheet. By defining a report set (a packaged group of synchronized, customized reports) multiple reports can easily be generated and distributed. Drill to detail and the decomposition tree are also available. The add-in is competitive with other add-ins on the market for reporting and analysis; however, it does not support write-back.

Platform Extensions

Plug-ins and add-ins from third parties can be used to extend the core functionality of the ProClarity platform. You can use MapInfo Corp.'s MapX Plug-In (which you can find at www.mappinganalytics.com) to incorporate geospatial mapping capabilities and show sales or customer information by sales region, state, country, and so on. The EZForecaster Add-In from Parker Edwards Inc.'s ParkerSoft (www.parkersoft.com) can incorporate more than 20 time series forecasting techniques to project future values. Meta Data Management available in the DWGuide Navigator from DWSoft Corp. (www.dwsoft.com) provides the ability to visualize source tables and audit the numbers being delivered to the Analysis Services cube.

Recommendation

The Knosys ProClarity product family is a robust and extendable analytic development platform with a sophisticated, general-purpose, OLAP client tool and very functional Excel 2000 Add-In. Companies that have in place, or are planning to deploy, Microsoft SQL Server 2000 Analysis Services and are looking for an analytic framework would do well to consider this product.

Paul Dean (312-984-7160, pdean@braunconsult.com) is vice president of OLAP Consulting Services at Braun Consulting.

RESOURCES