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:
