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 (http://www.sqlserver.org.au/).