Blogging about all things SAS

I have been trying to work out for ages what the sas_wdetemptable in the spm schema in the Mysql database is there for.

The reason I wanted to know is that over time there seems to be records in there and the table seems to just keep growing, especially in our development environment when we were doing lots of test SPM loads and calcs. When you are adding 10,000 odd rows to SPM every week this table had me almost as concerned as the sas_elementcolumn table.

A track to SAS Tech Support on this came back with nothing should be in there, truncate it.

We are just finishing moving all our SPM loads from our tried and true ‘out-of-the-box’ SAS DI Studio jobs (which no longer works with the latest SAS 9.1.3 Foundation patch), to the new ‘build it yourself’ BMF loads (please don’t ask!)

So I was watching the Mysql database today to see if I could diagnose what was causing a performance issue with the calcs, and finally I think I undertsand one of the uses for the sas_wdetemptable.

So this is what I think happens…..

When you submit the calc, SPM:

1) Reads any rows in the sas_elementcolumn table for the project/period you are calcing for (so in our case 120,000 reads).

2) Writes these records to the sas_wdetemptable (120,000 writes)

3) Deletes the records from the sas_wdetemptable (120,000 deletes)

4) Somehow merges the records in the sas_wdetemptable with the new calc’d value (no idea on this one as I couldn’t see it ) and then inserts them back into the sas_elementcolumn table (120,000 inserts)

5) Deletes these records from the sas_wdetemptable (120,000 writes)

So now I know why the calc takes 40 minutes and the Mysql database seems to get fragmented.

And the reason we had some left, I think it is because every now and again the Web Server or Mysql instance would get stopped mid calc in Dev, and then the records in sas_wdetemptables won’t get deleted (its got me think about whats in, or more importantly not in sas_elementcolumns though when this happens)

Weird thing is the Mysql database is not using a lot of memory or cpu for this work, so need to read up on tuning this beast me thinks.

Setting up SAS Share to enable you to Import and Export ABM models is one of those things that always seems to be a bit tricky to get working.

I found a public doc on the old ABC Tech support site here, that explains the steps. I have also cut and paste the content below in case the doc disappears.

One thing to note is that if you have two SAS servers wanting to talk via this SAS Share link (i.e one with SAS ABM and the other with SAS DI or EG etc) you need to make sure the SAS Share names are identical on both servers or it wont work!

Oh and don’t forget that you need SAS Share OLEDB client on every PC that wants to export/import. You can install this from the SAS ABM Client install page on your ABM Webserver.

(more…)

A while ago we had a major problem as a result of upgrading our Windows servers and operating system, where we started to get a large number of I/O errors when running our batch schedule.

In the end we fixed it by changing the Paged Pool Usage Maximum set from 80% to 40% on the servers as our Windows guru’s worked out that SAS was filling the Windows cache faster than Windows could clear it, causing the I/O errors.

SAS support also suggested we follow this instrauctions in this tech support note:

http://support.sas.com/resources/papers/IOthruSGIO.pdf

If you have struck another fix then let me know.

While researching and testing the default Web Report Studio metadata security I noticed this little trick for young players, which means changes to WRS/WRV Metadata Security for a user are not instantly applied/recognised in WRS/WRV.

Words from the SAS tech support note “Changes to SAS® Web Report Studio role memberships might not immediately be enforced“:

“SAS Web Report Studio analyzes role memberships every 30 minutes. So, for example, if you move a user from the WRS Report Author role to the WRS Report Consumer role, the user will continue to have author privileges until the next time that he logs in after the next role membership analysis is performed. ”

So effectively the WRS/WRV applications are caching the Metadata security to remove the need for the web server to query the Metadata Server each time a user logins.

So try not to be fast when testing your WRS Metadata security changes, else you will think you have done something wrong. And remember to logout and log back in after the cache has been updated.

You can update the LocalProperties.xml file to change the refresh time, and therefore make the changes appear sooner. Details on how to do this are in the SAS Tech Support note.