page static_html.xsl b2005_01_27

Business Intelligence Portal Sample Application for Microsoft Office

In my journeys on-line looking for SharePoint and BI stuff, I came upon this new sample which I wanted to share. Microsoft has release a new Business Intelligence Portal sample application for SharePoint and Office. The sample can be found at:

http://www.microsoft.com/downloads/details.aspx?FamilyID=3d11925e-1ed6-43b0-bb94-fe69170ccf82&displaylang=en.

It is a GotDotNet project whose workspace address is

http://www.gotdotnet.com/Workspaces/Workspace.aspx?id=40063240-4d29-42d6-a163-47dcdf2efcaa

From the home page

"Business intelligence portal sample application for Microsoft Office 2003 is an integrated, customizable, multi lingual web-based OLAP solution that enables employees in an organization to create and share structured (OLAP/Relational) and unstructured (Documents, URLs) views, based on various data sources, such as SQL Server 2000 Analysis Services, offline cube files, relational databases, SQL Server Reporting Services reports, spreadsheet data files, XML Web services, XML files, and any requested URL."

"The business intelligence portal is entirely built on top of Microsoft Windows SharePoint Services. It extends Office Web components capabilities, and uses SQL Reporting Server. The 'BIP Viewer' is a smart Web part that can show multiple views from multiple sources and filter data based on Web part connections with other Web parts such as the Business Scorecard accelerator."

The figure below is a screenshot of the BIPortal from the Users' Guide

 

The Viewer Part is used to display 1, 2, 3 or 4 data views (workpads) inside a tiled rectangular area. Each panel can contain a view, which can be one of Report, Url, Gauge, Spreadsheet, Data analyzer, Pivot table, or Chart. Since this is an open-source project, I am assuming that other can and will be added. But this is a pretty comprehensive list for your typical BI Portal. Also, the user can choose which panels to display by showing or hiding each one. There are also resize handles between the tiles which can be dragged up-down or left-right to control the relative size of the view tiles. Each view panel has a refresh property which controls if and how often it will refresh itself.

This framework is based upon client-side behaviors and part connects. Parts make web service calls to implement their behavior and acquire their data. Thus the user experience is not at all like your typical web site or SharePoint site for that matter - the window does not refresh every time you click on something. This is, IMHO, how web development should now be done. But I digress - back to the parts.

There is a very flexible Tree part which can be configured at run-time to contain a hierarchical menu of reports. Each report node can be a document or a view. When the user clicks on a tree node, the corresponding document or view is displayed by the Viewer part. The Tree part can actually contain more than one tree. The designer or user can specify which tree is the default - the others can be selected from a drop-down list on the root menu node.

The Favorites part is a place to bookmark views which you are paying particular attention to at a given time. Since the Tree can contain many private and shared views, the favorites list gives the user rapid access to particular views. One thing I would like to have on the favorites is the ability to add textual annotation. But since the view itself has a description field, this is not a real shortcoming.

The last part is the Data Analysis part. This part provides a canvas for displaying query and filter tools. A tools menu lists the available tools. Developers can add additional tools by implementing the tools interface in their code. Sample code is provided in the documentation.

Speaking of documentation, it is quite good for a GotDotNet project. There is an administrator's guide, a user's guide, and a developer's guide.

The URL view is like a browser window - in fact all views are implemented with iframes, so it makes sense to have a URL view. But because the part communication framework wraps this URL, you can do some cool things by having a parameterized URL where other parts control the values of parameters.

Customizing the BI Portal

One thing I struggled with was having the URL view generate connection events like its native "data source changed" event when the user navigates within the URL view's web page. This is important to maintain state consistency between the parts. The first thing I tried was to add an onreadystatechange handler to the URL part. In the event handler I then told the parent Viewer part to raise the part intercommunication event 'ChangeDataSource'

parent.UpdateObjectCollection('ChangeDataSource', "no", "", url, 1, oRef.WPQ);

This event was picked up by my custom Analysis Tool I added to the BIP Data Analysis part. Adding custom tools is as simple as placing two text files into the BIP Analysis Tools folder in Sharepoint. You tools then shows up in the tools menu both in the Tools part and in the Viewer part - plug and play software. Viewers can be customized or augmented the same way.

The problem, however, was that the url I was passing to the ChangeDataSource message was that which I got from the iframe.src property. After pulling out some hair (amazing I have any left after years as a developer) I realized that the iframe either doesn't know or won't tell you the actual URL being displayed (even if the URLs are in the same domain). Of course it is possible that with a bit more hair pulling that I would have figured it out. But I decided to cheat and add to my root page generation template (for the generator populating the iframe) an onLoad handler which calls up to the URL view, which then calls up to the Viewer part, which then broadcasts the message to other parts on the page. The end result is two-way communication between my custom analysis tool and my customized URL viewer.

Bugs and other issues

1. Possible errors in original project source

I tried creating a site from scratch and using these parts but got lots of errors. While struggling with my part interconnect issues, I found two places in the code where web part names had been hard-coded. I don't know if this was an oversight or intentional, but I fixed it and haven't noticed anything misbehaving. Basically I changed the DataSourceChangeAddin() and ViewChangeAddin() to follow the template of the others by having

document.all['ActiveAddinView' + sWPQ]

instead of the hard-coded

document.all['ActiveAddinViewWPQ2']

I also changed DataSourceChangeAddin to take sWPQ as a 2nd parameter as the others do. Again, I can't figure out why the original source didn't have it.

I did not try building a site from scratch after making this fix, but hopefully it would now be possible. I looked and didn't find anywhere else where part ID were hard-coded in the source.

2. Saving view state

Changes to views don't automatically persist. Sometimes it warns you that there are unsaved changes - sometimes it doesn't. So I got into the habit of always saving my view by right-clicking on the views icon in the tree and selecting 'Save'. But even this didn't aways work. Sometimes the first view I add to a tree folder just won't persist its state. But if I delete this first one after adding others, then the others work properly.

2a. Saving Tree state

There are command for setting the default tree and the default view but they do not work.

3. "Big Screen" application

Like most SharePoint sites, this one really benefits from a large display. I run at 1600x1200 and it was just fine. At 1280x1024 it is also ok but it would not be very usable at a lower resolution.

4. Unsupported

This is a first release, unsupported framework. But source is provided. So unless you have a SharePoint developer at hand I would be hesitant to use this in a production SharePoint environment.

5. Tree object order

Folder and Views in the Tree are displayed in alpha order - you cannot control the order. You cannot move folders and views. A useful addition would be context menu commands "move up" and "move down".

Conclusion

This is in my opinion a very promising new toolkit. If an active GotDotNet community of developers forms, then it should quickly become more robust and evolve in the direction which this community of users wants it to go. Anybody doing BI on the Microsoft platform should evaluate this offering.