Friday, September 25, 2009

Which cube browser for Microsoft OLAP

I am often asked which cube browser I recommend for Analysis Services cubes. The answer is typically a few minutes long, since there isn't one ubiquitous cube browser that satisfies everyone. It's a bit like asking someone which vehicle you recommend. It really depends on what the purpose is and what you value. A high performance sports car might be appropriate for some people, but would be inappropriate as a family car or to tow the boat to the coast. For me, the best vehicle is a sports bike, but this would be inappropriate for most road users. The same applies to cube browsers, some users need something very simple and easy, while others need sophistication and flexibility. Having said that if Microsoft Excel had a few more features, it could quite easily be the ubiquitous browser.

Two cube vehicles that complement each other well, unsurprisingly, are Microsoft Excel and SQL Server Reporting Services (RS). Excel is very flexible, while RS can deliver tailored reports, with flexible parameters and easy links to drill down/across to other reports.
In this way, RS is perfect for naive users that need easy to consume information with click-through for more detailed and associated information. Note; RS does not support ad hoc cube browsing. Microsoft also have PerformancePoint, which is a great tool for delivering visual data to a thin client. It has functionality somewhere in-between Excel and RS and many features not available in either. PerformancePoint supports (very rudimentary) ad hoc cube browsing, but it is very powerful at delivering visual and interactive data. So PerformancePoint is ideal for presenting visual data to naive users, and for data savvy users there can be a great deal of “data flexibility”. PerformancePoint can deliver this in a semi structured way, so that users don't drive a cube browser, rather, they drive a dashboard of their data. There is a good example of rich PerformancePoint dashboard on SQLS Live Dashboard. Notice how you can click on scorecard items, and they act as filters on the charts.

Another important cube browser (although not yet truly ad hoc) is Excel Services. Excel Services is essentially an Excel spreadsheet accessed through a thin client, Internet Explorer. Any spreadsheet can be published, even one that is attached to an OLAP cube, so it can be a useful vehicle to deliver live cube data. There are a couple of good Excel Services examples on NZ Census. Notice how you can interact with the spreadsheet by drilling down hierarchies and applying filters. Note too; how the data is live (you might want to switch to the weblogs spreadsheet). However, in the current release of Excel Services you cannot change what is on rows, columns and filters. I have many customers that love Excel Services, since it permits their Finance dept to design and publish reports entirely within Excel, and their report users always see live data.

There are hundreds, if not thousands, of third party cube browsers. They really aren't that hard to develop.
I wrote the first thin client cube browser, ThinSlicer, which is still used by many organisations. When I wrote the first version of ThinSlicer, in 1998, I thought that its shelf life would be short; surely Microsoft will offer a highly functional, low cost, thin client cube browser? How wrong could I be? It is now 10 years on, and the only ad hoc thin client cube browser we have from Microsoft is PerformancePoint, which while it has an ad hoc cube browser, it is not the reason anyone would buy it. An example of a more functional thin client cube browser would be Report Portal. This product supports quite strong ad hoc cube browsing and charting. I must add that there are many other commercial cube browsers, and I really don’t want to list them, since the list and relative strengths are continually changing.

If you are embarking on a data warehouse or business intelligence project, I would encourage you to leave the cube browser choice until quite late in the process. (Just like you shouldn't purchase your production hardware until you are almost ready to deploy a solution.) This is particularly true if you are not familiar with cubes and are not sure what you and your users will need. Microsoft’s SQL Server is probably the best foundation for a data warehouse/business intelligence solution. Particularly its OLAP cube and related technologies such as data mining. I would encourage you to use Excel 2007 and Reporting Services (which is free with any version of SQL Server) as the initial cube delivery vehicles, and trial other products when, and if, you find the need. If you are looking for better thin client visualisation, do try PerformancePoint and Excel Services before you engage with third party vendors. These products are very useful, and they are likely to be greatly improved in future versions. I should add that PerformancePoint dashboards will be incorporated into SharePoint MOSS in future, which makes a lot of sense to me. One other consideration, if you are delivering to users outside of your Intranet/internal network is licensing. SQL Server RS and Excel are quite easy to licence for use over the Internet. Unfortunately, it’s not so easy with PerformancePoint and Excel Services. If that is what you would like to do, I suggest you talk to your Microsoft reseller early on.


Sandeep Giri said...

If you are looking for an open source solution that is platform independent, look into OpenI (pronounced "open eye") - (full disclaimer: I am the project lead of this open source project)

OpenI is an open source BI application for on-demand or SaaS deployments. Based on J2EE, OpenI enables you to easily visualize data from OLAP and relational databases, where you can intuitively build and publish interactive reports, analyses, and dashboards.

OpenI has 2 specific features for OLAP cube browsing -- first is you can create a new OLAP-based report by selecting a cube, and choosing which measure/dimensions you want on your cross-tabs and which chart types you want to use; the other, perhaps more interesting, is called "explore data" where you point to a cube and select the measure(s) you are interested in -- and the tool automatically creates a dashboard with multiple charts, each displaying how the measure varies with respect to each single dimension in your cube -- in practice, we have found this feature to be very effective in "eye balling" the data in the cube and discover more interesting measure-dimensions relationships from a single view

richard_w_parsons said...

Nice post - a good summary of how to approach. And yes, there are a great many third party cube browsers out there. Fancy one myself, actually. What I like about the CALUMO approach is the tight Excel integration, and simple web publish Excel to web. BI is obviously about more than slicing and dicing data and ad hoc analysis. The purpose of BI is BPM - to enable us to trigger a positive shift in the business. Being able to very quickly (and iteratively as necessary) move from viewing to analysis to reporting to distribution to interactivity (with fast effective write-back) is critical. In choosing your cube browser, also look for open doors to immediate business performance improvement outcomes.

snap said...
This comment has been removed by the author.