|
RATCLIFFE Technical Services Limited |
NOTE: |
|
Issue 6. July 2001 |
Hints, tips, and experience for SAS(r) users |
|
|
This month's issue of NOTE: is once again full of information on SAS topics, application development in particular. I hope you find it informative. To date I have published NOTE: monthly, but from this issue I shall be publishing it a little less frequently, and less regularly too. I aim to produce between 4 and 6 issues per year from now on. I appreciate the kind messages of support that I receive from time-to-time and I hope you continue to find the content useful. I have decided not to proceed with the Object-Oriented Programming "workshop" that I had promised in issue 4. I have found it difficult to describe OOP with just words - diagrams are a necessity I believe. I haven't found a satisfactory way of including diagrams in this HTML newsletter - it's fine for those with permanent internet connections, but for those with dial-up connections it can be very troublesome. So, until I find a solution I'll focus on more code-based articles. -Andrew |
|
|
Accessing SAS data from non-SAS clients |
Dare I say it? There are some people who believe that SAS's OLAP front-ends are not as attractive and/or functional as those from some other vendors. There, I've said it, and I shall probably be hounded by SAS's marketing police for evermore! But it's true; I have more than one client who would like to use Business Objects or Brio to access OLAP data stored and maintained in SAS. In one case, it's not that they believe that Business Objects is superior to SAS's options, it is simply that Business Objects is the corporation's chosen business intelligence client and therefore my clients must make every effort to comply. So, what are SAS's product offerings? Well, if you want an off-the-shelf solution there's Enterprise Guide; for more flexibility, i.e. you need to do a little programming or building work, there's SAS/EIS and WEB/EIS; and for a programmed solution you can choose between SAS/AF, WEB/AF, or Enterprise Integration Technologies. All are fine for their designed purpose, but what about my clients who need to use a third-party product to access their SAS data? Well, ODBC might seem an option, but that only provides the business intelligence client with access to individual flat SAS tables, i.e. ODBC does not support access to multi-dimensional data. An independent option is Futrix (www.futrix.com). It is a very powerful and flexible business intelligence tool, but it is still a SAS-based client (even with the web-based interface). The answer lies with Open OLAP Server, part of the SAS/MDDB Server product. Open OLAP Server (OOS) allows access to SAS data from any client software that supports Microsoft's OLE DB for OLAP protocol. OOS is a continuously-running server that can supply SAS data, and metadata about that SAS data, to any client software that is able to issue OLE DB for OLAP (ODBO) commands. Client software that meets these requirements includes MS-Excel 2000, Brio, Cognos PowerPlay, ProClarity, and SAS Enterprise Guide. Whilst the clients are all MS-Windows clients, the SAS OOS server can be running on any one of a variety of platforms including UNIX and MS-Windows. Communication between the client and the server is handled by TCP. Note that Business Objects is not included in my list of clients that support ODBO. But if you read their sales literature you would believe that they do support it. The truth is that they support "optimised" ODBO interfaces for chosen ODBO servers such as MS SQL Server and Oracle. They do not provide support for the true Microsoft ODBO standard!
To use OOS, you need to register your data (data set, MDDB, or HOLAP model) in an EIS metabase, and then add the OLAPMETA attribute to it. Then start the server with AF C=sashelp.opnolap.listener.scl. On the client PC, you must install the freely distributed SAS OOS client software (plus your chosen ODBO client software!). You're now ready to go. With MS-Excel, open a new sheet and select Data->Get External Data->New Database Query. In the Choose Data Source screen, click the OLAP Cubes tab, select <new data source> and click OK. In the Create New Data Source screen, enter a name for your data source, select 'SAS OLE DB for OLAP Provider for SAS/MDDB Server' as your OLAP provider, click Connect and enter your server name, user ID and password and click OK (or just click OK if your server doesn't need a signon, or you're running server software on your client), and select your OLAP cube. If you click OK enough times you'll be returned back to MS-Excel and you will be shown the PivotTable Wizard. Just drag appropriate row headers, column headers, and measures onto the skeleton shown, and your data will be extracted from SAS and shown in MS-Excel. So, with OOS you can now access your SAS data from powerful, attractive, third-party business intelligence clients. Great isn't it? Just what many people have always wanted. But don't rush out and buy the software and spend time setting it all up, because there's a catch. Apart from documented functional limitations, such as no ACLs (Access Control Lists) for security, OOS has fundamental performance and scalability limitations. The OOS is implemented in SAS/AF SCL. This results in performance that is slower than if the server had been implemented in a compiled language.It also results in memory constraints due to the fact that data is held internally in SCL lists. The outcome is that to fully drill and view sashelp.prdmddb3 (a 1.3MB file) requires an additional 80MB of memory. Yes, that's right, it needs 60-times more memory than disk space! Scale that up to a practical size of MDDB and you need to scale-up the memory requirements too. One of my clients managed to fill 1GB of memory in an attempt to partially drill down through a 300MB MDDB. SAS are aware of these limitations but will not be fixing them in the short-term. The server software needs to be re-implemented in a compiled language that uses optimised garbage collection for its memory management. That's not likely to be until several point releases into V9 of SAS. And that's a real shame because the OOS provides a tremendously flexible means of accessing SAS data. Without an industrial strength OOS, access to SAS data is via either off-the-shelf SAS-based products, or through a significant amount of client programming. It is disappointing that SAS chose to release a product that offers so much but fails to deliver in the real world. I've heard reports of some SAS customers successfully accessing large data stores with OOS, but I feel sure they are accessing their data in a restricted way. If you think that OOS ought to satisfy your needs, I recommend that you give it a trial and see for yourself before committing too many resources to the project. And please let me know of your results. In the meantime we must wait for SAS to give priority to the re-implementation of OOS... |
|
|
Using build functions from SCL |
If you're in the habit of using SAS/AF to build tools to help you with your SAS/AF development work, you should know of CALL BUILD. It's documented in OnlineDoc in SAS/AF->SAS Component Language: Reference->SAS Component Language Dictionary->Build. Using CALL BUILD you can compile code without submitting PROC BUILD. |
|
|
Save repetitive interface activities |
Last week I needed to copy several text files into several respective catalog source entries. Given the number of files I didn't want to do them individually by hand. I was reminded of the ability to create macros that perform display manager commands. The resulting five line macro did the job for me, so here it is for you as a little reminder of the flexibility of macros. I was copying specified source entries from c:\sasapps\myapp to the server.cat catalog. Each DM command needs to be bounded by quotes. One of my DM commands needed its own quotes, so I had to make careful use of multiple quotes to be sure they resolved into the desired number. %macro saver(ent=); dm 'clear pgm'; dm "include ""c:\sasapps\myapp\&ent..sas"""; dm "save server.cat.&ent..source"; %mend saver;
%saver(ent=init); %saver(ent=term); %saver(ent=extr01); %saver(ent=extr02);
etc... |
|
|
|
If you use formats a lot you will probably have reached a point where you want to permanently store formats rather than keep including PROC FORMAT in your code to generate the formats afresh in each new SAS session. When you run PROC FORMAT it will, by default, store your formats in the WORK library (in a catalog named FORMATS). You can change this behaviour by adding the LIB= parameter to the PROC FORMAT statement. You can use LIB= to have your formats stored in any library of your choosing. However, if you want SAS to use the formats, you need to understand how SAS searches for formats. It's quite simple: SAS searches the WORK.FORMATS catalog, then the LIBRARY.FORMATS catalog, then it uses the FMTSEARCH option for further catalogs to search. So you can see that you can assign a permanent directory to the LIBRARY libref, and then generate your formats to that libref. If you want your format catalogs to be searched in a different order, you can include WORK and/or LIBRARY in fmtsearch. I believe that use of the LIBRARY libref should be avoided. Libraries containing format catalogs should be added to the format search path by using the FMTSEARCH option. The LIBRARY libref cannot be shared between separate parts of an application if those separate parts want to use different libraries. It is very difficult to create modular applications with single-use resources like the LIBRARY libref. FMTSEARCH is far more flexible. |
|
|
Advertisement |
||
|
Useful SAS-related services and products |
|
|
|
|
If I were not constrained by the need to earn money by working(!), this diary provides a guide to some of the world-wide events I might like to attend. Those marked with an asterisk are those that I will be attending. If you plan to go to any of the listed events, please let me know - I'd be very grateful for any comments after the event. And it would be nice to meet if I am going too. |
|
|
|
August 20th, SUCHI (SAS Users Chicago International), Chicago, USA.* September 25th - 26th, VIEWS, London, England. Premier SAS conference in UK.* October April 2002
May 2002 June 2002 March 2003 |
|
|
|
You can subscribe and unsubscribe by visiting our web site at www.ratcliffe.co.uk. Or, to subscribe just click here and hit the Send button in your email client; and to unsubscribe, just click here and hit the Send button in your email client. Please do not include a message in the body of the email, it will not be seen by a human! Back issues are available on our web site at www.ratcliffe.co.uk. Please send comments to note.editor@ratcliffe.co.uk. NOTE:
(c) 2001 Ratcliffe Technical Services Limited. All
rights reserved. Republication by permission only. You may forward copies only if no fee is involved. Please encourage the recipients of the forwarded copy to subscribe and get their own free copy of NOTE:. Andrew Ratcliffe is a registered partner of SAS UK. NOTE: is a production solely of RTSL and has no affiliation with SAS UK. Andrew is chairman of VIEWS (the UK's independent SAS user group). NOTE: is a production solely of RTSL and has no affiliation with VIEWS. This edition of NOTE: was sent free to 387 subscribers worldwide. This is a valid HTML 4.01 Transitional document |
|