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 { [Measures].[Order Quantity] } ON COLUMNS,NON EMPTY { ( [Dim Product].[English Product Name].[English 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 { [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
Here’s how our example query and the result will look in Data Virtuality Studio:
