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.
The Denodo MDX Service is distributed as a DenodoConnect that can be downloaded from the Support site.
This Denodo MDX Service distribution consists of:
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>
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> |
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 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.
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.
Denodo MDX Service provides:
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:
<?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> |
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.
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.
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.
In order to create the Schema in Mondrian’s XML format, you can use Pentaho Schema Workbench. Follow these instructions:
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
You can connect to the Denodo MDX Service from Microsoft Excel in two ways.
You can connect to the Denodo MDX Service from Microsoft Power BI Desktop by following this steps:
You can connect to the Denodo MDX Service from Tableau by following this steps:
Follow these steps to secure with SSL the incoming connections:
server.ssl.enabled=true server.ssl.key-store-type= server.ssl.key-store= server.ssl.key-store-password= server.ssl.key-alias= |
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:
server.ssl.enabled=true server.ssl.key-store-type=JKS server.ssl.key-store=<DENODO_HOME>/denodo_server_key_store.jks server.ssl.key-store-password=password server.ssl.key-alias=denodo-sever-self-signed |