5

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.

topwik
  • 3,487
  • 8
  • 41
  • 65
  • something like this is probably the way to go for now: http://www.mdxsamples.com/csharp.html – topwik Apr 03 '12 at 20:04

5 Answers5

7

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
Magnus Smith
  • 5,895
  • 7
  • 43
  • 64
3

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).

ic3
  • 7,917
  • 14
  • 67
  • 115
Marc Polizzi
  • 9,275
  • 3
  • 36
  • 61
1

Indeed, you can use Ranet OLAP version of the codeplex and google.code is not updated, but it is available on the official site of the project Ranet OLAP

HTML5 online demo

Petr Leschenok
  • 33
  • 4
0

One thing we've done in the past that works fairly well, though some might see it as a cop-out - we've used SSIS packages with embedded MDX queries that flatten data out and stores it in two dimensional SQL tables.

For example - we took an OLAP cube and flattened data out by day, week, etc. along with the calculations as additional columns. This allowed us to do super-fast queries against the data using AJAX. This solution works particularly well for MDX calculations that take a while to process for data sets that update once a day or once a week. We'd just trigger the SSIS-to-SQL sync package to run at night after the normal ETL processes had run.

MDX calcs that might have taken 20 seconds to refresh could be flattened into SQL and retrieved in milliseconds, giving the users essentially the same data as their full OLAP reports and instant response times. We then paired this method up with charting libraries to display summary data.

Just food for thought.

Jeff Lewis
  • 318
  • 2
  • 13
  • I am considering working up a demo of a bunch of flattened data i have on Repair orders and parts tickets with row counts of approx 2 million using a fully covering index for each query at each level of detail required (disk space is cheap) data is only updated daily.. I am hoping this will give me the ability go generate summary info on the web side and allow for full export to excel pivots using sax/openxml .... I have not been able to find any component that provides a satisfactory experience with large data volumes THoughts? – Richard Davison Jun 17 '14 at 00:53
  • Sounds pretty close to what we do, though in recent weeks I've actually been working with ADOMD.NET to query directly against one of our cubes and rendering charts in Highcharts.js. So far it's working, though there is processing overhead compared to the flattened data method. – Jeff Lewis Jun 17 '14 at 15:43
0

if you're using C# and are writing your own custom UI, using custom webservices, you might want to try using Ranet. This was an open source project but has since been closed off. I don't know if the commercials would make sense for you. Ranet works by implementing ANTLR, so if it makes sense spending the time to implement this (it's going to be hard) then you can try leveraging as much of ANTLR as you can, which has a .NET distribution. Alternatively, you can use XMLA and ADOMD.NET which will give you intuitive objects to work with for generating charts, but it's an inefficient protocol. If you use Java, you can use olap4j which will give you a lot of what you need. Your options depend a lot on your server's interface. Are you using Mondrian? Do you have an XMLA servlet? Custom webservices?

  • i'll have to look at ADOMD.NET some more. The initial downside was having to build the MDX queries as strings and setting the string as the command text of the data access object. There's no clean OLTP stored procedure equivalent of passing some parameters to a stored proc and having the stored proc worry about the MDX syntax; although i did read something about C# stored procs in the SSAS world... – topwik Apr 03 '12 at 17:52