Jenny Notestein
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.
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
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 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 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.
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
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
Figure 5 Prototype Request Requirements
Private Sub btnRequestRequirements_Click()
'Get the request requirements object by telling the Broker
'what report we want
oRequestBroker.RequestRequirements cbolReports.Text,_oRequirements
'The gathering of the requirements is way simple here. A
'future version will have a structured meta information
'methodology that will allow a local engine to generate a
'wizard-like interface for requirements input.
lblDataRequired.Caption = oRequirements.Caption
End Sub
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.
Figure 6 Request Requirements Module
Public Sub RequestRequirements(sReportName As String,
oRequirementsOUT As Object)
'Build the requirements structure for the report requested by the
'client. This will become a much more comprehensive structure
'for use by a client-side wizard.
'Set the Requirements write-once properties
oRequirements.ReportName = sReportName
oRequirements.ReportObjectName = RemoveSpaces(sReportName)
'Simple data structure population. These will be stored in an
'odbc database when the wizard is on the scene
Select Case sReportName
Case asReports(0)
oRequirements.Caption = "No Data Required"
Case asReports(1)
oRequirements.Caption = "Person's Name"
End Select
Set oRequirementsOUT = oRequirements
End Sub
Figure 7 Stub Requirements Class
clsRequirements.cls
Option Explicit
'Internal property values
Private sReportName As String
Private sRequirementCaption As String
Private sRequirementValue As String
Private sReportObjectName As String
Public Property Get ReportName()
ReportName = sReportName
End Property
Public Property Let ReportName(vNewValue As Variant)
'Only let it be set once
If sReportName <> "" Then Exit Property
sReportName = vNewValue
End Property
Public Property Get ReportObjectName()
ReportObjectName = sReportObjectName
End Property
Public Property Let ReportObjectName(vNewValue As Variant)
'Only let it be set once
If sReportObjectName <> "" Then Exit Property
sReportObjectName = vNewValue
End Property
Public Property Get Caption()
Caption = sRequirementCaption
End Property
Public Property Let Caption(vNewValue)
sRequirementCaption = vNewValue
End Property
Public Property Get Value()
Value = sRequirementValue
End Property
Public Property Let Value(vNewValue)
sRequirementValue = vNewValue
End Property
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.
Figure 8 Stub Notification Object
clsNotify.cls
Option Explicit
'Our internal variables are private. They are set by properties
'and methods
Private bFinished As Boolean
Private sReport As String
Public Function SetFinished(sReportIN As String) As Boolean
'This is the function that the Broker calls to let us know
'it is finished, and what the report is
sReport = sReportIN
bFinished = True
End Function
Private Sub Class_Initialize()
'When the class is instantiated, we set default values
bFinished = False
End Sub
Public Property Get Finished()
'This is the property that we keep checking to see if
'the Broker is done.
Finished = bFinished
End Property
Public Property Get Report()
'This is the property that the Request generation form
'will call after finish is set to get the report name
Report = sReport
End Property
Figure 9 Stub Report Request Method
Public Sub ReportRequest(oRequirementsIN As Object,
oNotify As Object)
'Process the request for a report
'Create a report object and class (lest we think there is too
'much stuff going on for what we are doing, let us not forget
'that this is the beginning structure of the app seeing its
'way through the prototype stage)
Dim oReportObject As Object
Dim oReport As New clsReport
'Create the Report requested
Set oReportObject = CreateObject(
oRequirementsIN.ReportObjectName & ".Report")
'Request that the report be generated
oReportObject.Request oRequirementsIN, oReport
'Notify the client we are done, and pass the results
oNotify.SetFinished oReport.FileName
End Sub
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.
Figure 10 Stub Report Object
Report.Cls
Option Explicit
Public Sub Request(oRequirementsIN As Object, oReportIN As Object)
'Processing will take place here (I've the strong urge to say
'"This part is obvious and is left for the student to explore
'at home." hee hee). This will be the heart of the DataMart
'services in the coming articles.
'Result File name ready for viewing
oReportIN.SetFileName "REPORT1.XLS"
End Sub
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.
Figure 13 Messaging Classes
Field.cls
Option Explicit
'External handles
Public Filters As Filters
'Internal variables
Private sName As String
Private bAString As Boolean
Private sDescription As String
Private bRequired As Boolean
Private iSize As Integer
Private vValue As Variant
Private sSelectedFilter As String
Private Sub Class_Initialize()
Set Filters = New Filters
End Sub
Public Property Get AString() As Boolean
'AString because String is a reserved word.
AString = bAString
End Property
Public Property Let AString(bNewValue As Boolean)
bAString = bNewValue
End Property
Public Property Get Description() As String
Description = sDescription
End Property
Public Property Let Description(sNewValue As String)
sDescription = sNewValue
End Property
Public Property Get Required() As Boolean
Required = bRequired
End Property
Public Property Let Required(bNewValue As Boolean)
bRequired = bNewValue
End Property
Public Property Get Size() As Integer
Size = iSize
End Property
Public Property Let Size(iNewValue As Integer)
iSize = iNewValue
End Property
Public Property Get Value() As Variant
Value = vValue
End Property
Public Property Let Value(vNewValue As Variant)
vValue = vNewValue
End Property
Public Property Get SelectedFilter() As String
SelectedFilter = sSelectedFilter
End Property
Public Property Let SelectedFilter(sNewValue As String)
sSelectedFilter = sNewValue
End Property
Public Property Get Name() As String
Name = sName
End Property
Public Property Let Name(sNewValue As String)
sName = sNewValue
End Property
Fields.cls
Option Explicit
'Internal variables
Private coFields As Collection
Public Sub Create(sKey As String)
'Make the name the same as the key
Dim cField As New Field
cField.Name = sKey
coFields.Add Item:=cField, Key:=sKey
End Sub
Public Function Item(vKey As Variant)
Set Item = coFields.Item(vKey)
End Function
Public Function Count() As Integer
Count = coFields.Count
End Function
Private Sub Class_Initialize()
Set coFields = New Collection
End Sub
Filter.cls
'This class contains information on the filter type in question
'Later a rule property can be added that will be used to more
'precisely validate data entry
Option Explicit
'Internal variables
Private sName As String
Private sDescription As String
Public Property Get Name() As String
Name = sName
End Property
Public Property Let Name(sNewValue As String)
sName = sNewValue
End Property
Public Property Get Description() As String
Description = sDescription
End Property
Public Property Let Description(sNewValue As String)
sDescription = sNewValue
End Property
Filters.cls
Option Explicit
'Internal variables
Private coFilters As Collection
Public Sub Create(sKey As String)
'Make the Name the same as the key
Dim cFilter As New Filter
cFilter.Name = sKey
coFilters.Add Item:=cFilter, Key:=sKey
End Sub
Public Function Item(vKey As Variant)
Set Item = coFilters.Item(vKey)
End Function
Public Function Count() As Integer
Count = coFilters.Count
End Function
Private Sub Class_Initialize()
Set coFilters = New Collection
End Sub
Header.cls
Option Explicit
'External Handles
Private sReportID As String
Private sReportName As String
Public Property Get ReportID() As String
ReportID = sReportID
End Property
Public Property Let ReportID(sNewValue As String)
sReportID = sNewValue
End Property
Public Property Get ReportName() As String
ReportName = sReportName
End Property
Public Property Let ReportName(sNewValue As String)
sReportName = sNewValue
End Property
ReportBuildMessage.cls
Option Explicit
'External handles
Public Header As Object
Public Fields As Object
Private Sub Class_Initialize()
Set Header = New Header
Set Fields = New Fields
End Sub
modMain.bas
Option Explicit
Sub Main()
'These classes are in their own project so all programs can use
'them. They are compiled into an OLE.DLL and can be referenced
'in the Tools.References to facilitate early binding.
End Sub
Figure 14 Display Field Data
Private Sub DisplayFieldData(iFieldNumber As Integer)
'Move message classes information into the visual controls
Dim oField As Object
Dim iFilters As Integer
Dim iFoundFilter As Integer
Set oField = oMessage.Fields.Item(iFieldNumber)
iFoundFilter = 0
lblFieldName.Caption = oField.Name
lblFieldDescription.Caption = oField.Description
iFilters = oField.Filters.Count
Dim iLoop As Integer
For iLoop = 1 To iFilters
If iLoop > 1 Then
Load optFilterType(iLoop - 1)
optFilterType(iLoop - 1).Top = _
optFilterType(iLoop - 2).Top + _
optFilterType(iLoop - 2).Height + 100
optFilterType(iLoop - 1).Visible = True
End If
optFilterType(iLoop - 1).Caption = _ oFi
eld.Filters.Item(iLoop).Name
If oField.Filters.Item(iLoop).Name = oField.SelectedFilter
Then iFoundFilter = iLoop - 1
End If
Next iLoop
optFilterType(iFoundFilter).Value = True
txtValue.Top = optFilterType(iLoop - 2).Top + _ _
FilterType(iLoop - 2).Height + 300
txtValue.Visible = True
txtValue.Left = optFilterType(iLoop - 2).Left
txtValue.Text = oField.Value
End Sub
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.
Figure 15 Beveling the Panels
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Panel.Bevel = 1
DoEvents
Sleep 250
Panel.Bevel = 2
Figure 16 Panel Click Event
Private Sub sbMain_PanelClick(ByVal Panel As Panel)
'Handle interaction with the status bar
Select Case Panel.Index
'Back button
Case 2
'Display button action
Panel.Bevel = 1
DoEvents
Sleep 250
Panel.Bevel = 2
'Collect Field Data
CollectFieldData pbMain.Value - 1
'Move, use pb as locator
pbMain.Value = pbMain.Value - 1
'Turn Back off if we just entered the first screen
If pbMain.Value = 1 Then
Panel.Visible = False
pnlFieldData.Visible = False
pnlPickReport.Visible = True
End If
'If pbMain.Value > 1 then
If pbMain.Value > 1 Then
DisplayFieldData pbMain.Value - 1
End If
'Change Finish to Next on backing off last screen
If pbMain.Value = pbMain.Max - 1 Then
sbMain.Panels.Item(3).Text = "Next >>"
End If
'We are in the fields
If pbMain.Value > 1 Then
End If
'Forward button
Case 3
'Display button action
Panel.Bevel = 1
DoEvents
Sleep 250
Panel.Bevel = 2
'Finished
If pbMain.Value = pbMain.Max Then
Exit Sub
End If
'Collect Field Data
If pbMain.Value > 1 Then
CollectFieldData pbMain.Value - 1
End If
'Turn Back on if we just left the first screen
If pbMain.Value = 1 Then
If cbReportList.Text = "" Then
Exit Sub
Else
Set oMessage = Nothing
oRequestBroker.GetReportDescription _
cbReportList.Text, oMessage
pbMain.Max = oMessage.Fields.Count + 1
End If
sbMain.Panels.Item(2).Visible = True
pnlPickReport.Visible = False
pnlFieldData.Visible = True
End If
'Move, use pb as locator
pbMain.Value = pbMain.Value + 1
'If pbMain.Value > 1 then
DisplayFieldData pbMain.Value - 1
'Change Next to Finish on entering last screen
If pbMain.Value = pbMain.Max Then
sbMain.Panels.Item(3).Text = "Finished"
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.
Figure 17 Request Broker
Broker.cls
Option Explicit
Public Sub GetReportsList(asReportList() As String)
'Test Harness/Stub for Reporter to develop against.
ReDim asReportList(0) As String
asReportList(0) = "How much are we paying for an Employee..."
End Sub
Public Sub GetReportDescription(sName As String, oMessage As
ReportBuildMessage)
'Test Harness/Stub for Reporter to develop against.
PopulateDummyData oMessage
End Sub
Private Sub PopulateDummyData(oMessage As ReportBuildMessage)
'Test Harness/Stub for Reporter to develop against.
With oMessage
.Header.ReportName = "The Test Report"
.Header.ReportID = 819
.Fields.Create "Employee"
With .Fields.Item("Employee")
.Name = "Employee"
.AString = True
.Description = "Employee's Name"
.Required = True
.Size = 35
.Value = ""
.SelectedFilter = "= (Equal)"
.Filters.Create "= (Equal)"
With .Filters.Item("= (Equal)")
.Name = "= (Equal)"
.Description = "A Value that is Equal " & _
"the entered value."
End With
.Filters.Create "Like"
With .Filters.Item("Like")
.Name = "Like"
.Description = "A Value that is similar" & _
" to the entered value. Wild " & _
"cards can be used."
End With
End With
.Fields.Create "Total Yearly Compensation"
With .Fields.Item("Total Yearly Compensation")
.Name = "Total Yearly Compensation"
.AString = True
.Description = "The amount that the Employee " & _
"receives in a year as total compensation"
.Required = False
.Size = 6
.Value = "25000"
.SelectedFilter = ""
.Filters.Create "= (Equal)"
With .Filters.Item("= (Equal)")
.Name = "= (Equal)"
.Description = "A Value that is Equal " & _
"to the entered value."
End With
.Filters.Create "< (Less Than)"
With .Filters.Item("< (Less Than)")
.Name = "< (Less Than)"
.Description = "A Value that is Less " & _
"Than the entered value."
End With
.Filters.Create "> (Greater Than)"
With .Filters.Item("> (Greater Than)")
.Name = "> (Greater Than)"
.Description = "A Value that is Greater" & _
" Than the entered value."
End With
End With
.Fields.Create "Job Description"
With .Fields.Item("Job Description")
.Name = "Job Description"
.AString = True
.Description = "Employee's Name"
.Required = True
.Size = 255
.Value = "Building machines that go PIIING!"
.SelectedFilter = "Like"
.Filters.Create "= (Equal)"
With .Filters.Item("= (Equal)")
.Name = "= (Equal)"
.Description = "A Value that is Equal " & _
"to the entered value."
End With
.Filters.Create "Like"
With .Filters.Item("Like")
.Name = "Like"
.Description = "A Value that is similar" & _
" to the entered value. Wild cards" & _
" can be used."
End With
End With
End With
End Sub
modMain.bas
Option Explicit Sub Main() End Sub
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.
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.
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.
This article is reproduced from Microsoft Systems Journal. Copyright © 1995 by Miller Freeman, Inc. All rights are reserved. No part of this article may be reproduced in any fashion (except in brief quotations used in critical articles and reviews) without the prior consent of Miller Freeman.
To contact Miller Freeman regarding subscription information, call (800) 666-1084 in the U.S., or (303) 447-9330 in all other countries. For other inquiries, call (415) 358-9500.