Tag: SAS 9.1

Roles (or not) in Access Controls: SAS® 9.1.3 vs SAS® 9.2

Today I noticed a difference between SAS 9.1.3 and SAS 9.2 with respect to the use of roles in metadata security access controls.

In SAS 9.1.3 it was possible, though not recommended, to use roles in access controls such as Access Control Entries (ACEs) and Access Control Templates (ACTs). Here is a screenshot of SAS Management Console 9.1 where I am in the process of adding a group to an ACT. Notice that the SAS Web Report Studio roles are available for use (I have highlighted them with a red square).

I noticed today that SAS 9.2 prevents you, at least from within SAS Management Console, from using roles in access controls. Here is an equivalent screenshot of SAS Management Console 9.2, where I am also in the process of adding a group to an ACT. This time only the normal groups are available for use, none of the roles are available.

It was good to see this enhancement in SAS 9.2, as it helps promote good practices. Roles exist to provide a container for groups of users to gain access to application functionality. It is not recommended that they be used in access controls that secure general metadata objects such as folders, servers etc. SAS 9.1.3 introduced roles, with hard-coded or implicit capabilities, where they were used only by SAS Web Report Studio as far as I am aware. The use of roles was significantly expanded in SAS 9.2, with configurable/customizable capabilities to allow administrators to control the availability of application functionality in SAS Management Console, SAS Enterprise Guide, SAS Add-In for Microsoft Office, SAS Web Report Studio and SAS BI Dashboard.

I was surprised I hadn’t noticed this improvement until today, but then I guess I am not usually inclined to use roles in access controls ;)

If you want to find out more about roles and capabilities in SAS 9.2, I would definitely recommend reading Kathy Wisniewski‘s paper Be All That You Can Be: Best Practices in Using Roles to Control Functionality in SAS® 9.2 from SAS Global Forum 2010 available from http://support.sas.com/resources/papers/proceedings10/324-2010.pdf

Best Practices with SAS® 9 Metadata Security Presentation at SFANZ 2010

Thanks to everyone who attended my presentation at SFANZ last week, especially those who asked questions and provided feedback. If you would like a copy of the presentation, you can download it here in both PowerPoint and PDF formats. It should also be available from the SAS Forum Australia & New Zealand site too.

These are my notes that go with the slides …

Abstract

SAS9 metadata security is becoming increasingly more important as SAS9 platform installations continue to grow and evolve. With more content, larger user communities, and a wider variety of application interfaces, time-poor SAS platform administrators are looking for better ways to manage security with their organizations valuable metadata and data resources. Knowledge and use of current SAS9 metadata security best practices can be a key differentiator between stressed platform administrators and well organized administrators. One group might spend their time applying ad-hoc quick fixes, and tracking down authorization conflicts, whereas the other group will plan ahead to reduce day to day overheads and minimize the impact of change. This paper will provide an outline of some of the key SAS9 metadata security best practices together with information on where to go to find out more.

Slides 1 & 2 : Title / Agenda

I will start with a quick introduction, move onto practices that are more procedural in nature and then cover some of the major implementation practices. This is quite a large topic for the short space of time available and so I can only really scratch the surface. I hope that you decide to discover more about this topic, so I will finish up by pointing you at some resources where you can find more information, including my blog where I expand on some of these topics.

I’d like to start out with a quick intro to metadata security with a show of hands. Please raise your hands if you have used SAS Enterprise Guide to view the contents of a SAS table… Now raise your hands if you have every created or run a SAS stored process… Now those who have used SAS Data Integration Studio to create and run ETL jobs… How about those who have used SAS Management Console to manage servers… All of those tasks are based on things represented in SAS metadata and the ability for you to view and possibly update them is managed through metadata security and access controls. The SAS platform administrator is responsible for appropriately securing the metadata to meet business requirements. Managing access to metadata in an efficient manner is not always easy and that’s where knowledge of best practices can help.

Slide 3: Best Practices

Best practices can be thought of as collected wisdoms from the community. They are shared methods proven through experience to be more effective than others. They might be more robust, cost less to implement, take less time to manage, and be easier to change as requirements evolve. Given enough time most people will discover these methods for themselves. We make mistakes along the way and learn from them for next time. People often share these experiences and contribute to the knowledge of the community as a whole. If we can learn best practices from other people we can save ourselves time and effort.

