Blogging about all things SAS

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.

Following my post about SAS and the free sharepoint webparts SAS have posted a blog with a lot of great details here:

http://blogs.sas.com/sasdummy/index.php?/archives/103-SAS-Web-Parts-for-Microsoft-Sharepoint.html#extended

Enjoy!

And another great Article from BI Keynotes : http://bikeynotes.blogspot.com/2009/04/plateforme-sas92.html

This one outlines the changes in SAS 9.2 (again translated from French by Google not me!)

Out of interest anybody upgraded to 9.2 BI/DI yet?

Any feedback good or bad?

• Support for Java 5, Support for Java 5,
• Replacement of Apache Tomcat 4.1 in JBoss Application Server 4.2, Replacement of Apache Tomcat 4.1 in JBoss Application Server 4.2,
• Availability of a WebDAV server developed by SAS as a substitute for Apache HTTP Server and Xythos WebFile Server: SAS Content Server, Availability of a WebDAV server developed by SAS as a substitute for Apache HTTP Server and Xythos WebFile Server: SAS Content Server,
• Support for virtual environments (VMware, XenSource ,…), Support for virtual environments (VMware, XenSource ,…),
• Full support for Linux (Red Hat / SuSE ),… Full support for Linux (Red Hat / SuSE ),…

Platform Platform

• Electronic Software Download (ESD) Electronic Software Download (ESD)
• Simplification and automation of deployment, Simplification and automation of deployment,
• Reorganization of metadata repositories, Reorganization of metadata repositories,
• Simplifying the management of safety (abandonment of the “multiple inheritance” in favor of the “single inheritance” based on the records), simplification of security management (abandonment of the “multiple inheritance” in favor of the “single inheritance” based on the records),
• Logging standard with SAS 9.2 Enhanced Logging Facility, Logging standard with SAS 9.2 Enhanced Logging Facility
• Improving the promotion partial (formerly supported by the plugin BI Manager): Improving the promotion partial (formerly supported by the plugin BI Manager)
  • New objects supported (OLAP cubes, SEG projects, invites ,…), New media object (OLAP cubes, SEG projects, invites ,…),
  • Improvement of the interface (filters selection, management of dependencies ,…), Improved interface (filters selection, management of dependencies ,…),
  • Executable in batch mode … Executable in batch mode …
• Management of dynamic prompts and cascading, Management of dynamic and cascading prompts
• Enhanced support roles, Improved support roles,
• Simplification of the centralization and personalization of Web applications, … Simplification and centralization of the personalization of Web applications, …

SAS Data Integration Studio 4.2 SAS Data Integration Studio 4.2

• Redesign of the graphical interface, Redesign of the GUI
  • Layout persistent processes, Layout persistent process,
  • Indicators of progress of implementation, Indicators of progress of implementation,
  • Possibility to use more than once a table in a single process, Possibility to use more than once at the same table in a single process,
  • Undo / Redo at will, Undo / Redo at will,
  • Monitoring the execution order of the process … Control the execution order of the process …

• • Control of mappings and their spread, Control mappings and their propagation
  • Debugging and support the execution step by step and partial, Debugging and support of the execution step by step and partial
  • Detailed logging of processes and objects processing, detailed process of logging and processing objects,
  • Documentation automatic … Automatic documentation, …

SAS SAS 9.2 Business Intelligence Business Intelligence 9.2

• SAS Web Report Studio 4.2 SAS Web Report Studio 4.2
  • Improved GUI (AJAX, drag-n-drop, progressive loading ,…), Improved GUI (AJAX, drag-n-drop, progressive loading ,…),
  • Support improved links between reports (Report Linking) Support improved links between reports (Report Linking)
  • Integrated Scheduling of reports (Built-In Scheduler), Integrated scheduling of reports (Built-In Scheduler)
  • Enhanced support prompts, Improved support prompts,
  • Support for improved management of roles, support for improved management of roles,
  • Management of the comments … Management reviews, …
• SAS OLAP Cube Studio 9.2/SAS OLAP Server OLAP Cube Studio SAS OLAP Server 9.2/SAS
  • Improved GUI: copy / move / rename the cubes, you can view tables and cubes, availability of an interface dedicated to MDX permissions, … GUI Improved: copy / move / rename the cubes, you can view tables and cubes, availability of an interface dedicated to MDX permissions, …
  • Possibility of creating a time dimension from a date column, Possibility of creating a time dimension from a date column,
  • Updated incremental ( “in-place” or “managed”), Updated incrementally ( “in-place” or “managed”)
  • OLAPOPERATE procedure to perform the administrative tasks in batch mode … OLAPOPERATE procedure to perform administrative tasks in batch mode …
• SAS Enterprise Guide 4.2 SAS Enterprise Guide 4.2
  • Simplification of the GUI, Simplification of the GUI
  • New tasks: List Report Wizard (procedure REPORT ),… New tasks: List Report Wizard (REPORT procedure ),…
  • Support for improved management of roles, support for improved management of roles,
  • Support for conditional execution (if-then-else ),… Support for conditional execution (if-then-else ),…
• SAS Add-In for Microsoft Office 4.2 SAS Add-In for Microsoft Office 4.2
  • Support for Microsoft Office 2007, Support for Microsoft Office 2007,
  • Possibility to open (display mode) reports SAS Web Report Studio, … Possibility to open (display mode) reports SAS Web Report Studio, …
