Denodo MDX Service - User Manual

Last modified on: 05 Aug 2021

Download original document


Please note: This software is currently in BETA.

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 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"

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:

<Schema name="DenodoSales">

  <Cube name="SalesCube" visible="true" cache="false" enabled="true">

    <Table name="impala_store_sales">

    </Table>

    <Dimension type="StandardDimension" visible="true" foreignKey="ss_customer_sk" highCardinality="false" name="Customer">

      <Hierarchy name="default" visible="true" hasAll="true" allMemberName="All Customers" primaryKey="c_customer_sk">

        <Table name="oracle_customer">

        </Table>

        <Level name="Customer Country" visible="true" column="c_birth_country" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">

        </Level>

      </Hierarchy>

    </Dimension>

    <Measure name="Total" column="ss_net_paid" datatype="Numeric" aggregator="sum" visible="true">

    </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>

 <Execute xmlns="urn:schemas-microsoft-com:xml-analysis" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">

 <Command>

    <Statement>

   SELECT

   [Measures].Members ON COLUMNS,

   [Gender].Members ON ROWS

   FROM [Sales]

    </Statement>

  </Command>

   <Properties>

    <PropertyList>     

     <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.

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:

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

  1. Unzip the files.

 

  1. Add the Denodo JDBC driver to the /drivers folder

  1. Run the workbench using workbench.bat

  1. 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.

  1. 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.

  1. 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

Limitations

  • If you are accessing Denodo MDX Service from Tableau and you have to restart the Denodo MDX service once Tableau has already been connected, your Mondrian session will be lost but Tableau will not notice it and throw an error on next connections. You will have to restart Tableau to fix this.