Menu
Download Free Trial
Table of contents

Connecting to Microsoft Analysis Services

There are two ways to connect to Microsoft Analysis Services using Flexmonster Pivot Table:

  1. via XMLA – an industry standard for data access in analytical systems
  2. via Flexmonster Accelerator – special server-side utility developed by Flexmonster

If you already have configured XMLA (msmdpump.dll) it will be preferable to start with option #1. In case you do not have XMLA or you need some advanced features (increase loading speed, use credentials, etc.) – option #2 is a better choice.

Connecting to MS Analysis Services via XMLA

XMLA (XML for Analysis) – an industry standard for data access in analytical systems, such as OLAP and Data Mining. Please follow the steps below to configure a connection to Microsoft Analysis Services via XMLA.

Step 1: Embed the component into your web page

You already have in your HTML page an empty component. If Flexmonster Component is not embedded – return to Quickstart. Your code should look like the following example:

<div id="pivotContainer">The component will appear here</div>
<script src="flexmonster/lib/jquery.min.js"></script>
<script src="flexmonster/flexmonster.js"></script>

<script>
	var pivot = $("#pivotContainer").flexmonster({
		toolbar: true,
		licenseKey: "XXXX-XXXX-XXXX-XXXX-XXXX"
	});
</script>

Step 2: Configure XMLA access to the cube

If you have XMLA already configured please skip this step. Otherwise, please refer to the article that explains how to set up an HTTP endpoint for accessing an Analysis Services instance.

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

By default, browser prevents JavaScript from making requests across domain boundaries. CORS allows web applications to make cross-domain requests. Please read this step-by-step instruction to enable CORS for IIS to read data from Microsoft Analysis Services.

Step 4: Configure report with your own data

Now it’s time to configure Flexmonster Pivot Component on the web page. Let’s create a minimal report for this (replace proxyUrl, catalog and cube parameters with your specific values):

var pivot = $("#pivotContainer").flexmonster({
	toolbar: true, 
	report: {
		dataSource: {
			dataSourceType: "microsoft analysis services",

			/* URL to msmdpump.dll */
			proxyUrl: "http://olap.flexmonster.com/olap/msmdpump.dll",

			/* Catalog name */
			catalog: "Adventure Works DW Standard Edition",

			/* Cube name */
			cube: "Adventure Works",
		}
	},
	licenseKey: "XXXX-XXXX-XXXX-XXXX-XXXX"
});

Launch the web page from browser — here you go! A pivot table is embedded into your project. Check out the example on JSFiddle.

Optimize data loading

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

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

Try on JSFiddle.

Connecting to MS Analysis Services via Flexmonster Accelerator

Everyone who has ever worked with multidimensional databases analysis has most probably faced the problem of finding a suitable component that not only performs all required functions but also executes them fast. Flexmonster solved this problem and created Flexmonster Accelerator for Microsoft Analysis Services cubes – a special server-side proxy that helps you to increase data loading speed from server to customer’s browser.

Working with OLAP cubes, a browser component is communicating with the server via XMLA protocol. It’s no secret that the XMLA protocol is heavy and exchanges a lot of excessive information. Thus, it takes too much time and memory to load and process the data.

We replaced XMLA protocol and use direct requests from the Component to a server.

In this way, we have come up with solutions to two major problems that many of those who work with big data had faced to a different extent:

  • We made big data transfer from server to browser enormously fast. Our tool allows you to transfer large multidimensional data in super easy and fast way. Reporting becomes more enjoyable and prompt for your end users.
  • We greatly reduced the load on a browser memory.

Please refer to Getting started with Accelerator guide to find step-by-step instructions.