Getting Started with DataEngine Web API

DataEngine provides a suite of APIs that allow the user to analyze the data from multiple data sources, including SQL Server, other SQL based RDMS servers, NoSQL service, web service, structured files from file/network systems, and more. The aggregating data can be consumed by other controls or application. Note: Web API Edition is available only with ComponentOne Studio and Ultimate.

Because the DataEngine Web API uses a column-oriented data model—recently used with great success in many open source and commercial analytical databases and libraries—it can reach high performance: up to hundreds of millions records in a fraction of a second.As the aggregating data is fetched from the Web API, the client is simple, and it only sends the corresponding query to the server with some format to fetch the data.

When using DataEngine, first we need to develop a Web API server.

How to develop a DataEngine Web API Server

Step One: Create a Web API application via Visual Studio.

Make sure the .NET Framework version is 4.5, and choose “ASP.NET Web Application” to create.

DataEngine01_CreateWebApp

Select “Empty” template and check “Web API”.

DataEngine02_SelectTemplate

Step Two: Install “C1.Web.Api.DataEngine” package to the application.

image3

The package and its dependency packages will be installed:

image4

Note: The DataEngine package is now available on GrapeCity NuGet. You can update the package from GrapeCity NuGet Source: http://nuget.grapecity.com/nuget

This GrapeCity NuGet source is added to Visual Studio when you install the MVC and Web API Editions. If they're not installed already, you'll need to add the source manually to Visual Studio.

You will find the package and its dependency packages would be installed.

Step Three: Add a Startup item for the application.

Right-click the application project. In the popup context menu, click “Add” item and then select “New Item…”

DataEngine05_AddNew

Select “OWIN Startup class” template in “Web” and click “Add” button.

DataEngine06_Startup

You'll find the “Startup1.cs” file is added to the application and it is opened in Visual Studio:

DataEngine07_OpenedinVS

Step Four: Register the data source in “Startup1.cs”

using Microsoft.Owin;  
using Owin;  
using System.IO;  
using C1.DataEngine;  
using WebApplication16.Models;  

[assembly: OwinStartup(typeof(WebApplication16.Startup1))]  

namespace WebApplication16  
{  

    public class Startup1  
    {  
        private static string DATAPATH = Path.Combine(System.Web.HttpRuntime.AppDomainAppPath, "Data");  
        public void Configuration(IAppBuilder app)  
        {  
            // For more information on how to configure your application, visit http://go.microsoft.com/fwlink/?LinkID=316888  
            app.RegisterDataEngine("complex", DATAPATH, "Complex", (ws, tableName) =>  
            {  
                Workspace w = new Workspace();  
                w.Init(ws);  
                System.Data.DataTable table = ProductData.GetDataTable(100000, tableName);  
                DbConnector.GetData(w, table, tableName);  
                w.Save();  
                w.Dispose();  
            });  
        }  
    }  
}

Now you can register the DataEngine data and the memory data by the extended methods RegisterDataEngine and RegisterDataSet. In the next release, it will support to register the SSAS data.

When using the DataEngine data source, we should prepare the data files via the following codes:

  Workspace w = new Workspace();  
                w.Init(ws);  
                System.Data.DataTable table = ProductData.GetDataTable(100000, tableName);  
                DbConnector.GetData(w, table, tableName);  
                w.Save();  
  w.Dispose();

We can use DbConnector to fill the data supported by ADO.NET into the data files. We can also use ObjectConnector to fill the data from an arbitrary IEnumerable to the data files.

In order to improve the first running performance of the Web API server, we can initialize the workspace out of the application.

Here, the data is randomly generated. You can add the ProductData class to the Model folder:


using System;  
using System.Data;  

namespace WebApplication16.Models  
{  
    public class ProductData  
    {  
        private static Random r = new Random();  

        public int ID { get; set; }  
        public string Product { get; set; }  
        public string Country { get; set; }  
        public DateTime Date { get; set; }  
        public int Sales { get; set; }  
        public int Downloads { get; set; }  
        public bool Active { get; set; }  
        public double Discount { get; set; }  

