How to Use OLAP Cubes in Microsoft SASS with Your ASP.NET MVC Application

August 10 2018

Online Analytical Processing (OLAP) allows users to analyze database information from multiple databases at a time. Optimized for querying and reporting, it lets users query the database to show aggregated and calculated data in a PivotGrid or PivotChart.

Benefits of OLAP include:

Easy to use
Easy summarization of data
Easy to analyze data
Helps to find data patterns
Allow quick report creation
Helps in decision making

How to create Excel-like pivot tables and charts in ASP.NET MVC

ComponentOne MVC OLAP is part of ComponentOne Studio Enterprises and ComponentOne Ultimate packages. With ComponentOne Studio's OLAP control you can create Excel-like, web-based pivot tables and charts in your ASP.NET MVC projects. This article shows how SSAS Cubes can directly bind to an MVC OLAP control to quickly make a report for a large Data Sets, using PivotGrid or PivotChart.

In another article, we discuss how to work with SSAS cube directly at client side using Wijmo Controls.

ComponentOne Studio's OLAP control 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.

You can quickly create reports using an OLAP PivotPanel, PivotGrid, and PivotChart. The smart pivot panel interprets data and intelligently places fields according to their data type in an intuitive, modern UI. Let's look at the different ways in which OLAP allows to bind data:

Working with raw data

This is more suitable for a relatively small amount of data, especially when number of records are less than ten thousand. In this case, a list of objects can be assigned to the PivotEngine using the Bind property which accepts Model and CollectionViewRequest.

PivotEngine then uses arbitrary service to analyze and then display data in the PivotGrid. The view and fields can be later modified using the smart PivotPanel to generate a report suiting your business needs.

Use this code for ASP.Net MVC:

@(Html.C1().PivotEngine().Id("indexEngine")
        .Bind(Model))

Use this code for ASP.Net MVC Core:

<c1-pivot-engine id="indexEngine">
    <c1-items-source source-collection="Model"></c1-items-source>
</c1-pivot-engine>

Working with a data engine

Large data records often affect the OLAP performance, the alternative is 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, refer to ComponentOne DataEngine Services.

Use this code for ASP.Net MVC:

@(Html.C1().PivotEngine().Id("dataSourceEngine")
        .BindService("~/api/dataengine/dataset10")
        .RowFields(pfcb => pfcb.Items("Country"))
        .ColumnFields(cfcb => cfcb.Items("Product"))
        .ValueFields(vfcb => vfcb.Items("Sales")))

Use this code for ASP.Net MVC Core:

<c1-pivot-engine id="dataSourceEngine" service-url="~/api/dataengine/dataset10">
    <c1-view-field-collection c1-property="RowFields" items="Country"></c1-view-field-collection>
    <c1-view-field-collection c1-property="ColumnFields" items="Product"></c1-view-field-collection>
    <c1-view-field-collection c1-property="ValueFields" items="Sales"></c1-view-field-collection>
</c1-pivot-engine>

Direct connections to OLAP SSAS cubes

One can use SSAS to create cubes using data from data marts and data warehouse for deeper and faster data analysis. 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.

Use this code for ASP.Net MVC:

@(Html.C1().PivotEngine().Id("ssasCube")
        .BindCubeService("http://ssrs.componentone.com/OLAP/msmdpump.dll", "Adventure Works"))

Use this code for ASP.Net MVC Core:

<c1-pivot-engine id="ssas">
    <c1-cube-service url="http://ssrs.componentone.com/OLAP/msmdpump.dll" cube="Adventure Works"></c1-cube-service>
</c1-pivot-engine>

The major difference between direct connections to OLAP SSAS Cubes and local data, or remote data is based on how PivotEngine consumes these connections via the ItemSource property. Everything else, including the steps to build, edit, save and restore views remain the same.

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

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

You'll need to 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:

Header Name	Value	Details
Access-Control-Allow-Headers	Origin, Content-Type, Accept	These are the names of the fields required to be used in the actual request. Values should be comma-separated.Origin –where the cross-origin or preflight request originates from. This setting tells the server that the origin, which performs the request is a known one.Content-Type –the content (MIME) type of the entity body sent to the recipient.Accept – this field specifies the media types which are acceptable for the response.
Access-Control-Allow-Origin	URI names that may access the resource. When an asterisk (*) is defined, all domains are allowed.	The names of the allowed domains should be separated by comma (,).
Access-Control-Request-Method	POST	HTTP method to be used in the actual request. The XMLA protocol specifies an HTTP POST method.
Access-Control-Allow-Allow-Credentials(authenticated access only)	true	Allowed values are:true – allows supplying credentials with the request.false – disable supplying credentials with the request.

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 you'll need to apply:

HTTP Handler Name	Required Access Level	Details
OPTIONSVerbHandler	Read	Specifies that the handler requires READ access to the requests.

Access the cube securely

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

Use a proxy service that communicates with the cube on a secured protocol. This proxy should support the XMLA protocol. For Microsoft, use ADOMD.NET.
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.

Summary of connecting Microsoft SSAS cubes to MVC OLAP

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

Connect Microsoft SSAS cubes to MVC OLAP

MVC OLAP connections

Option 1: is adequate for small data sets, in the order of 10,000 items or less. It is the simplest option, since it does not require any extra server-side API to communicate.

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

Option 3: ComponentOne Data Engine Services allow you to create your own custom servers using the data you want, with great performance for huge data sets. It also allows you to use SSAS OLAP cubes. It requires you to create an API to communicate. The ComponentOne Studio Web API Edition, is included in the ComponentOne Ultimate bundle with MVC.

Read the ComponentOne Studio ASP.NET MVC controls OLAP documentation.