SAS Strategic Performance Management
Well finally got confirmation that SPM 5.1/9.2 (now known as StM 5.1) has been released.
Doco is available on the SAS Support site here:
http://support.sas.com/documentation/solutions/performmanagement/stm/index.html
You will need to email your friendly SAS Support team for a password (only if you have a SPM license though)
Had a tipoff from a regular support.sas.com user that there is now a few SPM 5.1 guides and usage notes now on the site.
So is SPM 5.1 released?
I haven’t seen any announcements, but then again I might have missed it.
Here are the docs I have found so far:
http://support.sas.com/documentation/installcenter/en/ikfmofrii/63034/PDF/default/install.pdf,
433KBhttp://support.sas.com/kb/37/794.html,
18KB
Let me know if you know.
Not a lot of blogging lately, because every now and again we get the privilege of doing some custom work for a customer, and we are flat out building a new replacement SPM 2.x portlet for them.
The customer needs the portlet to only show scorecards where values existed for the column sets selected (their scorecard hierarchy is quite volatile)
They also wanted to be able to provide some additional interactivity so we built it all in flash.
Gotta say (sorry flash portlet) that I have a new (portlet) love!
As you may know SPM doesn’t have any published API’s for portlets we could use so we extracted the SPM data into Oracle for this purpose. But I have been toying with creating some code in Mysql that would do exactly what we are dong in Oracle so it will work directly off SPM.
Any way (blowing our own trumpet) here is what the portlet does:
Its so beautiful …..
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.
So as you probably know SPM 2.x requires the use of MySql as the backend database to store all the SPM structure and data.
By default MySql creates a single file on the server for the database. Not bad if you are on Unix but if you are on windows (and with SPM you are probably on Windows) then when SPM gets large the single database file gets large.
Having a single file on windows that is over 2GB is a bad idea (in my opinion anyway and especially if you are on 32 bit windows and of course i you are on SPM 2.x you are on 32 bit windows)
One of the other challenges with MySQL is unlike some other databases it just seems to grow and grow and never reclaims any of its internal space. In fact the only way to reclaim space ans shrink the database file is to export the entire database, delete the database, recreate it and reimport the exported file. Time consuming and fraught with risk.
However there is an answer…. (well a suggestion anyway)
You can tell MySql to create a number of smaller files to effectively partition the database files. How I hear you ask…..
Well in the my.ini for the database added these lines:
innodb_data_home_dir=drive:/dir1/dir2/dir3/ibdata
innodb_data_file_path=ibdata1:2000M;ibdata2:2000M;ibdata3:2000M;ibdata4:2000M;ibdata5:2000M;ibdata6:2000M;ibdata7:2000M;ibdata8:2000M;ibdata9:2000M;ibdata10:2000M;ibdata11:2000M;ibdata12:2000M;ibdata13:2000M;ibdata14:2000M;ibdata15:2000M;ibdata16:2000M:autoextend
innodb_log_group_home_dir=drive:/dir1/dir2/dir3/iblogs
So the first line says where tostore the db files.
The second line says to create 16 2GB files and then automatically extend the last one to infinity (and beyond 
if the MySql database needs to go over 32 GB.
One downside is that MySql will create all the files when it starts up, so you will suddenyl have consumed 32GB of disk. You might want to tune the number of files based on your environment.
Lastly before you do this you will need to backup your MySql instance, stop the Mysql service, remove the old MySql DB files and then restart MySql, which will recreate the DB files with the new partitonedversons automagically.
Then reimport the backup and wahoo you are away (plus ypu have just regorg’d the db and reclaim any lost space as well).
And of course you will ask, yes we asked SAS Tech Support and yes they ‘indicated’ this was not unsupported whichmeans it is supported.
Partition away!
As part of one of our projects we have been rolling out the SAS Strategic Performance Management suite to a large number of users.
Our goal was to deliver scorecards to individual employees to assist with the performance management process.
Its been an interesting roll out so know I have some time I will post some of our experiences over the next few weeks so anybody else that is contemplating a similar rollout can leverage what we learnt.
Out of interest if there are any readers out there that are using SPM let me know.
We are rolling out SPM in anger and as we started approaching a large number of scorecards (> 1,000) we started to notice perfomrnace of the calc’s were not what we desired.
So our talented DI team on the project have created their own DI Studio code to do the rollups across the scorecard hierarchy.
One of the traps for young players was the fact that the linkid’s between scorecard/elements were not a simple parent.child relationship as we had expected.
But once we got the logic for the linkids from SAS Technical Support, we were away laughing (and calc’ing)
Its a pity we cant use the standard SPM calc’s for the rollups, but needs must.
So roll your own if you’ve gottem.
Been struggling with how to trap a divide by zero issue’s in SPM formulas, so we don’t show the dreaded NULL value.
With a little help from SAS support, finally worked out what I need is:
IF((/100))
So basically if the dividing calculation results in an error (i.e one of the dividers is 0 or missing) then put a 0, otherwise do the calc.
I was looking for the latest user guide for SPM 2.4 and clicked in the link here on the SAS Support site and got the message:
"SAS Software Documentation
Documentation for this product is available for customers only. Please contact SAS for details about accessing this documentation.
See your product HELP system for additional information. "
Over on the SAS Support site.
http://support.sas.com/documentation/onlinedoc/spm/
(thanks to the friendly Presales dudes for the pointer)
So ever wanted to know where the Period values iN SPM are actually stored?
Well as long as you aren’t trying to do some dodgy updates to the SPM schema, as you know that is against the rules…
The first trick is that Periods are stored in the SAS Solution Data Mart layer not in SPM itself.
They are stored as a master details record with:
Of course as I have said don’t touch these manually and you should really use the prebuilt SAS Time view to look at them
Recent Comments