Thursday, July 10, 2008 10:28 PM
ISV Virtual Design Review Team
MCG Consulting explore Reporting Services, Business Intelligence and Performance Point Server
At a recent Fasttrack Architectural review we spent the afternoon with MCG Consulting, who provide commercial and operational systems for the Food and Drinks industry. The session focused primarily on .Evolve, which delivers ERP functionality through a browser tailored for the specific requirements of the Food and Drink industry.
They were an early adopter of .net and their current offering is based on ASP.net 1.1 and SQL 2000/2005. They are now looking towards the future and how they migrate to .net 3.5 and SQL Server 2008 as well as how they could exploit technologies such as Business Intelligence.
Discussion
As usual a broad range of topics were raised and discussed within the session but the main focus became business intelligence and how this could be exploited by their application suite. This is a familiar theme that often gets raised.
We began by discussing Reporting Services. This is a component of SQL Server and is included in every edition of SQL Server from Express up to Enterprise. We would recommend looking at the SQL Server 2008 release of reporting services as this has a series of interesting new capabilities. The first of these is the inclusion of new chart and gauge functionality. It’s now possible to have multiple axes on a single chart and by enabling scale breaks we can also accommodate two widely different scales on the same data (See Figure 1).
Figure 1: Sample report illustrating some of the Reporting Services 2008 features.
Other new chart types include pie charts, donuts, funnels, scatter and polar graphs. The gauges are useful for creating KPI’s pages. Reporting services now provides both radial and linear gauges. So for example you can use gauges that look like speedometers or thermometers. An assorted sample is illustrated below (figure 2)
Figure 2: An illustration of some of the gauges available within Reporting Services 2008
Another interesting new feature is the ability to have multiple groups on the same axes. An example of this can be seen in the report (Figure 1) where we have both a group showing total sales by geography and a group showing the total orders by year on the same line.
In Reporting Services 2005 it was possible to render reports in many different formats such as HTML, PDF, Excel etc. The one format that was noticeably absent was Microsoft Word. This has been added to the list of eight renders which are supplied with the 2008 version. Of course ISV’s have the opportunity to extend this list if they wish.
Because MCG are focused on thin, rather than rich, clients we didn’t discuss the Visual Studio report controls that allow developers to host reports within a rich client. It’s also possible to store the reports on a users local disk as files (optionally be encrypted for security) and still render them in a rich client application using these controls.
A good source of Reporting Services sample reports as well as examples of how to use the Reporting Services SOAP API, Reporting Services scripting and create your own reporting services extensions can be found on Codeplex i.e. http://www.codeplex.com/MSFTRSProdSamples
We moved on to discuss Analysis Services. The idea of OLAP and cubes is often new to many developers. However the basics are pretty simple. The aim of OLAP is to give users the ability to query their data quickly and intuitively. When confronted with a relational database users need to create a complex query to provide the data, which is usually beyond their capabilities. However with Analysis Services, and an appropriate client tool, the user can query the data themselves. One excellent client query tool is Excel. This allows the user to “slice and dice” the data to get the exact answer they require. And Excel 2007 has some pretty nifty graphics of its own (See Figure 3).
Figure 3: Excel 2007 pivot table displaying data from Analysis Services. Making use of some of the BI centric conditional formatting that is available within Excel 2007.
One of the issues that organisations have with using desktop tools like excel is the lack of centralised control. For a variety of reasons users will often end up using different versions of the same report and therefore potentially working from different view of the data. One solution to this is to use Microsoft Office Sharepoint Server (MOSS) which provides with the ability to use Excel Services. Using Excel Services we can store the spreadsheet on a server and render it within the desktop browser. Figure 3 is actually a screenshot from a rendered spreadsheet. So a sheet such as the one above can have up to date figures sourced from Analysis Services but the user is prevented from altering the actual spreadsheet itself. A level of interaction is still possible by marking certain cells as parameters. The spreadsheet can update whenever the user updates the values of these parameters. A more detailed overview of Excel Services can be found here.
This in turn led us on to discussing the advantages of using SharePoint as a BI portal. Having the ability to display various BI components on pages in logical groupings allows users to consume a great deal of information very quickly. An example of a SharePoint BI portal can be seen in Figure4 which illustrates a collection of web parts on a page including several excel views.
Figure 4: Sample SharePoint page from a portal illustrating a series of analytical web parts on a single page.
Finally we discussed Microsoft Performance Point Server. Performance Point provides additional analytical tools such as scorecards, strategy maps, heat maps, interactive drill troughs etc. The culmination of all this technology is that the developer can produce a very comprehensive BI portal containing with multiple pages. Each page can be targeted at a specific business view and can contain a number of web parts which provide the users with a set of view into the data (eg Figures 5 and 6)
At that point we called a halt to the discussions. We covered a vast amount of material from basic reports through to full portals with analytical components on them. MCG left to consider how they could integrate some of the above into a future version of their offerings.
Figure 5: Performance Point Server using a performance map, scatterchart and decomposition view.
Figure 6: Performance Point Server illustrating the use of a scorecard, strategy map.