Here are some of the things that can be avoided with knowledge of metadata security best practices:

  • An ad-hoc security implementation, triggered by individual email/phone requests, consisting of hundreds or thousands of per-user, per-object based access controls
  • A chaotic implementation with unpredictable or unexpected access controls (only unexpected from the administrator’s perspective – the metadata server will always make a consistent access decision but it may not be what the administrator expects due to a large number of ad-hoc, unexpected and conflicting access controls)
  • A metadata security implementation which is undocumented, practically undocumentable, or out-of-sync with the available documentation.
  • The significant effort that may be required to rationalize and consolidate an ad-hoc security implementation when business access rules change.

This presentation consists of a mixture of my own experiences as well as information I have picked up from others along the way. You will probably recognize some of these from your own discoveries too.

I will start with a few procedural practices…

Slide 4: Continuous Learning

To understand and implement best practices we need a solid understanding of the software. Just as the software we use evolves over time, so do best practices. They change to match changes in software, and also change as new or improved techniques are discovered. For example, best practices with SAS 9.2 differ somewhat from best practices with SAS 9.1.3. To keep up-to-date with best practices we need continuous learning by interacting with other people in the same field and sharing experiences. Here are some of the mechanisms by which we can stay up-to-date. They include SAS training courses, SAS certification, SAS documentation, conference papers, online communities as well as knowledge of additional 3rd party software such as Metacoda Security Plug-ins.

Slide 5: Concept Awareness (1): Identity Hierarchy

As a platform administrator, there are 3 key concepts that we need to know about in order to understand metadata security access controls, plan a security implementation and assess the impact of changes. The first of these is the identity hierarchy. You can think of this as a tree consisting of the user and the groups they are a member with multiple branches that represent nested group memberships. This hierarchy plays a key role in resolving access decision conflicts. Here we have an example of the identity hierarchy for a user as is shown by Metacoda Security Plug-ins.

I discuss this topic further in a separate Identity Hierarchy blog post.

Slide 6: Concept Awareness (2): Inheritance Paths

Another important concept is inheritance paths. Most metadata objects inherit their access controls from other objects. An understanding of inheritance paths helps us know where those inherited permissions are most likely to come from, and where any changes we make are likely to flow to. There have been some significant changes between SAS 9.1.3 and SAS 9.2.

With SAS 9.1.3 a number of objects had multiple inheritance paths where they inherited their access controls from multiple parents. Whilst this provided flexibility, it also caused some confusion as it was necessary to consistently secure all inheritance paths otherwise you could end up with situations where an object that was secured through one inheritance path was unsecured through another.

SAS 9.2 replaces this with a single inheritance model which is simpler to understand and easier to secure.

The SAS Management Console Inheritance tab, part of SAS 9.2 and available from SAS Institute as a plug-in for SAS 9.1.3, provides a mechanism for us to view the inheritance paths for an object.

I discuss this topic further in a separate Inheritance Paths blog post.

Slide 7: Concept Awareness (3): Access Decision Flow

The last major concept I will mention is the access decision flow. These are the rules the SAS metadata server follows to determine for a specific user, a specific object and a specific permission whether that user is granted or denied access.

The flow diagram shown here comes from the SAS® 9.2 Intelligence Platform Security Administration Guide, Chapter 5 Authorization Model in the section titled Authorization Decisions.

The layout of the diagram has changed from SAS 9.1.3 to SAS 9.2. Whilst it looks complex to start with, the rules are straightforward and memorable. It does not take long before you can make a determination yourself without having to refer to the diagram.

Some might say that with the new explore permissions tool in SAS 9.2 it is no longer necessary to understand this flow chart. I disagree because whilst the tool allows you to see the permissions for a specific situation, it is an understanding of the rules behind it that allows an administrator to think more generally and understand the ramifications and potential conflicts for any changes that she may make.

Slides 8 & 9: Plan Well

As with many things, time invested in planning upfront pays dividends later. Planning will help you develop an efficient security plan that meets your requirements. A clear idea of your goals will help you avoid an overly complex implementation that is hard to manage.

