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:

let pivot = new Flexmonster({
  container: "pivotContainer",
  componentFolder: "node_modules/flexmonster/",
  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):

let pivot = new Flexmonster({ 
  container: "pivotContainer",
  componentFolder: "node_modules/flexmonster/",
  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", 
    } 
  } 
});

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

Live example

Optimize data loading

The subquery parameter helps Flexmonster take less time for loading and rendering. Below is an example for 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]" } ]
  }
}