How to Integrate Ignite UI Pivot Grid in ASP.Net MVC Applications

[Infragistics] Mihail Mateev / Friday, May 17, 2013

Ignite UI Pivot Grid is a WEB client (HTML5/jQuery/jQuery.UI based ) implementation of the very successful Infragistics Pivot Grid (xamPivotGrid) , well known from XAML platforms like WPF and Silverlight. Pivot Grid is a component that provides the same functionalities like Pivot Table and developers could embed this control in their applications and get pivot table functionalities out of the box.

Introduction

A pivot table is a program tool that allows you to reorganize and summarize selected columns and rows of data in a spreadsheet or database table to obtain a desired report. A pivot table doesn't actually change the spreadsheet or database itself.

The term pivot table is a generic phrase used by multiple vendors. In the United States, Microsoft Corporation has trademarked the specific form PivotTable. Pivot tables can be seen as a simplification of the more complete and complex OLAP concepts.

On-Line Analytical Processing (OLAP) is a category of software technology that enables analysts, managers and executives to gain insight into data through fast, consistent, interactive access to a wide variety of possible views of information that has been transformed from raw data to reflect the real dimensionality of the enterprise as understood by the user.
OLAP functionality is characterized by dynamic, multi-dimensional analysis of consolidated enterprise data supporting end user analytical and navigational activities including:

calculations and modeling applied across dimensions, through hierarchies and/or across members
trend analysis over sequential time periods
slicing subsets for on-screen viewing
drill-down to deeper levels of consolidation
reach-through to underlying detail data
rotation to new dimensional comparisons in the viewing area

Microsoft SQL Server Analysis Services (SSAS) delivers online analytical processing (OLAP) and data mining functionality for business intelligence applications. Analysis Services supports OLAP by letting you design, create, and manage multidimensional structures that contain data aggregated from other data sources, such as relational databases.

XMLA

XML for Analysis (XMLA) is a SOAP-based XML protocol, designed specifically for universal data access to any standard multidimensional data source that can be accessed over an HTTP connection. Analysis Services uses XMLA as its only protocol when communicating with client applications.

As a developer, you can use XMLA to integrate a client application with Analysis Services. Developers can use XMLA to integrate a client application with Analysis Services, without any dependencies on the .NET Framework or COM interfaces. Applications require XMLA and an HTTP connection to Analysis Services.

In this study we will not talk about how to set XMLA http access for SQL Server Analytics Services (SASS).

You could learn how to set up an SQL server that provides analysis services and XMLA HTTP Access from the great article “How to set up XMLA HTTP Access for SQL Server Analysis Service 2008 and access the Adventure Works 2008 from an Infragistics PivotGrid application”.

XMLA and Ignite UI Pivot Grid

Pivot Grid to use XMLA via Infragistics Data Source (igDataSource) using serverUrl option

There are two main options:

Direct XMLA provider via SQL Server HTTP Access
Remote XMLA provider (using XMLA via controller / WEB service)

Often requirements could be to use a different format to transfer data between WEB service and WEB client (for example JSON, which is very popular last years)

In this study we will demonstrate direct and remote XMLA access and also how can implement service with a different format (JSON)

Sample Demo

Sample demo is an ASP.Net MVC 4 project, generated with Visual Studio 2012

We have 2 projects in the solution:

1. WEB client – ASP.Net MVC 4

2. ASMX service (used for JSON serialization of the XMLA data)

In the WEB client (ASP.Net MVC 4 ) application there are :