These are some of the things worth considering when you are planning your SAS metadata security implementation:

  • Collaborate with everyone who has a stake or may be able to help. As well as talking to the business about their security requirements, talk to the IT department too. They will know of any general security policies that you may need to be aware of.
  • Your IT department will also be able to help you integrate with enterprise directories such as Microsoft Active Directory or LDAP which will be a major time saver for you. Whilst it is not trivial to implement, enterprise directory integration is well worth the effort as it allows you to automatically synchronize your SAS metadata users and groups with existing users and groups in your enterprise directory and leave user and group management to your IT department.
  • Be prepared for the security requirements to evolve over time as the business itself changes with new projects, new teams, new data sources, new servers etc. You may also need to be ready for changes and new discoveries during your implementation. A good security plan will help you to make changes without requiring a significant effort.
  • Consider which groups of users will perform which job roles and identify the SAS software and features they will need access to. In SAS 9.1.3 this will help you with your security plan and software distribution requirements. In SAS 9.2 you will also be able to use this to plan your roles & capabilities to determine which SAS software features they will have access to, such as the ability within Enterprise Guide to save files to the local computer and the accessibility of the various plug-ins in SAS Management Console.
  • A well thought out tree folder structure that partitions content based on projects, organizational structure and/or job roles can significantly simplify your security plan and minimize ongoing maintenance.
  • Once you have your folder structure planned, you can determine how you should secure it.
  • Remember that you will also need to secure content, such as servers, groups and access controls that are not contained in folders.
  • When your plan is ready, document it, then implement it using your documentation.
  • Plan your testing too. It is easy to underestimate the amount of time required for sufficient testing, especially if security is important to your organization.

Slide 10: Develop a Security Plan

When developing your security plan consider what type of model you require and how complex it needs to be. You will want to keep it as simple as possible whilst still meeting access requirements. Try to avoid an overly complex plan.

You might have an “Open” security model if you organization doesn’t have any specific access requirements. In this model anyone can do almost anything given access to the appropriate software.

Moving on from this, smaller organizations, perhaps just starting out with SAS, might have a basic security model where their users are divided up into administrators who are also developers, a single group of authors and everyone else who are consumers.

Alternatively, larger organizations with more mature SAS installations may need a more complex security model. This might have multiple administrators, independent teams of developers, multiple groups of authors and multiple groups of consumers all with different access requirements. There may well also be a need for OLAP member level and BI row level security.

Now onto a few implementation best practices…

Slides 11 & 12: Prefer ACTs over ACEs

There are 2 ways of securing content in the SAS metadata repository.

The first is to use explicit permissions on individual objects – also known as Access Control Entries (or ACEs).

The second is to create re-usable packages of identities and associated permissions called Access Control Templates (or ACTs) and apply them wherever they are needed.

Whilst ACEs are very simple to apply, especially for a first-time administrator, it is best practice to use ACTs. This is because ACTs are much easier to manage and are change-friendly. When the time comes, as it always does, to change the rules you only have to change the definition of the ACT, and that change will flow through everywhere the ACT has been applied. If you secure with ACEs you will need to visit and change each one individually.

As an administrator one of my goals is to try to minimize ACEs, replacing them with appropriate ACTs where possible. This screenshot shows our ACE Reviewer software displaying a list of ACEs for me to target.

When minimizing ACEs, be aware that there are a number of ACEs that cannot, or should not, be removed. A number of SAS applications create and manage their own ACEs, and these ACEs should be left alone. Additionally, ACEs used for OLAP member level security, or BI row level security, cannot be replaced with ACTs.

Slide 13: Prefer Groups over Users

It is an industry standard best practice to secure content using groups rather than users. This is not specific to SAS software, but we follow the same principle when using SAS software. The user population, their location and roles within the organization, changes very regularly whereas groups are relatively static. Users change, they join and leave the organization, they move departments or get seconded, and they get sick or go on leave. Don’t be afraid of groups containing 1 user, you might only have 1 marketing director, but if she goes on extended leave and someone else takes over her role for a short while, you will be glad you created a Marketing Directors group.

One of our goals as administrators is to have a security plan where we can manage access on a day to day basis, not by changing access controls, but by adding and removing users from groups.

To keep on top of this best practice we can regularly look for access controls that refer to users and improve them by removing the users and replacing them with appropriate groups. This screenshot shows our ACT Reviewer software highlighting an ACT that contains a reference to a user.

Slide 14: Secure Folders over Objects

Another security best practice, that is not just specific to SAS software, is to secure groups of related content using folders rather than securing the individual items.

