Using MVC OLAP to Analyze Big Data

OLAP for ASP.NET MVC allows you to populate data from the model or from a Read action, and then it transfers all data to the client where the aggregations are performed. However, this may not be ideal where the data is huge, when lot of data is transferred to client side. A few issues may arise.

• Serialisation may slow down the app at server side.
• Application performance can take a hit at client because of huge in memory data.

The DataEngine Web API is designed to specifically solve this kind of problem. DataEngine Web API uses a column-oriented data model which is widely used in many open source and commercial analytical databases and libraries. Data Engine can handle up to hundreds of millions records in a fraction of a second. It aggregates the data on the server and sends the result to client thus making the application very efficient. Additionally, you can use DataEngine to connect to SQL Server Analysis Services.

Adding DataEngine to the app.

DataEngine Web API is available as NuGet package from the GrapeCity NuGet package source. DataEngine can be used in an Web API app(with MVC) or pure WebAPI app to be hosted separately. This example considers Web API(with MVC).

Configuring DataEngine

DataEngine provides a suite of APIs that allow the user to analyze data from multiple data sources, including SQL Server, other SQL based RDBMS servers, NoSQL service, web service, structured files from file/network systems, and more. The aggregating data can be consumed by other controls or application. Detailed steps of configuration can be found here. In the example below, the dataengine imports a simple datatable with a million records and names it "Complex10". The Configuration method of the Startup.cs class looks as below. Note: To execute Startup.cs class along with your application, install “Microsoft.Owin.Host.SystemWeb" Nuget package in your application.

   public partial class Startup  
    {  
        private readonly HttpConfiguration config = GlobalConfiguration.Configuration;  
        private static string DATAPATH = Path.Combine(System.Web.HttpRuntime.AppDomainAppPath, "Data");  
        public void Configuration(IAppBuilder app)  
        {  
            app.UseDataEngineProviders()  
            .AddDataEngine("complex10", DATAPATH, "Complex10", (ws, tableName) =>  
            {  
                Workspace w = new Workspace();  
                w.Init(ws);  
                System.Data.DataTable table = ProductData.GetDataTable(1000000, tableName);  
                DbConnector.GetData(w, table, tableName);  
                w.Save();  
                w.Dispose();  
            })  
        }  
    }  

It creates a workspace and uses DBConnecter to get the data from ProductData model, When a query is performed, it creates memory mapped files of blended data in the DATA folder. Since it has already created the data files, subsequent queries for same set of data is faster. This is a Persisted memory-mapped files methodology. In the View, OLAP can then be configured as follows to call the dataengine Web API and get the aggregated data.

  @(Html.C1().PivotEngine().Id("dataEngine").BindService(null, "complex10")  
  .RowFields(pfcb => pfcb.Items("Country"))  
  .ColumnFields(cfcb => cfcb.Items("Product"))  
  .ValueFields(vfcb => vfcb.Items("Sales")))  


  <div class="row">  
  <div class="col-sm-4 col-md-4">  
  @Html.C1().PivotPanel().ItemsSourceId("dataEngine")  
  </div>  
  <div class="col-sm-8 col-md-8">  
  @Html.C1().PivotGrid().ItemsSourceId("dataEngine")  
  </div>  
  </div>  

The BindService property of the PivotEngine\PivotPanel takes the service url and name of the view. Incase the DataEngine Web API is hosted separately, the url can be passed to BindService.

More from the ASP.NET MVC OLAP

Demos: ASP.NET 4.0 | ASP.NET Core Take a look at the documentation for more details. Download C1Studio