MDX Builder 2.0

MDX experts: Spend your time developing, not debugging

Logic problems and syntax errors are the two most common pitfalls for developers as they code utilities and applications. So programmers have come to insist that their development environments contain sophisticated tools that can help them avoid syntactical errors. MDX queries are even more susceptible to problems that stem from improper logic and syntax errors because MDX queries incorporate special characters such as braces, brackets, and parentheses that help define multidimensional data. BiSense's MDX Builder 2.0 for SQL Server 2000 Analysis Services and SQL Server 7.0 OLAP Services provides a visual development environment for MDX that lets query writers and developers focus their attention more on producing desired results with MDX and less on debugging errant code.

Setting Up the Scenario

To begin my testing, I downloaded MDX Builder Professional from the BiSense Web site and installed it on my Windows 2000 Professional machine without incident. Next, I supplied a serial number and code, which the program requires when you execute the full version for the first time. The first window that opened after I launched MDX Builder asked me to specify the OLAP server connection. After I selected my server and appropriate logon credentials, the program presented a list of OLAP databases. I chose to use MDX Builder Professional against a SQL Server 7.0 OLAP Services machine because my custom-designed cubes reside in that environment.

After I selected the database, I chose my Finance cube from a list of cubes in my OLAP database and loaded it into MDX Builder. This cube contains accounts receivable information for healthcare agencies. Then, a window opened to MDX Builder IDE, a suite similar to Microsoft Visual Studio. MDX Builder IDE comes with multiple dockable windows and a complete list of MDX functions that you can drag and drop onto the Visual MDX Diagram window. Equipped with the list of functions and all the defined dimensions and measures from my Finance cube—Diagnosis, Patient, Branch, and Physician, for example—I was ready to begin building my query. Before I started, however, I stepped through a small tutorial that loads on startup and familiarizes you with the MDX Builder interface. The tutorial also walks you through a simple query example that uses the MDX function TopCount() with data from the FoodMart cube that Analysis Services and OLAP Services provide.

Satisfied that I understood the MDX Builder interface, I used the tutorial query concepts to build a simple query that would run against my Finance cube. Building the query on an MDX Builder DynamicCube, which displays the MDX query elements graphically, I chose my Cost measure for the columns axis and the Diagnosis dimension for the rows axis. Next, I watched MDX Builder dynamically build the MDX text query as I dropped the graphical elements onto the Visual MDX Diagram window. Then, I ran the DynamicCube. Although the query returned useful data in the DynamicCube Viewer (another small window in the IDE), I needed more detailed data and other measures. I noticed, however, that when I double-clicked an axis that contained detail members, the drill-down functions automatically dropped into my cube.

Building the Query

I was now ready to build a complex query. The first step in data analysis with OLAP is to formulate business logic questions that the query will return answers for. So, I devised the questions "What are my 10 highest cost-of-care diagnoses?" and "What was my profit margin for these diagnoses?" To generate reports that would answer the questions, I had to develop a more complex query that contained a calculated measure for Profit. And to produce the calculated measure, I had to use the Charge and Cost measures.

My next task was to review the function list in the MDX Builder function library, which contains all the MDX functions available for Analysis Services and OLAP Services. As I selected each function I wanted to use, a dialog box informed me that I could press F1 for help with the function. I was looking for specific how-to examples similar to the examples that SQL Server Books Online (BOL) provides. But pressing F1 brings up a window that gives only the function's syntax, which already appears in the Visual MDX Diagram. However, in the MDX Builder installation folder, I accidentally discovered an Examples subfolder that holds several sample DynamicCubes suitable for loading and applying against the FoodMart cube. In addition, several sample queries yielded calculated members that I could use to develop my own query, as Figure 1 shows.

With these samples in hand, I reconnected to my healthcare Financial cube. MDX Builder's CalcBuilder program helped me use the Charge and Cost measures to create a calculated measure for Profit. The CalcBuilder also lets you create named sets, calculated members, and calculated cells. After I saved the calculated measure, I returned to my DynamicCube and dragged it onto the columns axis along with the Charge and Cost measures. Then I used the built-in TopCount() function to show me the five highest-cost diagnoses. I built the entire query graphically by dragging and dropping the TopCount() function, dimensions, and measures from the IDE onto the Visual MDX Diagram, where I had defined my axes. Although the process was straightforward, it was also limiting, because I could work only in the graphical interface. If you want to modify the MDX query directly, MDX Builder currently requires you to change elements graphically as well.

Other drawbacks surfaced. As I developed the DynamicCube, I encountered several logic errors, which the built-in debugger didn't catch; the error messages occurred after I clicked Run DynamicCube. The OLAP server, not MDX Builder, returned the error messages. The debugger checks only for improperly built DynamicCubes. And another debugger limitation exists: It doesn't check the DynamicCube's logic when you submit the DynamicCube to OLAP Services.

Gathering and Testing the Results

After I ran my DynamicCube, I received the results in the Dynamic-Cube Viewer. Manually executing a query every time you modify it, which you have to do with MDX Builder, makes sense in a development environment. However, an interactive view that automatically updates the pivot table with each modification would be a helpful feature. I liked the Export to Microsoft Excel feature, available in MDX Builder Professional, which lets you maintain your custom formatting as one of the export options, as Figure 2 shows.

To make sure that the query worked seamlessly, I copied it from my DynamicCube to another MDX-capable application, the MDX Sample Application that ships with SQL Server OLAP Services. Success! The query worked perfectly. Of course, this portability is MDX Builder's major strength. MDX Builder isn't an end but a means for making MDX queries work in any environment that supports MDX, such as ADO MD.

A Complete Development Solution

BiSense targets MDX Builder 2.0 Professional to MDX experts, not to new users, and the company makes that fact clear in the online Help FAQ. Early in my testing process, I discovered that MDX Builder assumes that the user is very familiar with the MDX language and knows how to logically visualize multidimensional data. For this reason, I would discourage anyone who has little or no experience writing at least a moderately complex MDX query from working with MDX Builder as a learning tool. If you aren't an MDX expert but still need a simple graphical tool to learn MDX, I recommend that you stick with Microsoft's free MDX Sample Application, which ships with OLAP Services.

MDX experts might expect that because of its graphical nature, MDX Builder would be much more intuitive to use and faster than writing lengthy code. In fact, most MDX tools, even the MDX Sample Application, can graphically manipulate MDX query elements. Instead, MDX Builder's power lies in its ability to present a complete development solution for MDX, including every function and its syntax, as well as a 100-percent drag-and-drop environment. The drag-and-drop feature may be an advantage for some developers and a detriment to others who occasionally want to be able to modify their queries directly. Other benefits include the ability to save information such as calculated members and named sets back to the OLAP cube and to export query results to Microsoft Excel. If your job is to write MDX queries, you prefer a purely graphical environment, you waste a lot of time tracking down syntax errors, and you have $1300 budgeted for MDX applications, MDX Builder Professional might be a perfect fit.