Jenny Notestein is an enterprise development consultant in the New York financial district. She can be reached at notestein@msn.com.

Click to open or copy the ARCHITEC project files.

Click to open or copy the PROTO project files.

This is the first of a three-part series on enterprise development in the corporate world using Microsoft¨ Visual Basic 4.0 Enterprise Edition. In this series, we will look at what's involved in building a robust, multitiered client/server application. We'll be developing the architecture and various components of a 32-bit end-to-end data-warehouse-type reporting system that demonstrates scalability, maintainability, and team development through a useful example of a reporting facility that's built on the Services model with a thin client, an SQL Serverª datamart, and fully decoupled, reusable ActiveX servers implementing business logic.

If you're a corporate developer who is relatively new to Visual Basic, you'll want to check out Joshua Trupin's article, "Visual Basic 4.0 Provides Easy Entry to the Advanced Features of Windows 95," MSJ October 1995, and "Exploring the Client/Server Capabilities of Visual Basic 4.0 Enterprise Edition," by Guy Eddon and Henry Eddon, MSJ March 1996.

This series will cover the lifecycle of a typical corporate application, without the meetings. This month we'll focus on application architecture, which first develops from end-user requirements, is then tested through a working prototype, and is then further refined through the demo-and-feedback process, which finally leads into building the actual application components. The second part of this series will focus on the development and implementation phase, including issues such as optimization, pool management, security, and internationalization. The third part of this series will wrap up the implementation and will cover rollout topics such as QA, audit, ongoing maintenance, and enhancement requests.

The Application

In many corporate environments today, data is stored in a wide variety of DBMSs across an equally wide variety of platforms: DB2 on MVS, RDB on VMS, Oracle on Unix, Sybase on NLM, Microsoft SQL Server on Windows NT¨, and on it goes. Information is commonly found across several of these systems, due to years of data accumulation and partitioning across domains. There is a real business need for a generic facility to access this data and produce reports for management.

Using the tools that Visual Basic 4.0 Enterprise Edition provides, we will be building such a generic facility: a data-warehouse-type decision support reporting system for line managers. While the component architecture that we'll be developing can be used to gather data and produce formatted reports for just about any department or function in the business world, we'll use the standard human resources name-and-salary report for our prototype. But keep in mind that the goal of this system is flexibility; it can just as easily hit seven different datasources for a full analysis of what salaries the company is paying Visual Basic developers (broken out by department and shoe size), or it can produce the reports necessary to determine the final cost of some primary product for the company, such as a grilled cheese sandwich, given that the fair market value of its subcomponents (bread, cheese, butter, labor costs, fire suppression system, and so on) are stored on various data servers. But you'll have to try this stuff at home.

OK, assume the problem has been defined (managers need information), and the requirements have been collected (where the information is stored, how the users want it to be presented, and what security mechanisms will be implemented); the next thing to consider is the architecture of the application. Like most aspects of the development process, the architecture will be iteratively determined, meaning it will be refined as we go through several passes of breaking the requirements down into component blocks that are then mapped to software objects. A first pass of the architecture might produce something like Figure 1.

Figure 1 Preliminary Architecture

User Services

The client application will be fairly lightweight, with a 32-bit Visual Basic 4.0 front end through which the user is presented with a listbox of options. The user can choose the desired report, enter any necessary parameters for producing the report in the fields presented, and select the desired presentation format. The entire selection process takes advantage of the popular wizard interface. For our prototype, the presentation format will be either a Microsoft Excel worksheet, a Word document, or a raw SQL statement that will be displayed in a human-readable format by a viewer. The client side is scalable to Internet access using, for example, a Microsoft Internet Explorer 3.0 (IE 3.0) Excel Viewer module. Client workstations will be running Windows NT to take advantage of integrated security.

Business Services

