Can MDX do what nextanalytics does? One of our blogs explored this point. A well known blogger and industry veteran Mosha Pasumansky, accepted the comment as a challenge to demonstrate that MDX could indeed easily produce the sample analytic posed in the blog. Please note, it wasn't actually a very tough analytic, as compared to the other examples we've been walking you through.  In any case, the ensuing interaction was enjoyable for everyone who checked in. Mosha wrote a great post in his own blog and was successful in proving that MDX was easily capable of delivering on the sample query.  His response can be found verbatim here.  

Our own opinion on what he wrote was that it must be understood that when Mosha wrote the code, (1) he assumed the cube already existed, and (2) it took someone (very?) skilled in MDX to produce it.  Mosha is one of the inventors of MDX and an architect on Microsoft Analysis Services engine, and an author of a book on MDX, by the way.

Can you write the kinds of MDX that Mosha can?  If you can't, or you don't have a cube, then we now present nextanalytics as an alternative. In that sense, we compete with MDX. Even if you do have a cube, you can have a combined solution by using a simple MDX query, augmented by the capabilities of nextanalytics.  In this sense, we complement MDX. 

The point is, less experienced MDX and SQL and CSV programmers working with a wide variety of data sources can now perform extremely powerful analytics either using nextanalytics by itself or with it running complementary to the query language and server capability.  All in all, this means that pretty well no matter what data source you use and platform you run on, with only a modest level of business intelligence experience, you can now obtain advanced analytics.

Overview

The blog has the example embedded in it. Here's the solution in nextanalytics.

The full nextanalytics script is shown in the Detail section, following this overview.

Detail

The following is the entire script. It was recorded using an Open Source "Author Mode" which is provided with every download as well as at CodePlex.com. The entire thing can done with clicking, no typing was needed (except for the page titles).  It's the kind of thing a business user could do, without needing a DBA or BI analyst looking over their shoulder.

Notice that this example doesn't need a cube. It runs perfectly fine off a CSV file (from Excel) or SQL query.  It could also run off an MDX server, even if it didn't support the advanced functionality that Microsoft OLAP has such as SAP BW or Essbase.   If you are using an IBM Cognos product, or an SAP Business Objects Crystal product, you could also take advantage of nextanalytics by using nextanalytics to write its processed data into a table or importable file. nextanalytics also has an Open Source module which creates a DataGrid and DataTable on the fly, which means that many popular charting components such as Dundas Visualization can render the results.

By examining the following code, we think you'll agree that it is readable, could be thrown away without a significant loss, or it could be enhanced by a stranger (to the code) when the analytic requirement evolved.

As a convention, we like to keep the SQL in a different file (as follows). This makes it easier to modify it externally, or programmatically as the case may be...