In this article, we'll describe how to connect an OLAP cube and how to query it.

Prerequisites

• Installed SSAS with a processed OLAP cube. To create an OLAP cube, you can use this tutorial, and Microsoft SQL Server Data Tools can be downloaded here;
• A user account with full permissions on Analysis Services database and cube;
• IIS Web Server providing an application which points to the SSAS instance. For detailed information on implementation, please see Microsoft documentation;
• Network acces from Data Virtuality to the web server.

Connection

Click 'Add Data Source', scroll down to Other and select OLAP XMLA. Enter the required information as shown below:

• Alias - name of your data source. You can enter any name, but keep in mind that the name can contain only Latin letters, digits, and underscores.
• DB-config: Catalog is the name of the Analysis Services Database and DataSource is the name of the Analysis Service instance (hostname )
• Path - webserver path to the application (OLAP,  full path: http://<host>:8080/olap/msmdpump.dll for testing)

Alternatively, you can use the following statement:

EXEC SYSADMIN.createConnection(name => 'olap', 
jbossCLITemplateName => 'olap_xmla', 
connectionOrResourceAdapterProperties => 'host=<hostname>,
port=8080,path=/olap/msmdpump.dll,db-config="Catalog=<databasename>;
DataSource=<serverinstance>",user-name=<username>,password=<password>') ;; 
EXEC SYSADMIN.createDataSource(name => 'olap', translator => 'olap', 
modelProperties => 'importer.useFullSchemaName=false', 
translatorProperties => '') ;;

Example Query

Let's consider this query on Microsoft SQL Server:

SELECT NON EMPTY { LMeasures].POrder Quantity] } ON COLUMNS,
NON EMPTY { (  Dim Product]. English Product Name].sEnglish Product Name].
ALLMEMBERS ) }
DIMENSIONS PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM 
(Cube_AdventureWorks DW2012) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR,
 FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS;

To process it, we can use the following syntax in Data VirtualIty (please note that the result must be parsed with ARRAYTABLE):

select w.* from table( exec "olap.invokeMdx"(
 "request" => ' SELECT NON EMPTY { LMeasures].POrder Quantity] } ON COLUMNS,
 NON EMPTY { ( Dim Product].{English Product Name].sEnglish 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

Here's how our example query and the result will look in Data Virtuality Studio: