Connecting to Microsoft Analysis Services

Flexmonster supports both tabular and multidimensional model types. There are two ways to connect to Microsoft Analysis Services using Flexmonster Pivot:

1. Via XMLA - an industry standard for data access in analytical systems. Works for multidimensional models only.
2. Via Flexmonster Accelerator - a special server-side utility developed by Flexmonster. Works for both multidimensional and tabular models.

If you already have XMLA configured (msmdpump.dll) it will be easier to start with option #1. Option #2 is the better choice if you:

• Do not have XMLA.
• Use a tabular model.
• Need some advanced features (such as increased loading speeds, use of credentials, etc.).
• Use Azure Analysis Services.

Connecting to Microsoft Analysis Services via XMLA

XMLA (XML for Analysis) is an industry standard for data access in analytical systems such as OLAP and Data Mining.

Follow the steps below to configure a connection to Microsoft Analysis Services via XMLA. To work with tabular models or Azure Analysis Services, use Flexmonster Accelerator.

Step 1: Embed the component into your webpage

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

let pivot = new Flexmonster({
  container: "pivotContainer",
  componentFolder: "node_modules/flexmonster/",
  toolbar: true
});

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

<FlexmonsterReact.Pivot
 toolbar={true}
/>

<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.

Step 3: Enable cross-origin resource sharing (CORS)

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. Check out an example on JSFiddle.

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]" } ]
  }
}

Try it on JSFiddle.

What’s next?

You may be interested in the following articles:

• Configuring the authentication process
• Getting started with Flexmonster Accelerator