Best BI Software for Kimball Method?

I am a financial analyst with an MBA trying to gain a depth in understanding the BI universe. I am currently devouring the DW Toolkit, 2nd edition book, in addition to my own research, and have learned a great deal in only the past few weeks. In my department, we are fortunate to have as our core data a well implemented dimensional model, although it is purely Access 2007 based.

I've witnessed first hand the power of this approach and would like to see it move into the professional software space.

I would like to hear people's thoughts on what BI company (SAP, Cognos, Microsoft, Oracle, etc.) would be the best suited for the Kimball philosophy on data warehousing and also which ones would be anathema to such an approach. I think I already have a good feel for SAP BW based on this posting!

Please forgive my naivete if this question is too simplistic. The jargon and sometimes slippery definitions I am finding in the BI world makes it difficult to navigate.

Thank you!

Its getting harder to figure out who has what these days, with all the consolidations and marketing hubris going on. But if you ignore platforms and "solutions" and just look at end user reporting and analytics tools, all the major vendors do a good job of supporting dimensional data warehouses implemented on relational platforms. In this list I would include Business Objects (SAP), Cognos and MicroStrategy (in no particular order). I would not include Microsoft because their tools are tied to their database, not because of any lack of capability or functionality.

SAS has an analytics product geared towards non-techical business users, but it has not had much success, but their core statistical product has been the tool of choice for heavy-duty statistical analysis and modeling. There are also a slew of smaller companies and open source tools out there, but I have not used any of them so I am unable to comment.

As far as database platforms go, Oracle does the best job implementing star schemas in relational form in a shared resource hardware environment. SQL Server has hybrid capabilites, support both relational dimensional models and MDDB (i.e. 'cube') data structures. Their relational support for star queries is somewhat pedestrian compared to what Oracle offers. For highly parallel MPP environments, I like the approach Aster Systems takes when dealing with star schemas in those environments. They allow replication of entire dimension tables across hardware partitions so that dimensional data can be co-located with the facts.

Thanks very much for the quick reply!

We use Cognos as our EP tool (combined with our internally built Access budget consolidation system). It seems Cognos does a good job with dimensional modeling - at least as it is rendered in it's publish containers. My perception is that the SAP BW is like a Swiss Army knife in that it is can do a lot of things, but no one thing really well (meaning simply).

We currently have three different data warehouses: SAP BW, Cognos, and Microsoft - although it seems Microsoft simply took their SQL Server product and relabeled it DW. My current work is to see which of these three vendors (or a fourth option) would be the best for the future. I am starting up a pilot study that takes our DM-style financial data and putting it on a SQL Server. It's integration with Excel & SharePoint for reporting & dashboards intrigues me.

I also am interested in the Excel Data Mining Add-In, but have read somewhere that data mining only works in fully normalized schemas (vs. star schema). Is that correct?

I also am interested in the Excel Data Mining Add-In, but have read somewhere that data mining only works in fully normalized schemas (vs. star schema). Is that correct?

No. A well designed dimensional DW on a relational platform is a far better choice for data mining. Data mining typically involves ad-hoc queries against large data sets. A star schema is optimal for those types of queries. It also usually involves mataining detailed (atomic) level data, which is why you need a relational platform (as opposed to a MDDB (aka cube), which stores data in aggregate).

Of course, you can use a normalized schema, but queries can become very complex and, generally speaking, performance will be terrible.

I am very relieved to hear that. Thank you very much for your help!

>>It's integration with Excel & SharePoint for reporting & dashboards intrigues me.

I've been using Microsoft SQL Server 2005/2008 for a couple of DW initiatives over the last couple years. It is a solid DW platform with a lower barrier of entry (less expensive/easier to get started with) than most of the "enterprise" class systems. However, there are some holes in Microsoft's BI stack, and not all the tools are seamless.

I find SSIS only so-so as an ETL tool. It does a good job of moving data and the package concept is useful, but it certainly won't help you build a "self-documenting" ETL solution (as claimed by dedicated ETL tools such as Informatica.) I all too frequently find myself reverting to handcoded SQL instead of using SSIS.

SSRS is a pretty good basic reporting tool, and SharePoint integration offers some interesting options for delivering reports (users can run their own reports through SharePoint, or reports can be pre-generated to a SharePoint document library.) In addition to developed reports, there are currently two tools for end-user ad-hoc reporting: Report Builder 1.0 (easy to use, but limited) and Report Builder 2.0 (much more powerful, but not as easy). I believe SQL 2008 R2 is supposed to include the best-of-both-worlds Report Builder 3.0.

For drill-down analysis, Excel is an excellent client against a SSAS cube. Very little training is required for users that already use Excel... as far as they are concerned, they are working with a pivot table.

While Microsoft has done a reasonable job covering reporting and analysis, they have somewhat neglected what I call the middle ground of BI: dashboarding. First off, there is not a good low-cost solution here. Another big problem has been a lack of product stability. Their dashboarding/scorecarding/KPI functionality keeps jumping around from one product to another, and is about to jump again:

ProClarity --> Microsoft Performance Point Server --> SharePoint 2010

Hopefully that will be the last jump for a while! It looks like SharePoint 2010 will be a big improvement over SharePoint 2007. (The concept is good with 2007, but for a 3rd generation product it behaves way to much like a 1.0 version!) With 2010 just around the corner, I would not invest in a SharePoint 2007 deployment at this point.

Please don't come away thinking I don't like using Microsoft for DW--I do. In fact, SQL Sever is my favorite Microsoft application! I'm just trying to give an objective view of some shortcomings. I expect to stay on Microsoft as DW/BI platform, but maybe add some Business Objects into the mix on the BI side as our corporate visibility (and budget) increases! We are also about to pilot Dundas Dashboard as a low-end dashboard/scorecarding solution.

Thank you very much, VHF!
I really appreciate your honest assessment.

The SharePoint 2010 dashboarding approach is news to me. I'll have to do some research on that. We've already deployed SP 2007 at my company and it has helped us in our deparment with some improved functionality.

I'm attempting to teach myself SSAS (a business-side guy who is very comfortable with Cognos Planning at least), which may be a quixotic effort. But, I am encouraged to hear that there are some good opportunities down that road.

If you're looking for information on what makes for an effective dashboard (i.e., visual business intelligence), take a moment to check out Stephen Few's website: www.perceptualedge.com. His book, Information Dashboard Design: The Effective Visual Communication of Data, should be required reading for anyone presenting data (check out his "Library" link). He demonstrates how to present information that leverages human visual perception: i.e., his concepts are based on science. His writing style is easy to read and comprehend; no advanced degree required to understand his point.

Off the top of my head, two companies whom I believe have taken Few's principles to heart are xlcubed.com (Excel add-in) and tableausoftware.com. Fortunately, Few's principles are software independent, which is ultimately his point. I've been able to transform, successfully, how I present charts/graphs in SSRS, Excel, and Crystal Reports.

Thanks very much, pzajkowski.

I read and became a huge fan of Tufte years ago. I am glad to see there is an author of a similar mindset but coming from a business data perspective (as opposed to scientific/academia). I'm adding his three books to my wish list. The dashboard one is particularly interesting.

Thanks again!