MDX Query Adapter

< appboard | 2.6 | builder | data sources | database

Page Contents

1. Overview

This page provides details for configuring the MDX Query adapter in in AppBoard.

Multi-Dimensional eXpressions (MDX) is a query language for Online Analytical Processing (OLAP) databases, in a similar way that SQL is used with relational databases. This adapter provides a way to execute MDX queries against OLAP databases that have XML for Analytics (XML/A) enabled - which is basically a web service interface to the OLAP database.

AppBoard ships with a generic XML/A driver that is known to work with: Pentaho Analysis (Mondrian), Microsoft SQL Server Analysis Services, Palo, and SAP BW.

The XML/A interface of your OLAP database may not be enabled by default, please refer to your database administrator to check or enable it and to obtain the XML/A URL required by this adapter during configuration.

AppBoard is restricted to queries that return two dimensions. For queries that have more dimensions an error message will be shown when adding/editing the MDX query.

1.1. Dynamic Queries

It is possible to create queries with dynamic components that can depend on session information, evaluating some expression, or through user interaction via Server Side Filter actions. For more information refer to the Dynamic Queries documentation.

2. Data Source Configuration

Each configured MDX Query adapter represents a specific connection to an XML/A service endpoint. The adapter then allows for any number of queries to be defined. The adapter needs to have a unique name against all the other adapters configured in AppBoard, and each query requires a unique name against other queries on the same adapter. There are also some naming restrictions for queries as outlined in the relevant sections below.

2.1. Connect

The connect step is for configuration information regarding establishing a connection to the XML/A service. The table below details the available fields:

Name Description
Driver Set to org.olap4j.driver.xmla.XmlaOlap4jDriver. This should not be modified unless instructed by support.
Server URL for XML/A By default shows a sample URL for Microsoft SQL Server Analysis Services. This needs to be updated for your particular OLAP database. As noted at the top of this page, the XML/A service may need to be enabled and this URL and access credentials provided by the database administrator. This is a required field.
Catalog Name This setting is not required but may be necessary for your OLAP database.
Schema Name This setting is not required but may be necessary for your OLAP database.
Other Connection Parameters Additional connection parameters passed to the XML/A driver.
Timeout (seconds) The maximum duration (in seconds) to establish a successful database connection. Setting this value too low, in the case where the database cannot service the connections fast enough, will result in degraded performance.
Username User name credential used to connect to database.
Password User password credential used to connect to database.

2.2. Queries

By default only new or edited queries are actually run against the database to ensure the query is valid. This is done to prevent delays when editing the data source for cases where there are many existing queries. To force an existing query to be re-validated/discovered without changing it or losing any configuration then update the MDX Query with a trivial change such as adding a space character.

Name Description
Name Name of this MDX query entity. This field is limited to 256 characters. Note: if editing the name all references internal to AppBoard are updated automatically, however in some cases Data Processing Scripts may have hardcoded references that will break as a result of the rename. In cases where AppBoard can detect this an error message is presented to the administrator identifying the affected scripts. The name is restricted to alphanumeric characters, underscores, and hyphens. This is a required field.
Description A summary of the purpose of the query and any details or notes to provide for the system administrator. The Description field has a character limit of 512 characters.
Cache Timeout (seconds) Maximum time (in seconds) a response from the database is kept before discarding and re-querying for new data. If set to zero then no caching is performed at all.
Query Timeout (seconds) Maximum time (in seconds) to wait before timing out an executing MDX query.
MDX Query MDX statement defining the records to be returned by the query. The MDX Query can contain a maximum of 8000 characters. This is a required field.
Merge Row members This option merges multiple set identification members per row into a single field.
Data Processing Script(s) List of one or more Data Processing Scripts to be run on the query.

2.3. Explore & Associate

These steps are common to all the data sources, refer to the main Data Sources documentation for more information.