« Public MDX Classes | Main | Tableau Software »

OLAP Front-Ends and MDX SELECT

I'm reposting this 2003 article because, unfortunately, it's still pertinent:

Choosing an OLAP Client

an important criterion that everyone overlooks
Tom Chester, 2003

Somehow it has come to be that most of the popular off-the-shelf OLAP clients are fundamentally crippled compared to their relational brethren. And this despite the fact that OLAP is supposed to be enabling technology.

To compound matters, this (as yet undefined) drawback tends to slip beneath the radar. That is, it is a criterion not typically considered when organizations evaluate OLAP front-ends.

The Problem

Can you imagine selecting a relational front-end tool that doesn’t allow a developer to specify a SQL statement? Of course not, this would be a non-starter. Yet this is the lay of the land in the OLAP niche. Many of the shrink-wrap clients that work with Analysis Services do not allow the developer to attach a query – an MDX SELECT statement – to a given view or report. This is even true of the venerable Microsoft Excel, arguably the most commonly used front-end to Analysis Services. Whereas Excel allows a SQL SELECT statement to be attached to a PivotTable (when sourced from RDBMS), and has done so since the inception of PivotTables in version 5.0, it does not provide the ability to attach an MDX SELECT when the PivotTable is sourced from a cube. Excel is not alone here.

And since the developer community takes this in stride, blithely expecting the respective canned UIs to answer every possible question posed by users, no pressure is exerted on the software companies to get their acts together. 

There’s a potential price to pay, a looming pitfall which this article intends to shed light on.

Who needs to SELECT? 

Some argue this is a moot point; that one of the main purposes of OLAP is get out of the customization mire; that a primary value of shrink-wrap clients is to eliminate the need for developers to write MDX SELECT statements; that basic cube browsing is all that’s needed by users, thank you very much. 

These are admirable sentiments. But the fact is, once users get past the initial euphoria of plain vanilla cube browsing, there is an inclination to start asking more complex questions. Not to mention the hard-core analysts, who need to ask complex questions from the get-go. There are countless questions that basic cube browsing cannot easily answer. Questions such as “What are the top 10 stores, and what are the top 3 products per store?” Or “Show me sales for each day of the year that falls after a holiday.” 

Naturally, different client tools expose different functionality. The more robust clients indeed go beyond basic cube browsing, and provide user interfaces that allow more complex questions to be asked and answered. While this functionality is to be applauded, this is a slippery slope, one that leads to an inordinately complex user interface for the majority of users who don’t need to do the fancy stuff. Not to mention the fact that even the most robust clients, those capable of answering a myriad of complex questions, fail to come even remotely close to exposing the full power of the MDX language.  

In short, you may never need to write an MDX SELECT statement. However, in the event your users require functionality not provided by a shrink-wrap UI, be careful not to paint yourself into a corner. 

How to Tell

You might think it’s a simple matter to discern which tools support MDX SELECT. Not quite. To be able to pose the right questions to your prospective software vendor, and make sense of answers that are apt to be confusing, you must understand the role of MDX within Analysis Services. 

MDX, an acronym for Multi-Dimension eXpressions, is more than a query language. It is also a calculation language. MDX is used to define calculated members, calculated cells, and a handful of other calculation constructs within Analysis Services. While calculations can be defined on-the-fly as part of an MDX SELECT statement, they are typically defined in the cube, within the OLAP database. When calculations are defined in the cube, they are along for the ride, free for the taking. That is, MDX calculations defined in a cube are automatically exposed via any and all OLAP clients.

Software vendors, less than eager to reveal their weak points, take credit for supporting MDX by virtue of the fact that MDX calculations are viewable. But that’s not the issue. The question to pose to the front-end vendors is this: “Does your product allow a developer to specify a custom MDX SELECT?” And since many of the vendors market a suite of products, don’t assume that just because product A supports MDX SELECT, that product B from the same vendor does so too.

Does Data Source Flexibility Merit an Exception?

There are shrink-wrap products that are tightly bound to Analysis Services, and those that are not. The tightly bound products, such as ProClarity (from the company of the same name) and Novaview (from Panorama Software, the original developers of Microsoft’s OLAP engine), are well integrated with Analysis Services, and expose many of the server’s advanced capabilities, including MDX SELECT. Then there’s the generalist camp – those front-end tools that work with a variety of data sources, at the expense of tight integration with any particular back-end. These are the players who tend not to expose MDX SELECT. I have one question to pose to these vendors: Do you know how to spell pass-through?

In other words, support for MDX SELECT is not a lot to ask for; no fancy UI required; just give developers a place to stick a (long) text string, and pass it through. As most of the vendors are no doubt working on XML/A implementations, this is the perfect opportunity to provide a sorely needed capability to the developer community.

