Challenge
Credit-Suisse has to comply with the Sarbanes-Oxley act (SOX) passed by the US government in the wake of the Enron scandal. They looked at a variety of
solutions, and chose to investigate Microsoft's SOX solution framework. Since Pacific Northwest Software (PNWSoft) helped develop the original
framework, we were subsequently invited back and successfully completed the enhancements that Credit-Suisse needed to move forward under time
and budget.
Credit-Suisse had several goals that needed to be met.
- Massive scalability. They had to support hundred of thousands of documents and tens of thousands of users. The original Microsoft framework was based
on SharePoint, and had that product's limitations; it could only support up to 2,000 documents and its performance got exponentially slower as additional
documents were added.
- Web-only deployment. They could not easily deploy a required component of Microsoft's framework - Microsoft's InfoPath, because they have thousands of desktops of
different standards located throughout the world.
- Robust reporting capabilities. They needed the ability to report on any of the parameters in the documentation.
- Fine-grained permissions. Because of the potentially sensitive nature of the documentation, users need to only see information that relates to their
business function.
Solution
PNWSoft worked on the original Microsoft Solution Accelerator Framework for Sarbanes-Oxley Act, so we were intimately familiar with its workings. We have also
spent years developing Microsoft SharePoint based solutions so we knew its abilities and limitations as well. This allowed us to quickly develop a core architecture for
Credit-Suisse.
Scalability is more than just the number of transactions per second that the solution can do. The solution has to meet the performance goals, but it also has to
present the vast quantities of information in such a way that people can use it efficiently. We analyzed the common work tasks that people would perform, and made
sure that each part of the user interface was capable of supporting those tasks. For instance, with several thousands users, we couldn't just display a drop down with
all of the users. That would be an impossibly awkward interface. We developed a context sensitive user selection dialog. It would first show a subset of users that
were related to the context of the user selection.
In the above example, you can see two user selection dialogs. The first one is using context information to only show the users who are used on documents in the
same area. The next dialog shows all the users who possible could appear as a choice for that selection. Because the users are loaded asynchronously, the dialog
loads quickly even with twenty thousand or more potential users. The text box at the top allows users to interactively limit the selection. For instance, if "mi" is
typed into the box, the following appears.
You can see that the list was dynamically narrowed down to just the names that contain "mi" in them. This allows the user to efficiently select the proper person.
We also had to prevent the thousands of users from trying to simultaneously edit documents. Microsoft's framework allowed multiple edits, and the first user to save
"won", and the rest lost their work. Obviously that solution only works when people synchronize their work outside of the solution. Credit-Suisse is a global
organization, so there is no way to provide synchronization outside of the solution. We took advantage of SharePoint's document management functionality to expose
the checkin, checkout, and undo checkout functionality. This prevents users from losing work by only allowing one user to make changes at a time, the other users
see that the document is checked out by the editing user and can only view it. These are just a couple of the hundreds of improvements that we made to support true
scalability.
To increase the performance of the solution, we profiled the existing framework and implemented alternative layers on top of that. These alternative layers did
not have the scalability issues that the Microsoft's framework did. First and foremost, we created a "shadow" relational database of the XML data in the documents.
This allowed us to quickly retrieve sets of documents that matched certain criteria instead of looking through each one.
Then we added in a caching layer that allowed the individual web servers to scale in performance and not be database bound. In true enterprise applications, you cannot just
speed up the code to satisfy performance needs, you also have to design the system to run over many servers and to scale well when doing so. A key requirement of that is
to uncouple the performance of the servers from each other. It would do no good to add additional front-end servers, if the back-end server was the limiting factor. In
a data driven solution, like this one, the primary way one achieves this is by having the front-end servers cache data from the back-end servers. Then each
transaction on the front-end server does not require a transaction on the back-end server, and by adding additional front-end servers, the solution can scale.
Then we purged the write operations of any SharePoint API call that would not scale. We replaced them with alternative calls that we know from years of
SharePoint programming would scale very well. For instance, creating a new document in SharePoint is an n2 operation. That means that time that it takes to
complete is proportional to the total number of documents squared. In testing we saw this taking multiple minutes with the estimate number of documents that
Credit-Suisse wanted to store. Obviously, this is unsatisfactory. We used an alternative approach, to copy a template file and then modify that with the
new document data. This approach scaled very well and was close to constant time to "create" a new document.
In order to remove the requirement for Microsoft's InfoPath product, we create an ISAPI filter that intercepted calls for the XML InfoPath files. The users were
redirected to a server side web page that allowed editing of the XML. Thus we were able to still support the same InfoPath format and compatibility, but we didn't
require any client side software other that Internet Explorer.
The shadow database not only helps the operational side of the software, but it allows an excellent reporting system to be built. By normalizing the data contained
in the XML, it allows reporting solutions, such as Microsoft SQL Reporting Services to be used. We implemented Microsoft's SQL 2000 Reporting Services for the fixed
format reports.
We also used Microsoft's SQL 2005 Reporting Services to implement ad-hoc reporting.
As you can see, the ad-hoc reporting is extremely powerful. It allows a normal user to select various fields from various documents and even compute aggregates based on the
relationships between the documents.
In order to provide the fine-grained permissions that Credit-Suisse was looking for, we analyzed the data that they wanted stored and created a hierarchical set
of rights that allowed access to it. We then created a set of roles that mapped to a set of rights. Users could either be explicitly assigned a role for a given part
of the hierarchy, or their role would be implicitly determined by where they are mentioned on the documents. For instance, if an user is not assigned to
the "no access" role, and they are the owner of a document, then they are automatically given read and edit rights to that document. They then can assign other users
to that document's workflow, and those users not automatically have the ability to read and edit certain parts of that document. In this way, only the upper level
users (those who will be creating top level documents), need to be granted explicit permissions, and merely by creating and assigning users to those documents, additional
users are added to the system and automatically given rights to just the specific documents that they are responsible for.
Results
Credit-Suisse called our solution "Management Assessment of Risks and Controls
System" or MARCS for short. MARCS meet all of the original criteria, and went
on to be extended in more ways and used for other compliance activities at
Credit-Suisse.
It was rolled out to a global audience of users in over 100
separate legal entities that comprise Credit-Suisse. Since compliance with the
Sarbanes-Oxley Act was extended for foreign based corporations, MARCS was used
in a "dry-run" capacity for FY2005, and performed admirably.
Sincerely,
Mike Hayner
Chief Technical Officer
Pacific Northwest Software
+1 206 234-1709
+44 020 7193 4011
mike [dot] hayner [at] pnwsoft.com