How are most people using MDX queries? Should I be using MDX results to drive a web UI? [closed]

Question

I'm trying to do some analysis for an upcoming project.

It has something to do with trending, charting and analysis; so think MAX, MIN, AVG, SUM etc over a period of time.

Say we have an OLAP cube that's setup to figure out these calculations against a time dimension.

In theory the backend is there to query the cube and get the results for some object A for some property B for a bunch of days in a month or a year or whatever the case may be (i.e over the last 5 years and you can manipulate the rendered date range by using a slider window similar to those used on finance stock charts to expand or narrow your field of view).

Some of us are thinking we can query the cube using MDX to drive a UI that uses some HTML 5 charting tools.

I'm new to OLAP, MDX, Cubes etc but it seems that there isn't a clean way of retrieving the results of an MDX query in .NET code (we'll be using C# in an MVC web site).

So far what we've found will probably work best is ADOMD.

I'm wondering if there are any alternatives folks can suggest.

Is anyone using an OLAP cube and MDX queries to drive their web site?

It seems to me that if the cube is already setup properly to answer questions like object A for property B for the last 2 months, then we should be able to query the cube for exactly that data and display it how we see fit on some UI. I'm not sure there's a clean way of doing so though.

Any suggestions, insight, ideas would be appreciated.

Answer 1

5 years ago...

I worked on a project which had a drag-and-drop interface (HTML and an awful lot of JS) to allow users to construct custom cube queries exactly as they wanted. We called an ASP with ajax to go get the cube data with ADOMD and return it as an HTML table. Charting was via custom JS which created SVG. (It would be easier these days with .NET and JQuery, but still a lot of work.)

It sounds ropey, but worked well and was reliable enough to sell to the UK's largest hospitality firms to analyse their sales data.

Whenever I had a problem, and Googled for help, it seemed like I was the only one who was presenting OLAP data over the Internet (most people were within an intranet situation, and wewre not using web-browsers to deliver either). I don't know if the situation has changed now, but I do believe OLAP is seen as a way to look at your own data, not manage a customer's data and enable them to see it.

My advice:

1. If you hear the words 'pivot tables' then run away
2. If customers want user-level or location-level security (roles), run away
3. Make sure you are very good at writing MDX, since people will ask you to construct complex reports for them (they won't always want to use a UI to configure a report)
4. Cubes take time to build, but sometimes people expect to see real-time changes when they hit refresh (SQL does this, but OLAP needs reprocessing time)
5. Drillable charts are cool, and great for finding anomolies

Answer 2

I would say that OLAP & Cubes are quite well suited for this task and you're not barking up the wrong tree. I'll take the example of icCube and its Web reporting; the charts you can see on the links are generating MDX queries based on the current filter selection; you get as well events (e.g., cell clicked) and drilldowns support. This is done in pure Javascript so you should be able to integrate it in your own Web site. The tool has been designed to primarly target OEM solution; so it is highly configurable to meet your own need; for example, you can use your own charting library (currently supported: Google, ExtJs/Sencha, amCharts, protoviz, flot).