PrintView Printer Friendly Version

EmailEmail Article to Friend

Reader Comments (11)

thanks a lot dear, im very interesting for your article. im very impresing for this :)

jasa iklan

April 16, 2010 | Unregistered Commenterjasa iklan

HHH Yes, the design of national policy is important, how our economic development plans for the next five years, how the implementation, how to make our economy even faster. Are designed to advance our focus to invest money in what ways it should be carefully arranged. psfoxp psfoxp - supra shoes.

October 18, 2011 | Unregistered Commenterogcssq ogcssq

Go the Salvatorre Ferragamo way- Need to pep up your day at work? Do it with style and rightly so coach purse outlet. Get a smart and sassy fashion handbag inspired by a Salvatore Ferragamo. Make sure it comes with a dual buckle in pastel shades of lime greens and lemon yellows. The entire look should be compact yet the coach outlet store online interiors should be spacious with loads of compartments.

Go the Guess way- Need a tiny and trendy handbag for an evening out at the coffee shop then make it look like an exclusive piece from the coach purses outlet Guess fraternity. Single straps that have a silver tone, lots of studs on elegant faux patent leather can take you places! Look out for embellishments and rhinestones that louis vuitton add a designer element to your piece.

Go the Prada way-If you need a fashion handbag that is large on space and louis vuitton outlet also chic then go in for pretty pinks with elegant top and center stitched detailing. Ensure it comes with magnetic snap closures for easy access when you are on the go. This spacious cheap coach purses can look sophisticated when accented with silver tone rings.

Go the Calvin Klein way- If you have a fetish for clutch bags, then go in for a silver colored one or make it pure black to match your exclusive gowns. Get a long frame for a classic look. A smooth and sleek curved clasp would be the icing on the cake to achieve this look. Look for an inbuilt shoulder strap, which makes it more versatile.

If you are not fortunate enough to own a trendy designer-labeled louis vuitton outlet then you can source stores that offer beautiful creations on the lines of a Gucci or a Christian Dior. Perfected with precision, these fashion handbags are bound to save you a few bucks and you can be a fashion diva without really spending like one!

A trip through any trendy mall will reveal a plethora of brands that offer a multitude of designs, shapes, textures and sizes. Dolce and Gabbana, Fendi, Christian Dior, Louis Vuitton, Chanel, Versace and coach purses many such brands have rewritten the traditional norms of fashion and the way handbags are being made today.

When fashion goes the designer way; it does not always adhere to branded clothes or branded shoes. Women are known to carry one-of-a-kind coach purse to add some bling to any subtle outfit. All fashionistas spend hours scrounging through the most sought-after branded outlets in their search for the ultimate trendy louis vuitton outlet. An important part of any ensemble, designer handbags can either make or break your entire look! It’s about finding the right match and using it with great élan.

November 23, 2011 | Unregistered Commentercoach purse outlet

To be honest. How nice! Very good, and we share information! Too amazing things. Thank you for your masterpiece, worth reading. rqxyep rqxyep - Shop Hermes Bags.

November 25, 2011 | Unregistered Commenterynksyk ynksyk

Tory burch flats are a very good investment when it comes to shoes, these kind of replica replica tory burch replica tory burch shoes sale boots tory burch boots tory burch replica shoes for sale reva tory burch outlet tory tory burch boots online burch reva flats shoes tory tory burch outlet online tory burch factory outlet online store burch outlet online tory burch boots cheap fake will last a long time.If tory burch boots replica tory burch sale replica you are looking for a fashionable,modern tory burch boots online shop and be up fake tory burch boots replica tory burch flip flops replica tory burch bags to date with the latest flat styles without tory burch outlet online replica tory burch flip flops going over your tory burch flip flops budget, tory burch tory burch flats flats are the right option tory burch outlet sale online replica tory burch flats for tory tory burch replica flats burch replica boots you.Tory burch flats are are well designed with a a large,metallic, tory burch replica bag circular logo placed near the replica tory burch bags toe does little more than draw the eye along the length of replica tory burch boots the foot, the ballet flats are available in assorted colors,from grass green to orchid pink, purple and papeete turquoise,stylisy and colorful.Tory burch flats are perfect for busy days about town when compromising on style is not an option.The best choice in tory burch flats is the reva flat. This type goes with any style and shall remain a Tory burch boots classic for years to come. Before you head out to buy your tory burch flats, This will ensure that you are getting shoes of the best quality of materials that will be comfortable for tory burch replica shoes your feet.Tory burch flats are the closest to what i would call a must-have pair of shoes.

December 2, 2011 | Unregistered Commenterhemin

I think you are right when you say this. Hats off man, what a superlative knowledge you have on this subject…hope to see more work of yours.

March 1, 2012 | Unregistered Commentercheap jordans online

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>