Ad Hoc Query Tools: Do They Really Support Ad Hoc Querying?
.
.
.
.
.
.
..
.
.
Dr. Paul Dorsey
President, International Ad Hoc SIG
I have been fascinated with ad hoc query tools since I first played around with Oracle’s Easy*SQL over 10 years ago. I thought these tools were a great idea and wondered why they never caught on with users and developers. Now that we have 10 years of experience in this area, the tools are finally getting pretty good and the market demand is growing almost as fast as the Internet.
I recently completed a three-month review of seven of the top tools in the industry. A large part of the review was done by an executive MBA class at a private Eastern university. This gave me access to the opinions of both users and IS professionals as they reviewed the products.
This effort represents the fourth time I have reviewed these tools. Each time, the tools get better and the demand is stronger.
Along with the explosive growth of the Internet, ad hoc query and reporting tools are perhaps the most significant new development in computing of this decade. Most of the rest of the improvements in computers and technology have only impacted how efficiently we do things. But ad hoc query tools have the potential to fundamentally change the way that users interact with the corporate database—and can thus change the way that managers make decisions.
The growth of data warehousing is being fueled by users wanting direct access to their data. Existing production systems are too big, too complex, and too slow for users to be able to directly interface with them.
Unfortunately, most of the development effort from the vendors of ad hoc tools for the last year has been devoted to giving us better report writers, embedded graphics and fancier graphing options. None of these efforts brings us any closer to really being able to do ad hoc querying.
Five years ago, while teaching at
However, most decision making is based upon a static model. The user comes to the database to make a decision. The user then decides what information is required. They get it, and they make the decision. This is what we teach them to do in business school, and this is how 90% of current ad hoc query tool users still make decisions.
Because there are so few “true” iterative ad hoc queryers out there, we do not complain that the tools do not support iterative ad hoc querying. Instead, we ask for better report writers, fancier widget support, and other features outside of ad hoc querying. We will not get a good ad hoc tool from the vendors until we demand it.
After completing my 4th Ad Hoc Query Tool Shoot-Out in December, I am struck by how far we have come. Yet at the same time, I am disappointed that we still haven’t achieved my original vision of ad hoc query tools. Even though we are certainly somewhat closer to my goal for these tools, the vendors (responding to our own requests) have not moved us nearly as close to this vision as I would like. For example, we still lack a reasonable method of creating complex data filters (SQL “WHERE” clauses) in most of the popular tools.
I am encouraged that the market for ad hoc tools has grown so fast. But I am discouraged that I can’t really do iterative querying any better this year than last year. But there is some reason for hope: First, users are now starting to embrace the notion of iterative querying. Last year, about 5% of all ad hoc users surveyed reported iterative querying behavior. This year, the number is up to about 10%. Second, I think that at least a few of the vendors are catching on to the vision of iterative querying. It is now a commonly found term in the sales literature of the vendors. Now we just need to get them to incorporate it into their product development.
My understanding of the industry changes every time I do a shoot-out, and this one was no different. Several new issues emerged and I will use this article to discuss them.
Ad hoc query is a process with three different elements: Extracting the desired data (query), formatting the results (reporting), and analyzing the results (OLAP). Each vendor devotes different amounts of resources and delivers different levels of capabilities in each of these areas. But the reality is that, as users, we want the whole package. What we are looking for is an integrated tool that allows us to navigate the seas of our corporate data. It doesn’t help to be able to do great reporting if you can’t find and extract the data that you want to report on.
However, many companies can have their needs fully met by a tool that does not support all three areas. If your firm does not want to devote the resources to building a dedicated data warehouse, or if the nature of your application requires more reporting and less analysis, a query/reporting tool is probably the best choice. For firms that only require a ‘power user’ reporting capability, with more emphasis on formatting flexibility and less on query or analysis, there are a few tools that can support that need. And if your primary goal is executive reporting on a summarized data warehouse, an OLAP tool may be your choice.
Data extraction is perhaps the most important piece of an ad hoc tool. Until you have the data you want to format, there is not much point to working with the tool. Because of the difficulty in working with production data, an ad hoc tool must have a metadata layer that sits between the user and the production data and presents the user with a business-oriented view of the physical database structures. This metadata layer is more than just a collection of RDBMS VIEWs of the data. An intelligent metadata layer can be used to dynamically create a better SQL query than a VIEW can. Examples of good metadata layers can be found in Cognos’ Impromptu and Software AG’s ESPERANT products. (NOTE: Since this article was written, Speedware acquired Software AG in October 1996 and has been implementing Esperant as part of its complete BI line of products.) Tools such as Crystal Reports that lack a sophisticated metadata layer cannot be used effectively as ad hoc tools.
After the tool supplies a metadata layer, it must also supply a way of using it to construct queries that get information from the database. This “data extraction” piece is the area most neglected by the vendors. Only ESPERANT has come up with any interesting innovations in the last year. The ESPERANT interface represents such an important development that I will devote the next section to it.
Unfortunately, the industry still has not fully solved the
major problem of data extraction. Iterative
querying requires the ability to create an arbitrarily complex logical filter
to extract information. However, most
users understand Boolean logic so badly that they cannot even create simple
logical filters. I use the following
example: “Give me the names of all
employees who reside in neither
The second element of ad hoc tools is end user production reporting. This really has nothing to do with ad hoc query support, but the market demands that we be able to format our query results into production quality reports. An over-emphasis on reporting has probably been the leading reason that most vendors have spent so little effort on data extraction. We are all seemingly so impressed by neat looking reports. Two years ago, I rated SQL*Assist (produced by Software Interfaces which is now a Platinum company) as the best ad hoc tool on the market. It was not selling well in the marketplace because it didn’t have a good report writer as part of the product. Instead, it was merely the best little tool on the market for looking at data in a relational database. However, because of market pressure, they spent the next year writing Pro*Reports. This is a production report writer that is one of the best on the market. Now, Platinum gives us a two-year-old data extraction capability with a great report writer. I wish they had spent more time improving the data extraction portion of their product.
End user reporting is something we have been doing for years. Of the tools reviewed in the shoot-out, IQ and Pro*Reports had the strongest, most elegant report writers.
The final element of ad hoc tools is the OLAP piece. OLAP gives us the ability to easily analyze and report on multidimensional or time-oriented data, using techniques such as data pivoting, drill-down, graphics, etc. Different vendors take different approaches to offering OLAP capabilities. Some download summarized data into a multidimensional cube for analysis (on either the user’s PC or on the server), with the argument that this delivers several orders of magnitude of performance improvement over retrieving data from the production database. Others espouse a “ROLAP” (relational OLAP) approach, which delivers OLAP capabilities directly against a SQL database. Some bundle query/reporting and OLAP within a single tool (e.g., Brio Query), and others deliver separate products (e.g., Cognos’ Impromptu and PowerPlay; or Software AG’s combination of ESPERANT and DSS Agent).
Of the tools reviewed, Cognos’ PowerPlay tool is clearly the current leader in the OLAP race. They have a relatively clean user interface that has been in development for years. IQ has just bought a new OLAP tool and Business Objects is getting ready to release their own. Software AG plans to add OLAP capabilities into future releases of its tool. But the lines between query/reporting and OLAP tools are starting to blur, and this market segment is in a time of very rapid change—so it’s hard to name the ultimate winners and losers at this stage.
However, the shoot-out team concluded that each part of the ad hoc solution (query, reporting, and OLAP) deserves equal attention. However implemented, all pieces should be in place and reasonably strong.
ESPERANT, the ad hoc query offering from Software AG, was the real surprise of the shoot-out. It is a relatively new product that has the most revolutionary ideas in the industry. ESPERANT handles data extraction using a natural language interface. Of course, natural language is not a new idea. Many companies have tried and failed to make the idea work. I have frequently declared that natural language is a dead idea that can never work for ad hoc query.
The way that ESPERANT makes it work is that it doesn’t try to accept natural language as input. Instead, it uses a ‘point and click’ interface similar to the other products. The difference is that ESPERANT shows the query in English as it is created using a more or less traditional method. This is a much easier problem to solve than trying to understand free-form English.
The other thing that ESPERANT does is to prevent the user from forming queries which would return illogical or incorrect results. For example, once one field is selected, only fields that make logical sense are displayed for further selection. ESPERANT even detects and protects against the infamous many-to-one-to-many relationship that most tools ignore.
The ESPERANT interface is not perfect, however. In part, the problem is that some English phrases can have multiple interpretations. For example, I quickly found what I characterize as an error when we asked for a list of customers who have ordered two or less items in a given time period. The result set included customers who ordered two items and those ordering one item, but ESPERANT failed to retrieve the customers who did not order any items—which is the group we would primarily be concerned with. To get the desired result, we had to specifically ask ESPERANT within the query to also include customers with no orders. Software AG argued that this was a matter of interpretation, but it did finally agree with me and informed me that this behavior has been changed for the next patch release.
The question we must ask ourselves is whether ESPERANT (or a similar interface from any vendor) will make more mistakes than the user. My intuition is that ESPERANT is probably already better than the typical user. And we can expect that as Software AG continues to improve the interface over time, it will make fewer and fewer mistakes. This is definitely the way that data extraction should work. I look forward to the upcoming race to see who can best implement this approach.
The tools that focus on reporting, such as IQ, Pro*Reports and Crystal Views all assume that the natural user interface is to approach the database with a report in mind and jump into building it. They conclude that a two step approach, where the users first extract their information and then format it, is wasteful and inefficient.
Most other tools assume a two step approach. The user first extracts the information and then moves it into a reporting phase. These steps are usually well integrated into the same tool.
The shoot-out team was unanimous in preferring the two step approach. Everyone felt that the two step approach was more consistent with the way they thought about the problem of ad hoc query.
As the tools become stronger, it raises an interesting question: Can a single reporting tool be used for the entire enterprise? This would require that the reporting tool be easy enough for ‘power users’ but sophisticated enough to support IS professionals doing production reporting.
Currently, the production tools such as SQR or Oracle Reports are too hard for anyone but IS professionals to use; and the reporting tools included with the ad hoc tools lack the sophistication for producing complex production reports.
IQ and Pro*Reports are contemplating trying to support full production reporting and Oracle is adding wizards that should support end user creation of basic reports. We will have to wait to see if any tool is actually able to bridge the gap.
It took me a while to warm up to the need for an OLAP component to ad hoc tools. I thought that if we could get our information directly from the data warehouse or even directly from the production database through VIEWs, then there was little need for an OLAP tool that was only giving us performance increases and taking away the ability to query the full database.
Then I was reminded of one of the early end user reporting systems developed by GTE back in the early ’80s. They built a HyperCard stack of about 200 reports that were downloaded to the users’ Macintoshes every night after hours. The users would arrive in the morning, and bring up their canned reports. They would be able to navigate from one report to another by clicking on the hypertext links in each report. The users loved it. The person I interviewed from GTE claimed that there was little demand for live data access.
The GTE example sounds a lot like a primitive OLAP tool. The difference is that with an OLAP tool, the user has the ability to quickly select from thousands of reports rather than simply a few hundred.
OLAP gives the users the ability to almost instantly bring up any normal slice of their data that they can imagine. Then, if they need further information, they can go back to the basic data extraction/reporting parts of the tool and get the information. OLAP has the potential to greatly increase the usability of an ad hoc tool.
As mentioned earlier, there are differing approaches to OLAP. One is to download the result of an end user’s query to the PC, populate it into the OLAP cube, and then use it for subsequent analysis. This could be characterized as ‘ad hoc OLAP’. Cognos’ Powerplay supports this approach, with Impromptu (or some other tool) used to create the initial query. Another approach is a carefully constructed (and typically large) multi-dimensional data warehouse. Oracle’s Express is an example of such a tool, utilizing proprietary MDB structures. MicroStrategy’s DSS Agent also falls into this category, but instead relying on standard SQL RDBMSs with star or snowflake schema structures.
The tools we reviewed mostly support the first approach (ad hoc OLAP). The only exception might be Brio Query which seems best suited to star schema databases that could be quite large, especially now that the major DBMS vendors are supporting star indexing similar to RedBrick.
The one area of OLAP that is key but not supported by any of the tools reviewed here is data drill-down (or “drill-back”) from the multidimensional cells. Multidimensional cubes are usually summarized data. An important feature is a link back to the OLTP that allows the user to double-click on a cell of information and have the tool fire off a query back to the OLTP database to retrieve transaction level details. MicroStrategy’s DSS Agent (which is also resold by Software AG) supports this kind of drill-down; but I became aware of it too late to be able to include it in the shoot-out. Select*Star also supports this capability but they were in the process of a major rewrite and, as such, were not able to participate.
It is easy to ignore the need for good performance in these ad hoc query tools. They all look so elegant in the demonstrations. However, running on a small test database is different from running in a production environment. Some tools, such as BusinessObjects, are still bringing the full result set of an ad hoc queries back to the user’s PC prior to displaying anything. This is acceptable for many queries (even on large databases). But if users are routinely bringing back large result sets, then a tool that first downloads all information to the client will not work.
I am not trying to be overly critical of Business Objects. It has a fine tool with customers who are more enthusiastic about their experience with the tool than most others. At the last meeting of the Ad Hoc SIG, every Business Objects user present gave the product very high marks. It should be noted that each vendor makes design decisions and tradeoffs that are targeted at the needs of its anticipated customers. If you do not fit the customer profile of that company, you can experience serious performance problems.
My vision for the next generation of ad hoc tools will support the ad hoc tool automating the creation of the data warehouse. The data warehouse is really a logical construct, not a physical database. The ad hoc tools’ metadata layer is itself an implementation of a data warehouse. Physical warehouses are simply different implementations of the ad hoc tools’ metadata layer.
The process would work like this: The warehouse architect first implements a “virtual warehouse” by creating a metadata layer with the ad hoc tool. This allows the designer to determine if the warehouse will functionally meet the users’ needs.
If the meta layer alone delivers adequate performance, then the task is accomplished and we don’t need to build a physical data warehouse. Many warehouses on smaller systems are unnecessary; they can be implemented with a simple metadata layer.
If, however, performance is not adequate (as is usually the case), then the warehouse architect commands the ad hoc tool to generate the warehouse creation and population scripts. Since the metalayer already contains all the information needed to build the warehouse, it should be a straightforward task for the ad hoc tool to generate the script. I have been talking with one of the vendors about this idea; and I hope that we will have this capability within the next year.
In an executive MBA program at a private eastern university, I offered an elective course to evaluate ad hoc query tools. I advertised that the course was appropriate for both technical and non-technical professionals. By a wonderful coincidence, the course attracted eight systems professionals and eight non-technical managers. For the first few weeks we discussed various ad hoc query issues, and I taught the class the basics of relational theory and SQL. Then we had the seven participating vendors send in representatives to present their products. Each vendor was given a three hour block of time to present their product, or talk about their company. There was also a lab with the products installed where the team could get hands-on exposure to each of the products. After the entire class was exposed to a product for the evening, everyone wrote up his/her perceptions and passed them to the two-person team assigned to that tool.
Each product was assigned to a technical and a non-technical evaluator. The technical evaluators looked at performance, ease of support, and metadata layer creation. The non-technical evaluators rated the products for ease of use and functionality. One of the realities about this field that took me a long time to accept is that systems people cannot evaluate an ad hoc tool for its functionality to the users. Tools that systems people like are invariably different from tools that appeal to the user community. The small team worked with the tool for several weeks and then wrote a detailed report evaluating the tool. These detailed (15-20 page) reports can be ordered from the International Ad Hoc SIG.
Our study was based upon the evaluation of seven ad hoc query tools: Brio Query, IQ, Cognos’ Impromptu and PowerPlay, Platinum’s SQL*Assist, Crystal Views, Business Objects, and Software AG’s ESPERANT. Some of the technical criteria that we considered were overall architecture, query governors, security, multidimensional capabilities, and the ability of the tool to automatically generate a metalayer layer from the RDBMS data dictionary. From the end user perspective, we looked at issues such as data extraction, reporting capabilities, and query assistance.
Brio Query is an OLAP tool that runs against a star schema relational DBMS. It is less well suited for use as an ad hoc data extraction tool against a production DBMS. This was the first tool we reviewed. Touted as the All-in-One query tool, Brio Query offers native and ODBC connectivity to most major RDBMS, a utility for generating metadata by reading the data dictionary of the RDBMS, and a mature report design interface.
This tool was not well received by the team. To be fair, the overhead projector was acting up that evening and the presentation didn’t go as well as it should have. The authors took another look at Brio and were happier with the tool after a second look. If you focus Brio on a star schema data warehouse, it is actually quite a serious competitor to Cognos’ PowerPlay.
Brio has targeted Cognos as its primary competitor, specifically its PowerPlay product. Brio believes that multidimensional analysis can be implemented faster with Brio Explorer than any other tool. The underlying assumptions are that your firm’s data requires no cleansing, resides in an RDBMS or an MDB back end, and does not approach terabyte sizes. Aggregation and partitioning are considered by Brio to be issues for the DBA, and therefore it does not offer an intelligent SQL builder—a utility which can identify that summary tables exist and automatically use them to provide faster results for a given query. Given that summarization is assumed, there is no true drill-down/drill-up capability.
Brio's interface for ad hoc querying is a good fit for the power user with knowledge of the database. For the novice user, it offers an Advisor window which will guide the individual through the query definition and report design process. A feature called outliner mode allows the user to format the results of the query into a presentable document. However, Brio requires that the entire data set be returned to the PC which is not always optimal. In its next release, Brio is planning a Server Scheduler, which will provide a mechanism for batch reporting as well as accepting one-time query requests. This lets the user return to retrieve the data set at his/her convenience, rather than have their PC frozen until the query completes.
The main problem with Brio’s Query is that it is a pretty
weak tool in supporting data extraction from a non-star schema data
structure. Also, its reporting
capability is not as strong as some of the other tools.
Platinum has clearly been driven to provide the enterprise wide reporting tool, and it looks like they have delivered! SQL*Reports is packed with reporting features, and has taken a stand as a viable replacement for Oracle Reports. There are still some features missing that are needed to create all of the reports that a major production reporting tool does; but SQL*Reports is closer than any other tool we reviewed to being able to support production reporting.
SQL*Assist, Platinum’s answer to ad-hoc querying, is starting to show its age. But Platinum’s representatives took our feedback positively, and we hope for some big additions to this tool in its next release.
Multidimensional analysis was not a feature offered at this time. But with the reporting success it has achieved, we are confident that Platinum will waste no time in gearing up for the OLAP world. They have recently purchased a new OLAP tool and I look forward to its integration into the product suite.
For the last year, almost all ad hoc development at Platinum has been devoted to Pro*Reports. As a result, even though two years ago SQL*Assist was the best ad hoc tool on the market, it is now behind the major players. However, my conversations with the company leave me cautiously optimistic. It is putting more of the playful functionality of SQL*Assist into Pro*Reports in the next version.
To summarize: The new developments, clarification of the strategy and the new purchase of the OLAP tool should all help to make Platinum a serious contender.
IQ, like Pro*Reports, also offered excellent reporting capabilities. IQ Software recently purchased Scribe as its reporting engine and put a new user interface on the tool. This is not simply an upgrade of IQ’s old ad hoc query tool, but is a new tool with great reporting. I think this was the right move for IQ: Its old text-based report writer had a rough transition to Windows. IQ has great development talent at the company and some of the best thinkers in the industry. Their decision to jump-start a new development effort by purchasing a serious reporting engine and do a first rate GUI interface was a great idea.
The tool is still quite new so it lacks the polish that it should have in the next version. Currently, IQ lacks a particularly clean implementation in the data extraction area. Also, its interface for metadata definition could be easier to use.
For multidimensional analysis, IQ has made the right decision: Buy, not build. It has recently purchased an MDB (multidimensional database) engine which reportedly runs against RDBMSs and some proprietary MDBs, as well as having its own proprietary database. Unfortunately, it had not been integrated in time for us to evaluate. But it looks like it might be a very good addition to IQ’s product line.
If your main interest is a reporting tool, then IQ should
be a serious contender. If you want
reporting right now and do not need great data extraction abilities for a few months,
then IQ could still be considered. I
can’t really comment on how well its multidimensional tool will interface, as
it is a recent acquisition.
Bringing the power of object technology to query and reporting tools, IQ/Objects offers several powerful capabilities. It introduces the concept of object-based reporting where the query designer is a document container that allows users to correlate and integrate results of multiple queries into a single output.
IQ/Objects also includes the ability to execute queries and save the result set for later viewing. The saved output can be saved on the server for sharing. This eliminates the need for 50 users to run the same query on Monday morning, thus saving processor resources and minimizing network traffic.
Using a companion product called IQ/Smart Server,
IQ/Objects allows you to tackle some key requirements of data warehousing. IQ/Smart Server implements a three-tier
architecture allowing the execution of queries on the server, eliminating the
‘fat-client’ syndrome. This reduces
network traffic and facilitates large-scale deployments.
Cognos offers two separate tools to provide reporting solutions for the enterprise. Impromptu is a mature ad hoc query/reporting tool which extracts data from the OLTP. This tool will generate a metalayer automatically, although it will need an administrator’s touch. Impromptu does not read in referential integrity at this time, but is expected to do so within its next release in late February.
Impromptu (along with ESPERANT and Business Objects) was one of the only tools which offered a tool level id and password, thus hiding database login and password from the user. In most organizations, there will likely be multiple query tools on a desktop. Hiding database access from the user protects against users circumventing security by simply accessing the database from another tool.
PowerPlay is Cognos’ answer to multidimensional analysis. Simply create a query in Impromptu, click on the Transformer button on the tool bar, and map the data set to dimensions and measures. With Cognos’ tools, you are simply minutes away from multidimensional analysis. I have seen organizations which formerly walked away from the thought of multidimensional analysis swept away by this tool. The only downside is size: Currently, PowerPlay can only handle approximately 500,000 rows of input. This is a limitation of memory, not performance, though it is unrealistic that a PC geared for ad hoc querying will have more than about 32 to 48 Mb of RAM. In the next release, the client side portion of PowerPlay is projected to handle 200 million rows of input while also offering a server side component which will store up to twenty times more data. This server component has been rumored to be implemented using Oracle blob, though no official announcement has been made.
Cognos’ products were not the overall choice by the team (that honor went to ESPERANT). However, Cognos is a market leader with a solid suite of tools. It has all three pieces (data extraction, reporting, OLAP) at a mature production level.
Two years ago, our analysis determined that Business Objects (BO) was a great ad hoc query tool, and it is still near the top. This is more of a surprise than it appears to be, considering the lack of new development within this time frame. While some tools targeted Business Objects as their competition, BO focused on implementing multidimensional analysis—not a surprising move. What was surprising, however, was that BO chose to build an MDB rather than buy one. As a result, it has spent the last year developing a tool that will not stand up to the competition in its first release.
As a query tool, BO is still a leader, with reporting a strong point. But its most powerful feature is that of administration. This tool actually reads the data dictionary to build joins—an area where most others simply miss the boat. And for the super ‘anti-Windows’ techie, BO has made the SQL available which can be used to build the metalayer, making it possible for the administrator to attach modifications such as DECODE statements to minimize administrative key tapping.
BO can provide more, including the concept of a ‘Universe’ which defines an interest area and protects data from being viewed by users who have no authority . These ‘Universes’ draw an amazing parallel to ‘roles’ (objects defined in Oracle databases) which control access to logically grouped database objects. Unfortunately, BO does not read the data dictionary to create Universes, nor can a user build queries which span across multiple Universes or interest areas.
Universes are used for interest area security. However, they require a great deal of setup. In terms of security, Business Objects also requires a tool level id and password, thereby hiding the database login and password from the user.
My main worry about BO is the underlying architecture. Whereas many tools open cursors to the DBMS and thus fetch the result set of a query only “on demand” and in small chunks, BO requires that the full result set always be returned to the PC. For most users, this does not seem to be a problem. Far more people complain about BO’s disconnecting from and reconnecting to the database between each query. However, users should be aware that if they need the capability to return large amounts of data from their ad hoc queries, then BO may be unsuitable as the delay to return these large result sets before seeing any output can be substantial.
ESPERANT has taken a rebellious approach to ad hoc querying... English! This tool lets the user construct queries using English and/or SQL in addition to using its point-and-click ‘SQL Expert’—a tool which, for example, intelligently hides tables from the selection window which have no join paths to the tables already selected and thus eliminates Cartesian joins. The SQL Expert also protects against other semantic query mistakes, such as trying to aggregate at multiple levels of detail within a single query.
This product also includes one of the strongest SQL generators of any ad hoc tool. It is one of the only tools which generates correlated subqueries—a key feature for handling the complex data filters discussed earlier. It will also generate multi-statement SQL requests (e.g., involving CREATE/DROP VIEW as well as SELECT), allowing it to perform complex queries like numeric comparisons and percentages directly on the server. We looked very hard for problems in the way ESPERANT builds SQL. We were not able to find any problems, other than its interpretation of “customers with 3 or fewer orders” mentioned earlier. As mentioned earlier, Software AG has already corrected this.
The tool’s Administration component will automatically generate a default metadata layer (called a ‘DataView’); like Impromptu, extra effort from the administrator can then greatly improve what users see. Joins are extracted directly from the catalog if ODBC is being used to access the database. One difference from many tools is that ESPERANT can also be used directly against a database (i.e., without a metalayer) if permitted by the administrator. A tool level login is optional for added security.
This tool was the hands-down favorite in our group, and is sure to be a key player in the future of ad hoc querying. Multidimensional analysis is not yet available directly within ESPERANT, though it is Software AG’s long term focus to provide this capability. However, Software AG has a strong cooperative agreement with MicroStrategy under which Software AG resells and provides its own support for MicroStrategy’s DSS Agent (perhaps the strongest relational OLAP tool on the market). ESPERANT, coupled with the products from MicroStrategy, makes for a combination that is as strong as any tool suite on the market.
As was mentioned earlier in this article, ESPERANT represents a radical approach that was enthusiastically embraced by the entire evaluation team. I have to admit that although I think ESPERANT has an interesting approach, I thought it less mature than the other products in some areas. I probably biased the evaluation criteria against ESPERANT because of my stated requirements for having a complete solution (data extraction, reporting and OLAP). Even with all that and my quite vocal criticism of the product’s handling of the query “Customers with 3 or fewer orders”, the team still selected ESPERANT as their tool of choice for ad hoc querying.
Users and systems people alike love this tool. It should be included in every evaluation.
The one standout ad hoc feature Views offered was that of
‘roles’. A role captures a concentration
area for users to view the RDBMS. For example,
a users possessing the ‘Finance’ role could only view
information pertaining to the ‘Finance’ department. These roles are the guidelines upon which SQL
“WHERE” clauses are built to filter out data from the view of non-privileged
users. We must point out that
We do not mean to take any credit away from
ESPERANT was the tool of choice by the team. Its ease of use and revolutionary approach captivated both the technical and non technical evaluators. ESPERANT’s lack of integrated OLAP capability is a serious limitation; but Software AG can deliver MicroStrategy’s DSS Agent to address ad hoc or enterprise-level OLAP needs. (Software AG is also in the process of creating a direct interface from ESPERANT to DSS Agent to make ad hoc OLAP almost transparent.) Another solution would be to use ESPERANT for data extraction and reporting, and then use IQ’s or Cognos’ OLAP tool. Since most OLAP tools are separate anyway (e.g., Impromptu and PowerPlay are totally independent products), using a query/reporting tool from one vendor and an OLAP tool from a different vendor is not an unreasonable solution.
Cognos, IQ, Platinum and Software AG(teamed with MicroStrategy) are vendors whose own products cover all the necessary elements of ad hoc query. Each vendor has unique strengths that make them all serious contenders.
If you primarily need a sophisticated reporting tool, we
suggest either Pro*Reports or IQ.
Business Objects is very good on the ad hoc side but the effort devoted to the development of its OLAP tool has put it behind the other vendors. BO is still a very good product and perhaps the easiest to use, and its users are among the happiest of any in the industry.
This is a very exciting time in
the ad hoc query industry. Data
extraction, reporting and OLAP all have their places in meeting the end user
information access needs of organizations.
Picking the right tool is key to the success of
your data warehousing project. Good
luck!
NOTE: This article was published in 1995. No additional shootout or
update has been done since then.