The OLAP Services connector, known by the type name OLAP, exposes stored procedures for calling analysis services backed by an OLAP server using MDX query language. This connector exposes a stored procedure, invokeMDX, that returns a result set containing tuple array values for a given MDX query. invokeMDX will commonly be used with the ARRAYTABLE table function to extract the results.

Since the Cube metadata exposed by the OLAP servers and relational database metadata are so different, there is no single way to map the metadata from one to other. It is best to query OLAP system using its own native MDX language through. MDX queries may be defined statically or built dynamically in Data Virtuality Server's abstraction layers.

Prerequisites

Before starting to create a connection in Data Virtuality, the following preparations need to be done on the Microsoft SQL Server Analysis Server:

  1. You need a valid Microsoft SQL Server Analysis Server instance with an OLAP cube. If not configured already, you can find a detailed guide on creating OLAP cubes here: http://www.codeproject.com/Articles/658912/Create-First-OLAP-Cube-in-SQL-Server-Analysis-Serv. It is possible that you need the SQL Server Data Tools for Business Intelligence Development:
  1. The cube needs to be processed at least once. Otherwise, you have no data and you will get an error message from the Analysis Service. The message is somewhat unclear and you should make sure that you can rule this out as a problem.
  2. A user account that will be used for the data source is required. This account needs to have full permissions on the cube and its components.
  3. Additionally, the Analysis Service instance needs to be configured to be accessible via HTTP and the IIS (Internet Information Service). A thorough guide on this comes from Microsoft: http://msdn.microsoft.com/en-us/library/gg492140(v=sql.110).aspx. The Guide also shows a few means of testing the XMLA connection.
  4. It is also required to have a working network (either LAN, MAN or WAN) that connects the Data Virtuality service with the IIS.

Metadata

Before issuing Queries to the OLAP system, we need to configure a connection using the SYSADMIN.createConnection() procedure:

Example
call SYSADMIN.createConnection( 'olapalias', 'olap', <connection properties> );
call SYSADMIN.createDatasource( 'olapalias', 'olap', '<model properties>', '<translator properties>')

Usage

The OLAP connector exposes one low-level procedure for accessing OLAP services.

InvokeMDX Procedure

invokeMdx returns a resultset of the tuples as array values.

Procedure invokeMdx(mdx in STRING) returns resultset (tuple object)

The MDX parameter is an MDX query to be executed on the OLAP server.

The results of the query will be returned such that each row on the row axis will be packed into an array value that will first contain each hierarchy member name on the row axis than each measured value from the column axis.

The use of Data Roles should be considered to prevent arbitrary MDX from being submitted to the invokeMDX procedure.

Example

You can now insert MDX queries via the invokeMdx procedure. It is necessary, however, to pass the result with ARRAYTABLE in order to get the mapping onto output columns. You can reorder the output columns and they don't have to be in the same order as in the MDX query.

SELECT w.*
FROM table(
exec "olap.invokeMdx"(
"request" => 'SELECT NON EMPTY { [Measures].[Order Quantity] } ON COLUMNS,
NON EMPTY { ([Dim Product].[English Product Name].[English Product Name].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Cube_Adventure Works DW2012] CELL PROPERTIES VALUE,
BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS'
)
) a, ARRAYTABLE(a.tuple COLUMNS "English Product Name" string, "Order Quantity" string) w

See Also

Connecting and Querying OLAP for information on how to connect and query an OLAP cube