Building a Client/Server EIS

Building a Client/Server EIS

By Greg Shanker - SAS Institute,

Inc.

David Hand - Electronic Data

Systems,

Inc.

With the ever-expanding range of SAS® System

client/server capabilities, users have gained greater flexibility in developing their decision support applications. In fact, such a wide variety of configuration and processing alternatives are now available that it may not always be readily apparent which technique is best for a given application. BUilding efficient disnibuted applications reqnires matching available technology with specific business needs. In addition to understanding technical choices, it is crucial to identify user needs and activities in order to anive at an effective systems design. This paper outlines several of the SAS System's facilities for bnilding disnibuted applications. It discusses the computing reqnirements of some common user activities. Finally, an application developed by Electronic

Data

Systems Inc., EDS, is presented to further illustrate efficient implementation of these capabilities.

SAS System ClientlServer Capabilities

Although many definitions exist in the industry today, most people agree that client/server computing involves the use of two or more machines connected over a network for a single application need. Typically, users interact through a graphical interface on their local wOlkstations, with data located in a remote server environment. The efficiency of client/server applications depends on the disnibution of processing between the two systems. Among the SAS System's most widely used facilities for' disnibuted computing are: terminaI emulation, network file servers, SQL based middleware, and SAS/CONNEC~ software.

Terminal Emulation

Terminal emulation is rarely considered a true client/server capability, but it can provide an appropriate architecture for some applications. With termioal emulation, users login directly to a remote processor. All processing is performed on the remote machine, with presentation displayed on the user's local system.,

Character-based terminal emulators emerged with the introduction of personal computers. More recently, graphical emulators have gained popularity due to their ability to provide access to UNIX or other

X

-Windows systems.

X -Windows emulators have become very popular with SAS System users migrating to UNIX environments. UNIX RISe-based systems offer a powerful, cost effective alternative to traditional mainframe systems. UNIX systems are excellent platforms for complex analytical processing against large volumes of data. Many SAS System users are

cutting costs and increasing decision support capabilities by relocating applications to UNIX platforms.

In addition to facilitating the relocation of applications off mainframes, terminal emulation may also serve as a migration tool for moving to true client/server systems. One of the biggest hurdles organizations face in implementing client/server is adopting too many new technologies at the same time. Organizations are often overwhelmed when they attempt to introduce new relational databases, server platforms, network infrastructures, and client tools ... all at the same time. If an organization can proceed in a stepwise fashion-and gain experience with new server platforms-then users can maintain productivity, and the organization can make more infortned decisions about additional system components.

The SAS System's exclusive Multiple Vendor ArchitecturelM helps facilitate a stepwise approach to client/server deployment. Because the SAS System looks and operates the same on a wide variety of platforms, users can develop applications on one platform. and later partition those applications for multiple platforms-without rewriting code, or changing the user interface. '

Network File Servers

Network file servers provide shared external storage for mUltiple LAN workstations. Unlike tenninaI emulation, in which all processing is perfonned on a remote server, processing with file servers is

perfonned on the local workstation. The local system interacts with a file server simply as an external storage device. Data requests are performed through network I/O, rather than against an internal disk drive. Although network I/O is slower than local disk access, high-speed lANs using Ethernet or Token Ring are generally adequate for activities requiring moderate amounts of data.

Users can take advantage of file servers for storing both SAS System programs and data. Most organizations store SAS System executables on a common file server for access by multiple users. Often the entire SAS System is loaded on the file server, with specific executables then loaded on individual work-stations for optimized performance. In addition to storing the SAS System, a file server can also store data. Storing a single copy of read-only data on a file server eliminates the need to create separate copies of the data on each user's individual workstation. Sharing a single copy of data reduces overall storage requirements, improves the ease of maintenance, and helps preserve data integrity. If large volumes of data are stOred on a file server, system perfonnance can be significantly improved by defining indexes on the data. Indexes allow the SAS System to directly locate and access the desired data. If only a subset of data is required for a particular operation, the SAS System can use an index to directly access the desired storage, which can mean a significant reduction in network I/O. Indexes can also improve the performance of local I/O as well. File servers are most appropriate for sharing moderate amounts of data, or when indexing can be used efficiently. If the processing performed on the local workstation requires accessing each record of a large data set, then retrieving this data from a file server will generate a large amount of network traffic. This situation only intensifies when applications make mnltiple passes of the same data, and bring it across the network several times.

