Different approaches to accessing SSAS

Question

I’m interested in knowing some different approaches for retrieving data from Analysis Services, to use in either objects in code, or for end-user reporting.

I’ve used two different approaches in the past, one was using ADOMD to pull results and put these into a dataset, the other was using SQL OPENQUERY to a linked SSAS server to get results out as a SQL stored procedure result set. Both of these had advantages and disadvantages.

Over the years I’ve seen various questions along this line, so forgive me for any duplication, but what other methods are there for getting SSAS data into a format where other people’s code could use it?

I’ve considered XML result sets from SSAS over HTTP, then Linq to XML – Anyone have any experience with that?

Ideally I’d like a dataset with typed columns, or objects with properties, but I’m more interested in general approach than code samples. How have you got data from SSAS, apart from SSRS/Other dashboard controls?

Answer 1

I know MS is supposed to support XML/A (XML for Analysis). I am shortly releasing an ajax library to do XML/A requests from web pages.

While I am currently focused on Pentaho's Mondrian, it should work for MS SQLs XML/A too. If you are interested, I am doing a presentation on it on January 13. (see: http://wiki.pentaho.com/display/COM/January+13,+2010+-+Roland+Bouman+-+OLAP+and+Analysis+for+web+applications+using+XMLA) I will be releasing my code by that time too (probably underr a LGPL license)

I would love to get feedback from people that use other XML/A servers, so if you are interestd, it would be great to work together on this.

UPDATE:

the project is now available at http://code.google.com/p/xmla4js/ There is API documentation, code samples, and build scripts. It's LGPL so you're free to use it in your applications, even for commercial purposes. The license does require that you release any modifications to the library itself as LGPL (but this does not affect the application that uses the library)

UPDATE2

The project no resides on github at https://github.com/rpbouman/xmla4js It works in the browser as well as in nodejs.

Answer 2

I've never used it myself, as we only use ADOMD and Excel to connect to SSAS, but at some point we considered using HTTP and XML. We ended up going the ADOMD route because of a shortened dev schedule, but I guess it's another option that allows for access to SSAS outside of the .Net world.

Here's a link which I found useful when prototyping: Configuring HTTP Access to SQL Server 2008 Analysis Services on Microsoft Windows Server 2008

Answer 3

XMLA is the "high power" approach -- but I'm not aware of a toolkit or library that really exposes the full capabilities of XMLA; I think you would have craft it up yourself. For the projects I've done, that's just way too much work.

Instead, I used ADOMD.NET for retrieving results in code; the CellSet class in particular is fairly rich. For end user analysis (slice and dice), most often I use Excel Pivot Charts (which are fabulous!); sometimes I also use Visio Pivot Diagrams. For fixed reporting, Reporting Services can access SSAS directly, and it even has it's own query builder.

BTW, in case it helps, I have a chapter in my book about integrating SSAS with web sites as a way of offloading SQL Server: Ultra-Fast ASP.NET. My code examples use ADOMD; I also walk through building a simple cube, configuring automatic updates with SSIS, using proactive caching, building simple MDX queries, etc.