Business services will consist of a middle tier of remote automation servers known collectively as the Report Server. The components of the Report Server consist of one or more virtual data objects, one Request Broker, and one or more Report Builders. They will be written in 32-bit Visual Basic 4.0 and run on Windows NT Server, again using integrated security. They are out-of-process ActiveX servers (EXEs), which allows them to run in separate processes/machines. The business services will also be running Microsoft SQL Server 6.5 as a local datamart staging area for the data pulled from the various remote data repositories and for use by the Report Server components for storing programmatic and configuration information. Scalability can be handled in three ways: first, you could separate the Report Server components and the SQL Server onto separate machines, and, if necessary, separate the ActiveX servers across several machines; second, you could implement multiple instances of the Report Server components; and third, you could implement a second SQL Server for two-tier data cache, also if need dictates. For this prototype, all the servers are running on the same machine.

The Request Broker will monitor the relationship between the client and all of the components of the Report Server. When the client sends a request (Step 1 from Figure 1), this component will interpret the information and send it to the appropriate virtual data object (Step 2), depending on the report selected.

Each virtual data object corresponds to a particular way of looking at a specific set of data that will support a suite of reports. The appropriate virtual data object will receive the information about the report from the Request Broker, such as the parameters entered by the user. Using RDO, the virtual data object will retrieve the appropriate data from the remote data servers and will store it in the local SQL Server data cache (Steps 3-5). The virtual data object will then return control to the Request Broker (Step 6), along with information about where the data is stored in the local data cache.

The Request Broker will send the location of the data to the appropriate report builder (Step 7) depending on the formatting selected by the user. The Report Builder generates the report and places it in the datamart (Step 8). Next, the report builder will send the location of the formatted data to the client through the Request Broker (Steps 9 and 10). The client now communicates with the datamart to display the formatted data (Steps 11 and 12). When the user is done and closes the app, the client disconnects from the Request Broker and sends a message to the SQL Server to drop all cached data that relates to this client session.

Data Services

Data is retrieved from remote data repository servers, which are the above-described wide variety of DBMSs across the enterprise. As shown above, the client requests data retrieval through the virtual data object components of the Report Server, and it stays in the local cache only while the client remains connected to the application.

So here we have a basic system architecture that's maintainable in that it's fully modular; it's designed to be built with ActiveX servers that can be reworked or replaced as reporting requirements change within a department of users. It's also extensible and scalable in that new virtual data objects can be added as the application is rolled out to different departments with different reporting needs, for which remote data servers can be hooked-in or unhooked as necessary. The modular design permits additional hardware to maintain or increase performance as the system grows.

Finally, the architecture is designed for large-scale team development; different teams can write each collection of objects, such as the client, the Request Broker, and the virtual data objects. Other groups would include the report design and layout team.

Building the Prototype

The next step is to build a quick prototype. The prototype phase is an integral part of the development of a solid architecture. A working demo helps the users figure out what they need the application to do now and in the future. For the developers, the prototype lets us test our initial plan and get a more precise definition of how the application's components work together. The sooner these considerations are built into the plans, the more time, money, and nerves will be saved.

When building the prototype, as with any project in Visual Basic 4.0, one of the first things that we'll do is use Visual SourceSafeª. This can saw large blocks off the development time that would otherwise be spent coordinating the ongoing changes to shared code modules. It also saves time by providing the ability to roll back code changes and to track versions, which is important for debugging as well as handling upgrade and installation issues.

When setting up the Visual SourceSafe database for the prototype application files, selecting the "Use network name for automatic user login" option in the 32-bit Administrator will enable Windows NT integrated security (see Figure 2). The project and each module should be checked in as they are created.

Figure 2 Visual SourceSafe Security

The code for the major sections of the prototype is next. For each of the class modules that we'll be creating in the following section, set the instancing property to Creatable, and set MultiUse and the public property to True; this will allow one copy of the ActiveX server to service multiple clients. For the ActiveX server modules, set the startup form to Sub Main and the start mode to ActiveX Server in the Project Options dialog box.

