If you need a cross-platform query and analysis tool for end users, BrioQuery should be on your evaluation list. It runs on Macintosh, Windows 3.1, and Unix Motif. A Windows 95 version should be shipping by the time you read this review. BrioQuery bolsters its data-access and reporting features with crosstab and pivoting capabilities.
Expanding into the world of Unix is perhaps the most significant enhancement in version 3.5. BrioQuery now runs on SunOS, Sun Solaris, AIX, and HP. Other improvements available on all platforms include the ability to create virtual tables and calculated fields in data models (simplified views of physical databases), local processing options such as two-pass reports and client-side calculations, and an auditing function that tracks end-user activities.
BrioQuery comes in three versions. Administrators should choose the Designer version because it can create shared data models and repositories (described later in this review). The Explorer version also creates data models, but it lets users query databases directly without using data models. The Navigator version lets end users access data through data models. It does not create or modify data models or access databases directly.
The ability to specify filters in a data model gives administrators even more control over data accessed by end users. BrioQuery adds these filters to the where clause automatically before sending a query to a data source. End users can supplement global with local filters. If you want to hide a column, you cannot remove it from a topic based on a table. Instead, you can create a virtual topic from the original topic and remove the item from the virtual topic. Other query tools, such as Software AG's Esperant (see preceding review), let you hide columns without this extra step.
When you create a new data model, you can include a comma-separated list of multiple databases on a server. This seems like a useful way to permit queries that merge data from multiple databases. When I tried it, however, BrioQuery created new empty tables in the first database from the structures of tables in a second database. I do not think most DBAs will appreciate this.
However, BrioQuery provides features that make life easier for both end users and administrators. By default, topics display the names of data items they contain, but if you're not sure what a topic means, you can click on a small icon on each topic to toggle the display to the first 10 rows of data. An Advisor window sits on the lower right corner of the screen offering context-sensitive suggestions on what to do next.
The Designer version lets administrators create BrioQuery repositories on a database server. A repository consists of two tables that store BrioQuery data models, queries, reports, report templates, and report style sheets. Users can download and use these components, and BrioQuery's "Automatic Distributed Refresh" feature sends users new versions of updated data models, queries, and reports (but not report templates or style sheets).
After designing a query, you click the Process button to retrieve the results, which BrioQuery displays in a tabular grid. The bottom of the screen has four tabs labeled Query, Results, Pivot1, and Detail1. Clicking Pivot1 accesses another workspace that has a floating window called the Outliner. (See Figure 1.) You drag items from the request line into the Outliner's three list controls: row variables, column variables, and numeric variables for the body of the crosstab. The main Pivot window refreshes each time you alter the Outliner controls. You can drag and drop items within the Outliner to restructure the pivot table. You can also drag and drop the pivot table column or rows, but you must grab hold of a small tab, not the larger row or column labels. The right mouse menu has a drill-down option that can add additional dimensions to either axis.
Major-league analytical tools have built-in knowledge of time periods and can convert dates to weeks, months, quarters, and so forth to support drill-down and roll-up through time dimensions. BrioQuery lacks this ability, so I had to create a calculated field to group dates into months.
In addition to computing totals, averages, minimums, maximums, and counts for the body of the crosstab grid, BrioQuery can show non-null counts and averages, the count of nulls, and percent of row, percent of column, or percent of grand total. You can also display multiple calculations such as a sum and an average within the grid. The new Spotlighter opens up a floating window that lets you format crosstab cells based on conditions. For example, you could color all cells with a negative amount in red, and all cells with amounts over $100,000 in green.
Unfortunately, I found some situations where BrioQuery's default assumptions caused me to do extra work to make the results palatable. For example, BrioQuery does a poor job of sizing columns. I often had to widen long character columns. BrioQuery truncates these by default, but you can turn that off and turn word wrap on, which compensates nicely. Numeric and date columns were frequently too narrow, so I had to widen them as well. Few query tools have this problem, so I think BrioQuery has room for improvement here.
Also, when I queried all records from a 24,000-record table, BrioQuery did not begin displaying data until it had about half of the result set. By contrast, Software AG's Esperant displays results much faster and experiences less drag while caching additional records.
A BrioQuery document (file) has at least one pivot and detail tab, but you can create additional crosstab reports within the document. BrioQuery updates all crosstab and detail reports in a document when users modify and reprocess queries.
If your users create several similar reports, BrioQuery's style sheets and templates will help you achieve greater consistency. A style sheet lets you save and reuse formatting options. A template is the skeletal structure of a report but without any data.
Unlike Software AG's Esperant and many other query and analysis tools, BrioQuery does not yet offer any charting or graphic capabilities, and this is a serious weakness. However, you can export query results to a spreadsheet or a charting package.
Brio's documentation includes a query and reporting guide for version 3.0, plus a separate 3.5 update guide. Having to read and correlate both manuals is a chore. The help seems to be a little behind, too. It does not say anything about the Spotlighter or support for Unix, both of which are new to version 3.5.
