Wednesday, June 20, 2007

Yesterday I attended the monthly Sydney SharePoint User Group. The presenter was Grant Paisley who is the Chief Solutions Architect at Angry Koala. Grant demonstrated some of the ways Microsoft's Business Intelligence solution set has been improved in 2007. He covered a lot of ground and I thought I would write about the many great tips and insight he presented to us. This may hop around a bit as I have the memory span of a goldfish and the handwriting of a spider.

To begin with, Grant made a very interesting remark....he basically suggested that Business Intelligence had been brought out of the "back room" and was now what Microsoft would call a "first-class citizen".

We've all spent the last decade creating enormous quantities of data and we don't know how to manage it (or even find it!)...Now the major software vendors like Google and Microsoft are trying to get a handle on these problems and this overriding need to unify all our scattered data is becoming central to their business models. The proof of how seriously Microsoft takes this is that they are in the process of turning SharePoint into the web platform that will underpin all of their applications. I bet within 5 years the only significant Microsoft product that does not leverage SharePoint services and integrate with it- and I'm including desktop applications here - will be SQL Server itself.

So Grant's analysis that Microsoft is assigning more importance to Business Intelligence makes perfect sense because Microsoft would be missing a big piece of the data management pie if BI was given junior status. More to the point, the individual BI tools are being rolled into the SharePoint stack.

To prove this, Grant showed a slide of the BI stack...At the bottom was what he called the Operational Systems (Oracle, SAP, PeopleSoft, SQL Server, etc). These were the initial data repositories. The next level of the stack was the BI Platform: Reporting Services, Analysis Services. These provided the data for the third level of the stack, the End User Tools & Performance. Here I saw the new PerformancePoint product mentioned. The final level of the stack was the Delivery layer, where the end users manipulated and displayed the data. Again, the key element (at least to my eyes) was the SharePoint platform (read: MOSS), because on it sits Excel Services, KPIs, and published reports.

Grant then gave a demonstration of manipulating an Excel spreadsheet attached to an OLAP cube via Analysis Services. Someone asked where the best place to put KPIs is, because at the moment you can do that in a variety of places (Analysis Services, Excel Services). He suggested that the best place is within Analysis Services as those KPIs then become available everywhere else.

Grant demonstrated how to organize an Excel spreadsheet; using Pie Charts and Heat Charts he helped visualize the data within the spreadsheet. Next he published the spreadsheet to a report library using Excel Services. He mentioned that you can publish individual items, worksheets, or the whole report. One great tip he showed us was how to rename the Excel objects (such as the worksheet) before he published the form. This is important because the web parts refer to the object names and so it's a good practice to give them meaningful names ahead of time.

He had another tip - if you "Convert to formulas" (it's an Excel option somwhere) you'll get full control over how the spreadsheet appears and can move the cells around. I didn't understand this requirement too well, not being much of an Excel user, but it is apparently helpful when your end users are, how to put it delicately...."particular" or "precise" about how they want the spreadsheet showing up!

Finally Grant showed us a neat little add-in for Excel allowing a user to data-mine within the client. The link for these tools is here. As an example of its use he conducted a little campaign analysis on the Excel spreadsheet. Using the addin he was able to quickly drill down on what influences prompted the users to want to purchase certain products.

Obviously Grant provided a great deal of information and demonstration for us. There's a lot of progress being made in the Business Intelligence space and I thought it was a great overview.
Incidentally, he is a SQL Server MVP and is also the president of the Sydney SQL Users Group (


  1. Richard Weiner2 July 2007 at 10:14

    Agree here with you. I expect SQL will get involved in overall communications more and more. After all, communications are for exchanging data. SQL storage is seen today as the most logical way of data management and collaboration development. The trouble I am experiencing now is the ease of management. And that's especially applies to security management. For example we already deployed the Sharepoint portal where our users exchange their documents. Some documents they use are restricted to the project they belong to and that's not a simple task to organize. Recently, I've learned about the tool from Scriptlogic that manages permissions. So that seems that Sharepoint is gaining popularity in third-party companies, I hope we'll see it integrated into the windows environment more smoothly.

  2. Hi Richard, thanks for the link to the Scriptlogic tool, I'll give it a look.

    You're right that management is a big concern in SharePoint. It seems like SharePoint raises all the issues that have been hanging around IT for a decade, but nobody had to worry about because there were more fundamental technical impediments. I'm not saying this is a bad thing - these issues had to be addressed eventually, and many of them are well understood, but running into all of them at once can seem a bit daunting to any organization!

    I agree that vendors and the community are starting to release the tools that will make all of this manageable, and standards of practice are forming.


Note: only a member of this blog may post a comment.