• SAS BI Dashboard 4.2 SAS BI Dashboard 4.2
  • Availability of two new dynamic indicators (based on Adobe Fash) Availability of two new dynamic indicators (based on Adobe Fash)
  • Support alerts, support alerts
  • Ability to edit the scales of values, Ability to edit the scales of values,
  • Support for passing parameters to each Web Report Studio, Support for passing parameters to each Web Report Studio,
  • Available in the form of a portlet JSR-168, … Available in a Portlet JSR-168, …
• * SAS Table Server 9.2 (new server IOM) Table 9.2 SAS Server (IOM new server)
  • Management of security for access to relational data sources (SAS or other), Management of security for access to relational data sources (or SAS),
  • Support for transactions (delivered with a new SAS data source based on a “fork” Firebird called Vulcan), Support for transactions (delivered with a new SAS data source based on a “fork” Firebird called Vulcan)
  • Engine optimized access to relational data, … Engine optimized access to relational data, …

Great post over at BI Keynotes : http://bikeynotes.blogspot.com/2009/04/sas-web-infrastructure-kit.html

Unfortunately it is in French, so care of google translate, here is a repost.

In SAS 9.1, SAS Information Delivery Portal is the Web application “SAS Portal Web Application Shell. The latter is provided with the component SAS Web Infrastructure Kit and specifically the module SAS Integration Technologies. For information, the module SAS Integration Technologies is the technological base of the SAS 9 platform and is delivered with all the packages based on components of the latter (eg, SAS Data Integration Server or SAS BI Server).

Playing the role of technical base, the Web application “SAS Portal Web Application Shell” loads the full functionality offered by the SAS portal. By default, only basic features are active. To “wake up” all the features in sleep provided by the Web application “SAS Portal Web Application Shell,” the components supplied by SAS Web Infrastructure Kit must be added by installing the SAS portal.

Below is a list of functionalities and features of the Web application “SAS Portal Web Application Shell”:

* Support for single sign-on (Single Sign-On or SSO) to other SAS web applications (eg, SAS Web Report Studio or SAS Web OLAP Viewer for Java).
* Support only applications stored SAS Portlet Collection Portlet “. For information, the portlet “Collection Portlet” open-ended version supports all BI objects and allows, for example, to list the reports SAS Web Report Studio and opened automatically by redirecting users to SAS Web Report Viewer (viewer reports provided with SAS Information Delivery Portal).
* Only administrators (ie members of the Technical Group “Portal Admins”) can create pages (personal or shared) and access the management application preferences. Therefore, an end user will not be a mere consumer of the information published by the directors.
* Full support of the repository and files WebDAV:
o The portlet “WebDAV Graph Portlet” to create charts from data published in XML format in the WebDAV repository,
o The portlet “WebDAV Content Portlet” to restore files WebDAV within portal pages,
o The portlet “WebDAV Repository Navigator” to explore the contents of WebDAV.

Note: Although the documentation indicates the contrary, it is possible to use WebDAV portlets with a server other than Xythos WebDAV Server WebFile (usually Apache HTTP Server or IBM HTTP Server). To function properly, these portlets need a pre-established connection to the WebDAV repository. For more information, please see the comments associated with this article.

In conclusion, if you have a package of the SAS 9 platform (with the exception of SAS Enterprise BI Server, which already includes SAS Information Delivery Portal), you have the component SAS Web Infrastructure Kit and you can deploy a portal SAS “light “no additional cost. Although limited in terms of features, the Web application “SAS Portal Web Application Shell” allows you to centralize user access to various Web applications from SAS.

For more information on the Web application “SAS Portal Web Application Shell” and the existing differences with SAS Information Delivery Portal, see the section “Introduction to the SAS Web Infrastructure Kit” (available on the website of U.S. support SAS) at the following address: http://support.sas.com/rnd/itech/doc9/portal_ov/index.html.

A lot of companies are moving towards centralising SAS on servers and moving uses to Enterprise Guide as the front end.

On e of the issues with this apporach is that users are anable to save or open SAS Datasets on their local PC, because the SAS engine is in the Server.

So you have to copy or ftp the files to the server before you can use them, which is a hassle (or worse if the server is locked down)

While I was downloading the SAS Sharepoint Webparts that Iven provided a link to (thanks Iven) I went for a bit of a browse in the SAS Downloads area to see if there were any new toys.

I stumbled across a new task for EG called:

Upload/Download SAS Data Sets — Tasks for SAS Enterprise Guide

which you can dlownload from here:

http://www.sas.com/apps/demosdownloads/webparts_PROD_1.1_sysdep.jsp?packageID=000563

It solves this issue for you.

Ps. as I typed the word stumbled in this post my head was filled with the words “stumble trip, stumble trip”. If you have small kids then I highly recomend the book “we are going on a bear hunt” , it obvioulsy sticks

Having an issue where our BEA Managed Server is blowing the JVM limit (32bit windows) and so crashed.

Found this and needed to remember it, so excuse the note to self

Formulas for calculating memory
The following formulas give an approximation of how you need to set the arguments. These
formulas are not exact and may need to be adjusted according to your processing needs.

MEM_MAX MAXPERMSIZE <= 4GB RAM

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.

ABCtech (who created Oros and who SAS brought a few years ago) created a tool called Easy API that enables you to automatically submit a Import. Export or Calc in ABM.

The utility is a visual basic script (.vbs) that requires a few parameters in a txt file.

One of those parameters is ABM server, but unfortunately nowhere does it describe what this connection string should be. Well in case you wanted to know this worked for us:

http://hostname/SasSolutions/ABM/Services/AutomationService.asmx

and how could you not know that you would ask …..