If you find yourself securing individual objects, then you may be creating a significant maintenance headache for yourself. Instead, think about whether the item can be moved either to an existing folder, with appropriate access controls, or to a new folder specifically created and secured for the new access requirements.

We can create hierarchies of folders that are aligned with our access requirements, whether they are project, organizational structure or job role based, or a hybrid. Then we take advantage of inheritance paths, where objects inherit their access controls from the folder they are contained in, and secure the high level folders with ACTs, leaving the individual objects alone to inherit permissions.

One of the improvements in SAS 9.2 that helps us with this practice is the WriteMemberMetadata permission. This permission allows us to grant access to modify the contents of folders without having to grant permission to modify the folder itself.

Slides 15 & 16: Wide Denials, Narrow Grants

For me, this practice is one that can help avoid significant headaches with permission denials and identity hierarchy conflicts through precedence mismatches.

This is best explained by an example…

A folder has been created to contain content that should only be visible to members of the Australia group and not members of the Asia/Pacific group. It has been secured to grant the ReadMetadata permission to Australia and deny it from Asia/Pacific.

But there is a problem with this…

Sam and Tara are both members of the Australia and Asia/Pacific groups but by different mechanisms:

Sam is a direct member of Australia and an indirect member of Asia/Pacific because the Australia group is a member of the Asia/Pacific group. Due to identity precedence rules Sam can see the folder.

Tara has been made a direct member of both groups when she should have been a direct member of the Australia group only. Due to identity precedence conflict rules Tara cannot see the folder even though she is in the Australia group.

With an understanding of identity hierarchies and the access decision flow, this makes sense and is easily corrected by removing Tara as a direct member of the Asia/Pacific group. However, in practice, these identity precedence mismatches can easily recur, and if we are using enterprise directory integration we may not be in direct control of the group memberships either.

A solution is to only ever deny permissions widely to the PUBLIC (or SASUSERS) group and then explicitly grant permissions narrowly back to those specific groups that require access. Because PUBLIC/SASUSERS are lowest in the identity hierarchy the potential for these identity precedence mismatches is practically eliminated.

Slide 17: Thorough Testing

After implementing your security plan it is good practice to test it thoroughly. This is especially important in a high security environment where it may also be a legal requirement. Testing will give you more confidence that there are no conflicts with unexpected grants or denials of permissions.

Test a variety of different classes of users: users who are not registered in metadata, users who are registered in metadata but not direct members of any groups. Test users with single and multiple group memberships looking for potential conflicts.

Test a variety of different actions such as open, update, rename, move and delete, to test a variety of different permissions.

One of the benefits we have as administrators is the ability to impersonate users. We can use our own credentials to log in as a specific user and test access as if we were them. Remember that this impersonation is only at the SAS metadata level and not at the operating system level.

I also like to set up a private administrator-only environment. I call it Lev9 and use it for disruptive or destructive testing that you would not want to do even in your development environment. It allows you to keep your developers happy too. Disruptive testing includes things like significant changes to high level access controls that could affect a significant number of users. Destructive testing allows you to test the ability or inability to delete important things like servers and high level folders without impacting other users.

Slide 18: Regular Reviews

Your security plan and its implementation will both evolve, and sometimes in different directions. If security is important for your organization then you will want to review your plan and its implementation regularly, update the plan to meet changing business requirements, and keep the implementation in line with the plan updating the documentation accordingly.

This screenshot shows some of the security implementation documentation that can be exported from our software. We have had a number of people approach us about this because they needed a mechanism whereby they could generate this documentation and publish it internally and regularly to prove that appropriate metadata security controls have been put in place.

Slide 19: Summary

That brings me to the end of the best practices part of my presentation. The key things I hope you will take away from this presentation are to keep learning, plan your implementation well, secure using ACTs rather than ACEs, groups rather than users, and folders rather than objects. Be careful with permission denials, test your implementation carefully and thoroughly, and review it regularly.

Slides 20 & 21: Resources (SAS 9.2) / (SAS 9.1.3)

If you would like to find out more about this topic, I have a couple of slides in the presentation with a list of resources that will help. I won’t go through the list here but you will find it in the online version of the presentation. There are resources for SAS 9.1.3 as well as resources for SAS 9.2. All of these links are also in a prior blog post and on my platformadmin.com blog Reading List.

Slide 22: About Metacoda