SQL Based Middleware

Structured Query Language (SQL) based middleware allows client applications to forward SQL queries to a remote RDBMS, and retrieve the results. Relational databases offer greater functionality and control than simple file servers. Relational databases allow users to retrieve individual records, rather than pages of storage. Databases also support standard SQL functions for performing processing back on the server platfonn. Many popular RDBMSs offer even

greater server processing capabilities through stored procedures, and provide support for user-written procedures. SQL requests can be passed to the RDBMS directly on the same system containing the database, or they can be forwarded across a network with the appropriate middleware.

The SAS System supports RDBMSs through various SASIACCESS® software products. SAS/ACCESS

software establishes the connection with the RDBMS, and manages the commuuications with the database. The SAS System supports two facilities for generating SQL requests passed to the database:

• SASI ACCESS software provides a transparent

interface to many data sources by translating SAS System requests into the appropriate SQL syntax for a particular database. With SAS/ ACCESS software's ACCESS and VIEW Descriptor technology, the SAS System dynamically generates the SQL passed to the database, based on the specific SAS process.

• The SAS System also offers users complete control over the SQL passed to the database with the passthru SQL facility in PROC SQL. SAS/ACCESS software

products can reside on the same system as the RDBMS, or they can reside on a client platform and communicate through SQL-based middleware. This architecture is ideal for activities requiring relatively small amounts of data, such as light query and reporting. SQL requests can be forwarded to a relational database, which can return small subsets of data very efficiently. If the amount of data required by the client application becomes large, however, this computing model quickly breaks down. Networks become overloaded, response time degrades, and under-powered workstations bough down. This problem has been popularly labeled Fat Client Syndrome. It occurs when too much data is passed through the network for processing on client workstations.

SASICONNECT Software

SAS/CONNECT software supports facilities for moving data and forwarding SQL requests to a server platform. It also provides a uuique capability for dynamically distributing application logic to remote systems. SAS/CONNECT software allows the SAS System on one machine to communicate with a SAS process on another machine. The software provides three capabilities for distributed· processing: Data Transfer Services, Remote Library Services, and Remote Computing Services.

Data Transfer Services

Data Transfer Services enable the transfer of data between two machines. SASICONNECT software supports the movement of SAS data sets, SAS catalogs, SAS graphics output, and external files. Movement can be in either direction between machines. When moving data or files between machines with different architectures, SAS/CONNECT software performs all necessary . translations. This includes converting SAS datasets to the appropriate target platform format, as well as recompiling SAS applications.

SAS/CONNECT software also supports subsetting of data during transfers through the use of WHERE clauses. This allows users to move only the desired data in a single operation.

By moving samples or subsets of data to local workstations, Data Transfer Services enable users to off-load processing cycles from remote platforms. Activities such as data analysis, applications development, or report writing--which require several passes of the data-can be performed on fast, inexpensive desktop computers. In addition to making more efficient use of available processing resources, Data Transfer Services also reduce user dependence on shared networlc and server resources. Users can continue to work during temporary networlc or server outages.

Data Transfer Services are also appropriate for many query and reporting activities. As mentioned in the discussion of middleware, some applications only require small amounts of information from a large central database. SAS/CONNECT software's

support for dynamic subsetting of data with the WHERE clause allows applications and users to request specific records from potentially large databases. The Query Window component of

SAS/ASSIST'" software, which provides a graphical

interface for retrieving remote data, uses the Data Transfer Services of SASICONNECT software. Data Transfer Services can also be used to schedule the distribution of data, reports, or programs. Since the SAS System can operate in batch,

SAS/CONNECT software can be used to distribute

or collect data and programs to and from a centralized location. For instance, a batch job on the mainframe can initiate a connection with a PC, then summarized data can be downloaded to a LAN server during the evening.

Remote Library Services

Remote libraiy Services allows a user to assign a libnarne in a local SAS session for data which resides on a remote system. Once the libname is assigned, the remote data appears to the user as a local resource. Requests for data referenced by the remote libnarne are implicitly forwarded to the remote SAS session, which returns the· requested data. The forwarding of requests and the transfer of data takes place transparently, without explicit user action. With Remote library Services the returned data is processed in memory on the local system. A physical copy of the data is not created on the local system. Remote library Services require a peer-to-peer protocol such as APPC, TCPIIP, DECnet, and NETBIOS.

