Querying & Reporting: Using Cognos NovaView

NovaView lets you easily delve into a data warehouse

Cognos NovaView 2.0, a querying and reporting tool for SQL Server 7.0 data warehouses, lets users delve into data in a data warehouse. NovaView 2.0 consists of two products, the NovaView client and NovaView Administrator. The NovaView client is the application users interact with. You can use the NovaView client to create standalone applications, such as NovaView for a single user, and to execute applications. The NovaView Administrator lets you create, manage, and control security for public NovaView applications.

The NovaView Administrator plugs into the Microsoft Management Console (MMC), as other Windows NT and third-party tools do. You can use the NovaView Administrator to create a central application that is available to users based on the user's security permissions. Then you can control security by defining the NovaView roles that link back to the NT security system. Setting up security with NovaView is similar to setting it up with the role-based Microsoft Transaction Server (MTS) security system. Using the NovaView Administrator to manage security is easier than creating your own security system with custom applications.

To test NovaView, I installed the NovaView client on NT Server 4.0, which was also running SQL Server 7.0 with OLAP services. I also used the NovaView demo application, which uses the FoodMart database that comes with OLAP Services.

NovaView Terminology

Before I dive into NovaView, let's review its terminology. NovaView consists of several elements. The first element, a domain, defines a high-level view of the data in the application. For example, the demo application has three domains: Sales, Finance, and Warehouses. The next level of detail is the binder. A domain can contain any number of binders, and each binder contains one or more views. The Sales domain contains the binders: Sales History, Regional Analysis, Store Performance, and Additional Reports. The next level, is the view; it displays the multidimensional data in charts or crosstabs. The Regional Analysis binder contains the views Promotions by Stores and Exceptions by Region. This logical structure will become clearer as you walk through a NovaView application. It's important to understand the structure NovaView uses, so you can create well-structured, easy-to-use applications.

Features and Functionality

NovaView displays the demo application's view when you start it for the first time. From the application, you can click the domain name to drill down into the Sales, Finance, or Warehouses domain. For example, if you click on the Sales domain, you'll see the four binders for Sales (the buttons along the bottom of the screen) and the first view from the Sales History domain, as Screen 1 shows.

Screen 1 shows multidimensional data in the chart and in the table, with crosstabs below the chart. You can manipulate the display and results with the button controls on the screen.

The first view you see when you enter a domain is the default view assigned to that domain. Clicking one of the buttons of the four binders for the sales domain displays a pop-up menu with the views for that binder. To change views, click the binder you want, then select the new view. You can also change views by clicking the Preview View and Next View buttons on the toolbar. These buttons walk through the views defined for the binder.

The buttons on the left side of Screen 1 are slicers. A slicer button lets you change the slice of data (dimension) you're presently viewing. For example, clicking on the Gender button displays the list of options for this dimension, as Screen 2 shows. You can double-click any option to drill down to that particular data. For example, to filter the display of sales by males, double-click M, and the chart and crosstab will update accordingly. The other slicer buttons work the same way. You can return the view to the previous data by clicking the Gender slicer button again and double-clicking All Gender.

The View menu on the toolbar lets you control the display configuration, as Screen 3 shows. You can turn on and off the slicers, chart, crosstab, and view title options, and you can change other configuration parameters. The View Tabs option turns tabs on and off for each view in the current binder. The tab feature makes a binder behave like an Excel Workbook with multiple spreadsheets or charts. Clicking a tab displays the corresponding view. You can also turn on the toolbars, which let the user configure the chart and the crosstab. Screen 3 also shows the active toolbars.

The charts in a view are hot, which means they let the user drill into the data directly from the chart. The user right-clicks an element in the chart and selects Drill Into, then the level name from the shortcut menu. For example, if the chart displays store sales by country, users can click on USA to drill down to view US sales. As long as the database contains the lower-level data, you can continue to drill down into a chart element containing that data. You can reverse this process by clicking a higher level name and selecting Drill Up.

You can also expand any entry in the crosstab (the upper right corner of the cells) by clicking the + sign button for that entry. This action drills down one level. To move up a level, click the - sign button. You can also click the small chart in the crosstab to turn on and off elements in the chart that are on that level.

Another NovaView feature is What If analysis. You can right-click an entry in the crosstab and select What If from the shortcut menu. This displays the What If dialog box, which lets you enter a new value for the cell. After you enter a new value and click OK, the view updates with the new data. Screen 3 shows the results of changing the Alcoholic Beverages entry to 110,000. The small red triangle in the upper right corner of the cell signifies a new number in the crosstab where a What If condition is currently active. The chart also displays the new data. You can remove the What If condition by selecting What If again for that cell and by clicking the Remove button, which causes the chart and crosstab to revert to their original values.

This article touches on only a few features of the NovaView 2.0 client. You can also use the client application to create new applications and perform other analysis tasks. NovaView integrates with Excel and HTML applications, but you can't publish dynamic Web pages with it. Also, the documentation notes that clients must use Internet Explorer (IE) 4.0 or later, which limits the pages for use on the Internet.

NovaView is a powerful tool, but it brings up several issues for developers and DBAs. First, the limited support for Web applications requires that you have NovaView on every user's desktop, which requires client installation and maintenance on each system and raises the total cost of ownership (TCO). Second, the NovaView package comes in several flavors. The NovaView Administrator is another package in addition to the NovaView client that you need to purchase. This adds to the cost and makes for yet another piece of software to install and maintain.

Third, I'm always leery of powerful graphical tools that create query applications against a production server. I've seen such tools bring down servers. Make sure you test querying tools with real data and a reasonable user load before you deploy them.