Just before I finish I would like to mention my company Metacoda. We are a SAS Alliance member that specializes in the development of software to integrate with SAS software with the goal of improving your productivity through enhanced metadata visibility. Many of the screenshots you have seen in this presentation were taken from our Metacoda Security Plug-ins product. If you would like to find out more then please visit our web site. We also have a flyer in your conference bag with more details.

Slide 23: Q&A

If you have any comments or questions please feel free to contact me using any of the mechanisms available here.

Inheritance Paths

This is another companion post for my Best Practices with SAS® 9 Metadata Security presentation at next week’s SAS Forum Australia & New Zealand 2010 to expand a little on the concept of Inheritance Paths.

Importance of Inheritance Paths

Inheritance paths play a key role in SAS metadata security, providing a mechanism whereby objects can inherit some, or all, of their access controls from other objects. They provide a foundation for the development of an efficient security plan that meets security requirements with a minimum of access controls and ongoing maintenance.

When determining whether a user has been granted or denied a permission on an object in metadata (such as the ability to update an information map with a grant of the WriteMetadata permission), the SAS metadata server will first look to see if the object has any relevant direct access controls applied to it. If there are no relevant direct access controls on the object in question, then the grant or denial of the permission will be derived from the object’s inheritance paths instead. The inheritance paths consist of the objects parents, its parent’s parents and so on.

In any given metadata repository the vast majority of metadata objects will not have any direct access controls applied to them. Almost all metadata permission determinations will be made from inheritance paths. This is a good thing. Inheritance paths mean we can set a small number of key access controls in a few strategic locations high up in inheritance paths, such as high level tree folders or application servers, and rely on inheritance to propagate those permissions to the majority of objects lower down in the inheritance path.

The structure and content of an object’s inheritance paths will depend on the type of metadata object in question (as well as the version of SAS you are using – more on that in a moment). The SAS documentation provides more information on the specifics but it is often easier to use the SAS Management Console Inheritance tab when looking at specific objects.

With SAS 9.1.3 we used to have to work most of this out in our head. Things are much easier now with SAS 9.2 thanks to all the new metadata security goodies, such as the SAS Management Console Explore Authorizations and Inheritance tabs, but it is still important for a platform administrator to have a good understanding of inheritance paths. Whilst you may be able to find out that Susan has been granted WriteMetadata on the Current Sales Forecast information map through an inherited access control, it is an understanding of inheritance paths that helps you find the underlying source of that grant. A knowledge of inheritance paths is also key to planning a good metadata security plan as well as understanding the impact of any changes that are made.

Visualizing Inheritance Paths

An object’s inheritance paths can be easily viewed using the SAS Management Console Inheritance tab. This is a standard feature in SAS 9.2 and available as a downloadable plug-in for SAS 9.1.3.

When using SAS Management Console 9.2 you can access the Inheritance tab from an object’s properties dialog by using the Advanced button on the Authorization tab. The following is an example of the Inheritance tab in SAS 9.2 for a table named CUSTOMERS showing the single inheritance path consisting of the hierarchy of folders it is contained in. The tree path for this table is /SAS Folders/Shared Data/Sales Data/CUSTOMERS which can be seen in reverse in the Inheritance tab.

Whilst the Inheritance tab is not included in a standard installation of SAS Management Console 9.1, it can be easily added by downloading and installing the plug-in from the support.sas.com site. You can find the 913INHERITANCETAB01 download together with installation instructions in the SAS BI Administration Utilities 9.1.3 area.

Once installed in SAS Management Console 9.1, as shown in the screenshot below, the Inheritance tab can be seen directly in the properties dialog for an object (i.e. alongside the Authorization tab). You may notice that this is in a different location than with SAS 9.2 (where it is accessed from a button within the Authorization tab).

This additional feature is also described in SAS Usage Note 20960: The authorization inheritance utility is now available for the SAS® Management Console. As mentioned in the usage note, it is easier to view the inheritance path if the Show Association Node checkbox is unchecked (as I have done below). It must be manually unchecked with SAS Management Console 9.1, but is unchecked by default in SAS Management Console 9.2.

The example above shows the CUSTOMERS table in a SAS 9.1.3 installation where the table can be found in the tree folder /Shared Data/Sales Data and associated with the Sales library assigned to the SASMain application server.

You may have noticed that there are multiple inheritance paths for this table in a SAS 9.1.3 installation, whereas there was only one inheritance path in the SAS 9.2 installation…

