What is the best way to use SQL Server Analysis Services data in a line of business application?

Question

We'd like to see if we can get some improved performance for analysis and reporting by moving some of our key data into Analysis Services cubes. However, I haven't been able to find much in the way of good client front ends.

Our users have Office 2003. The move to 2007 is probably at least a year out and the Analysis Services add-in for Excel 2003 isn't great. I also considered just creating a winforms app, but I haven't had much luck finding robust controls for SSAS data. Meanwhile, Reporting Services seems to make you force your multi-dimensional data into a two dimensional dataset before it can be used in a report.

I hope that I'm missing something obvious and that there are some great client tools somewhere that will allow us to bring the multi-dimensional data from SSAS to a client application in a meaningful way. Performance Point seems like overkill, but maybe it's the best option.

Does anyone use SSAS data in line of business apps or is it primarily used for adhoc analysis? If you are using SSAS data for line of business apps, what technology(ies) are you using for the client front end?

Answer 1

I've heard good things about this control.

http://www.datadynamics.com/Products/DDA/Default.aspx

Answer 2

I am on a project now that is using SSAS 2008. We were able to get all of our pilot users upgraded to Office 2007 and during the pilot of this project we used Excel 2007 as the front end tool. It turned out to be a great move (for us..YMMV) and I have been very impressed with how well the data features of Excel 2007 work. That being said it doesn't serve all our business needs and we're still going to use a reporting tool (MicroStrategy) as part of the client tool offerings to this project. This article (http://www.microsoft.com/downloads/details.aspx?FamilyId=2D779CD5-EEB2-43E9-BDFA-641ED89EDB6C&displaylang=en) was very helpful too.

Though you didn't ask directly I'll still say that the FE tools won't do much if the back-end design isn't right. I recommend googling Ralph Kimball and buying The Data Warehouse Toolkit book. There is even one tailored to SSAS 2005. Also search for the Microsoft Project Real whitepaper.

Answer 3

I've used Dundas' control for OLAP. Very good and easy to use.

Answer 4

Also recommended is the DevExpress kit, the ASPxPivotGrid works directly against cubes, with some flexibility over what & how groups/dimensions/etc get shown via properties. Good prices and products. I don't work there :)

Take a look at some of the Demos:

http://demos.devexpress.com/ASPXPIVOTGRIDDEMOS/OLAP/Browser.aspx

Also nice integration with their charting stuff.