MDM over ODATA : Extending MDS with ODATA Services and Entity Framework

B    - By David Nolting

Introduction

In my last post I discussed the benefits of using SQL Server Master Data Services to build reliable MDM solutions.  That post covered modeling and a quick comparison of MDS against required MDM features.  In this post, I look at extending MDS with ODATA services so that Entities defined in the MDS model can be consumed through clients relying on JSON and ATOMPUB standards.  ODATA services work exceptionally well with the concept of entities so it seems to be a natural tool for publishing MDS entities.

Solution Architecture

For important reasons, MDS tables are very metadata centric allowing it to manage versions, models, and staging.   So it is not practical to connect directly to the underlying MDS tables.   The way we extract readable entity information is by creating MDS Subscription Views in MDS which are nothing more than auto-generated SQL Views.   These views create a representation of MDS entities that match the model definitions.   Once the views are created, we can perform LINQ to SQL queries to expose entities; however, ODATA works so well with Entity Framework why not model the Subscription Views in Entity Framework and expose the EF model out over ODATA services?   This solution provides a very small code footprint and can be built with a few clicks.  It allows consumers to filter entity data on any of the existing entity attributes.

MDS Subscription Views to Entity Framework

MDS allows you to auto-generate SQL views for entities.   An example view for the sample customer entities looks like the following:

With that, it is easy to create an Entity Framework model based on the subscription views.  I created a basic .Net class library that contains an ADO.NET Entity Data Model:

I create the model from the Customer View :

The Entity Framework Model contains an entity named Customer which is the same name as the View.   This entity is readable and contains al the master data attribute information associated with a customer:

ODATA Services ( REST )

We now have an Entity Framework model built around our MDS Solution and we created it entirely with the EF wizard.  The next step is to create an ODATA service that publishes Customer entites and allows filtering on attribute information. ODATA's built in filtering mechanism can be accessed through HTTP URL query syntax. Our first step is to create a WCF Application project and to add the Entity Framework project as a reference:

I removed the default WCF services created with the project.   Now we need to add our ODATA Service to our project as follows:

The service generates the following code:

We merely have to reference the EF Model’s entities in the code.  The service is created locked-down so we have to open up access to the entities in the model. 

Once compiled, we can view the ODATA service in the browser ( right click on the service and select View in Browser ).   Accessing the URL http://localhost:56408/MDSService.svc/Customers returns all the customers in MDS:

With ODATA filters, I can query the data on any attribute.  So, for example, queries on the City attribute results in a filtered list of MDS entities.

Another filter on the SalesDistrict_Name returns the following:

Conclusion

Within a matter of minutes and with modification to just 2 lines of generated code, I was able to wrap Entity Framework around MDS and expose my MDS customer entities over ODATA.   Once available as an ODATA service, consumer applications can query on any attribute in the MDS list.    This is a powerful and very easy solution for SOA over MDS using the Microsoft Technology stack.   Off course, this is just a start.   In a real world application, ODATA operations would more than likely be added to provide more powerful filters and analysis on MDS entity data.  This would involve writing some LINQ code against the Entity Framework Model.   But often, MDM systems need a quick way to expose raw entities over SOA for referential checking and this solution seems to fit the bill.