Transition Report for
Report Writing Tools Evaluation
NYU Stern School of Business
March 27, 2002
Prepared by
Table of Contents
Section 1 – Business Goals………………………………………………………………….. 2
Section 2 – Requirements………………………………………………………………….. 4
Section 3 – Solutions………………………………………………………………….. 6
MS Access………………………………………………………………….. 7
Crystal Reports………………………………………………………………….. 10
Brio………………………………………………………………….. 14
Cognos Impromptu………………………………………………………………….. 15
Actuate………………………………………………………………….. 17
Acumen………………………………………………………………….. 18

ClearPath………………………………………………………………….. 18

Section 4 – Conclusion………………………………………………………………….. 19

Section 1 - Business Goals

Client Summary

NYU Stern School of Business (Stern School) is one of the top business schools in the United States. As such, Stern School needs to manage a wealth of information related to various aspects of students and staffs’ life such as admissions, registration, bursars, human resources, career development and alumnae involvement.

Historically, Stern School databases were developed independently of each other to streamline a single administrative function such as admissions, HR, students registration, students financial information, executive MBAs, alumnae, and so on. Different technologies and RDBMSs were used for each database.

While Stern School needs to maintain seven distinct databases, each database is comparatively not big. For example, Admission’s DB of Stern has had ¼ million records, student’s DB 300,000 + records, alumnae’s DB are 70,000+ records, and HR DB 1000-2000 records.

The seven databases are Admission DB (SMS – Student Marketing System), EX MBA DB (Executive MBA), Student DB (AIS-Administration Information System), Office of Career Development DB (OCD), Ex Develop DB (Continuing Education), DOTS Alumnae DB and HR DB.


Figure 1:

Current Issues

Currently Stern School faces a few issues that it wants to address:

  • Database access is not uniform. Some reports are implemented using MS Access, some using C, and some using Access and Java.
  • Reports are often received in a hard copy format and subsequently typed into other system. Same information needs to be entered multiple times in several databases.
  • New custom reports take weeks of programming effort and are not re-usable
  • Changes in business rules result in a need to re-write queries that are scattered across many report layers. In effect, reports are scattered in all layers, that makes reports hard to maintain, since many queries have to be adjusted, and they are interconnected.
  • While it is easy to extract data from a single database, it is challenging to extract and relate data from multiple databases simultaneously. Many reports require information from multiple sources.

The customer wants to achieve higher degrees of integration of databases, establish a uniform “Stern way” of reporting, and find a way to encapsulate business logic so that reports become more maintainable and re-usable.

Achieving that will allow Stern to

  • Introduce a common set of tools
  • Save time and money on training
  • Extract, process and present information to end users faster
  • Provide high-level integration of Stern databases by being able to extract and process data from multiple databases.
  • Implement custom reports faster, either by database users or by Stern IS staff.

Project Goal

The goal of the project is to evaluate various report writing tools and to make a recommendation to Stern. The recommended tool must be able to integrate with all the databases and must provide a common standard “Stern way” of reporting.

Section 2 - Requirements

The following is a list of functional and non-functional requirements that we gathered during client interviews.

Functional Requirements

  • Tool must be able to access multiple data sources simultaneously.

Some reports require joining data from several databases. An example of such report: Are there are Stern Alumnae (Alumnae DB) who were recommenders for Stern applicant (Admissions DB) who can participate in a recruiting event (Office of Career Services DB) that I can contact in Merrill Lynch?

  • Tool must be able to join/relate data from different databases based on some key.

Figure 2:


  • If Stern accepts our report writing tool recommendation and deploys the tool, Stern will probably want to migrate most of existing reports to a new tool. Thus our Tool should make it easy to migrate existing reports and should try to minimize migration time.
  • Tool should be able to connect to multiple databases simultaneously using dedicated login names and passwords.

Non Functional Requirements

