Using the MDX Editor 

Use

You may create new OLAP queries or edit the MDX statements of existing OLAP queries with the MDX Editor.

 

Prerequisites

·        You have at least one OLAP data service configured as a BI system.

·        You are connected to a portal.

·        You have created and zoomed to the level of an iView in which to place your query.

·         The MDX Editor is only available for OLAP data services.

·         The MDX Editor is not available for BW-native data services (services defined based on the BW Web API Connection)

 

Procedures

To create new OLAP queries with the MDX Editor:

...

       1.      From the BI menu, choose MDX Editor.

       2.      In the MDX Editor dialog box, select a system from the System drop-down list.

       3.      In the Object Browser, select metadata and syntax examples with which to construct your query, using the Browse, Search, and Syntax Examples tabs:

¡        Use the Browse tab to browse for and select specific metadata to use in your statement. You may browse for and select any type of metadata:

                                                  i.       Click Browse. The metadata list is populated with the selected system as the root node, followed by the list of its children in a tree.

Optionally, restrict the level of metadata that appears in the metadata list by using the drop-down lists:

Browse Lists

List	Values	Action
Schema	All schemas in the selected system	Click Browse to populate the metadata list with the selected schema as root node
Cube	All cubes in the selected schema	Click Browse to populate the metadata list with the selected cube as root node
Dimension	All dimensions in the selected cube	Click Browse to populate the metadata list with the selected dimension as root node
Hierarchy	All hierarchies in the selected dimension	Click Browse to populate the metadata list with the selected hierarchy as root node

 

                                                ii.       In the metadata tree, locate and select the object to use in the statement.

                                               iii.       Maximize or restore the metadata list at any time using the up and down arrows.

¡        Use the Search tab to search for and select a specific member to use in your statement:

                                                  i.       The drop-down lists are automatically populated with the currently selected values in the drop-down lists on the Browse tab, if available. Change the values to search within a different hierarchy or level. You may search for members across all levels in a hierarchy, or within a specific level.

                                                ii.       Place your cursor in the Member field, and type the name of the member you want to find. This field also functions as a drop-down list, which you can use to select from the history of searched terms.

                                               iii.       Click Search. If the member is found, you may select it in the list below.

¡        Use the Syntax Examples tab to select a template with which to build your statement. The MDX Editor provides several templates organized into different categories. After you transfer the syntax example to the Editor pane, replace the objects in brackets (<< >>) with selected metadata from your data service.

       4.      Drag-and-drop or double-click to transfer the syntax example or the unique name of the selected object to the Editor pane at the current cursor location.

       5.      Edit your statement as desired in the Editor pane, using any of the following methods:

¡        Type directly into the pane.

¡        Transfer metadata objects into the pane using the procedure in step 4.

¡        Use the editor icons:

Editor Icons

Icon	Function
	Cut
	Copy
	Paste
	Undo
	Redo
	Validate
	Run

 

       6.      Validate your statement by clicking the Validate icon.

       7.      Run your statement to preview its result by clicking the Preview icon.

       8.      Maximize or restore the Preview pane at any time by clicking the up or down arrows.

       9.      Press Save to save your statement.

   10.      Press Close to close without saving.

·         The MDX Editor cannot create a preview of results that consist of a single cell, such as the result of the statement SELECT FROM <<cube>>. You may save such a query, but the resultant data service will not have input and output ports.

·         The MDX Editor allows you to save a statement even if it is invalid. We recommend you check the validity of your statement using the Validate icon before saving it.

 

To edit MDX statements of existing OLAP queries using the MDX Editor:

See Modifying Existing Queries, and follow the editor usage instructions above.

If you use the MDX Editor to save changes to a Grand Totals or Zero Activity template, your result will be changed to a horizontal display (results listed across columns) instead of a vertical display (results listed in rows).

 

Result

Once you have saved a statement, you return to the Visual Composer workspace, where a data service has been configured with the MDX statement you have created.

See Working With Queries for ways to continue working with your query.