Because processing is performed interactively against a centralized copy of data, Remote library Services enable users to work with the most current version of data, without making separate copies. Operating against a single database also helps maintain the security and integrity of the data. Remote library Services also support locking and updating of remote data, which are necessary for transaction-style applic3tions.

Although Remote library Services surface remote data transparently to end users, the data still must travel through the network. If the application makes several passes of the data, then it may be more efficient to copy the data to the local system. Additionally, if large amounts of data are required, then it may be most efficient to perform the processing back on the server rather than moving the data through the network.

Remote Computing Services

Remote Computing Services allow the SAS System to forward statements to a remote machine for execution. Because the SAS System's MultiVendor Architectore1M insures the same functionality on all platforms, any programs which can operate in a client environment can also be forwarded for execution on the server. Applications can be prototyped in a desktop environment, and later partitioned for execution in a distributed environment. Unlike Stored Procedures, any SAS logic can be relocated to a wide variety of server platforms. Users have tremendous flexibility in confignring their applications for optimal performance.

Remote Computing Services are ideal for computationally complex activities which require

multiple passes of large data files. Moving the application logic to the server is often much more efficient than flooding the network with data. Executing application logic on the server. where the data resides. also helps maintain data security and integrity.

Using SASICONNECT Software Together

with SASIACCESS Software

All of the features of SASICONNECT software described above can also be used in conjunction with SASI ACCESS software on the server platform. SAS/CONNECT software's Data Transfer Services can be used to retrieve subsets of data stored in an RDBMS on a remote platform. Remote Ubrary Services allow users to edit remote relational databases. Most importantly. Remote Computing Services offer tremendous flexibility for splitting processing of relational data between client and server platforms. SAS/CONNECT software offers the same functionality described under SQL-Based Middleware. plus the option for applications partitioning.

SASISHARE Software

SASISHARE® software extends the SAS System's client/server offerings by providing RDBMS like server capabilities. SASISHARE allows the SAS System to operate in a persistent server environment. A SASISHARE server can support multiple connections from both local and remote clients. By managing data requests. the server can allow multiple clients to update the same SAS data files. The

SASISHARE server also supports ANSI SQL, and provides the same functions discussed under SQL Based Middle-ware.

Application Usage

Understanding available technologies is only half the battle. Bnilding efficient applications requires matching available technology with specific business needs. This next section discusses the characteristics of some common business tasks. and offers suggested implementations.

Ad-hoc Query and Reporting

By definition ad-hoc queries are generated at different times. against a variety of data sources. and can request either large or small amounts of data For this type of activity. the architecture for transferring data is probably less important than a data

organization strategy. and the selection of appropriate end user tools. SQL-based middle-ware, or SASICONNECT software's Data Transfer Services or Remote Ubrary Services. all provide excellent facilities for quickly fetching small amounts of data from a remote system. The success of query and reporting systems depends more on selecting tools that match the experience level and needs of the users-and on organizing data for easy access. The SAS System offers a variety of tools and interfaces for query and reporting activities. One of the most popular facilities is the Query Window component of SASI ASSIST software. The Query Window provides a point-and-click interface for generating SQL requests. It generates both SAS System SQL. as well as native SQL for many RDBMSs. Although the Query Window provides a powerful tool for accessing enterprise-wide data, it requires users to know the location and organization of their data The profile facility within the Query Window helps address this issue by restricting the user's environment and by enabling goveming of queries.

For users unfamiliar with the data, a customized interface may be more appropriate. A customized menu-driven application can allow users to select data based on familiar business terms. Users can subset data on the basis of time. geography. product line or other factors ...• without knowing which columns from which tables contain the information they need. or how to code SQL requests. The SAS System's applications development facility. SASI ~ software. provides the ability to create customized interfaces.

In addition to selecting the proper end user tools. organizing data for quick and easy access is crucial for the success of a query and reporting system. The discipline of Data Warehousing offers a strategy for separating operational data from decision support data. By separating these types of data. business users can achieve greater access to valuable information without impacting the performance of on-line transaction processing systems.

Report Generation

Generating reports often requires several iterations to produce the information and format desired. Performing this iterative work on a server system against large data files. or against remote data files. can be cumbersome and expensive. A solution preferred by many users is to move samples or

subsets of data to local workstations in order to create the desired reports. Once the programs which generate the reports are complete, they can later be executed on the server system containing the entire data. Reports can either be saved on the server platform or dynamically forwarded to the server for execution. The SAS System's MultiVendor Architecture insures the portability of SAS programs.

