Denodo MDX Service - User Manual

Last modified on: 28 Nov 2022

Overview

This component allows exposing Virtual DataPort databases as OLAP cubes, previously defined using a logical schema. It supports the MDX (multidimensional expressions) query language and the XMLA (XML for Analysis) format. It is useful for dimensional data exploration — for example analyzing sales by product line, by region or by time period.

MDX (MultiDimensional eXpressions) is a query language for Online Analytical Processing (OLAP). It allows querying the multidimensional data stored in OLAP Cubes.

The Denodo MDX Service is a customization of the open source Mondrian (R)OLAP engine created by Pentaho, including a limited set of additional features that allow data to be extracted from Denodo Virtual DataPort using the VQL query language, and authentication to work in an integrated way.

Installation and Configuration

The Denodo MDX Service is distributed as a DenodoConnect that can be downloaded from the Support site.

This Denodo MDX Service distribution consists of:

Command-line executable scripts for Windows and Linux (/bin folder)

Configuration files: application.properties, mondrian.properties and log4j2.xml (/config folder)

Java libraries (/lib folder)

Denodo MDX Service application jar: denodo-mdx-service-<version>-jar

Denodo driver jar: denodo-vdp-jdbcdriver-dist-<version>-full.jar

If you need to use a JDBC driver corresponding to a Denodo update different to the one that is distributed, you only need to replace this jar.

For installing it just download the .zip file and extract the service into the desired folder.

In order to run it, you need at least Java 8 and the environment variables JAVA_HOME and PATH correctly configured.

After running the script in the /bin folder, you can use the Denodo MDX Service from an XMLA client, using HTTP Basic Authentication or SPNEGO with Denodo-valid credentials.

Denodo XML Service runs at: http://localhost:8087/xmla/<dbname>

Configuration

The Denodo MDX Service allows you configure the following properties at the config/application.properties file:

server.port=8087

denodo.mdx.connection.jdbcurl=jdbc:vdb://localhost:19999/?noAuth=true

denodo.mdx.catalogs.<schema_id>.name=<SchemaName>

denodo.mdx.catalogs.<schema_id>.location=<SchemaLocation.xml>

server.port: Port at which the MDX Service listens to HTTP requests. Default is 8087.

denodo.mdx.connection.jdbcurl: The database URI to access Denodo from the XML Service. Default is jdbc:vdb://localhost:9999/?noAuth=true. This URL should allow the absence of configured credentials by means of the noAuth=true parameter.

denodo.mdx.catalogs.*: It is necessary to set Mondrian catalog schemas to be used for this instance. For each catalog schema to be configured into the service, both a name and the location of the schema file (XML) need to be specified. XML schema files are expected to follow Mondrian's schema format. At the end of this document there is a subsection about how to create a schema. To add a catalog you have to append an identifier to denodo.mdx.catalogs, before name and location. Example: denodo.mdx.catalogs.someschema.name and denodo.mdx.catalogs.someschema.location.

denodo.mdx.catalogs.<schema_id>.name: Name of every catalog.
denodo.mdx.catalogs.<schema_id>.location: Schema locations are expected to be specified in one of two forms:

1. As a path relative to the Denodo MDX Service's installation "/config" folder. For example, "schemas/SomeSchema.xml" would mean "$DENODO_MDX_SERVICE_HOME/config/schemas/SomeSchema.xml".

2. As a local absolute file URL. For example "file:/home/users/mondrianuser/schemas/SomeSchema.xml"

Kerberos Configuration Authentication

Kerberos Authentication needs to be enabled in the file config/mondrian.properties. You have to configure the parameter mondrian.allow.kerberos.authentication to true. Uncomment the line that is in the file.
Basic Authentication might not work with this parameter enabled.

Create schema

A multi-dimensional database can be defined by a logical model that consists in a set of cubes, hierarchies, and members mapping onto a physical model.

The schemas are represented in a XML file that contains cubes, dimensions, hierarchies, levels and members. The elements of the schema are linked to VDP views by means of Table elements in the schema specification. A example of a schema:

</Table>

</Table>

</Level>

</Hierarchy>

</Dimension>

</Measure>

</Cube>

</Schema>

You can find the documentation about how to create schemas here:

https://mondrian.pentaho.com/documentation/schema.php

In addition you can use Pentaho Schema Workbench to create the schema. This is a Java application that can help you design the Schema. There is an Appendix about this tool at the end of this document.

Features

Denodo MDX Service provides:

XMLA provider: XML for Analysis is a protocol that allows clients to interact with an OLAP server via SOAP messages.

Parsing of the MDX language into VDP Query Language (VQL) to retrieve answers to dimensional queries.

Authenticated connectivity to VDP using HTTP Basic Auth or Kerberos (SPNEGO).

Querying Data

MDX is the query language used to access OLAP cubes. It is a standard language introduced by Microsoft with a different structure to that of SQL, and adapted to multidimensional structures.

A basic MDX query:

SELECT

[Measures].Members ON COLUMNS,

[Gender].Members ON ROWS

FROM [Sales]

There is a brief MDX tutorial at the Mondrian web site: https://mondrian.pentaho.com/documentation/mdx.php

The Denodo MDX service will accept XMLA requests, containing an MDX query. These XMLA requests will be sent in SOAP format via HTTP.

Below we can see an example, using Postman as a client to make the MDX query:

The request type has to be POST
Open the Body tab and check the data type for raw.
Open the Content-Type selection box and select XML
Enter your raw XMLA request data into the body:

<?xml version="1.0" encoding="UTF-8"?>

<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">

<SOAP-ENV:Body>

SELECT

[Measures].Members ON COLUMNS,

[Gender].Members ON ROWS

FROM [Sales]

</Statement>

</Command>

