OData

The OData protocol is a perfect solution for querying data via HTTP. Instructions $select, $filter... can limit the data transfer to the just necessary weight.

But OData is entity oriented. Thus, natively, it is not possible to express an analysis request.

The .QueryByCube() function will help remove this limitation artfully.

The code bellow use the Microsoft WCF Data Services technology. Learn more about WCF Data Services. The same result can be obtained defining an ASP.Net WebAPI OData controller.

QueryByCube allows you to build a data analysis REST API.

Make your cube OData queryable!

First, in your web project, use the New Item command to add a WCF Data Service.

The only modifications you have to do in the initialized code file are to specify:

  • the data source class (in my sample I call it SalesAnalysisDataContext)
  • the access rule policy
public class SalesAnalysisDataService : DataService<SalesAnalysisDataContext>
{
    // This method is called only once to initialize service-wide policies.
    public static void InitializeService(DataServiceConfiguration config)
    {
        config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
        config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V3;
    }
}

AdaptiveLINQ OData scenario

The data context class role is just to expose a queryable collection provided by .QueryByCube() function.

using AdaptiveLINQ;
...
public class SalesAnalysisDataContext : IDisposable
{
    public SalesAnalysisDataContext()
    {
        DataContext = new AdventureWorksEntities();
    }

    AdventureWorksEntities DataContext;

    public IQueryable<SalesCubeItem> SalesAnalysis
    {
        get
        {
            return DataContext.SalesOrderDetail.QueryByCube(new SalesCubeDefinition());
        }
    }

    public void Dispose()
    {
        DataContext.Dispose();
    }
}

Because this code use the .QueryByCube() extension method, do not forget the using AdaptiveLINQ; in file header.

WCF Data Service will use the built-in Reflection Provider to consider SalesAnalysis property as a OData queryable collection.

As the Reflection Provider documentation says: each data class that is an entity type must have a key property.

A SalesCubeItem instance isn't really an entity (key varies according the queried fields) but this does not prevent to define a foo key.

To do this, just complete the partial class like this:

[DataServiceKey("UnusedKey")]
public partial class SalesCubeItem
{
    public int UnusedKey { get; set; }
}

Test your analysis service

A great tool to test WCF Data Service is LINQPad.

In LINQPad, add a connection to your WCF Data Service:

LINQPad connection

Now, try this query to get sales per customer:

LINQPad query

Specify the query by adding filter, sort and limit:

SalesAnalysis
.Where(result => result.TotalSales > 100000)
.OrderByDescending(r => r.TotalSales)
.Take(5)
.Select(item => new {
	Customer = item.Customer,
	Sales = item.TotalSales
})

LINQPad query result