3. PivotGridController (contains controllers implementations for all views (direct and remote XMLA, remote JSON)

4. Views :

DirectXmla (Direct XMLA implementation)
Index (Remote JSON via XMLA )
RemoteXmlaProvider (Remote XMLA implementation)

5. OlabWebService (ASMX service, used for JSON serialization )

6. WEB config (you need to allow Cross-origin resource sharing for ASMX service)

Demo project structure

Web Service

Landing page layout

From the landing page there are added three links that calls controllers / respectively return views for the each case that the sample covers:

Remote JSON provider
Remote XMLA provider
Direct XMLA

Direct XMLA Access

The Controller (In this case controller just returns a view)

public ActionResult DirectXmla()

{

    return View();

}

Data source definition.

Here the Url points to the address for SQL Server HTTP Access for OLAP

dataSource = new $.ig.OlapXmlaDataSource({

    serverUrl: 'http://sampledata.infragistics.com/olap/msmdpump.dll',

    catalog: 'Adventure Works DW Standard Edition',

    cube: 'Adventure Works',

    rows: '[Date].[Calendar]',

    columns: '[Product].[Product Categories]',

    measures: '[Measures].[Internet Order Count]'

});

Ignite UI PivotGrid definition – direct XMLA (igPivotGrid just uses data source – there are no specific settings)

$directGrid.igPivotGrid({

    dataSource: dataSource,

    width: "915px",

    height: "450px",

    hideFiltersDropArea: true,

    disableColumnsDropArea: true,

    disableRowsDropArea: true,

    disableMeasuresDropArea: true,

    tupleMemberExpanding: function (evt, ui) {

        toggleTupleMember(true, $directGrid, ui.axisName, ui.tupleIndex, ui.memberIndex);

    },

    tupleMemberCollapsing: function (evt, ui) {

        toggleTupleMember(false, $directGrid, ui.axisName, ui.tupleIndex, ui.memberIndex);

    }

});

Remote XMLA Provider

Controller implementation. In this case you need to add a specific attribute “remote-xmla-provider-endpoint” for your action in the controller. XmlaDataSourceModel is a class implemented in Ignite UI .Net libraries and you need to use it as a context for your view. To initialize the model you need to add a server Url for OLAP HTTP access.

[XmlaDataSourceAction]

 [ActionName("remote-xmla-provider-endpoint")]

 public ActionResult RemoteXmlaProviderEndpoint()

 {

     return View(new XmlaDataSourceModel { ServerUrl = "http://sampledata.infragistics.com/olap/msmdpump.dll" });

 }

Remote DataSource. In this case for serverUrl you need to use the action in the MVC controller: '@Url.Action("remote-xmla-provider-endpoint")'

remoteDataSource = new $.ig.OlapXmlaDataSource({

     isRemote: true,

     serverUrl: '@Url.Action("remote-xmla-provider-endpoint")',

     catalog: 'Adventure Works DW Standard Edition',

     cube: 'Adventure Works',

     rows: '[Date].[Calendar]',

     columns: '[Product].[Product Categories]',

     measures: '[Measures].[Internet Order Count]'

 });

Pivot Grid implementation (there is nothing specific – all differences are in igDataSource).

$remoteGrid.igPivotGrid({

    dataSource: remoteDataSource,

    width: "915px",

    height: "450px",

    hideFiltersDropArea: true,

    disableColumnsDropArea: true,

    disableRowsDropArea: true,

    disableMeasuresDropArea: true,

    tupleMemberExpanding: function (evt, ui) {

        toggleTupleMember(true, $remoteGrid, ui.axisName, ui.tupleIndex, ui.memberIndex);

    },

    tupleMemberCollapsing: function (evt, ui) {

        toggleTupleMember(false, $remoteGrid, ui.axisName, ui.tupleIndex, ui.memberIndex);

    }

});

Remote JSON (using WEB service *.asmx)

The Controller

When you have own serialization controller just returns the view

public ActionResult Index()

{

    return View();

}

Remote JSON WEB service (ASMX service)

In your implementation you need to create XmlaDataSourceModel and XmlaDataSourceManager that should be used to create a query.

Result is serialized as JSON – You can use for the your web method an attribute [ScriptMethod(ResponseFormat = ResponseFormat.Json)].

/// <summary>

/// Summary description for OlapWebService

/// </summary>

[ScriptService] 

[WebService(Namespace = "http://tempuri.org/")]

[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

[System.ComponentModel.ToolboxItem(false)]

public class OlapWebService : System.Web.Services.WebService

{

  

    [WebMethod]

    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]

    public string GetAdventureWorks()

    {

        const string serverUrl = "http://sampledata.infragistics.com/olap/msmdpump.dll";

  

        var model = new XmlaDataSourceModel { ServerUrl = serverUrl };

        var mng = new XmlaDataSourceManager();

        string result = mng.DoQuery(model, this.Context.Request);

  

        return result;

    }

}

Remote JSON view implementation

Remote Data Source using JSON (you need to use for serverUrl the Url of the your web method in the asmx service)

remoteDataSource = new $.ig.OlapXmlaDataSource({

    isRemote: true,

    serverUrl: 'http://localhost:14889/OlapWebService.asmx/GetAdventureWorks',

    catalog: 'Adventure Works DW Standard Edition',

    cube: 'Adventure Works',

    rows: '[Date].[Calendar]',

    columns: '[Product].[Product Categories]',

    measures: '[Measures].[Internet Order Count]'

});

Ignite UI Pivot Grid implementation (it is the same like in Direct XAML and Remote XMLA implementations)

$remoteGrid.igPivotGrid({

    dataSource: remoteDataSource,

    width: "915px",

    height: "450px",

    hideFiltersDropArea: true,

    disableColumnsDropArea: true,

    disableRowsDropArea: true,

    disableMeasuresDropArea: true,

    tupleMemberExpanding: function (evt, ui) {

        toggleTupleMember(true, $remoteGrid, ui.axisName, ui.tupleIndex, ui.memberIndex);

    },

    tupleMemberCollapsing: function (evt, ui) {

        toggleTupleMember(false, $remoteGrid, ui.axisName, ui.tupleIndex, ui.memberIndex);

    }

});

WEB config file

You need to add the lines below to your WEB.config file (you need to allow Cross-origin resource sharing for ASMX service). IE now 10 doesn’t require this settings but you need to add it for other browsers.

<system.webServer>

  ....

  <httpProtocol>

    <customHeaders>

      <add name="Access-Control-Allow-Origin" value="*" />

      <add name="Access-Control-Allow-Headers" value="Origin, X-Requested-With, Content-Type, Accept" />

    </customHeaders>

  </httpProtocol>

</system.webServer>

Running Pivot using JSON via WEB service

Remote JSON implementation (this is the first option)

Select Remote XMLA link to open the view with Remote XMLA implementation. In this case you will see that the data traffic from the remote WEB server to your client is more than 5 times more larger.

Select Direct XMLA link to redirect to Direct XMLA view. In this case you will see the pivot grid with the same data

Conclusions:

Infragistics Ignite UI supports XAML out of the box via Data Source (igDataSource) option “serverUrl”

You could use remote and direct XMLA providers. It is easy to implement own WEB service where to serialize data to JSON and Data Source could use this format.

Remote access leads to less data transfer and it will be better to use this approach (doesn’t matter which format you are using: XMLA or JSON)

Sample source code with all referenced scripts and libraries you could download here:

You are probably thinking, how do I get my hands on Ignite UI Pivot Grid? It’s easy.

Click on this image to get a fully support trial version of Infragistics Ignite UI controls:

To view all the samples and code for HTML, MVC & ASP.NET, click here: .

http://www.infragistics.com/products/jquery/samples

Follow news from Infragistics for more information about new Infragistics products.

As always, you can follow us on Twitter @mihailmateev and @Infragistics and stay in touch on Facebook, Google+andLinkedIn!