# Connecting and Querying OLAP

**URL:** <https://community.cdata.com/t/connecting-and-querying-olap/842>\
**Category:** CData Virtuality \[Archived\]\
**Tags:** article, cdata-virtuality\
**Created:** [July 12, 2024, 4:55pm UTC](https://community.cdata.com/t/connecting-and-querying-olap/842 "2024-07-12T16:55:02Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![anonymous](https://avatars.discourse-cdn.com/v4/letter/a/8491ac/32.png) [@anonymous](https://community.cdata.com/u/anonymous)\
**Post date:** [July 12, 2024, 4:55pm UTC](https://community.cdata.com/t/connecting-and-querying-olap/842/1 "2024-07-12T16:55:02Z")

</div>

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&nbsp;[this tutorial](http://www.codeproject.com/Articles/658912/Create-First-OLAP-Cube-in-SQL-Server-Analysis-Serv), and Microsoft SQL Server Data Tools can be downloaded&nbsp;[here](https://www.microsoft.com/en-us/download/details.aspx?id=36843);
- 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&nbsp;[Microsoft documentation;](https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/gg492140(v=sql.110)?redirectedfrom=MSDN)
- Network acces from Data Virtuality to the web server.

## Connection

Click ‘Add Data Source’, scroll down to&nbsp; **Other** &nbsp;and select&nbsp; **OLAP XMLA**. Enter the required information as shown below:

 ![Olap_connection.png](https://us1.discourse-cdn.com/cdata/original/2X/8/80c80a51410ceee34dd34ad07d180a48b45ff648.png)

- **Alias** &nbsp;- 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** :&nbsp; **Catalog** &nbsp;is the name of the Analysis Services Database and&nbsp; **DataSource** &nbsp;is the name of the&nbsp;Analysis Service instance (hostname )
- **Path** &nbsp;- webserver path to the application (OLAP,&nbsp; full path: http://\<host\>:8080/olap/msmdpump.dll for testing)

Alternatively, you can use the following statement:

> ```auto
> 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 => '') ;;
> 
> ```

## &nbsp;

## Example Query

Let’s consider this query on Microsoft SQL Server:

> ```auto
> 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):

> ```auto
> 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:

 ![dv.png](https://us1.discourse-cdn.com/cdata/original/2X/9/948307ff7322567fa6fffa5a7f6b064e2396978a.png)