Actual Sample Documentation Deliverable for a Consulting Engagement

 

Summary 

I spent the week working at the client site to get their SAS platform more fully configured and to get them started on a reporting architecture that suits their needs. We were able to get access to Informix configured and working - although there is still an outstanding tech support issue related to connection issues. The most significant outcome of the engagement was that everyone agreed that Stored Processes and the Stored Process Web Application were essential components to the client reporting platform and should allow for easy integration into the existing Cold-Fusion-developed architecture. Many technical issues relating to configurations and security architecture were discussed and most were resolved. See below for details of the engagement.   

Introduction 

We began Monday morning by discussing the attached draft of an agenda with the client in order to plan for the upcoming week of work. At this time I began to describe in detail how the SAS products work and how they might integrate into the client’s environment. CLIENT began to explain that they are developing a ‘portal’ of sorts to act as a central location for all of CLIENT ‘s reporting needs. The portal is being developed in Cold Fusion and CLIENT was looking for ways to ‘call’ SAS processes from this platform. I then suggested (and CLIENT agreed) that SAS stored processes would provide the best platform for such a deployment. Although SAS Web Report Studio may be useful down the road, it does not come with a portal (within the BI package) and does not provide the ‘deep linking’ necessary for an individual SAS WRS report to be addressed from the Cold Fusion environment. The SAS Stored Process web application does provide such addressability and would allow for the simplest and most effective marriage of the SAS and Cold Fusion environments.  

Informix Issues 

I began the technical part of the engagement by tackling the task of getting the Informix libraries set up on SAS by using the SAS Access to ODBC product, along with an Informix ODBC driver.  Here’s a rough summary of the process and issues: 

·          Identified and de-coupled the Informix ODBC driver installation materials from a CLIENT-developed CD containing and complete application. The ODBC installation materials were then moved to srvsas. 

·          Installed the Informix ODBC driver on srvsas. 

·          Configured an ODBC data source for connections to Informix to be used by any SAS Informix libraries. 

·          Configured several SAS libraries for access to Informix. A separate library was configured for each Informix database owner. This database owner information maps to the ‘schema’ when configuring the SAS library. 

·         After successfully accessing the Informix tables through EG, we discovered that they were inaccessible when using either a Stored Process or accessing an Information map through Web Report Studio. The error message was: ERROR: CLI prepare error: [Informix][Informix ODBC Driver]Connection not open. The problem was reported to SAS tech support under tracking number xxxxxxxx. 

·          I initially got the problem resolved by rebooting the SAS server and changing the ‘server’ parameter on the ODBC configuration to reference the IP address of clientdb (the Informix server). I did this because I was having problems pinging that server by its name. These steps temporarily solved all problems. 

·          After I left the site, Client discovered that the problem had recurred and re-opened the track with SAS tech support. While waiting for a response, CLIENT discovered that if they code an Informix libname statement within a stored process, the Informix data is accessible. This libname-coding is the current work-around to get stored processes to run while a longer term solution is worked out. The down side to this work around is that it de-centralizes SAS library management and still does not fix the WRS problem in accessing Informix-based information maps.

Developing the ‘ProgPercent’ Stored Process 

With my assistance, Client developed a SAS program (ProgPercent) to read an Informix table of CLIENT employee time-keeping information and create a pie chart of the time by program. The chart was developed to allow a date range to be supplied as a parameter. This type of report had been requested by the CLIENT finance director and concisely demonstrates how a user-friendly report can use SAS to directly access Informix data and be rendered in a browser with no special SAS software requirements on the end-users’ desktop. This model can be reproduced for many of CLIENT’s reporting needs. Listed below are some of the steps related to this development: 

·          After doing a bit of research, we discovered that the best way to have the above charting process coded was to make use of the _metauser macro variable. This allows any employ at CLIENT to run a chart of their own (and only their own) time-keeping data. We had started by using the SYSUSER macro for this purpose but realized that it always resolves to SASSRV when running as a Stored Process. 

·          I walked Client through the process of using EG to convert the above SAS program into a Stored Process which could be ran and rendered within EG.

·          We initially had a problem whereby the resulting stored process was unable to recognize any metadata libraries. I verified that the metaautoinit option was specified on the Stored Process Server, restarted the spawner service and this problem went away. 

·          After fine-tuning the charting program, we then made small modifications to allow it run within the SAS Stored Process Web application so that results could be rendered in a browser. We later discussed and implemented how the web-based parameter screens could be displayed in the browser (_action=properties) and how Stored Process without parameters could also be displayed. 

·          We tested the above Stored Process by running it from within the MS-Office Add in.

·          We found documentation that identified how to configure default settings for Stored Processes being run through the SAS Stored Process web application. Settings that may need adjusted include whether or not to display the output in a new window and whether to show the SAS log.

 

Other Issues Discussed or Resolved  

·          Demonstrated Web Report Studio and its interaction with an Information Map. We did not go very deep on this subject because we all agreed that WRS was not a necessary short-term component at CLIENT and we also discovered that there was a problem accessing Informix data through WRS (see above Informix issues). 

·          Discussed how SAS programs in EG could be saved as ‘.sas’ files, placed in .bat file and scheduled for automatic batch processing.

·          Discussed, created and tested an example of how Proc SQL could be coded to insert records into an SQL table. This function could be used as part of larger system to automate the extraction of data from the operational system   to add/modify records contained in a CLIENT SQL/Access based reporting system.

·          Discussed how SAS security could be implemented at CLIENT. We referred to the SAS tech support document ts750 (Securing SAS®9 Business Intelligence Content Managed in Metadata) and agreed to follow the ‘flat structure’ model suggested in this document. We discussed how groups and folders should be used as the building blocks of security (as opposed to individual files and users). We also discussed how most of the organization will only need read access to reports and the only write access necessary would be needed by the development staff. 

·          CLIENT would like to suppress the login box that appears when a user runs a stored process either in a browser or within the MS-Office addin. We referred to several documents and verified that all domain users are set up as users on srvsasweb but the box kept appearing. This is an item for SAS tech support. 

·          Discovered that the SAS/Graph maps have not been installed on the SAS server. I’m not sure why. CLIENT may want to investigate.