Just saw this post on Chris H’s blog here and I am replicating it so I can find it when I need it. (cause half my posts are there so I can find them when I need them)
If you want to know what version of SAS/Access works with what operating system and what release and what database etc, then look here:
http://support.sas.com/matrix/list?SAS=All&Engine=All&OS=All&googleTrack=on
I needed to install the SAS 9.1.3 Clients into a new Citrix server (yes it is unsupported in 9.1.3 but has worked well for the last 4 years, just don’t ask SAS Tech Support for help of course).
After the installs were all complete and we started migrating users from our old Citrix server, they started complaining of issues with DI Studio. These included:
- Nodes missing in the Inventory tree
- Options missing in the custom transform / custom code nodes
Adding the following option to the DI Studio ini file fixed both issues:
-DentityExpansionLimit=1000000
No idea why it fixed it and only reference on support.sas.com is a similar setting for Information Map studio here to fix it not being able to open more than 999 objects.
But as always fix it first, question it later.
If you want to use a Information Map as a data source for tasks such as DI Studio Jobs, Stored Processes, or building OLAP cubes, you can create a SAS Library that points to a folder that contains the Information Maps.
Each Information Map then gets treated as a table in the library.
To do this either use the following SAS code:
libname ImapLib sasioime
user=”username”
pw=”password”
metaserver=”servername”
metaport=8561
metarepository=”Foundation”
mappath=”/BIP Tree/InfoMaps/sales”
Of course you need to add your on environment settings for the variables.
Or you can create it in metadata by creating a libname with:
Issues to be aware of:
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)
I have read a few “how to” and “case Study” books on Data Warehousing over the last few years, and they all pretty much state if the quality of your data is rubbish, then the success of your Data Warehouse will be limited.
However it is often difficult to get an organisation to rectify all the Data Quality issues, before they embark on delivering reports and information to the business users who need it.
One of the interesting sessions at next years SAS Users Conference in New Zealand is by Zeeman van der Merwe who is talking about the work he is doing at ACC. I had the pleasure of meeting Zeeman a while ago and to talk to him about his project and he is definitely taking the recommended approach of sponsorship from the top and covering areas such as Data Governance, Data Stewardship and Data Quality reporting.
One of the Data Warehouse projects we are working on has a sister project dealing with Data Quality. It is fair to say we that we have yet to get the organisation to fully understand the impact data quality has on the business and the necessity to rectify the issues. Everybody does of course agree there are a lot of issues with the quality of the data which is a good start.
I always remember in my presales days at SAS the words customers always uttered “yes we have major data quality issues” shortly followed by “but we don’t have any money to pay to fix them”.
Anyway on this project we are lucky enough to have SAS Enterprise Data Integration Server at our beck and call and so have the ability to use Dataflux on the Warehouse data. So we have done a number of tactical Data Discovery and Data Validation pieces of works.
So far we have completed:
The Phone Number validation was the first one we did and we picked it as it was a discrete piece of work we could time bound, while we worked through the process to use Dataflux. We are now looking to close the loop by updating the augmented phone number data Dataflux produced inot the source system, and changing the business rules in the source system to rectify some data entry issues we identified.
I really recommend the idea of picking something small to start out with.
We are now looking at how we productionise the Data Quality routines into out standard Data Warehouse load and reporting processes. So far the options (in 9.1.3) look like:
Interesting thing to note is that in SAS 9.2 the Dataflux Integration Server component is bundled in eDI so you can just deploy the Dataflux Architect jobs and run them in your Warehouses standard process flows.
We still havent decided whihc option will work the best, but are thinking it is going to be the DI Studio option in the interim as consistency and stability of loads is one of our major focuses.
I have to say I love Dataflux and all that it does (I even believe the Dataflux team now have a stringer presence in the development of SAS Data Integration Server under the “Project Unity” banner).
I note that Dataflux jumped to the top of the Gartner Magic Quadrant in 2009. I always struggle to find this when I need them, so here are the Data Quality ones for 2008 and 2009.
Enterprise Guide 4.2 and Office Addin 4.2 now have the ability to access OLAP cubes that are not registered in SAS Metadata.
Effectively this allows you to use the SAS tools to access non SAS cubes (assuming you have the OLEDB connector installed)
These are called Private OLAP Servers.
Following extract from 051-2009: What’s New in SAS® Add-In 4.2 for Microsoft Office explains it well.
PRIVATE OLAP SERVERS
Also new in 4.2 of the SAS add-in is the ability to define private OLAP servers. A private OLAP server is a direct connection to an OLAP server, rather than choosing one that is defined in metadata. The SAS Add-In for Microsoft Office now supports connecting to third-party OLAP providers, such as Microsoft Analysis Services or SAP BW, or any other vendor that defines an OLE DB compatible provider.
This is useful for users who have already invested in an OLE DB provider for OLAP. Now it is possible to use SAS to view and analyze that data. In SAS Add-In 2.1 for Microsoft Office, this was not allowed; the SAS add-in was able to connect to only a metadata-defined OLAP provider.
Once the user has opened the cube into the PivotTable, it works the same as any other PivotTable. The user has the full breadth of functionality available to them, such as drill-through, and adding calculated measures and members.
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.
Powered by WordPress