Tool must be able to extract data from legacy RDBMS such as SQL Server 6.4 and 7.0. It is preferable for report writing tool to natively extract data from Ingress database. However, it is not a firm requirement since there is a workaround. Stern MIS department has a Seagate which can extract data from Ingress and export it into an SQL Server format.

Reports must be generated in real-time: 2-3 minutes at most

Tool must potentially be deployable on the Web

In order to receive a serious consideration, a tool must meet all of the functional requirements and achieve as many as possible of non-functional requirements. It should also be upgradeable and be issued by a developer that has a record of acceptance in the industry. Ideally, the tool will also be easy to use and come at a reasonable cost.

Section 3 – Solutions

After meetings with the Stern MIS department, we have come up with a list of reporting tools that are possible candidates for further evaluation. All of the following tools come from established companies that have a good track record and are likely to provide service, support, and upgrades in the future. The list includes Microsoft Access, Microsoft Visual Studio, Crystal Report, Brio Reports, Cognos Impromptu, Acumen, and ClearPath.

As we have established, the ability of the reporting tool to access and combine data from multiple data sources is the crucial factor in the report writing tool evaluation process. Therefore, as the first step in the selection process we set out to analyze each tool’s database access capability, as well as each tool’s architecture for integrating data from various data sources.

Design, implementation and generation of reports should be done in a user-friendly manner. Therefore, look and feel of the program is important, since users who are not experienced programmers will do many reports.

A detailed analysis of each tool’s database access capability as well as data integration architecture follows.

Database Connectivity Matrix

Native SQL Server connectivity / Non-native SQL Server connectivity / Native Ingres connectivity / Non-native Ingres connectivity
MS Access / X / X / X
MS .NET / X / X / X
VisualStudio / X / X / X
Excel / X / X / X
CrystalReport / X / X
Brio / X / X / X
Impromptu / X / X / X / X
Acumen
ClearPath / X / X

* Non-native connectivity relies on implementations of open database connectivity standards such as ODBC, JDBC, OLE DB.

Notes on ODBC Architecture

In the Open Database Connectivity (ODBC) architecture, an application (such as Access or a Microsoft Visual Basic program) connects to the ODBC Driver Manager, which in turn uses a specific ODBC driver (for example, Microsoft SQL ODBC driver) to connect to a data source such as SQL Server database. Access uses ODBC data sources to connect to data external to Access that does not have built-in drivers.

Open Database Connectivity is a standard through which many different types of data can be accessed by a single application. If a DBMS simply provides a means by which ODBC can access its data, the DBMS data becomes an ODBC data source. Any application that can communicate with ODBC (such as Seagate Crystal Reports) will instantly have access to any ODBC data source.

To connect to these data sources, one must install the appropriate ODBC driver on the computer that contains the data source. Then, he must define a data source name (DSN) by using either the ODBC Data Source Administrator to store the connection information in the Windows registry or a DSN file, or a connect string in Visual Basic code to pass the connection information directly to the ODBC Driver Manager.

Advantages

The biggest advantage to accessing data through ODBC is the ability to access a wide range of data with just one interface.

Disadvantages

Because of the many layers involved in passing data through ODBC from a database to an application, ODBC data sources often take more time to return data than other directly accessed data sources.

MS Access:

Accessing SQL Server: There are two ways for the Access database to access data stored in SQL Server.

The most seamless way is to use the new Microsoft DATA Engine. Microsoft DATA Engine (MSDE) provides an easy way for Access to extract and manipulate data from Microsoft SQL Server. The MSDE interface can be built once and will work for accessing both SQL Server and Access databases.

The second method is to use the Microsoft Access Jet data engine, which also provides capabilities to retrieve and manipulate data from SQL Server as well as abilities for bi-directional data replication between MS Access and SQL Server. Microsoft recommends using the MSDE engine for Access because of its seamless integration with SQL Server.