<Catalog>Foodmart</Catalog>

</PropertyList>

</Properties>

</Execute>

</SOAP-ENV:Body>

</SOAP-ENV:Envelope>

Finally, send request

The Denodo MDX Service supports some of the most common XMLA properties: Catalog, Content and Format. Please note that other XMLA properties might also work, but they should not be considered officially supported in the current version.

The Denodo MDX Service can be used as a data source from XMLA-compatible clients such as Microsoft Excel, Microsoft Power BI and Tableau. Find some examples for this kind of access at the appendices at the end of this document.

Supported MDX

The Denodo MDX Service supports many of the main statements, functions and operators of the MDX query language:

NON EMPTY, MEMBERS, CHILDREN, DESCENDANTS, ADDCALCULATEDMEMBERS, WITH_MEMBER, WITH_SET, FORMAT_STRING, SOLVE_ORDER, FIRSTCHILD, LASTCHILD, CURRENTMEMBER, PARENT, ANCESTOR, EXCEPT, GENERATE,

PREVMEMBER, NEXTMEMBER, LEAD, LAG, PARALLELPERIOD, OPENINGPERIOD, CLOSINGPERIOD, PERIODSTODATE, YTD, MTD, QTD, WTD, SUM, CROSSJOIN,

ORDER, HEAD, TAIL, TOPCOUNT, TOPPERCENT, COUNT, MAX, MIN, AVG, MEDIAN, STDDEV, <, >, <=, >=, =, <>.

Additionally, there are also some Mondrian-specific functions that you can use in MDX queries, such as IN and NOT IN.

Please note that other MDX functions and structures might also work, but they should not be considered officially supported in the current version.

Optimization Using Summaries

Every MDX query produces VQL queries in the VDP datasource of the Denodo MDX Service, some of these queries combine several tables and calculate an aggregation, so it could be a good option use Smart Query Acceleration that it is a technique that includes Denodo 8.

Smart Query Acceleration uses a new type of views called Summaries. These views store common intermediate results that the query optimizer can then use as a starting point to accelerate analytical queries. You can go in depth in the following link. To benefit from summaries, it is important that summaries must be properly chosen. You can use the Diagnostic&Monitor Tool to see the vql queries executed by MDX service Tool and create the appropriate summaries.

The AI & Recommendations Feature Pack for the Denodo Platform includes the Summary Recommendations tool, which makes the creation of summaries much easier. In this link it is explained how to generate summary recommendations.

Appendices

Appendix I. Pentaho Schema Workbench

In order to create the Schema in Mondrian’s XML format, you can use Pentaho Schema Workbench. Follow these instructions:

Download the Pentaho Schema workbench (psw-ce-***) from Hitachi Vantara - Browse /Pentaho 9.0/client-tools

Unzip the files.

Add the Denodo JDBC driver to the /drivers folder

Run the workbench using workbench.bat

Once the application opens, connect to Denodo using the Options > Connection action like below

You can then create a Schema with Cubes, Dimensions and Measures. At the Mondrian documentation there is a section about Mondrian Schema Workbench: https://mondrian.pentaho.com/documentation/workbench.php

Appendix II. Connecting from Microsoft Excel

You can connect to the Denodo MDX Service from Microsoft Excel in two ways.

From Analysis Services

Go to Data > Get External Data > From Other Sources > From Analysis Services

Write the url of the XMLA endpoint for the desired Denodo database and configure the authentication.

Select a Cube

Select the fields of the cube to get the data.

From SQL Analysis Services Database(import)

Go to Data > Get External Data > From Other Sources > From SQL Analysis Services Database(Import).

Write the url of the XMLA endpoint for the desired Denodo database.

The database has to be one of the catalogs configured in Denodo MDX Service. It is optional, if this field is left empty all catalogs will be displayed.

The MDX query is optional, if a query is introduced, the result of this query will be loaded in the excel sheet.

Configure the authentication.

If MDX query field is empty, you have to select the desired dimensions and measures of a single cube.

Data are loaded in the excel sheet

Appendix III. Connecting from Microsoft Power BI

You can connect to the Denodo MDX Service from Microsoft Power BI Desktop by following this steps:

Go to Get Data > SQL Server Analysis Services database.

Write the url of the XMLA endpoint for the desired Denodo database.

Select Import access mode

The database has to be one of the catalogs configured in Denodo MDX Service. It is optional, if this field is left empty all catalogs will be displayed.

The MDX query is optional, if a query is introduced, the result of this query will be loaded in the excel sheet.

Select Basic Authentication or Windows Authentication

A data preview is shown, push the load button and data will be loaded in the report.

Appendix IV. Connecting from Tableau

You can connect to the Denodo MDX Service from Tableau by following this steps:

Go to Connect > Microsoft Analysis Services

Write the url of the XMLA endpoint for the desired Denodo database and configure the authentication.

Select a database, a cube and a worksheet.

Select the dimensions and measures

Appendix V. Configure service to use SSL

Follow these steps to secure with SSL the incoming connections:

Open the /denodo-mdx-service/config/application.properties file
Add this properties:

server.ssl.enabled=true

server.ssl.key-store-type=

server.ssl.key-store=

server.ssl.key-store-password=

server.ssl.key-alias=

server.ssl.enabled: Property to enable ssl
server.ssl.key-store-type= The format used for the keystore.
server.ssl.key-store= The path to the keystore containing the certificate
server.ssl.key-store-password=The password used to generate the certificate
server.ssl.key-alias=The alias mapped to the certificate

To apply these changes, stop the MDX Service and start it again.
After this you will need to import the public key into the clients and also the certificate into the trustore of the clients.

For instance, if you have Denodo installed in the same machine and you already have a certificate configured for your Denodo installation, you can use it easily with a configuration similar to this: