The ProClarity API - Programmatic changes to ProClarity briefing books

We have a client who is using Analysis Services 2000 and ProClarity Analytics Server 6.3 in order to provide balance sheet and financial line information to their business. The system that I designed had to be able to aggregate financial data up a customer hierarchy but be able to aggregate the data into parents as they stood at the time the data was loaded and against the current parent. (i.e. A slowly changing dimension.) However, this dimension consists of over 1.5 million members and there are effectively two versions of this in the database.

Now this on its own is fine-ish, in that with the 3GB switch enabled and some tweaks to the cleaner thread interval the AS solution is stable and performs reasonably well. However, the original requirement did not specify security on these dimensions. As you can guess, there is now a requirement to place security on these dimensions, partially to limit access to the data but more so to provide a way for a given user's reports to open, defaulted to the customers for which they are responsible.

Now trying to add security using a "security cube" isn't really conducive to performance and adding 440 roles to AS, each with a different security context, isn't really good for maintenance or memory. (i.e. Think replica dimensions.) So, what else?

Well, as an approach, I wondered if it is possible to take a template ProClarity briefing book located in a PAS Store, load it and customise it for a given user and write it back to the PAS store. From previous experiences I know that just updating the XML in the PAS database for a given briefing book and pages causes problems down the line, to the point that they may no longer work. So how else? Can you make programmatic changes? I know that there is an API for ProClarity but the documentation is all for VB etc. and not .Net.

In the end I decided to give it a try and started to write an application that would retrieve a result set from a database specifying the template book and pages etc. that are to be customised and the customisations to be made. It also specifies the PAS store into which the book should be ultimately published and security assignments for the new book.

After contacting Microsoft on TechNet for some hints and tips (whose responses are pretty quick and impressive actually) I was on my way. See Hints and Tips for that thread.

The essentials to know, when using C#, are effectively as follows:

1. In order to have a briefing book loaded within a ProClarity application in memory, use the KProClarityClass.StartupBook method.
2. Once you have a KProClarityClass object, use the following objects, properties or methods as shown below...
   
   Application (Object)
   |
   CurrentView (Property)
   |
   PageCtrl (Object)
   |
   KPCommand (Object)
   |
   GetAxisMemberSet (Method)
   |
   KMemberSet
   
   As an example, use the KProClarityClass's Application property to retrieve a reference to the Application object. Now use the Application object's CurrentView property to retrieve a reference to the the ICurrentView interface etc. etc.
   
3. If you make any changes to a KMemberSet, you must use that object's associated KCommand object's execute method in order update all associated entities within the ProClarity object model.
4. If you copy elements etc. be sure to update the copied element's ID otherwise you will get errors when trying to publish the briefing book. (i.e. You will have two elements with the same ID which understandably is not allowed.) To do this, set the ID for the given element to: "{00000000-0000-0000-0000-000000000000}"
5. In order to commit changes you have made to a given briefing book page, you have to copy the "page" within the book, using the CurrentView's AddPageToBook method and then delete the original page. Please don't shout at me, I know, that sucks but that's just how it is okay?
   

In the end I changed my application to run as a custom SSIS task and to be multi-threaded using a blocking-queue in order to hand out template briefing books for updates to consumer threads etc. That all seemed to work although I did experience exceptions when running the task in debug mode. As can be seen by the following thread with Microsoft here, this was related to my code not being aware of the COM aspects of the ProClarity API.

All in all it was quite fun to develop, creating mini-caches for PStore connections etc. and to implement a solution that runs within SSIS. I added in the ability to set properties such as the number of threads to use and the ability to set the table and column names that store the relevant updates to be made.

Using simple test cases it appears to work quite nicely. Reports remain static, such that reports with both grids, totals and graphs etc. retain that layout when the member selections are changed. The task allows the ability to either update members by axis or to simply replace the MDX for the report. (Actually you could replace the MDX for a report and then customise the member selection immediately thereafter if you so wished but why?)

Before it's a finished beta v0.0.0.0.0.0.1 "product" (Oracle style versioning there) the task needs to accommodate pages within nested folders (i.e. currently only handles pages at the root or one level below) and to complete the security aspect. I also need to normalise the table structures that store the updates to be made against the template briefing book etc.

Anyhoo, if anyone out there is interested in using this please add a comment to this blog and I'll see if I can add it to our company's website for download. That naturally assumes anyone actually reads my blog...