Connect directly to Microsoft SSAS Cubes with Wijmo OLAP

Pivot tables are one of the most powerful features within Microsoft Excel. A pivot table allows you to analyze thousands of rows of data with just a few mouse clicks, show the results in an easy to read table, “pivot”/change the report layout by dragging fields around, highlight key information to management and include charts in your presentations.

With Wijmo’s OLAP module, you can add all that power to your web applications.

The OLAP module includes the following components:

• PivotEngine: A non-visual component that takes in raw data via its itemsSource property and builds output views exposed by the pivotView property.
• PivotPanel: A control that allows users to dynamically build and modify views using drag-and-drop and easy to use context menus.
• PivotGrid: A data grid that extends our FlexGrid to show pivot data, including collapsible rows and columns, subtotals, grand totals, and custom cell formatting.
• PivotChart: A chart control that extends our FlexChart control to show pivot data, including multiple chart types and hierarchical axes.

Working with Local Data

The simplest and easiest way to use the PivotEngine is to assign JavaScript arrays or CollectionView objects containing the raw data to the engine’s itemsSource property. The data can be calculated or retrieved from arbitrary services.

For example:

var engine = new wijmo.olap.PivotEngine({
    itemsSource: getDataArray()
});

Using local data is an easy, adequate approach for relatively small amounts of data (say up to about 50,000 data items). Once the engine is set up, you may build views using code or let users build them using a PivotPanel control. Once builds are defined, the engine will send requests to the server, retrieve the results, and expose them through the pivotView property. In most cases, the results will be displayed in PivotGrid or PivotChart controls connected to the engine or to the panel.

For large data sets, however, using local data can be impractical. Loading a million records of data over the wire can take a significant amount of time, especially when using mobile devices. In these cases, you may prefer to use a server to store and aggregate the data, and use Wijmo OLAP as a front-end to build views and display results.

Working with Remote Data

Wijmo OLAP supports two types of remote scenarios:

• Direct connections to OLAP SSAS cubes (Microsoft’s Sql Server Analysis Services), and
• Connections to ComponentOne’s data engine services.

Direct connections to OLAP SSAS cubes

This option is recommended if you already have an instance of SSAS installed and running. In this case, all you have to do is configure the server to give your application access to the data and set the PivotEngine’s itemsSource property to an object that contains the cube’s name and URL.

For example:

var ng = new wijmo.olap.PivotEngine({
    itemsSource: {
        url: 'http://ssrs.componentone.com/OLAP/msmdpump.dll',
        cube: 'Adventure Works'
    }
});

This is the only difference between an app that uses Wijmo OLAP with local or remote data. Everything else, including the steps required to build, edit, save, and restore views, as well as display or export results, is identical in local and remote data scenarios.

The sections below summarize the steps required to install, create, and configure SSAS OLAP cubes:

Step 1: Install SSAS

For detailed information on how to install the SQL Server Analysis Services, refer to the MSDN tutorial.

Step 2: Create and configure OLAP cubes

For detailed information on how to create, define, and deploy the OLAP multidimensional cube, refer to the MSDN multidimensional modelling tutorial.

Step 3: Configure the server

To enable the HTTP access to SQL Server Analysis Services, use an MSMDPUMP.DLL ISAPI extension. For detailed information on how to set up the MSMDPUMP.DLL extension, refer to the MSDN HTTP access tutorial. For an online accessible OLAP service for test purposes, use the url listed in the example above.

If you want to use the cube in cross-domain scenarios, you must set up Cross-Origin Resource Sharing (CORS).

To allow cross-domain requests to the OLAP service, enable the CORS behavior of your server. The following sections demonstrate how to enable CORS on Internet Information Services (IIS) servers. Cross-domain access requires you to configure the HTTP Response Headers and the OPTIONS method server response.

Configure HTTP Response Headers

To configure HTTP Response Headers, specify:

• The domains that will perform the data requests.
• An HTTP method through which the data will be transferred.
• The HTTP Headers that can be used in the requests.
• The user credentials that are going to be required if an authenticated access is used.

For example:

Configure the OPTIONS Method Server Response

To configure the OPTIONS method server response, specify the server response to the OPTIONS method requests. In IIS, configure the behavior of the OPTIONS method through the OPTIONSVerbHandler mapping settings.

The following example demonstrates the list of settings that you have to apply.

Access the Cube Securely

To implement a secured access to the OLAP instance, use either of the following approaches:

• Use a proxy service which communicates with the cube on a secured protocol. This proxy should support the XMLA protocol. In the Microsoft world, the solution is to use ADOMD.NET. For detailed information on this, refer to this forum thread.
• Send the credentials with a request header, even though thus the Username and Password will be visible on the client side (browser). For details on how to pass credentials with request headers, refer to this StackOverflow discussion. You can define the required callbacks and settings directly in the transport.read object, as they will be passed to the $.ajax method.

Connections to ComponentOne’s data engine services.

Wijmo OLAP offers another option which is to connect to ComponentOne’s data engine services, which offer high-volume/high-performance services as well as indirect access to SSAS OLAP cubes. The ComponentOne DataEngine services are documented separately. For more details, please refer to ComponentOne DataEngine Services.

Summary of connecting Microsoft SSAS cubes to Wijmo OLAP

The diagram below illustrates the three types of itemsSource you can use with Wijmo’s PivotEngine component:

Option 1 is adequate for small data sets, in the order of 50,000 items or less. It is the simplest option, since it does not require any server-side component.

Option 2 is the best option for accessing SSAS OLAP cubes. It does not require any custom server-side components (besides the SSAS OLAP server) and does not require you to purchase any additional products besides Wijmo OLAP.

Option 3 is the most powerful and flexible. ComponentOne Data Engine Services allows you to create your own custom servers using whatever data you want, with great performance for huge data sets. It also allows you to use SSAS OLAP cubes if you want. It does require you to write some code on the server side, and to purchase the ComponentOne Studio Web API Edition, which is included in the ComponentOne Ultimate bundle with Wijmo.

For a sample showing all the options above, please see our OlapServerIntro sample.