SASICONNECT software's Remote Computing

Services provide the facility for forwarding processing to a remote platform.

Applications Development

The same process described under Report Generation can also be used in developing complete applications. An application can be developed on a local workstation with sample data, and later deployed for execution in a distributed euvironment. The SAS System's portability enables the relocation or partitioning of an application. Portions of an application can be moved to the server environment to improve efficiency. The partitioning of applications is also flexible, and can be modified to tune performance or take advantage of changes in the computing environment.

Data Analysis

Data analysis is often exploratory and can involve large volumes of data Repeatedly pulling large amounts of data across a network should be avoided. Factors to consider in evaluating computing models for data analysis are:

• the amount of data required for the analysis • the computing resources available for the users. If a few analysts need to perform extensive exploration on large amounts of data, then processing directly on a UNIX server containing the required data may be most appropriate. If several users perform occasional analyses, then . the model described above under Report Generation may be effective. Analyzing samples of data on a workstation, and later executing resulting programs in a server environment, may make the most efficient use of available computing resources.

Interactive Reporting Systems (EIS)

Executive Wormation Systems (ElSs) provide on-line interactive reporting capabilities. Although the scope and type of information delivered can vary widely, users expect useful information with adequate response time. Tbe challenge in desigrting

these interactive systems is that tltis useful information is often derived from large amounts of data, which originate on remote platforms. In addition to identifying reporting needs, and developing an interactive front end, designing application flows and staging data are essential for a successful implementation.

A wide variety of reporting options are available with interactive reporting systems. Delivering standard reports through an on-line system can reduce paper flow and provide wider and quicker access to pertinent information. Other systems might offer greater flexibility through parameterized reports. Customized menus can allow users to dynamically subset

data

prior to executing prewrinen reports. The library of reporting components supplied with SASIEIS® software provides even greater flexibility for dynamic data exploration using drill down technology. These facilities can also be used together. The most robust applications generally incorporate several reporting techniques.

The following sections discuss configuration issues for various reporting facilities.

Standard Reports

Standard reports, typically run on a periodic basis against predefined data sources, have interest to a wide audience. Executing these reports on the system containing the data reduces network traffic. Scheduling their execution during off-peak hours also balances system load, and makes better use of available computing resources. Executing these reports once and making them available to many users also saves valuable computing resources, and improves the response time of simply replaying pregenerated reports. Once the reports have been generated, they can be stored on the original server platform-and retrieved by clients on an interactive basi8--{)r they can be distributed closer to client workstations. Storing the completed reports on a LAN server may improve response time.

The server platform where reports are executed can be the system where the data originate, or can be an intermediate decision support server. As discussed earlier, the discipline of Data Warehousing points out advantages of separating decision support activities from operational processing. Many sites have found tremendous cost savings in moving their report processing off mainframes and onto better price/performing UNIX systems.

Parameterized Reports

People often need similar types of infonnation, but may be interested in slightly different views of the data. Parameterized reports allow users to subset data prior to running a report. Customized menus might enable users to subset data based on geography, time, products, or other criteria. Once selecrions are chosen, users can then dynamically execute desired reports.

Depending on the size of the selected data, reports may be executed on client systems or back on the server platform. Small amounts of data can be brought back to a client machine fairly quickly with a variety of transport mechanisms, such as middleware or SASICONNECT software's Data Transfer or Remote Library Services. For larger volumes of data,

SAS/CONNECT software's Remote Computing Services can be used to execute reports back on the server platform. For very large or complex reports, which take several minutes or greater to process, users may want to run these reports asynchronously in a batch operation. SASICONNECT software's Remote Computing Services provide for this option.

Dynamic Drill Down Reports

SASIEIS software supplies a library of prebuilt reporting components for rapid applications development. These components allow for dynamic data exploration through drill down reporting capabilities. Although the components have no physical data size limitations, there are practical limitations as to the amount of data that can be processed interactively. The key to a successful EIS implementation requires surfacing manageable sized files to the client application. One approach is to produce summary files, and move these files to the client environment during a scheduled batch procedure. The client application can then operate efficiently against manageable local data. Altema-tively, a client application might stage local data by dynamically requesting subsets of data. Menu-driven front ends can be developed for identifying the subsets and transferring them to the desktop.

Summary

