SAS Portlets, Widgets, Themes and Tutorials for sale

Working with a customer where we have decided to utilise the current Oracle Data Warehouse as much as possible as the source of data for reporting, given the significant investment in the data warehouse over the last few years.

Rather than extract the Oracle data into SAS we were testing using SAS Information Maps and Web Report Studio over the top of the Oracle Star Schema’s.  I have to say I was impressed with the performance of the Web Report Studio reports given the number of layerswe were traversing, i.e

Web Report Studio > Information Map > SAS Workspace Server > Proc Sql >SAS Access to Oracle > Oracle (and back again).

Anyway one issue we found was that we couldn’t apply SAS Date Formats against the Oracle date fields, if we did it would just show ******* instead of a date.

We could use a datepart function to substring the date field and that worked ok, but that could only be applied in the Information Map, not against the Metadata definition which means other tools such as SAS OLAP Cubes and Enterprise Guide etc wouldn’t pick it up.

We tried  changing the Metadata definition for datefields from DATETIME20. to say DATE9. , but then our friends ****** would appear.  Even worse if we reapplied the DATETIME20. format to the metadata definition it set all the dates to 1960.  We had to delete the metadata definition and reimport it to get the correct dates again.

The solution was in the “Usage Note 6413: SASDATEFMT option can be defined globally in the config file or as an environment variable with SAS/ACCESS Interface to Oracle” here:

http://support.sas.com/kb/6/413.html

Although it talks about SAS 8 we applied the following statement to the sasv9.cfg file:

-set SASDATEFMT date

Rebooted the workspace server, deleted and re-imported the Oracle Table Metadata and wallah the Oracle date fields were know registered as Date. and aplpying date formats in the Metadata definition, Information Maps or Enterprise Guide worked the way they should!

As an aside in working through the options we worked out that thwre is a magic number that if you take it away from the numeric Oracle data value you get the numeric SAS date value, whihc makes sense I suppose given they both juts start counting days to manage dates, they just start counting from different points.

On a project I am working on we upgraded our SAS licenses from Data Integration Server to Enterprise Data Integration Server.

We were really impressed with the capabilities of Dataflux, and as we were using SAS DI Studio in anger to populate our warehouse, the idea of integrating the Dataflux rules into our ETL processes also appealed.

We had a demo of Dataflux version 8, which looks sexy and has a lot of great features (which you would expect from one of the top 3 data quality tools in the world).

Imagine our disappointment when we got the CD’s to find we had been shipped Dataflux 7.0, not version 8 that was demo’d.

Simple mistake we thought, so onto to our capable SAS account manager.

Well we were wrong, it seems that Dataflux 7.0 is certified (and works) with DI Studio, Dataflux 7.1 and 8.x will be supported in SAS 9.2.

Tricks for young players I suppose., but then again I am not so young anymore…..

I am sure Dataflux 7.0 and  DI Studio will do what we need, but still slightly disappointing.

Just spent the week attending the SAS Platform Administrator Fast Trackcourse.

I would highly recomend this course if you have to administer SAS9 Servers or just want a better understanding of how all the SAS 9 stuff fits together.

If you ever have to touch security in SAS 9 then you need to do this course!

Course outline is here:  http://support.sas.com/training/us/crs/pafast.html

I will be doing a few posts over the next  week to remind me of the things that I picked up and really want to remember.

Mental note to self…..

There is a  free tool, called Process Explorer, for Windows servers that provides enhanced monitoring of processes that are running on the servers.  Much better than the standard windows taskman or perfmon.

You can  downlaod it from here:

http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx

As I mentioned earlier we are trying to do some testing around performance tuning our queries (well I say we but Evan Wilson is doing all the real work!)

So I have been looking for papers that provide some ideas on how to monitor or tune the SAS environment to make things go faster.

I have posted links and abstracts on any papers I found over on our main website under SAS Forum Papers - Performance  Tuning

Let me know if you know of any papers I have missed.

Mental note to self, if you want to add a new deployment directory to deploy jobs too, for the scheduler then:

Within SAS Management Console right click on the Schedule Manager folder/tree and the select create new deployment directory.

Details are on page 142 of this, http://support.sas.com/documentation/onlinedoc/91pdf/sasdoc_913/mgmtconsole_ug_9952.pdf

Found an interesting paper by John M. Wildenthal of JPMorgan Chase called An Effective CLEANWORK.SAS® for Windows , outlining a way of automatically deleting work files for any SAS processes that have been terminated abnormally.

Abstract of the paper is:

SAS provides an effective utility for deleting orphaned work directories on Unix boxes. But their offering for Windows
merely deletes everything older than a particular time (http://support.sas.com/techsup/unotes/SN/008/008786.html).

This is unworkable for a server environment. This paper discusses a short program for Windows that parses the root
WORK directory and matches directory names to SAS v9 running processes. Directories that are not matched are
then deleted.

We are currently trying to monitor some SAS queries against our new Star Schemas to see if we can make them go faster.

So I have been doing a bit of research to see whats out there to help us do the monitoring.

In my search I stumbled across this article:

 

Monitoring SAS^® through the Web

ABSTRACT
Monitoring SAS processes and SAS work space in a mutli-user environment, such as UNIX, is key in maintaining optimal performance. This paper will present the SAS Monitor, a tool which captures SAS resources and uses SAS to analyze and display this information via a Web browser. SAS Monitor is useful for SAS/UNIX administrators and informative for end users since the information can be widely distributed through an intranet. Even though SAS Monitor has been tested on Solaris, it can be adapted to other flavors of UNIX. This paper will explore and explain some of the techniques used in capturing and publishing SAS resources on the web. 

They also provide example code for this at SAS Monitor - Source Code

 

Unfortunately the code is based upon  Unix and we are running windows. So the search continues….

As an aside we are still building the Star Schema’s using SAS Datasets but we plan to test SAS SPDS vs Oracle as a repository for the Star Schemas at the end of the year to see which performs better.  If one results in any noticable improvement we may move to it, as it will also solve any file locking issues we currently encounter with SAS Datasets.  More on that later.

Great post over on Hexware’s blog  with recommendations on what you should be documenting as part of your BI / Data Warehouse project.

I know of a couple of SAS Data Warehouse sites that are using a Wiki as a collaborative environment for documentation and we are using Mediawiki on a SAS BI/DW project I am working on at the moment.

We are using the Wiki to document all our processes, development standards, business rules and content (when we get time of course).

One of the things I struggle with is how to integrate the technical metadata, that resides in the SAS Metadata Repository, with the business metadata we are storing in the Wiki.

To that end at sasInct we are developing a web service that will interogate the SAS Metadata Repository and then display the results via a Mediawiki plugin.  The goal is to enable the technical metadata to be streamed to the Wiki in realtime and remove the need to copy and paste etc, or maintain two version.

We are also starting to make use of the description fields in Information Maps to provide business metadata to users when running a report, but we haven’t come up with an approach yet of how to populate that from the Wiki, or how to enable a user to click on an object in a report and open the relevant Wiki page with the business metadata.

Feel free to comment if you have done something cool in this space.

I always forget what portlets are installed by default in the SAS Portal (compared to the many I add to test etc)

So quick note for myself to remind me (sourced from Understanding Portlets)

(more…)