MDX Expression Builder : Need for a tool making it easier for functional users to write MDX expressions, queries.

MDX is great for business problem analysis. Moving averages, % of parent, YoY growth etc are business needs and surely MDX can handle it. There is only one hitch though. Most of the business users can not write it. There are so many tools including SS Management studio which allows me to write complex SQL queries many a times just using my mouse, but hardly anything to write MDX expressions as flexibly.

To make a start, at least a tool can be provided with the below functionality:

1. The interface would display the dimension hierarchies and and an area to create MDX expression. The area would have two panes, one which shows the MDX expression in AST hierarchy and other pane shows the real MDX script text.


2. The user can write MDX expression directly in the MDX script text pane and wheh toggles to AST pane, the expression would be shows as a expression tree.


3. The user can drag and drop the functions, operators or dimension members onto the AST pane and when switch it back to MDX script text pane, the MDX query text would be regenerated.


4. Of course, some wizards, templates can be pre-stored for common business expressions which gives the functional user some starting point to use it as is or further customize it.

In MDX studio by Mosha, I can paste a MDX query ant it generates a Syntax tree which i find extremely useful to analyse complext MDX statements. The problem is it is only one way, i.e. MDX query text to tree and not the other way around. Long back, I had seen an evaluation version of a tool named as Hungry Dog (if i remember correctly) which used to do it and I can not find them anymore.

E.g. in below screen, the left side provides a tree representation of a MDX expression and right a text representation. The tree one is easier to understand. Over time, the it could be made much more user friendly.

image

Unless we have something like above, we can not service those super intelligent functional users who understand business need in depth but can not analyze it themselves fully due to lack of a good tool to define MDX expressions.

If anybody knows a good tool which works on Analysis Services, kindly let me know.

2 comments:

Anonymous said...

Can anyone recommend the top IT automation utility for a small IT service company like mine? Does anyone use Kaseya.com or GFI.com? How do they compare to these guys I found recently: [url=http://www.n-able.com] N-able N-central support network
[/url] ? What is your best take in cost vs performance among those three? I need a good advice please... Thanks in advance!

manav said...

Great thoughts you got there, believe I may possibly try just some of it throughout my daily life.






Function Point Estimation Training

My Articles

Design

Cube structure optimization for MDX query performance in Analysis Services 2005 SP2: Tips for Parent Child Hierarchies usage

Fact table design for “State Workflow Analysis”: Analysis Services Dimensional modeling

Handling inter-dimensional members dependency and reducing cube sparsity using reference dimensions in Analysis Services 2005 SP2 : Cube design tip

Identifying intra-dimensional members relationship and reducing cube sparsity in Analysis Services 2005 SP2 : Cube design tip

Leaves() : An example to understand it for both regular hierarchies as well as parent child hierarchies

Aggregation design: useful tips

Level based attribute hierarchy: MDX query performance woes in SQL Server 2005 SP2: Is it fixed in post SP2 hotfix?

Parent child hierarchy to level base hierarchy conversion: hiding placeholder dimension members in client application

Trouble / Troubleshooting

Aggregate(), Sum() functions using calculated members does not work in Analysis Services 2005 SP2 (9.00.3042.00 version) but works in Analysis Services 2000 SP4

Analysis Services 2005 migration tool: Custom member formula issues in migrated database

Cube Partitions: Fact table not listing in Business Intelligence Development Studio in partition wizard

Analysis Services 2005: Many-to-Many relationship does not support unary operators with parent-child dimension

MDX

NextAnalytics and MDX : Part 1 - Swap Cells with Row Labels

Selecting dimension's default member based on a member property

Sorting members on member codes / member properties

Time Dimension: How to set Default Member to Current Month

Setting dynamic default member in dimension X based on the current member of dimensions Y

ADOMD.NET

Code : utility code for converting cellset to a data table

Others

Google specialized search for Analysis Services and MDX web resources integrated in my blog

Art of reading MDX articles

MDX Expression Builder : Need for a tool making it easier for functional users to write MDX expressions, queries.

Blogroll