Blogging about all things SAS

I have been spending a lot of time lately working with SAS Strategic Performance Management and the Batch Maintenance Facility (BMF).

The BMF enable you to automatically create SPM Scorecards by uploading .csv files and can automate the creation or amendment of SPM:

• Scorecards
• Elements (i.e metric, goals etc)
• Attributes (i.e Descriptions, Links etc)
• Cells (i.e Actuals, Targets etc)

Importantly BMF utilises the SPM API and loads via the webserver, rather than doing a direct SQL insert into the MySQl backend, like the old speedyloader routines did. This means it is also supported SAS technical support.
Also BMF allows you to create, edit and delete both scorecard structures as well as the scorecard data which means you can use it to prototype your SPM (which is what we are doing) and load data (which we may do, although we may use the standard SPM DI Studio jobs instead, I will post our decision and logic once we have evaluated both)

Its also pretty quick, we are loading 200 scorecards and 1400 elements in under a minute and this is on a windows platform, with limited RAM available.

Interesting enough deleting them via the SPM interface takes upto 10 minutes.

In the background are a couple of SAS Macro’s you will need to install and then call to do the load. There is also some pretty good documentation available to help you get started, but I can’t seem to find a copy on any public SAS website.

So you will have to ask your friendly SAS account manager for the doco if you want to have a go.

I use Enterprise Guide a lot to model data before I expose it to the SAS Metadata Server to create prototype reports using Information Maps and Web Report Studio etc.

Its often said it is ok to make a mistake, but not to make it twice as you haven’t learnt from your mistakes, well here is one mistake that I constantly make when doing the above process (I blame my failing memory due to age).

Enterprise Guide allows you to create new datasets where the variable (column) name has a space in it. The SAS Metadata Server does not like these at all. The problem is it imports the metadata for the dataset fine, its just when you go to view the data all you see is the number of rows, but no columns and no data.

It doesn’t matter if you access the dataset in Enterprise Guide ( via the Metadata Server of course) or use view data in the SAS Management Console, all you see is blank rows.

But drag the exact same table directly into Enterprise Guide and its all hunky dory.

Of course I then spend ages going through metadata security assuming I have read metadata rights but not read data rights and that is whats causing the problem.

Finally I will work out that I had a space in the column name, fix it and wallah. So I thought i’d blog about it just in case anybody else strikes the problem (or I at least remember to check my blog next time).

Oh and if you try and view data in Data Integration Studio you will get this error:

Warning: No rows returned by columns query for table DIM_ORG , no columns registered.
Warning: No rows returned by columns query for table DIM_ORG_OPERATIONS , no columns registered.

(at least one of the SAS interfaces reports that their is actually a problem, yah DI development team!)

Within the SAS Management Console there is a plugin called ‘Configuration Troubleshooter’ whihc has always been blank for me. I thought it was a bit like the ‘Roadmap’ capability in SAS Management Console, i.e a place holder for something that hadn’t been implemented yet (or just never got developed as expected).

But with a hat tip to Barry Stevens at Housing New Zealand, it is a extremely powerful tool and even easier to setup.

So what is the Configuration troubleshooter? Well according to an excellent paper from SAS called

Best Practices for SAS BI Administrators: Using SAS Configuration Troubleshooter to Keep SAS Solutiions and SAS® BI Applications Running Smoothly

it is:

“The Configuration Troubleshooter is a plug-in for SAS® Management Console that is used to query and display the configuration of a SAS Business Intelligence environment. The Configuration Troubleshooter gathers configuration data from three main sources: the SAS® Metadata Repository; Web servers and Web application servers; and configuration files, and displays the data in a window for review and interpretation.”

For me it is a great tool that enables you to investigate what you have got setup, how and where. It also enables you to create your own rules to document and monitor areasthat aren;t in the standard Configuration Triubleshooter setup.

Checkout the SAS paper and have a go.

I blogged earlier on how to purge your SAS Metadata Repository to reclaim the space from deleted metadata objects. Purging the metadata repository removes the deleted metadata objects from the repository.

However as the metadata repository is physically stored in SAS Datasets, you haven’t actually reduced the size of the metadata repository disk storage. To do this you have to run the %OMABAKUP macro with the reorg=yes option.

I have created a three part article that outlines how to do each step (well it has two parts for now as I am still working on part three and will blog it when I have finished)

I was using Enterprise Guide the other day to merge some data into a single table and I needed to generate a unique sequence for a derived column.

I asked around for the best option and was told that using ‘_n_’in a datastep would do i, but of course this would have to be a custom code node, and I was using the Enterprise Guide filter and query wizard, which meant I would have to create a second step within my process flow.

No biggy, but within Oracle I would have just called the Sequence function and all would have been good,

Then our resident SAS Geek (note the removal of Uber) mentioned that I could use ‘Monotonic()’. So I created a new computed column in the query, added Monotonic() as the expression and Bob’s you uncle (or even Aunt these days) EG produced a unique sequence in the new column.

The Monotonic() function is apparently experimental in SAS 9.1. But was mentioned in a SUGI paper : Helpful Undocumented Features in SAS

And according to Wikipedia it is:

“In mathematics, a monotonic function (or monotone function) is a function which preserves the given order.”