Connecting to Microsoft Analysis Services via XMLA

This guide describes how to connect to Microsoft Analysis Services via XMLA (XML for analysis), an industry standard for data access in analytical systems. XMLA works for multidimensional models such as OLAP and Data Mining.

To work with tabular models or Azure Analysis Services, we recommend using Flexmonster Accelerator — a special server-side utility developed by Flexmonster.

Step 1. Embed the component into your webpage

If Flexmonster is not yet embedded, set up an empty component in your webpage:

In pure JavaScript

Complete the Integrating Flexmonster guide. Your code should look similar to the following example:

const pivot = new Flexmonster({
  container: "pivotContainer",
  toolbar: true
});

In React

Complete the Integration with React guide. Your code should look similar to the following example:

<FlexmonsterReact.Pivot
 toolbar={true}
/>

In Angular

Complete the Integration with Angular guide. Your code should look similar to the following example:

<fm-pivot
 [toolbar]="true">
</fm-pivot>

In Vue

Complete the Integration with Vue guide. Your code should look similar to the following example:

<Pivot
 toolbar
/>

Step 2. Configure XMLA access to the cube

Skip this step if you already have XMLA configured. Otherwise refer to this article: how to set up an HTTP endpoint for accessing an Analysis Services instance.

By default, the browser prevents JavaScript from making requests across domain boundaries. CORS allows web applications to make cross-domain requests. Follow these step-by-step instructions to enable CORS for IIS to be able to read data from Microsoft Analysis Services.

Step 4. Configure the report with your own data

Now it’s time to configure the pivot table on the webpage. Let’s create a minimal report for this (replace proxyUrl, catalog, and cube parameters with your specific values):

const pivot = new Flexmonster({ 
  container: "pivotContainer",
  toolbar: true,  
  report: { 
    dataSource: { 
      type: "microsoft analysis services", 
      /* URL to msmdpump.dll */ 
      proxyUrl: "https://olap.flexmonster.com/olap/msmdpump.dll", 
      /* Catalog name */ 
      catalog: "Adventure Works DW Standard Edition", 
      /* Cube name */ 
      cube: "Adventure Works", 
    } 
  } 
});

See the full list of Flexmonster properties used to configure the dataSource object.

Launch the webpage from a browser — there you go! A pivot table is embedded in your project.

Live example

List of supported configuration parameters

When connecting to data from Microsoft Analysis Services, you can use the following properties of the DataSourceObject:

List of properties

Property/Type	Description
type String	The data source type. When connecting to data from Microsoft Analysis Services, set the `type` to `"microsoft analysis services"`.
catalog String	The data source catalog name.
cube String	The catalog's cube's name.
dataSourceInfo String	optional The service info.
proxyUrl String	The URL to the `msmdpump.dll` file.
effectiveUserName String	optional Specify this property when an end-user identity must be impersonated on the server. Specify the account in the `domain\user` format.
customData String	optional The `customData` property is added to the connection string when connecting to a cube. Commonly used for user authorization. You can check out the Using the customData and SSAS thread.
localeIdentifier Number	optional The Microsoft locale ID value for your language.
mapping MappingObject \| String	optional Defines how fields from the data source are treated and presented within the component. When using Microsoft Analysis Services, you can specify the field’s captions or hide the field from the dataset. Read more in the Mapping guide. It can be either an inline MappingObject or a URL to a JSON file with the mapping Live example.
roles String	optional A comma-delimited list of predefined roles to connect to a server or a database using the permissions defined by that role. If this property is omitted, all roles are used and the effective permissions are the combination of all roles. For example, to combine `"admin"` and `"manager"` roles, set the `roles` property like so: `roles: "admin,manager"`.
subquery String	optional Sets a server-side filter to decrease the size of the response from the OLAP cube. For example, to show reports for only one specific year set the `subquery` like so: `"subquery": "select {[Delivery Date].[Calendar].[Calendar Year].&[2008]} on columns from [Adventure Works]"` Live example.
requestHeaders Object	optional Adds custom request headers. Consists of `"key": "value"` pairs, where `"key"` is a header name and `"value"` is its value Live example. Note The `requestHeaders` property is not saved when obtaining the report via `save()` and `getReport()` API calls.
useGranularityNamesForDateFilters Boolean	optional Adjusts date filters to the cube structure. When set to `true`, date filters use `granularityNames`. When set to `false`, date filters use the unique name of the hierarchy being filtered. Default value: `true`.
withCredentials Boolean	optional Indicates whether cross-site `Access-Control` requests should be made using credentials such as authorization headers (`true`) or not (`false`). For more details, refer to MDN web docs. Setting the `withCredentials` flag to `true` is recommended when using Windows authentication and other types of server authentication. When set to `false`, the browser does not ask for credentials and does not include them in outgoing requests. Default value: `false`.

Optimize data loading

The subquery parameter helps Flexmonster take less time for loading and rendering. Below is an example of showing reports for a specific year from the date hierarchy:

report: {
  dataSource: {
    type: "microsoft analysis services",
    proxyUrl: "https://olap.flexmonster.com/olap/msmdpump.dll",
    catalog: "Adventure Works DW Standard Edition",
    cube: "Adventure Works",
    subquery: "select { [Delivery Date].[Calendar].[Calendar Year].&[2011] } on columns from [Adventure Works]"
  },
  slice: {
    rows: [ { "uniqueName": "[Delivery Date].[Calendar]" } ],
    columns: [
      { "uniqueName": "[Product].[Category]" },
      { "uniqueName": "[Measures]" }
    ],
    measures: [ { "uniqueName": "[Measures].[Order Count]" } ]
  }
}