The SAS System supports several facilities for distributed computing. This wide range of options provides tremendous flexibility in configuring applications for the most efficient use of available computing resources. Choosing the most appropriate techniques requires matching available technologies with specific business needs.

General Motor's Financial Audit

Application

This next section examines an application developed by EDS Inc, for General Motor's Corporation. This application illustrates efficient client/server design, and offers practical programming techniques. The primary focus of this secrion will be on the interaction between the Windows clients and the server, as it pertains to the General Motor's Operational Audit Staff (OAS) Field Auditors (FA) and Data Retrieval Group (DRG).

Background

In 1990, the General Motor's Operational Audit Staff (OAS) began a plan to better utilize resources pertaining to the gathering, reporting and analysis of corporate data for anditing purposes. The OAS has the potential of auditing any GM entity, subsidiary, joint venture or outside supplier for compliance with GM's stated policies and direction. The OAS consists of approximately 150 field auditors located world-wide. These auditors are a mobile work force, stationed at a location ouly for the duration of an audit.

This reporting and analysis may be done using data from any internal GM computer systems or from outside supplier data deemed relevant for the audit The OAS is NOT responsible for the accuracy of the GM Financial Statement, but may create reports varying in nature from Trial Balance Reports to Hourly Payroll Reports to Disbursement Reports. The analysis of these reports may be used to uncover fraudulent activities against the corporation, ensure compliance with Federal Government regulations, or to recommend changes in divisional policy to improve data security and/or integrity.

Phase one of this plan was to centralize the data retrieval function. In 1991 this phase of the plan was implemented with the formation of the Data Retrieval Group (DRG). The DRG has the responsibility of maintaining data files, and assisting the field auditors with reporting and data analysis.

Business Objectives

The OAS's primary goals for this project were to: • provide field auditors with direct data access • deliver parameterized canned reports • enable ad-hoc data analysis and reporting

Providing the OAS field auditors with direct access to data will allow them to more effectively use their auditing expertise to analyze data and form opinions based on this analysis. In addition, the direct access will reduce the Data Retrieval Group (DRG) workload, allowing more effective application of their resources. Finally, since SAS is portable across all platforms the DRG will replace their current, obsolete analysis tool.

In January 1994, a simple PC prototype was developed, utilizing SAS as the front end, and presented to GM Audit Staff upper management for approval. Once approval was attained, the prototype was demonstrated to the field auditors at the January 1994 audit conference. Subsequently, the Audit Staff provided EDS FIA with a list of key deliverables needed to make the project a success. EDS FlA, with assistance from the SAS Institute, developed a client/-server prototype to meet the key requirements.

Configuration

Server Environment

The original server platform for this project was a VAX Open VMS system. This platform was later changed to an RSl6000 running AlX. All development was performed for the VAX environment, and· later ported with minimal modification to the RS/6000. The server plat-form houses both the decision support database, and the master copy of the application source.

Consolidating several diverse data sources was a major objective of this project. Data originates on multiple platforms and in a variety of formats and databases. The DRG stages the data required by the field auditors in a Data Warehouse consisting of SAS data sets. Field Auditors have access to portions of this data based on preferences selected within the application.

A master copy of the production application also resides on the server in a SAS catalog. This catalog contains all FRAME, SCL, SOURCE and other entries used to create the Windows application. Once catalog entries are created or updated, members of the catalog are uploaded to the server using

SAS/CONNECT software.

Individual client PCs also maintain a copy of the application in a SAS catalog. The application provides a mechanism for updating client systems autumatically. The client catalog only contains the

executable code and can uot be edited by the field auditor.

Field Auditors

Field auditors will utilize laptop computers to access the Windows application. General laptop hardware configurations consist of 486/40 using 14.4 PCMClA Modems for communications. Auditors will use a remote access multi-protocol bridge to establish TCPIIP communication with the LAN. After successfully establishing remote communication, auditors will access the application to establish a remote SAS session with the server. The field auditor will have the ability to submit canned and ad-hoc reports interactively (true "client/server") or as batch! background processes which create server files for later downloading or printing. Print capabilities will extend to any Audit Staff server printer, EDS Print center, or to the auditor's local printer.

Data Retrieval Group

In contrast to the field auditors, the DRG is local to the LAN using 486/50 desktop workstations with 16M RAM. The DRG will have all the capabilities outlined for the field auditors, as well as some additional capabilities. The DRG has the responsibility of maintaining data files and canned report programs. All report programs will be maintained at the mainframe and downloaded to the server by simultaneously establishing a remote SAS session with both the mainframe (via EHAlLAPI), and the server (via TCPIIP).

