|
Autumn 1999
|
In the last issue many different methods of data access were discussed. The pros-and-cons can be somewhat overwhelming, making it difficult to choose a method that is right for you from the myriad available. This article will give examples of applications and how they use Universal Data Access. The examples will focus on the application point-of-view, examining the client-side solution chosen and the reasons for that choice.
Ad-hoc
reporting is best used for rapid report generation of a simple nature. The benefit
here is that a reasonably non-technical user with a bit of an idea of where
the data is can easily generate a simple report. Using tools such as Microsoft
Excel, Crystal Reports or Microsoft Access Report Writer a user can quickly
generate a nicely formatted report, graph or spreadsheet without the lengthy
process of requesting that information from the IS department. This in turn
relieves the IS department of having to be reactive to instant demands and the
IS department will be better able to plan their time effectively.
Users may start by importing data and looking at several views of their data. Finally when they have decided on an appropriate format, they print the report. They may additionally save the report for repeated use, possibly weekly or monthly. The apparent ease can be misleading, however, and generally, the less paid for the tool, the easier it is to make an error and get into trouble. For instance, Microsoft Excel’s Query Tool has a default setting (auto-query) that the first time you select a column (attribute) for display, it will pull in all data values for that column. While it is doing this, it cannot be stopped. If a 1 million row table is requested, it can get quite frustrating waiting. If it is not stopped the PC or server may run out of disk and cause a more serious problem.
The current version of Microsoft Office uses Open Database Connectivity (ODBC) through Data Access Objects (DAO) and is not the most efficient method of pulling data into Windows. Future versions of Microsoft Office (Office 2000) will have the ability to access data through ActiveX Data Objects (ADO) as well as the old DAO methodology.
As an extension to the previous, data access is often used to get legacy data into tools that are much better suited to producing graphical output. The impact of a bar chart can be huge, when compared with raw numbers. We are visually triggered creatures. Microsoft Excel is ideal at this type of reporting. Another advantage is that manipulating the data for reports, once it has been imported, is a simple task for a person who is familiar with the product.
Many Data Warehousing tools support ODBC as a method of accessing data. Only a few support Java Database Connectivity (JDBC) and fewer still support OLEdb at this time although the push is on to support OLEdb and eventually OLEdb for OLAP.
The approach here is to produce an off-line copy of a subset of a live database, pre-indexed for high-speed access for reporting and analysis. While these high-speed indexes may be ideal for data analysis, they are often disk intensive and would cause performance problems for an on-line transaction processing (OLTP) system.
There are two aspects of data access in this example. The data must be copied or replicated to the data warehouse and then the data stored in the warehouse must be accessed for processing. As most databases used in data warehousing are relational in nature, such as Microsoft SQL Server and Oracle, ODBC is ideal for copying the data. Accessing data from SQL Server or Oracle has been historically through ODBC, but OLEdb drivers are now available and quickly becoming the preferred method of data access to these databases.
There are several powerful tools available today that will perform an automated analysis of data. These tools will attempt to identify patterns and anomalies in data and provide the end-user with hints of what to look at. It will attempt to answer questions like: “When a person buys widgets, what do they usually buy with them?” The answer to this type of question will often determine what should be put next to widgets in a store aisle, for instance.
These tools are most often used in conjunction with a data warehouse or data mart, since they usually expect to take advantage of high-speed indexes.
Currently the method of access to the database is either ODBC or proprietary.
The OLAP and DSS tools are designed to allow you to browse your data. On Line Analytical Processing (OLAP) is a name for the new, multi-dimensional family of data analysis tools, like Cognos Impromptu, Andyne GQL, Seagate Crystal Info and a growing list of others.
These tools, like data mining tools, require high-speed indexes. OLAP in particular requires a very high-speed, multi-indexed system called a CUBE. Effectively, the cube pre-indexes every possible combination of values. As such, these tools often work best with a data warehouse or data mart.
Generally most OLAP applications have a proprietary database that gets ‘filled’ with data from a relational database. That proprietary database has all the indexing and totaling pre-created for high-speed analysis. A new data access methodology called OLEdb for OLAP was created to allow these analytical programs to access and build their indexes directly within the database such as SQL Server. OLEdb for OLAP is an extension to the OLEdb specification in Universal Data Access.
One of the most popular avenues of data access is through custom applications written in one of many development environments available. Application development tools like PowerSoft PowerBuilder, Microsoft Visual Studio (VB, VC++, etc), Delphi and many others can all make use of most, if not all, of the data access methodologies available today. The tools used to develop custom applications, the databases to be accessed, and the platforms intended to run them on will probably dictate what data access methods will be used.
Ninety-nine percent of all development applications support an ODBC methodology. They can access ODBC either directly through the ODBC Application Programming Interface (API) or through the older DAO method or, with Microsoft’s current development tools, Universal Data Access ADO which simplifies the required coding to access data either through a relational ODBC interface or non-relational OLEdb one.
In
development platforms provided by Microsoft in Visual Studio 6.0 the recommended
method of data access is now ADO and OLEdb or ODBC through the ODBC provider
for OLEdb. In fact, data access in Visual Studio 6.0 is entirely built around
ADO using drag-and-drop design with very little actual coding required as seen
in Figure 2. Views, relationships, stored procedures, and SQL queries can all
be defined easily in Visual Studio. Drag and drop from the data environment
to forms to create parent-child forms with grids. Most other development tools
are going this route also. This will relieve the programmer of a certain amount
of coding, but be wary of proprietary solutions. Open standards like ODBC will
result in a much lower probability of orphaned technology causing problems with
future development of custom applications.
Most Windows applications are now distributed in compressed cabinet files (CAB). These files are usually quite large because they can contain all the programs and libraries necessary for an application. CAB files work well in an Intranet environment where the client platforms and applications can be controlled. CAB files are delivered on an as needed basis and can be quite large requiring a long download time if a high-speed link or internal network is not available. If these restrictions are not inhibiting then creating a component-based ActiveX application, using ADO and OLEdb or ODBC driver to access data, may be the ideal solution.
On the Internet this approach can cause problems. If non-Win32 web clients attempt to access the application it may have some security ramifications. There is also a cost associated with overcoming some of the security problems by providing a signed CAB file, since an expensive Verisign server certificate must be purchased. It also has the disadvantage of requiring access to the internal Intranet and the MultiValue database to be provided to the Internet.
Java AppletsOne approach to making data available over the web is to use Java Applets. A Java applet is a secure program, which can be downloaded automatically by your browser and run over the Internet. This requires no configuration on the client machine, and provides the greatest flexibility since it is a full-blown event-driven GUI-base program. Applying an update to the program is as simple as updating the Java “class” file on your web server. Then next time the client’s browser accesses the data, they download the latest version and run it.
The data access method of choice is Java Database Connectivity (JDBC). JDBC offers all the functionality of ODBC but with the cross-platform abilities of Java. This approach has the disadvantage of requiring you to open up access over the Internet to your MultiValued database much as the CAB based method does.
Web Publishing with ASPActive Server Pages (ASP) is a strategy that is only available to Microsoft Internet Information Server (IIS) 3.0 or later users. It is recommended that you use IIS 4.0 at least if you plan this access. With ASP the data access is generated on the web server and only the results from the data access are provided to the browser.
Using this strategy, you can use ADO with OLEdb or ODBC access for your MultiValued database. Advantages are that you can hide the database access behind a firewall and abstract access to it through your ASP pages. This is a security benefit. On the other hand, you cannot tell when a client is finished with a database connection, so you will typically open the database connection in the Session object. This means that the database connection will stay around until the client has been inactive for too long. If the session timeout is set to 5 minutes, and a client tries to do the next step between other things he is doing, every 6 minutes, he will have the overhead of a new database connection every time he accesses the database. This is quite inefficient. The programming of this environment is quite complex but improving constantly. The preferred development environment is Microsoft Visual InterDev that now provides a VB like environment with interactive debugging capabilities.
Web Publishing Custom Application Programming InterfacesMicrosoft’s Internet Information Server, Netscape’s Web Servers and others provide an application-programming interface (API) that allows for the creation of a “snap-in”. This module can be called from the browser by entering a URL or from a form or applet.
The module is written in C++ or in some cases Java and can access the data directly from the server. This relieves some of the security concerns, as the only machine that needs access to the MultiValue database is the server. Depending on the language and platform the module is being written in many different methods of data access are available. In the Windows environment the entire Universal Data Access solution is available. The module can be written to take advantage of ADO with OLEdb or ODBC or can be written to call the ODBC driver manager directly. With Unix based platforms, Java and JDBC are better solutions.
The Liberty Web Publisher is an example of an Internet Server API (ISAPI) component or snap-in. It can open and cache a number of ODBC connections and communicate with a MultiValue database using the Stored Procedure ability of the ODBC data access method to call BASIC subroutines that return completed web pages to the calling browser.
Today, the MultiValue community has a great deal of choice in the standards based tools and technologies available to them to integrate MultiValue databases with the world of Windows and the Web. Whatever the new project may be, it will most likely have a number of different avenues available. As opposed to a few years back, where the trick was to find a solution that worked, the trick today will be isolating which tools and technologies will best suit the needs of the project and environment. Whichever data access method is chosen, JDBC, ODBC or OLEdb, Liberty has a solution for the MultiValue database.
For more information contact General Automation by phone at 01908 690695 or by e-mail dpeters@genauto.com
Last Updated: 10 December 1999