Blogging about all things SAS

February 14, 2010

DI Studio – Missing Nodes – DentityExpansionLimit=1000000

Filed under: DI Studio, SAS 9.1 — Tags: , — Shane Gibson @ 8:35 pm

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.

December 18, 2009

SAS Strategy Management (StM) 5.1 is out (formerly know as SPM)

Filed under: SAS 9.2, SPM — Tags: , , , — Shane Gibson @ 10:09 pm

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)

December 17, 2009

Jigging with Dataflux

Filed under: Dataflux — Tags: , , — Shane Gibson @ 10:03 pm

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:

  • Validation of Phone Numbers
  • Validation of Addresses
  • Customer/Person matching

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:

  • Purchase the full use Dataflux Integration Server
  • Schedule Dataflux routines to run on a PC
  • Manually run the jobs
  • Rewrite the Dataflux jobs in SAS DI Studio

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.

Gartner 2008 Data Quality Magic Quadrant

Gartner 2009 Data Quality Magic Quadrant

December 2, 2009

SAS 9.2 Private OLAP Server (playing nice with others and their toys)

Filed under: Addin for Office, Enterprise Guide, SAS 9.2 — Tags: , , — Shane Gibson @ 11:55 am

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.

 

 

 

 

 

November 27, 2009

Is SPM 5.1 / 9.2 here?

Filed under: SAS 9.2, SAS Solutions, SPM — Tags: , , — Shane Gibson @ 9:47 pm

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:

Let me know if you know.

November 5, 2009

Been busy building a new SPM Portlet

Filed under: SPM — Shane Gibson @ 10:16 pm

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:

  • Provide table and aggregated view in a single portlet as a edit portlet choice.
  • Allows the users to tailor the portlet via flash based option pull down bar, even when published, including
    • Scorecard via a hierarchy tree
    • Measure group
    • Column Set
    • Date
    • Table vs aggregate
  • Search for a scorecard based on its name
  • Allows users to click on a measure and see the scorecards/measures below that make that value up
  • Allows users to drag and drop columns
  • Allows users to sort columns by clicking on them
  • Doesn’t show scorecards with zero values
  • Has a flash graph (line and bar) based pop up window for each measure (and you can select only one measure not default to all)
  • Allows selection of default or specific date
  • Uses our WRV/WRS linkages tool with Portal Single Sign On (SSO) to allow uses to dril down on a number and showsa context sensitive (i.e based on measure, scorecard, period etc) parameter based WRV/WRS report
  • In aggregation mode allow users to select to show parent scorecard or not
  • In aggregation mode show multiple columns under each scorecard (rather than the SPM version of a single column for each scorecard then the next column for each scorecard)
  • Allows users to drill down on a scorecard in aggregation mode
  • Breadcrumbs that shows the hierarchy of the scorecard you have selected (i.e Parent > Child > Child 1)

Its so beautiful …..

October 21, 2009

SAS SPM 2.x and the mysterious sas_wdetemptable

Filed under: SPM — Tags: — Shane Gibson @ 8:39 pm

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.

October 1, 2009

SAS 9.1 / WRS 3.1 – Changes to Metadata Roles takes it time

Filed under: Architecture and Administration, SAS 9.1, Web Report Studio, x - Notes to Self — Shane Gibson @ 7:00 pm

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.

September 30, 2009

SAS 9.1 / WRS 3.1 Metadata Roles defined

Filed under: Architecture and Administration, SAS 9.1, Web Report Studio, x - Notes to Self — Tags: — Shane Gibson @ 7:29 pm

Researching definitions for each of the WRS default roles as part of the Advanced Metadata Security course.

The roles are documented in the SAS 9.1.3 Intelligence Platform: Web Application Administration Guide, Second Edition on page 130. What it says is:

“By default, everyone who can log on to SAS Web Report Studio can view, edit, and create new reports.

To implement security, each user of SAS Web Report Studio can be assigned to one or more standard roles. A user’s role assignments determine which SAS Web Report Studio menu items are available to that user.

  • By default, all SAS Web Report Studio users implicitly have the role. However, if you explicitly assign any members to the role, then only the explicitly-assigned members will have the role. This enables you to start using SAS Web Report Studio immediately after installation, yet still have the ability to restrict user access when locking down your deployment.

  • Each role is a superset of the preceding role. For example, members of the “WRS Report Author” role have all the permissions that apply to the “WRS Report Consumer”.

  • Once you explicitly assign members to a role, you must explicitly assign membersto each superset role. For example, if you assign members to the “WRS ReportAuthor” role, then all of the subsequent superset roles (in this example, “WRSAdvanced User”) also become explicitly-assigned roles. The reason is that WRSAdvanced User is a superset of WRS Report Author.

  • Once you explicitly assign members to a role, then any user who is not assigned to a role, or who has no metadata identity, can only view reports and manipulate reports (for example, select new data items to view in report objects).

WRS Report Consumer Users who have this role can view reports and manipulate report data in the View Report view. Users can copy, move, save, rename, or delete reports. Users cannot create new reports with the report builder or report wizard.
WRS Report Author *

In addition to the abilities assigned to WRS Report Consumers, users who have this role can create reports with the report builder or report wizard. Users can also schedule reports.
WRS Advanced User

In addition to the abilities assigned to WRS Report Authors, users who have this role can distribute reports. Users cannot create or delete recipient lists that are used for report distribution.
WRS Administrator

Users who have this role can perform all tasks that are associated with SAS Web Report Studio, including the ability to create and delete recipient lists that are used for report distribution.

This role provides full permissions to SAS Web Report Studio and should be safeguarded accordingly. This role provides application level administrator functionality. However, this role has no effect on metadata access (authorization) rights to report data.
WRS Prohibited

Users who have this role cannot log on to SAS Web Report Studio. Regardless of the user’s membership in any of the previous roles, if the user attempts to log on, the logon page displays the following error message: “This user is not allowed to access SAS Web Report Studio. Please contact your administrator.”

Some organizations might apply this role for users who are allowed to access some SAS applications but not SAS Web Report Studio. Alternatively, if an organization has multiple Web Report Studio installations, this role can be used to restrict some users from specific instances.

The corresponding metadata group entity is not created during installation. You must manually create the group in metadata if you want to use this user role.
*By default, WRS Report Authors can schedule reports, though you can change the default behavior and limit the scheduling feature to WRS Advanced Users. To do this, in your LocalProperties.xml file, specify true for the schedulingRequiresAdvancedUserRole property.

SAS Portal 4.2 open access (Public Kiosk part duex, the return of the Public Kiosk)

Filed under: Portal, SAS 9.2, x - Notes to Self — Tags: , , — Shane Gibson @ 1:58 pm

I posted earlier about the removal of the Public Kiosk in SAS 9.2 / Portal 4.2.

All the feedback I got stated that they turned off the Public Kiosk in SAS 9.1.3 / Portal 2.x as a matter of course.

Just noticed a SAS Tech support notice “Enabling unchallenged access to content in SAS® Information Delivery Portal 4.2” which outlines how to allow access to the portal without the need to login.

So obviously a few people still wanted it.

Older Posts »

Powered by WordPress