Operation

As mentioned above, the application was developed for two distinct groups of users, each with different responsibilities. In addition, both production and test environments are maintained. This dictates the potential need for several CONFIG.SAS and AUTOEXEC.SAS files. Using the macro variable

-SYSPARM on the command line, enabled the specification of environment information without the maintenance of multiple PC based configuration files. The follOwing sample command line is used to initiate the application.

C:\SAS608\SAS -OMS -CON FIG C:\CNFG.SAS -AUTOEXEC C:\AUTOEXEC.SAS

-SASUSER C:\sAS608\DRIS10\sASUSER -SYSPARM LPFAD

Contents of AUTOEXEC.SAS:

o/oI11acro z_main ;

r

Parse value of SYSPARM 'I

%Iet m_status=%substr(&sysparm,2,1); %Iet m-9roup=%substr(&sysparm,3,3) ; %Iet m_instl=%substr(&sysparm,1,1); %if %upcase(&m_status)=P %then

r

Assign Production environment 'I

%do;

%Iet m--proot = c:\dris10\; %Iet m--pdata = c:\data\dris10\; %Iet m_sroot = lusr/dris1 01 ;

%end; %else

%if %upcase(&m_status)=T %then

r

Assign Test environmnet 'I

%do;

%Iet m--proot = c:\drists\ ; %Iet m--pdata = c:\data\drists\ ; %Iet m_sroot = lusr/drists/ ; %end;

%If %upcase(&m-9roup)=FAD %then

r

Do field audtt specific processing 'I

%do;

dm 'formname sasuser.profile.deskjeUorm' ; options sysprint='lpt1:' ;

%end; %else

%if %upcase(&m-9roup)=DRG %then

r Do DRG specific processing

'I %do; dm 'formname sasuser.profileJaserjetform' ; options sysprint='lpt2:' ; %end; o/oI11end z_main ; o/aZ_main; .•• additional processing;

Initialization

During the client application's irtitialization, a SASICONNECT session is established with the server. Once this connection is made, the application offers the user an opportunity to update the application from the master copy maintained on the server. A time stamp contairting the date of the application's last update is stored in a SAS data set on the local PC. This value is passed to the server, and

all application modifications made since this date are retrieved by the client.

The following sample code is used to update the client's version of the application. This program

creates a temporary transport format file contairting

all application entries which have been modified since the last update of the current client. The no source (nsre) option is used on the CPORT procedure to remove the source code, so users will not be able to modify the application. The transport file is then transferred to the client PC, and imported to the application catalog.

submit remote continue;

filename tranfile '&w_swork&w_tptsfn'; libname ssascat '&w_scatlg';

r

Create transport file wtth no source 'f

proc cport catalog=ssascat.drisfa

run;

file=tranfile after='&w_aftdat'd nsrc

nedit;

r Download transport file in binary 'f

proc download infile=tranfile

outfile=pcimport binary status=no; run; endrsubmit; submit continue;

r

CIMPORTthe downloaded file 'f proc cimport catalog=psascats.drisfa

infile=pcimport nedit;

endsubmit;

Preferences

The field auditor sets numerous preferences which establish their operating environment. A stored list of

all preferences is saved in th,e SASUSER.PROFILE catalog. First and fore-most among these selections is the choice of Audit Location. This selection detennines the data the auditor can process, the output they can list and the printers to which they can send reports.

In addition, the auditor can select from two modes of subntission, interactive or batch. Interactive subntission acts much like the defirtition of true client-sever. A request for data, or a report, is sent to the server and the output is returned to the client. In contrast, batch sub-ntission spawns a server batchlbackground process and creates server output. Batch sub-ntission allows the field auditors to generate reports asynchronously, without having to

wait for the completion of tire report, or retrieval of information across the conununications line. Interactive submission alone proved· too slow and costly for conununications involving phone lines.

Report Selection

The primary function of this application is to provide a variety of parameterized reports for different auditing functions. After the appropriate environ-ment has been selected and a SAS session has been established with the server the auditor is able to select the desired reporting system. Individual reports are available for selection from a drop down list, populated by a SAS data set located locally on the client. The auditor selects the desired report and sets