Accessing Ingres: MS Access can retrieve data from Ingres via drivers implemented with OLE DB standards. OLE DB is a specification for a set of data access interfaces designed to enable a multitude of data stores, of all types and sizes, to work seamlessly together.

Another way of accessing Ingres data from MS Access is to use ODBC drivers. ODBC, just like OLE DB is a generic database access interface. It’s more widely used outside of Microsoft products. There is performance degradation when using these generic database access drivers because the client doesn’t directly interact with the server. Every interaction needs to be brokered through either ODBC or OLE DB drivers.

Usage:

To run a query in MS Access, one needs to select fields from appropriate tables and then refine their relationship in the MS Query window. In order to run queries accessing several databases, the appropriate tables must be present in MS Access file, and such tables must be linked to the tables in external databases. User must utilize the Import or Link Tables commands on the Get External submenu of the File menu. A query can then be constructed as if tables were part of the Access database.

Microsoft Access uses different icons to represent linked tables and tables that are stored in the current database. If the user deletes the icon for a linked table, he or she deletes the link to the table, not the external table itself. Here is an example of MS Access file that accesses dBase and Paradox databases by means of linked tables.

There are several ways to create a new query. A novice user will click on the queries tab and then select Create query by using wizard. He or she will be given a choice of tables or queries from which to select fields for the query. The selection is done via graphical interface in a very intuitive manner. Or, the user may elect to go directly to making a report by using a report wizard. Field selection will be done in the same intuitive way.

Lets assume that most tables come form the same (primary) database. However, we have a table (Table1) that is linked externally to a different database. The primary key in Table1 is ID, which corresponds to primary key in Categories table called CategoryID. When the user attempts to run a query combining these two tables from different databases, MS Access will issue an error message that fields are unrelated and asks user to clarify a relationship. The clarification can be done with a simple point-and-click in the window pictured here.

Such clarified relationship will be saved for future queries and reports. A query such as the one listed below can now be automatically generated. If necessary, the user can manually adjust it.

SELECT [Categories].[CategoryID], [Categories].[CategoryName], [Table1].[ID], [Table1].[Field1]

FROM Table1 INNER JOIN Categories ON [Table1].[ID] =[Categories].[CategoryID];

As we have mentioned above, a user could have chosen to run a report, rather than a query. In this case, the result would look as follows:

Table1
Category ID / Category Name / ID / Field1
1 / Beverages / 1 / 45634
Monday, March 25, 2002 / Page 1 of 1

Note that Category Name comes from the primary database, while Field1 comes from an external database. The matching could be done because we have indicated that ID corresponds to Category ID.

Connectivity

The following is the list provided in the MS Access documentation

Data sources Microsoft Access can import or link

Data source / Version or format supported
Microsoft Access database / 2.0, 7.0/95, 8.0/97, 9.0/2000
Microsoft Access project / 9.0/2000
DBASE / III, III+, IV, and 5
7 (Linking (read/write) requires Borland Database Engine 4.x or later )
Paradox, Paradox for Windows / 3.x, 4.x, and 5.0
8.0 (Linking (read/write) requires Borland Database Engine 4.x or later )
Microsoft Excel spreadsheets / 3.0, 4.0, 5.0, 7.0/95, 8.0/97, and 9.0/2000
Lotus 1-2-3 spreadsheets (Linking is read-only) / .wks, .wk1, .wk3, and .wk4
Microsoft Exchange / All versions
Delimited text files / All character sets
Fixed-width text files / All character sets
HTML / 1.0 (if a list)
2.0, 3.x (if a table or list)
SQL tables, Microsoft Visual FoxPro, and data from other programs and databases that support the ODBC protocol / Visual FoxPro 2.x, 3.0, 5.0, and 6.x (import only)
For an up-to-date list of supported ODBC drivers, see the Microsoft Knowledge Base.

Overall Tool Evaluation: MS Access offer the advantage of being familiar to users. It also provides tools for extensive data manipulation once the report is run. However, it tends to be more cumbersome to set up. A rule of thumb is setting a new, solid, extensive report in MS Access will take a day, including some testing.

