DBMS

BrioQuery Enterprise 4.0

By Robin Schumacher
DBMS, September 1996 Brio Technology's BrioQuery Enterprise 4.0 is a desktop decision-support product that offers integrated query, desktop OLAP, interactive charting, and reporting capabilities.

You'd be hard-pressed these days to find a major corporation that hasn't already implemented a data warehouse or doesn't even have one in the works. When constructing a warehouse, corporations face the daunting task of getting the collected data successfully into the hands of decision makers for analysis.

BrioQuery from Brio Technology was designed to handle this type of challenge. Brio Technology claims that BrioQuery is the original desktop online analytical processing (OLAP) tool. It sports an impressive set of query, reporting, charting, and interactive analysis tools in one tightly integrated product.

BrioQuery has the ability to handle all of the main requests that any corporate decision-support analyst could make. You can perform drill-down analysis, data aggregation, pivots, powerful information charting, customized reporting, and more. Best of all, you can perform data analysis without knowing a specific database vendor's syntax or even the location of the data. BrioQuery also offers a great feature for mobile users through its Desktop Database Cache, an optimized row set manager that stores query results and lets you disconnect from the host database and perform further offline study of the data.

While not for the novice Windows user, the tool offers a robust set of capabilities for the most demanding data warehousing OLAP tasks.

Getting Started
I reviewed BrioQuery Enterprise version 4.0 on a Pentium 133MHz with 32MB of RAM running Windows 95, and on a Pentium 90MHz with 49MB of RAM running Windows NT. Initially, I winced when I opened the software package and saw numerous diskettes instead of a CD for installation. I once had to install Microsoft Office from diskettes, and I am pretty gun-shy of diskette installs and greatly prefer a CD. I was quickly relieved to find, though, that the entire product is contained only on four diskettes. (The remaining diskettes are for ODBC drivers and Sybase Open Client software.)

BrioQuery doesn't require ODBC drivers for database connectivity; it provides native drivers for all of the major RDBMS vendors, including Oracle, Sybase, Microsoft, and Informix. The ODBC drivers are for end users that require ODBC for a particular reason. Once started, the installation of BrioQuery went fast and without incident. The total disk space consumed by the product was about 8.5MB.

BrioQuery also provides sample data for a variety of databases, in case customers wish to use their own database product instead of the Microsoft Access sample databases provided with the tool for training purposes. I loaded BrioQuery's sample data into an Oracle7 database as well as a Microsoft SQL Server database with no problems.

In addition to normal relational databases, contacts at Brio Technology informed me that BrioQuery will soon be able to act as a front end to multidimensional servers such as Arbor's Essbase.

Off and Running
You establish connection to database servers using the Open Catalog. This architecture stores database access information in file formats called Open Catalog Extensions (OCEs) that can then be chosen from pick lists when you need to access a database. Once established, OCEs provide an easy way for end users to connect to multiple database servers - a big plus in today's data warehousing environment in which you may have to go back and forth from central data warehouses to smaller data marts.

I was able to create several OCEs quickly by choosing the proper database networking software and database drivers from pick lists and then providing the host name connections. After I created the OCEs, the only things I had to enter when choosing an OCE were the user ID and password. You can also designate an OCE as your default catalog for quick access.

The designer interface is laid out in a large tab format that has options for the various analysis tools. Although the tab doesn't have the Windows 95 "3D" look, it lets you switch easily between all of the BrioQuery tasks necessary for interrogating a data warehouse.

You build an initial query by opening a table catalog that displays a list of all of the query objects (for example, tables or views) available for use. I was able to drag tables from the table catalog list onto the query tab to formulate a query. For joins, I selected multiple tables from the catalog and pressed the Add button on the toolbar to place my objects on the query tab. BrioQuery makes a good guess at what columns you want to join to form a query, but in some cases I had to delete several guesses to make a correct join.

I really liked one feature, built into a small button on each query object, that let me view the data in the underlying table or view. Clicking this button on the query object (the tables or views being used) changed the view of the object from a definition view, showing the object's columns, to a view that displayed database data. A smart addition to this feature is a limit on how much data the preview will retrieve - the default was set at 10 rows to keep performance quick. To see only a column's distinct data values, I double-clicked on it and pressed the Show Values button.

I was able to select the elements for my query by dragging columns to the request line or highlighting multiple columns and pressing the Add toolbar button. To run a query, I pressed the Process toolbar button, which built my result set from the database. Query files can be imported from disk and run through the query tab, which makes it easy to run repetitive queries. In addition to normal queries, users also have the ability to access and run stored procedures through the product. Used in conjunction with BrioQuery, stored procedures can be used by administrators to cut down on complex SQL parsing and can also serve as another level of database security.

BrioQuery does a great job of fighting the dreaded Cartesian join queries by prompting the user with a message stating that an improperly joined query exists and should be edited before continuing. This can be a big help in curtailing Cartesian joins that can be sent up against the millions-plus row tables that exist in many data warehouses.

Another intelligent feature that will be very popular with DBAs is the ability to limit user queries by both number of rows and processing time. DBAs can set this limit in BrioQuery's Repository Manager so that limits can be applied to all users of a DataModel or to each query inside the BrioQuery Designer. Such an option is a must for controlling runaway queries that eat away at a database server's resources.

Meta Master
Many organizations go to great pains to design metadata for their data warehouse. One of the primary uses of metadata is that it lets you use friendlier business terminology (which is more understandable to end users) when designing queries, rather than the cryptic names often used by IS. BrioQuery doesn't miss a beat in regard to metadata. The tool lets you employ metadata currently stored in any relational database and use it when defining Open Catalogs.

