Schema Workbench - Testing MDX Query

This guide is based on FoodMart data source, a MySQL database example provided when installing JasperReports Server. The purpose is to get familiar with MDX syntax.

1. Launch Jaspersoft OLAP Schema Workbench and open the OLAP schema created from the previous guide Creating an OLAP Schema.

2. Click on MDX Query icon.

   

Measures and Dimension

3. Try this basic MDX query:

   SELECT {
     [Measures].[product number],
     [Measures].[class number]
   } ON COLUMNS,
   {
     [Product]
   } ON ROWS
   FROM Products

   [product number] and [class number] are measures.
   [Product] is a dimension.
   Products is a cube.

4. Click on Execute to display the result:

   

ON ROWS - ON COLUMNS

Previously, measures were set as COLUMNS and Product dimension set as ROWS. But it can be switched: measures set as ROWS and Product Dimension set as COLUMNS.

5. Try switching ON COLUMNS and ON ROWS. Do Execute to display the result:

   

Dimension's Methods

[dimension].children

SELECT {
    [Measures].[product number],
    [Measures].[class number]
} ON COLUMNS,
{
    [Product].children
} ON ROWS FROM Products

[dimension].firstChild or .lastChild

SELECT {
    [Measures].[product number],
    [Measures].[class number]
} ON COLUMNS, {
    [Product].firstChild // or [Product].lastChild
} ON ROWS FROM Products

Drilling Dimension

Reminder: the Product dimension's hierarchy is Product > Brand > Name > SKU.

You can drill a dimension to focus the display on particular values. You just have to know your Brand, Name or SKU value.

SELECT {
    [Measures].[product number],
    [Measures].[class number]
} ON COLUMNS, {
    [Product],
    [Product].[Tri-State].[Tri-State Dried Mushrooms],
    [Product].[Big City],
    [Product].Queen.children
} ON ROWS FROM Products

Generating default MDX queries

Since the new Jaspersoft OLAP Schema Workbench 3.5.4 released, you have a new button to generate MDX queries.

• Click on MDX Query icon. Now just beside of Save As icon, click on Generate MDX Queries icon. Here is below a generated query:

  SELECT [Measures].AllMembers ON COLUMNS,
         Hierarchize([Product].[All Products].Children) ON ROWS
    FROM [Products];
  

Notes

Square brackets

• If your OLAP schema contains items named with special characters such as space in "Big City", you have to use square brackets [ ]. Example: [big City].
• If not, [ ] are not necessary. Example: Queen or [Queen].

See also...

• Jaspersoft OLAP Schema Workbench.