all appropriate report parameters. The list of avail-able reports, and subsetting criteria are filtered based on the auditor's preference environment settings. Once selections are chosen, the specified parameters are used to create temporary SAS data views. PROC SQL views are used to limit the amount of data processed by the report, without creating an extra copy of the data.

This next example illustrates how data is subsetted and reports are executed remotely, both interactively, and in batch. The actual report programs are stored on the server platform. The client application submits the sub setting logic, and desired report name to the local preview buffer. This submissiou is then retrieved from the preview buffer, and forwarded to the server for interactive or batch execution.

/* Begin setup of the preview buffer for either mode of submission. All & variables are resolved before the submit to the preview buffer occurs.

*'

Submit;

options mautosource sasautos=('&w_spgms') mrecall;

options source2 ;

libname library '&w_scatlg'; libname ssasdata '&k_dirnm'; '%global vloname ;

'%Iet vloname=&k_vlonam; procsql;

create view &vloname as select * from ssasdata.&k_datanm&k_obstxt; endsubmit;

/* Additional methods may be called to subset the data with dynamically created where clauses. The variables and values for the where clause are set using the parameters selected by the user.

*'

1* Append the program name to the code already in the preview buffer Recall that actual program code is located on the server.

*'

Submit ;

O/O&k..j]gmnm; endsubmit;

1* The preview buffer now contains the code to submit in either interactive or batchlbackground mode. The users preferences are evaluated to determine the mode of submission.

*'

1* H the user is in interactive mode submit the code in the preview buffer to the server

*'

if upcase( w_subtyp ) = upcase( c_inter) then do;

submit remote continue; endsubmit;

end; else

1* If the user is in batch mode, copy the code from the preview buffer to a temporary file on the PC

*'

do;

w_rc = filename( 'tempprog', w..j]temp II k_vlonam II c_tmp );

W3C = preview ('FILE', 'tempprog') ; w_rc = preview ( 'clear' ) ;

1* Upload the temporary file to the server

*'

submit remote continue;

filename sasprog

'&w_swork&k_vlonam&c_sas;; proc upload infile=tempprog

run;

outfile::sasprog status=no;

%sysrput m_retcd=&sysinfo ; endsubmit;

w_retcd = symgetn ( 'm_retcd' ) ;

if w_retcd = 0 then do;

/* Build the server command to submit the uploaded code in batchlbackground.

*'

w_batcmd = c_batch II ' 'II w_slogs II

k_vlonam II c_log II ' , II c_sysin II' 'II w_swork II

k_vlonam II c_sas II ' , II c_option ;

1* Utilize the X system call to remote submit the command to run SAS as a batch! background process.

*'

submit remote continue; x '&w_batcmd' ; endsubmit;

Report Retrieval

Output for interactive submissions are stored in text files on the client. Output and logs for batchlbackground requests are stored on the server. Access to these reports and logs are provided using extended tables. All auditors working at the same location have access to the same set of server based reports. These reports can be printed, deleted or downloaded.

Ad-hoc Reporting and Analysis

In addition to prewritten reports, field auditors also have capabilities for ad-hoc reporting and analysis. Field auditors use SASfASSIST along with Remote library Services for customized inquiries. RLS provides transparent access to remote data for local report writing. Once reports are developed on the PC, they can be remote submitted for execution against server data.

If field auditors believe their ad-hoc reports will have value in future audits, they can have them added to the standard reports maintained on the server. The application has been designed to accommodate the addition of reports, and is extremely eas y to extend.

Future Enhancements

SASIEIS software is currently being investigated for adding high level drill down analysis of audit criteria. SASIEIS objects may help audit-ors more quickly locate potential trouble spots. It is anticipated that highly summarized data will be downloaded based on audit location, and analyzed on local PCs.

Conclusion

The SAS System offers several facilities for implementing client/server applications. Choosing the most appropriate technique depends heavily on specific business needs. The SAS System's flexibility and unique capabilities for distributing application logic allow users to create powerful an efficient decision support systems.

References

SASfCONNECT Software: Usage and Reference, Version 6, Second Edition

SASfSHARE Software: Usage and Reference. Version 6, First Edition

SAS Guide to the SQL Procedure: Usage and Reference, Version 6, First Edition

SASfACCESS Interface to ... : Usage and Reference, Version 6, First Edition

Proceedings of the Nineteenth Annual SAS User's Group International Conference, "The SAS System: A Complete Client/Server Solution"