Multiple Inheritance in SAS 9.1.3 vs Single Inheritance in SAS 9.2

As shown in the examples above, SAS 9.1.3 had a few instances of multiple inheritance where an object could have multiple parents and therefore multiple inheritance paths. The CUSTOMERS table shown above for SAS 9.1.3 has two direct parents: both the Sales Data tree folder, and the Sales library. This multiple parentage resulted in some confusion among platform administrators. Whilst the access decision flow handled the potential for conflict, it did so in a way that caused some often unexpected outcomes where a permission may have been granted when the administrator expected it to have been denied. When I say ‘unexpected’ I mean from the perspective of a platform administrator who is not aware of the rules – it is totally expected when considering the access decision flow :) I have a follow-up post planned that discusses this potential issue and the associated practice of consistently securing all inheritance paths

These are some of the metadata objects I am aware of that have multiple inheritance in SAS 9.1.3:

  • DBMS Schema: inherits from both DBMS Server and Library (as documented in the SAS® 9.1.3 Intelligence Platform Security Administration Guide)
  • Table: inherits from both Library and Tree folder
  • OLAP Cube: inherits from both OLAP Schema and Tree folder
  • Deployed Job: inherits from both Job and Tree folder

… if you know of any others, please let me know and I will add them to this list. From what I can see the SAS 9.1.3 Security Administration Guide for SAS 9.1.3 only covers the DBMS Schema instance. It does not mention the OLAP Cube and Table instances which are also commonly encountered.

One of the significant metadata security changes in SAS 9.2 was the virtual elimination of multiple inheritance. Tables and OLAP Cubes, for example, now have single inheritance paths in SAS 9.2 and only inherit from folders.

I say virtual elimination in SAS 9.2 only to be safe because, although I have yet to discover any metadata objects types in SAS 9.2 that do have multiple inheritance, the access decision flow still handles multiple inheritance scenarios and the documentation stills refers to the potential for multiple inheritance. The SAS® 9.2 Intelligence Platform Security Administration Guide, Chapter 5 Authorization Model section titled Authorization Decisions states that “A grant from any inheritance path can provide access.” and “Having more than one immediate parent is not a common circumstance.”, but it does not provide any concrete examples.

If you are reading this and you do know of any multiple inheritance examples in SAS 9.2 then I would be very keen to hear from you :)

Learning more about Inheritance Paths

If you want to lean more about this topic, I would suggest any or all of the following:

Promoting Job Flows

I am occasionally asked whether it is possible to promote job flows between metadata repositories (e.g. DEV to TEST to PROD) using the SAS package file (SPK) import/export promotion feature. In past consulting work I have needed to promote job flows containing hundreds of jobs all interlinked with dependencies, so this is a topic close to my heart :)

In the early days of SAS 9.1.3 import/export of job flows was not supported and so the only choices availably back then were to either manually reconstruct the jobs flows in the target environment or do a full replication/promotion (what used to be called full promotion in SAS 9.1.3 is now called replication in SAS 9.2). Manually reconstructing jobs flows was a tedious and error-prone process especially with large and complicated job flows. Full replication/promotion was often not appropriate because it would overwrite the target environment completely. But that’s all history (and has been for a while) …

With SAS 9.2 there is no question about it – import and export of job flows is standard with SAS 9.2 so I will ignore it for the rest of this post and concentrate on SAS 9.1.3 instead.

Since the import/export of job flows was not initially supported in SAS 9.1.3, I occasionally talk to people who assume it’s still the case now (which is understandable). It’s great to see the smile spread across their faces when they find out it is actually possible with SAS 9.1.3 today – I remember back to how I felt when I first found out. I was faced with the prospect of manually reconstructing some awe-inspiring job flows and contacted SAS on the off-chance that someone might know a better way – which is how I was lucky enough to get to try out an early version. You can imagine how happy I was.

The ability to perform partial promotion of job flows crept relatively quietly into SAS 9.1.3 with a couple of hotfixes some time back. There is a usage note about it now too: Usage Note 31008: Hot Fixes 913SMC04 and 14JPS02 provide the capability to add job flows using partial promotion