MS Visual Studio and .NET: Same as MS Access. Using the same database access platform.

Crystal Reports:

Crystal Reports connects to almost any database available on the market. There are two methods of connecting to a database, Data File and SQL/ODBC. The first method is designed for the simpler, PC-based databases, such as dBASE and MS Access. The other method is to use ODBC to connect to any database that has an ODBC connection. Some of the databases in this group are MS Access, Oracle, Sybase, and MS SQL Server.

Accessing SQL Server: Crystal Reports has built-in capabilities to access SQL Server natively via the DataDirect facility in SQL Server. As specified in the Crystal Reports documentation, the tool has direct access to databases created by Microsoft's SQL Server, versions 6.0 and 6.5. The PDSSQL.DLL file installed with the program translates requests to the SQL server and then returns data from the server. It communicates directly with the Microsoft SQL Server driver MSDBLIB.DLL.

In addition, it also supports OLE DB as well as ODBC drivers that can talk to SQL Server non-natively. With ODBC drivers available for most common DBMS products, the range of data types that Seagate Crystal Reports can use is almost unlimited.

Accessing Ingres: None. It doesn’t appear that Crystal Reports has either native or non-native driver support for communicating with Ingres.

Usage

Crystal Reports works by connecting to one or more databases. Using these connections, Crystal Reports draws in the values from database fields user selects and uses them in the report, either in their original form or as part of a formula that generates more complex values.

As one can see from the exhibit below, Crystal Reports provides easy to use Explorer-like menu that lists all the databases user is connected to. The user can navigate through databases, tables, and fields much the same way as he would navigate through directories and file. When a custom basket of fields is selected, a report can be run.


If linking between primary keys is unclear, the user can use a simple graphic interface similar to that in MS Access to clarify the relationships. In the example here, two tables come from a foreign exchange trading database via ODBC connection, while the other three tables belong to a MS Access database.

Finally a report can be generated by an automatically created SQL query. Of course, additional formatting can be done on the report. Also, the query can be examined and adjusted as necessary. Since Seagate Crystal Reports uses SQL to communicate with ODBC, SQL programmers and Database Administrators can view and edit the SQL statements sent to ODBC, controlling exactly how data is retrieved from the data source.


Connectivity

The following sample list of data sources can access is provided in the Crystal Reports documentation. Of course the list is by no means exhaustive, since any database management system that implements ODBC interface can be accessed by Crystal Reports.

Common direct access database formats / ODBC data sources
Direct Access - dBASE, FoxPro, Clipper / ODBC access – Access
Direct Access –Paradox / ODBC Access – Excel
Direct Access –Microsoft Access / ODBC Access – INFORMIX
Direct Access – Jet / ODBC Access - SQL Databases through INTERSOLV DataDirect
Direct Access – DAO / ODBC Access - INTERSOLV DataDirect Library
Direct Access – Btrieve / ODBC Access - DataDirect Library vs. ODBC
Direct Access - Btrieve DDF files / ODBC Access - DataDirect Library structure
Direct Access – Exchange / ODBC Access - INTERSOLV DataDirect Library databases
Direct Access - ACT! / ODBC Access - Visual FoxPro
Direct Access - Oracle 7 / ODBC Access - Lotus Notes
Direct Access - Microsoft SQL Server 6.x
Direct Access - Sybase System 10/11
Direct Access - Arbor Essbase
Direct Access - NT Event Log

Overall Tool Evaluation: Crystal Reports is a simple to use, effective tool that was designed specifically to work with multiple databases.

Brio Reports

The powerful SQL query engine in Brio Intelligence processes complex queries against a wide array of relational and multi-dimensional data sources - with simple drag-and-drop functionality. With both browser-based and client/server tools, users are able to query data from multiple data sources in a single document - on-the-fly or from pre-built data models.