When building the ActiveX server executables, the EXE Options dialog box (see Figure 3) provides three levels of significance for entering version information-major, minor, and revision. This is the data that appears when File Properties is selected in the Windows¨ Explorer. Selecting the Auto Increment checkbox will automatically roll future revisions of the EXE forward.

Figure 3 Version Information

Prototype Code

In the prototype, a simple client interface will give the user a list of reports to choose from (see Figure 4). After selecting a report, the user will click a command button to continue. The code shown in Figure 5 will then be executed, passing the name of the chosen report and an empty requirements object, which has form-level scope, to the Request Broker.

Figure 4 Prototype Client Interface

The request requirements method of the Request Broker, as detailed in Figure 6, receives the report name and the requirements object. It completes the requirements object based on the report name selected. The completed object contains the parameter options from which the user will choose the specific data to build the report on. The requirements class itself is shown in Figure 7.

We'll also implement a callback class that can be instantiated on the client. This is the notification object that we pass to the Request Broker when we request a report, as shown in Figure 8. The Request Broker then invokes a method in the notification object that tells the client that it's finished, as shown in Figure 9.

The prototype should appear to the users to have full functionality, even though some of it may be impossible to create at this point. The solution is to stub out these functions. Figure 10 shows the code for the report object to which we pass in the report requirements and the report name chosen by the user, and which, at this point, passes back a filename for displaying to the user without retrieving actual data.

The Architecture Develops

Now that the prototype is finished, the architecture can be refined (see Figure 11). This view gives us the breakdown of the Visual Basic 4.0 components involved. At runtime, it's designed to work as follows.

Figure 11 Application Architecture, Take Two

The client application, called the Reporter, will send out a request for a list of reports, which is then provided by the Request Broker/Dispatcher ActiveX server and displayed in the client application. The user selects a report and the request goes out to the construction and information requirements function of the Request Broker, which sends back a report build message object. The report build message object has two components, a header and a fields collection. The fields collection is where the action is; it contains the information required to build the wizard pages that the user sees, as shown in Figure 12.

Figure 12 Request Wizard

Operators (=, <, >, and so on) will be displayed for each field, depending on its data type. Each field object contains the information used to construct one page of the wizard, which the user will step through to enter the parameters for the report. The field objects are two-way messages used to dispatch and collect the data from the client. The messaging code is shown in Figure 13. The procedure shown in Figure 14, Display Field Data, walks the fields collection and builds the wizard pages.

While working through the wizard, the user will click on panels of a 32-bit status-bar control that are painted to look like the "Back" and "Next" buttons. Unlike command buttons that provide visual cues to indicate when they have been pushed, the panels in the status bar do not automatically show that they have been selected. I wrote some code (Figure 15) to make the panels look and feel like buttons. The 250-millisecond pause after the DoEvents redraw of the panel allows the pushed effect to be perceived. Figure 16 shows the code for the Panel Click event, which is where most of the work done by the wizard is found.

When the user enters information in the wizard pages, that data is stored back into the field object. After the user has stepped through the entire wizard, the report build message object is then sent back to the Request Broker.

The Request Broker, the code for which is shown in Figure 17, uses the virtual data object to utilize the Microsoft SQL Server data cache to store the metadata on report construction and availability. In its present version the report list and construction requirements are still stubs, much like the prototype, although the construction requirements now incorporate a more elaborate messaging structure. A later version might include a requirements-management editor that will allow the user to enter not only the requirements options for each report, but also the appropriate filter types for each specific requirement.

When the report build message object is returned to the Request Broker after the wizard is completed, the deconstructor dispatcher function of the Request Broker takes the requirements data and determines which virtual data object ActiveX server will need to get the information next to fulfill the request. The deconstructor also generates a simple SQL statement of ANDs and sends it to the virtual data object that corresponds to the report selected. Only ANDs are used; ORs would require allowing the user to force operator precedence (such as parentheses), which would both complicate the UI and demand a higher level of expertise from the user. The AND-only SQL statement is more of a filter at this level than a logical interrelating of the fields.