So if you need to promote job flows between SAS 9.1.3 environments here are some pointers that you might find useful:

  • Promote the job flows using the SAS Management Console 9.1 BI Manager plug-in. Last time I tried I couldn’t export job flows from SAS Data Integration Studio 3.4 (even with the latest hotfixes at the time). I last looked many months ago though, so happy to be corrected if that has changed since.
  • Make sure the SAS Management Console 9.1 client installation you will be using (most likely your workstation) has been updated to at least the level of hotfix 913SMC04. I would tend to install the most recent SAS Management Console 9.1 hotfix 913SMC08 instead.
  • This is already in the instructions for 913SMC08, but you also need to ensure the client installation you will be using (most likely your workstation) has been updated to the level of SAS Foundation Services 1.4 hotfix 14JPS02. You might recognize SAS Foundation Services 1.4 from mid-tier installations, but it also contributes the BI Manager plug-in to SAS Management Console 9.1 client installations.
  • Using BI Manager in the source repository track down the folder containing the job flows you want to export and export them into a SAS package (SPK) file. If you can’t find the job flows try looking in the /Shared Data folder. Here is a screenshot showing job flows being exported from SAS Management Console 9.1:
  • Using BI Manager in the target repository import the job flows from the previously exported SAS package (SPK) file. Once imported you will need to re-schedule the flows.

MKDIRMD Macro: Creating Tree Folders in Metadata

Update 10Jul2015: After 5 years, the macro discussed in this blog post is now very dated. If you still have SAS 9.1.3 then it should be ok, but for newer versions of SAS software (such as 9.2, 9.3 and 9.4) there are better options provided as standard by SAS Institute:

1) SAS Platform Object Framework MakeFolder Metadata Utility: see my later blog post

2) SAS 9.4 has the Make Folder (sas-make-folder) command line tool documented in the SAS 9.4 Intelligence Platform: System Administration Guide, Third Edition” under “Batch Tools for Metadata Management”.

The source code for the MKDIRMD macro mentioned below has a check for SAS 9.2 at the end, that should include later versions of SAS (if used with later versions of SAS). However, I would strongly recommend using one of the newer supported methods instead (as mentioned above).

The inspiration for this post came from a question at sasprofessionals.net about code to create tree folders in a SAS® metadata repository. It sounded like a good challenge considering that:

  • Creating a Tree object in metadata is relatively straightforward, but finding the correct parent Tree object to associate it with takes more work. You can’t search by the parent folder name in isolation because there might be multiple folders with the same name at different locations in the tree (e.g. there could be 2 folders both named Reports at /ACME/Sales/Data and /ACME/HR/data). The parent folder could be specified by its object id but that’s a bit tedious. A neater method would be to allow the parent path to be specified in absolute terms (e.g. /ACME/HR/Data/) and get the code to walk down the tree to find the correct unique parent Tree object (e.g. Data under HR under ACME).
  • It should be able to create top level folders in the root of the tree as well as folders at lower depths. Top level folders are represented slightly differently in metadata than lower level folders. They don’t have a ParentTree association but are instead associated with a SoftwareComponent.
  • It should fail if a folder with the same name already exists in the parent folder. The metadata API allows you from create duplicate named folders in the same parent folder.
  • It should check all return codes and fail fast – it is being used to update metadata after all.
  • It would be good if it worked in both SAS 9.1 and SAS 9.2

I had some metadata API fun writing a MKDIRMD macro that satisfied all of the above. Here is an example of it being used:
%mkdirmd(name=ACME, parent=/);
%mkdirmd(name=Sales, parent=/ACME/);
%mkdirmd(name=StoredProcesses, parent=/ACME/Sales/);
%mkdirmd(name=Data, parent=/ACME/Sales/);
%mkdirmd(name=HR, parent=/ACME/);
%mkdirmd(name=StoredProcesses, parent=/ACME/HR/);
%mkdirmd(name=Data, parent=/ACME/HR/);

I have uploaded the MKDIRMD.SAS code I wrote so it can be used by anyone that may have a need for it, or by those who might want to review it as an example program that uses the SAS Open Metadata Interface to query and update metadata. It includes examples of XMLSelect filters on both attribute criteria and association paths. I am publishing this code under the GNU LGPL license so it can be freely used. I have tested the code in both SAS 9.1.3 SP4 and SAS 9.2 M3 but it is provided “as-is” and should be used with care.

Be aware that with any code like this, that updates your metadata, it is strongly recommended that you have a recent backup (that is known to work) and test the code in a non-production environment. I prefer testing things like this in a private administrator environment (Lev9) since even development environments should be considered valuable.