If you are looking to make your SAS ETL code go faster (and who doesn’t) then with a hat tip to our local SAS office there is a great (but whopping – 18 MB) paper from SAS called ‘ ETL Performance Tuning Tips’.
My favorite part is the bit about ‘MAGIC=102′, classic.
In SAS 9.1 you can’t incrementally load a SAS OLAP Cube, the load job effectively deletes the physical cube files and recreates them.
This is a slight problem if a user is actually viewing the OLAP cubes, as they will be locked and the cube build will fail.
Michelle Wilkies (good kiwi lass by the way) describes in the SAS Support paper How to Update Cubes on an Active SAS® OLAP Server how to go about ‘disconnecting’ the user before the build, but what if the users (persistent little buggers that the are) actually want the information and so keep logging back on.
Well you have some problems.
We thought about stopping the OLAP server, but in our case we only wanted to rebuild (i.e stop access) to some cubes, not all, stop the OLAP server and users can’t access any cube.
We thought about changing security rights on each cube to bock users, but it is time consuming.
We thought about splitting the OLAP Schema’s out into multiple schema’s based on subject area, This way we could change security rights on a single schema, update all the cubes for that subject and wallah.
But the issue is SAS Portal and OLAP Viewer both give nasty errors (rather than nice user friendly ones) if the OLAP Schema is not available.
Screenshot – OLAP Viewer with no Security rights
So the Portal OLAP Viewer shows a blank view (and no the cube doesn’t have dimensions called A and B)
Portal OLAP View – No Security
The OLAP Viewer shows a lovely Java error.
So what happens if you create a second OLAP Server/Service (with two OLAP Schema’s of course) and pause one of those, well the Portal doesn’t show an error, but the OLAP Viewer still does.
Nice blank OLAP Viewer portlet
Screenshot – OLAP Viewer with OLAP Server stopped
But still an ugly Java error in the OLAP Viewer.
So bring on incremental OLAP loads in SAS 9.2 I say!
A SAS stored process is a SAS program that is hosted on a server and described by metadata. One of the major values of stored processes is that they can be consumed by most SAS reporting tools, including SAS Portal, Web Report Studio, Enterprise Guide and the Addin for Microsoft Access.
I have always been a big fan of using them as I believe they are great for creating predefined business rules which users can consume in their reports without having to define any code.
There is an interesting paper at SUGI 31 Making your own Balanced Scorecard using the SAS Information Delivery Portal that describes how a combination of Stored Processes and the SAS Portal can be used to present users with a Balanced Scorecard.
Now if it was me I would have used SAS Strategic Performance Management , but then I hate reinventing the wheel.
But I think the paper shows how with a combination of SAS Portal and Stored Processes, ”there ain’t much you can’t do.
Over at Data Doghouse there is an interesting article about the pervasiveness of software suites vs standalone tools. They use the example of Microsoft Office as proof of its effectiveness in gaining market share, and I have blogged before about how the convergence of Business Intelligence vendors is mirroring that of ERP vendors in the past.
I don’t agree with their statement that Total Cost of Ownership (TCO) is the main reason niche players still flourish. In my experiences with both ERP and BI solutions, TCO is much higher when you have to integrate a number of different tools and technologies. However as most companies don’t track the cost of internal resources on projects (who typically do the integration) let alone TCO, the perception is that cost of these niche solutions is lower (well the license fee’s are anyway).
If we took the cost of replacing the niche products in a couple of years when they are brought out by the the big boys into consideration the TCO would be even worse.
What surprised me though was the positioning of SAS as a niche tools player and not a provider of Data Integration, Business Intelligence and Data Analytics suites.
If they were comparing vendors who had Database, Middleware and Business Intelligence offerings (i.e. Oracle, IBM, Microsoft) to niche players then yes SAS is a nicheplayer. But in terms of BI suites I don’t think so.
Just goes to show SAS is still one of the worlds best kept secret.
When SAS decided that they needed to provide enterprise scheduling capability they partnered with Platform Computing and bundled an OEM version of their LSF product into some of the suites, such as Data Integration Server and Business Intelligence Server (although I believe they are no longer bundled and have to be purchased separately).
Platform LSF provides the relatively unique capability of being able to schedule jobs across Mainframe, Unix and Windows servers (Oracle and Microsoft users will ask why this is needed, but remember SAS has been around for 25+ years, so there is still SAS on mainframes at most large SAS sites)
On the Platform website the marketing team have compiled a very descriptive description:
“Process Manager for SAS is comprehensive job scheduling software that automates the scheduling and execution of SAS jobs. It offers the sophistication of resource virtualization, optimal resource sharing, enterprise scalability and seamless manageability across the entire enterprise. SAS Grid-enabled Products -SAS Enterprise ETL Server -SAS Enterprise Miner -SAS Risk Dimensions”
The nice thing is SAS integrated LSF to talk to the Metadata Server, providing a reasonable level of automation.
Anyway there was an interesting paper called SAS® Scheduling: Getting the Most Out of Your Time and Resources at SUGI 30 in 2005, that outlines what LSF is, how to use it with SAS and even has some code examples.
If you ever need to change the passord for one of the SAS Administrator id’s (i.e. sasadm, sastrust, saswbadm, sassrv, sasdemo etc) then you will find that it is not a simple case of updating the hosts password and metadata passwords.
There are a few more places that you will need to edit.
SAS Tech Support have a document that explains what you need to do – TS736 – Changing Password for Default Users
The doc is orientated towards a solutions based install, but you will need to follow most of the steps, depending on what products you have installed (i.e. Business Intelligence Server, Data Integration Server etc)
As you will see lots of text edits required, but I wont rant about why this shouldn’t be so difficult, here’s hoping SAS 9.2 makes it a little easier…..
