Skip to main content

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:

 

Olap_connection.png

  • 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-configCatalog 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:

Be the first to reply!

Reply