Best option to retrieve Anaylysis Services (SSAS) cube data dynamically in ASP.NET MVC

4
votes

I need to dynamically add/remove dimensions and facts in web application and get results from SSAS. The cube in SSAS is defined and ready to use. So far the best idea I found is use MDX queries to get metadata from SSAS cube and display those data in web page. Then user can choose needed dimensions/facts and I need to costruct dynamic MDX query and get results from SSAS. Communication with SSAS is made with AdomdDataReader. Is there better options to solve this task that require less effor? Maybe somehow make use of Reporting Services (SSRS).

2
asp.net-mvcssasmdx

2 Answers

5
votes

You have a lot of options:

  1. Use LINQ + ADO.NET Entity Framework + SSAS Entity Framework Provider - this is the easiest way, especially if you need ASP.NET code dependent on SSAS data. Some data grids like ASPxGridView will allow users to change column order, filters and row order and generate LINQ changes dynamically in response to user actions.

  2. ADOMD.NET + MDX - you can do anything that way, if you know MDX really well, but it is a lot of work for a dynamic UI and I do not recommend this option if # 1 can do the work, especially if you need ASP.NET code dependent on SSAS / ADOMD.NET data (MDX + ADOMD.NET provide no type safety, no refactoring and unit testing support).

  3. (It is not MVC, but a regular ASP.NET option) For a maximum user level flexibility use Pivot Grids like XtraPivotGrid - your users will be able to move rows to columns and backward, change filters and order and the pivot grid will generate changed MDX for you. Again, I do not recommend this option if you need ASP.NET code dependent on this data.

(I work for Agile Design LLC - the company that created SSAS Entity Framework Provider)

EDIT: per the comment below: -Yes, SSAS Entity Framework Provider works with ASP.NET MVC and WebForms. The MSI includes a demo Web application.

0
votes

Do you mean you have to dynamically modify the cube structure? If so, that can be done via AMO: http://msdn.microsoft.com/en-us/library/ms345093.aspx

If you need to build a GUI for MDX queries, check out this: http://ranetuilibraryolap.codeplex.com/

Its not specifically for ASP.NET (Silverlight is supported though), perhaps you can reuse some of the code.