BrioQuery's Open Metadata Interpreter (see Figure 3) lets database administration personnel construct Open Catalogs that make use of a corporate metadata store.

Currently, your corporate metadata must be stored inside some relational database for BrioQuery to use it. What if you don't have any metadata? BrioQuery handles that by allowing an administrator to define a new source of metadata in the Designer and upload it into BrioQuery's own Repository Manager for use.

Getting Results
Once a query is processed, a user has a multitude of options available for working with the result set. The output is displayed in a grid format that permits columns to be resized and moved to different locations. Information can also be exported in a number of different formats, including Excel, Lotus 1-2-3, comma-delimited files, and more. I was able to export data in the results tab and then pull it into an Excel 7 (for Windows 95) spreadsheet with ease.

BrioQuery does a better job of letting you work with query result sets than many other OLAP products on the market. After you have pulled data into the results tab, you can then format, re-sort, perform computations, and arrange the information in numerous ways without having to re-execute the queries, as some other tools force you to do. This implementation in BrioQuery helps cut down on repetitious requests to the database and results in reduced network traffic - a plus in any decision-support environment.

A very powerful feature in the results tab portion of BrioQuery is the Retain Results option under the Results menu. Retain Results lets you store data in a local database cache that allows you to disconnect from the database and perform analysis offline. Mobile users will really appreciate the ability to carry out their decision-support work where and when they want.

Pictures Worth a Thousand Words
Users love to see information presented in graphical formats, and BrioQuery's charting function does an impressive job of providing an intuitive approach of graphing data and then analyzing it down to minute details. I was able to create polished-looking 3D charts that could be changed between bar and pie formats with a click of the mouse. Once I was satisfied with the graphical format, BrioQuery then let me highlight and drill down into portions of the graph for more detail. (See Figure 4.)

By clicking on a portion of a bar or pie chart, I could quickly drill down into other facts that concerned that segment of the overall graph. This ability gives the decision support analyst an easy way to uncover detailed information about areas of particular interest.

Charts may be used in conjunction with another integrated tool of BrioQuery Enterprise, the Detail Reporter. The detail tab lets you quickly drag and drop data elements into the document outliner to create organized reports. Charts or PivotSheets (a multidimensional feature of BrioQuery that lets you completely reorganize query result sets) may be inserted into areas of the report as shown in Figure 5.

Inside reports, you have the ability to highlight information that you want to stand out from the rest. Using the Spotlighter option, I created a formula that caused any Units under 300 to be displayed in red. This feature is handy for presentations in which there is a need to emphasize data that falls outside the norm.

Bumps in the Road
I experienced a few problems with BrioQuery during my tour of the product. When I tried to use the Oracle7.2 driver to connect to Personal Oracle7 (version 7.2.2) under Windows 95, I received an ORA-3126 error stating that nonblocking operations were not supported. However, I was able to connect to Personal Oracle7 with the Oracle7.1 driver, and I had no problems using the 7.2 driver under Windows NT.

I encountered a number of protection faults under Windows 95 when closing down the product, and I also experienced some screen corruption when retrieving saved queries. A contact at Brio Technology informed me that this was a known problem and that a patch was available for the bug. I found neither of these problems, however, in the NT version.

BrioQuery's online help has both good and bad features. The tool has a tutorial help window called the Advisor that follows you through the product, offering assistance along the way. The Advisor helped me several times when I was wondering what action to perform next.

However, I couldn't find a context-sensitive way of getting help in BrioQuery. None of the dialog windows had a help button to give a more detailed explanation of the various options. In the Windows NT version, I couldn't invoke the help contents from the main help menu. It appeared that the program was calling a help file named "brio.hlp" when the help file provided with the product was "brioqry.hlp." I encountered another execution problem on NT when I tried to run the Repository Manager - the setup program had incorrectly named the executable file. Once I corrected it, the Repository Manager ran fine.

The Bottom Line
Overall, I am impressed with the OLAP power that BrioQuery offers. While I wouldn't recommend BrioQuery Enterprise to timid Windows users, the product supplies real strength for savvy workers who must make their way through corporate data warehouses. With industrial-strength features, such as metadata management, exceptional query, formatting and interactive analysis abilities, and full-blown administrative control features, BrioQuery is one OLAP tool that's built for getting results for the enterprise.

FIGURE 3


--An example of the use of the Open Metadata Interpreter to retrieve column aliases from an alternate metadata source. Once defined, end users can use much simpler ıbusinessı names for tables and columns than what may be defined in the actual underlying database.

FIGURE 4


--BrioQuery offers exceptional graphing and charting ability. Users may create graphics from queries and then drill down into further detail from specific areas on the graph.

FIGURE 5


--Reporting is one area that won't disappoint BrioQuery users. In addition to creating typical text reports, users can combine charts with regular detail reports to add a graphical perspective of the data being presented.
Robin Schumacher is a senior DBA and developer working for Alliant Health System in Louisville, Kentucky. He administers both Oracle7 and Microsoft SQL Server databases on various platforms and performs client/server development with PowerBuilder on Windows NT. He is the coauthor of an upcoming book from Prentice Hall - due out early winter of 1996 -on advanced PowerBuilder development. You can email Robin at 104455.1156@ compuserve.com.
Table of Contents - September 1996 | Home Page
Copyright © 1996 Miller Freeman, Inc. ALL RIGHTS RESERVED
Redistribution without permission is prohibited.
Please send questions or comments to mfrank@mfi.com
Updated Wednesday, September 18, 1996