Each virtual data object needs to have very specific information on how the resultset is to be built for the report for which it is responsible. The appropriate virtual data object will do the hard work of building the complete SQL statement or statements, using whatever unions, joins, or decoding are required. The virtual data object will send the SQL to the appropriate remote data repository server or servers using RDO. This means that the box on which the virtual data objects reside-and not the end users' machines-will need to be configured with ODBC DSNs (datasource names) and the various components required for ODBC connectivity, including drivers, client resource libraries, and net libraries, for all of the diverse DBMSs and platforms on which they reside.

When the data comes back from the remote data repositories, the virtual data object uses part of the Microsoft SQL Server 6.5 data cache as a staging area to construct the resultset. The virtual data object will then send notification to the Request Broker. The Request Broker will receive this notification, which will include the name of the temporary object in the data cache. Using data from the construction and information requirements messaging system classes, the Request Broker will then determine to which Report Builder server the name of the resultset should be passed.

The Report Builders will be a series of ActiveX servers, with each one corresponding to a particular report format, such as Microsoft Word, Microsoft Excel, and so on. The appropriate Report Builder will build the resultset into a formatted report and insert it into the Microsoft SQL Server data cache. It will then notify the Request Broker that it's finished, passing the location of the finished report.

The Request Broker will send notification to the client Reporter with the location of the finished report. The Reporter will retrieve the report from the SQL Server using RDO and will display it on the client workstation, using the client machine's existing copy of Microsoft Excel or Word.

Design Considerations

Data and software distribution tends to have a pyramidal shape, as shown in Figure 18, in two respects. First, in the design phase, it is important to lock in the upstream (towards the servers) API first, building the pyramid from the top down. If changes in the API occur, maintenance becomes more of a nightmare the closer the pyramid is to completion. Second, in the implementation phase, if the API remains constant, then it's best to have the bulk of the complicated work done upstream, where components can be swapped in and out of one place with relative ease. This is why we're resisting the urge to store any information locally on the client, such as in a Microsoft Access database. This application is designed so that the lightweight Reporter that runs on the client might need to be upgraded only once or twice a year. Meanwhile, the middle-tier ActiveX servers, especially the virtual data objects, can multiply with abandon, with little or no distribution difficulty. All the new reports they represent will be immediately available to the clients, since a list of reports is sent downstream to the client each time the application runs.

Figure 18 Data Distribution

Another feature of the application design is data transport. Just about everything that's passed between the ActiveX servers of our application is notification messages, plus a small amount of additional information. There's not a lot of data being marshaled between the out-of-process ActiveX servers, since that would run significantly slower. Instead, we're using the SQL Server data cache to hold the actual data, and we're passing the location of that data between the ActiveX servers.

Although our application gathers data from all over the enterprise, the client Reporter connects with our SQL Server only. This means that the client machines need to be configured with only one ODBC DSN and one extremely small set of ODBC configuration files, since Microsoft SQL Server 6.5 natively supports ODBC. In addition to minimizing the ODBC client components, this design allows our application to use Windows NT integrated security if the clients are running Windows NT Workstation.

A well-designed application is scalable over multiple developers at design time and over multiple machines at runtime. A point of diminishing return will eventually be reached, where the amount of communications overhead will exceed the performance gained by breaking down the components any further.

Conclusion

In the past, a cynical developer could be justified in observing that with Visual C++¨ you can pursue a development path for days before realizing it's just not going to work that way; you have to scrap everything and start over. With Visual Basic 3.0, you'd spend only hours pursuing a development path before realizing that it just wasn't going to work at all.

Visual Basic 4.0 Enterprise Edition is different. With an intuitive language and IDE, with the tools to build reusable classes and ActiveX servers, and with many methods of data access, Visual Basic 4.0 provides the resources necessary to build large-scale corporate applications such as our reporting facility with relative ease.

In the second part of this series, we'll get more into the hardcore component development of our data-warehouse-type reporting system (read: lots more code), plus other exciting topics from the land of corporate client/server development, including optimization, pool management, security, and internationalization.