        private static int randomInt(int max)  
        {  
            return (int)Math.Floor(r.NextDouble() * (max + 1));  
        }  
        public static DataTable GetDataTable(int cnt, string tableName)  
        {  
            var sufix = cnt / 10000;  
            string[] countries = "China,India,Russia,US,Germany,UK,Japan,Italy,Greece,Spain,Portugal".Split(',');  
            string[] products = "Wijmo,Aoba,Xuni,Olap".Split(',');  
            DataTable data = new DataTable(tableName);  
            data.Columns.Add("ID", typeof(int));  
            data.Columns.Add("Product", typeof(string));  
            data.Columns.Add("Country", typeof(string));  
            data.Columns.Add("Date", typeof(DateTime));  
            data.Columns.Add("Sales", typeof(int));  
            data.Columns.Add("Downloads", typeof(int));  
            data.Columns.Add("Active", typeof(bool));  
            data.Columns.Add("Discount", typeof(double));  
            for (var i = 0; i < cnt; i++)  
            {  
                object[] values = new object[] {  
                    i,  
                    products[randomInt(products.Length - 1)],  
                    countries[randomInt(countries.Length - 1)],  
                    new DateTime(2015, randomInt(11) + 1, randomInt(27) + 1),  
                    randomInt(10000),  
                    randomInt(10000),  
                    randomInt(1) == 1 ? true : false,  
                    r.NextDouble()  
                };  
                data.Rows.Add(values);  
            }  
            return data;  
        }  
    }  
}

Step Five: Add cross domain support if necessary.

If you want your Web API server support cross domain requests, you can add the codes in the Configuration method of the Statup1.cs file.

app.UseCors(CorsOptions.AllowAll);

Install the “Microsoft.Owin.Cors” package in your application. Otherwise, the application cannot be compiled successfully.

Step Six: Install “Microsoft.Owin.Host.SystemWeb” package to make the Startup1 class run when the application runs.

Run the application, and the browser will be opened:

DataEngine08_OpeninBrowser

Append /api/dataengine/complex/fields to the url path in the address bar and navigate it. You can see the following:

DataEngine09_Edit

This means you've developed the DataEngine Web API server successfully. Now you'll need to create a client sample to access the services provided by the DataEngine Web API to fetch the aggregating data.

How to Use the APIs in the Client

After developing the server, we can create a client to communicate with the server. I use the AJAX method in jQuery library to send the queries in client for a simple sample.

The query service url is the path with the format:

http://localhost:7383/api/dataengine + [the key(the first parameter) specified in the data source registration part] + / + [command]

localhost:7383 is the root path of the Web API server that we developed before. You need update it to your root path. In the following, we'll use “localhost:7383” for the root path.

Here we use “complex” as an example. It is the registered key for the DataEngine data source. Different commands are required for different queries. DataEngine Web API has eight commands:

  1. fields: Gets all the fields according to the specified data source.
  2. rawdata: Gets the raw data of the data source specified by datasourcekey.
  3. analysis: Creates an aggregating query according to the specified data source and the view definition.
  4. status: Gets the status of the specified token.
  5. cancel: Cancels the request specified by the token option. When the query takes too long time and you don’t want to wait for the result or the result data is outdated because the view is changed, you can call this service to cancel the previous query.
  6. resultdata: Gets the result data for the aggregating query specified by the token.
  7. details: Gets the detail data for the specified cell.
  8. uniquevalues: Gets the unique values of a field. Getting value list could take considerable time if number of rows in the source data is very large.

Step One: Get the information of all the fields for the analyzed data.

Send a “Get” ajax request to the service url via the following codes:

$.ajax({  
type: 'GET',  
url: 'http://localhost:7383/api/dataengine/complex/fields',  
cache: false,  
dataType: 'json',  
processData: false,  

success: function (result, status, xhr) {  
     // the field list and total row count will be obtained from result.data.  
     // the data format is like the following  
/*{  
"data":{  
"fields":[  
{"binding":"Active","dataType":3},  
{"binding":"Country","dataType":1},  
{"binding":"Date","dataType":4},  
{"binding":"Discount","dataType":2},  
{"binding":"Downloads","dataType":2},  
{"binding":"ID","dataType":2},  
{"binding":"Product","dataType":1},  
{"binding":"Sales","dataType":2}  
],  
"rowCount":100000  
}  
}*/  
}  
})

Step Two: Get the aggregated data for the specified view definition.

If you want to get the aggregated data for the sales of the products in the countries, you can send a “POST” ajax request to the service url via the following code:

$.ajax({  
type: 'POST',  
url: 'http://localhost:7383/api/dataengine/complex/analysis',  
data: {  
view: '{"fields":[{"binding":"Active","dataType":3},{"binding":"Country","dataType":1},{"binding":"Date","dataType":4},  
{"binding":"Discount","dataType":2},{"binding":"Downloads","dataType":2},{"binding":"ID","dataType":2},  
{"binding":"Product","dataType":1},{"binding":"Sales","dataType":2}],  
rowFields:{items:["Product"]},  
columnFields:{items:["Country"]},  
valueFields:{items:["Sales"]}}'  
},  
dataType: 'json',  
cache: false,  
processData: false,  
success: function (result, status, xhr) {  
// the following information will be obtained from result.  
//{  
// "token":"93f00724-3877-41ea-80ff-bbeae96f5e36",  
// "status":'Executing'  
//}  
}  
});

The view definition is set in the data. You can specify the fields in rowFields, columnFields and valueFields to get the aggregated data what you want. You can also specify the aggregation function for some value field. For detail, please check the specification.

The response data format is like the following:

{  
"token":"93f00724-3877-41ea-80ff-bbeae96f5e36",  
"status":'Executing'  
}

“Status” indicates the status of the current query. It has four values: Executing, Completed, Exception and Cancelled. Its default value is “Executing”. When only token is obtained from the response, it means the status value is “Executing”. When the status is “Executing”, we need send the following request to the server:

$.ajax({  
type: 'GET',  
url: 'http://localhost:7383/api/dataengine/93f00724-3877-41ea-80ff-bbeae96f5e36/status',  
cache: false,  
dataType: 'json',  
processData: false,  
success: function (result, status, xhr) {  
// the status is obtained from result.  
}  
})

The following data will be obtained from “result” in the success function:

{  
"token":"93f00724-3877-41ea-80ff-bbeae96f5e36",  
"status":'Executing'  
}
  • If the status is "Executing", this request will be repeatedly sent until the status in the response is changed to “Exception”, “Completed” or “Cancelled”.
  • When it is “Exception”, it means some error occurs during the request. We can get the error information from the error field in the response.
  • When it is “Cancelled”, it means the request is terminated by the user via sending some request. You can find the detail in the specification.
  • When it is “Completed”, it means the analysis is finished and the aggregated data is prepared, we can send the following request to get it:
$.ajax({  
type: 'GET',  
url: 'http://localhost:7383/api/dataengine/93f00724-3877-41ea-80ff-bbeae96f5e36/resultdata',  
cache: false,  
dataType: 'json',  
processData: false,  
success: function (result, status, xhr) {  
// the aggregated data is obtained from result.data.  
}  
})

We can get the aggregated data from “result” in the success function:

{  
"data":[  
{"Product":null,"Country":null,"Sales":57300.0},  
{"Product":null,"Country":"Greece","Sales":6784.0},  
{"Product":null,"Country":"Italy","Sales":16977.0},  
{"Product":null,"Country":"China","Sales":8756.0},  
{"Product":null,"Country":"Spain","Sales":8953.0},  
{"Product":null,"Country":"Portugal","Sales":6688.0},  
{"Product":null,"Country":"Russia","Sales":9142.0},  
{"Product":"Aoba","Country":null,"Sales":10898.0},  
{"Product":"Aoba","Country":"Greece","Sales":6047.0},  
{"Product":"Aoba","Country":"Italy","Sales":4851.0},  
{"Product":"Olap","Country":null,"Sales":15398.0},  
{"Product":"Olap","Country":"China","Sales":8756.0},  
{"Product":"Olap","Country":"Greece","Sales":737.0},  
{"Product":"Olap","Country":"Italy","Sales":5905.0},  
{"Product":"Wijmo","Country":null,"Sales":8953.0},  
{"Product":"Wijmo","Country":"Spain","Sales":8953.0},  
{"Product":"Xuni","Country":null,"Sales":22051.0},  
{"Product":"Xuni","Country":"Italy","Sales":6221.0},  
{"Product":"Xuni","Country":"Portugal","Sales":6688.0},  
{"Product":"Xuni","Country":"Russia","Sales":9142.0}  
]  
}

The value which is null means it is a total value. For example,

{"Product":null,"Country":"Greece","Sales":6784.0}

It means the sale for all the products in Greece is 6784.0.

{"Product":null,"Country":null,"Sales":57300.0},

It means the total sale(for all the products in all the countries) is 57300.

Final Step: Get other data from the provided API

You can get the other data such as the detail raw data for some cell, all the raw data for analyzing and the unique values for some field, etc. Read more about DataEngine Web API

GrapeCity

GrapeCity Developer Tools
comments powered by Disqus