Boite à outils Office

How to consume the Commerce Server data for Power Pivot with a RESTful service

.Net, Commerce Server, Development, Power Pivot, SQL Server, WCF 1 Comment »

To follow up my last two posts on Commerce Server and Power Pivot (Create simple and effective reports for Commerce Server with PowerPivot (aka Gemini) et Understand and analyze your Commerce Server data with Power Pivot), I received a couple of questions on how to retrieve the Commerce Server data on a non local environment.

Indeed, most of you have no direct access to the SQL Server databases (I hope so ^^): the e-Commerce farm is hosting in another datacenter with its own vLan to secure the databases. In another word, there are no way to connect to your databases via a simple connection string.

So, what are the options to get the data?

  • SQL Server replication SQL Server between the production environment and your local environment:
    • Pros:
      • Many way to synchronize your databases (transactional , snapshot, …);
      • Strong and tested feature;
    • Cons:
      • You must have a SGBD (yes guys! Smile Some of the consultants hasn’t got a SGBD);
      • Expose the databases is not a best practices for a security point of view (even if you filter by IP), to secure it, you must have a dedicated internet line between your datacenter and the different end points. Which is quite expensive;
  • Export the data in a text format and then import them to the local SGBD:
    • Pros:
      • Excel and Power Pivot have a strong text import feature;
      • Excel is well known by the most of the consultants (the same who hasn’t go a SGBD). So, not difficult for them to get the data!
    • Cons:
      • Store the files on a local file system is not easy to manage in a long term perspective;
      • Difficult to share the Power Pivot file between different people;
  • Use the Power Pivot capacity to consume a data feed stream (Have a look on this MSDN article)
    • Pros:
    • Cons:
      • Very new technology so not so much test on it
      • Using a https certificate to ensure the transport between the web service and the end points.

In these 3 scenarii, it’s difficult to say that the solution is only in one of these three. All have pros and cons. It really depends on what you can do and what are the expectations. From myself I decided to show you the data feed option.

To expose your data in, the best solution is to use a WCF service. This service will be able to render the data in the desired format (here ATOM). Nothing new? Agreed! The trick here is to use the new WCF Data Service. The WCF Data Service enable you a RESTful exposition of your data. In another words, using the OData protocol!

To achieve this, we need to have an application web where we will add a new item : “ADO.Net Entity Data Model” connected to your SQL Server database.


In case of your dashboard are based on the order system, you have to connect to the <site commerce server>_Transactions database.


Then, add an new WCF Data Service item.


In the new created service, addin the InitializeService method the code below:

config.SetEntitySetAccessRule("DiscountsApplied", EntitySetRights.AllRead);
config.SetEntitySetAccessRule("LineItems", EntitySetRights.AllRead);
config.SetEntitySetAccessRule("OrderAddresses", EntitySetRights.AllRead);
config.SetEntitySetAccessRule("OrderForms", EntitySetRights.AllRead);
config.SetEntitySetAccessRule("PurchaseOrderPayments", EntitySetRights.AllRead);
config.SetEntitySetAccessRule("PurchaseOrders", EntitySetRights.AllRead);
config.SetEntitySetAccessRule("Shipments", EntitySetRights.AllRead);
config.SetEntitySetAccessRule("ShippingDiscounts", EntitySetRights.AllRead);

This code, will allow the service to retrieve the table content in read only mode.

You can now call you service in your favorite browser and get the below Atom XML feed. In this feed, you get all the entities that you give the read permission.


As it’s a RESTful service, you can play with the URI to navigate into the data. So for example, add LineItems at the end of the URL (ex: http://localhost//CommerceServerOrdersDataService.svc/LineItems)

If you call again your service, you will see a RSS feed like:


To switch to the xml view, in Internet Explorer, go to Internet Options > Content > Feeds and web slicers settings and untick the feed reading view checkbox.



And voilà! The development part is now finished and you’re able to consume this data feed in Power Pivot:

image image image


In Commerce Server, all the web services access are managed by an azman file with integrated security. I will show you in a next post how to do a dedicated one for this data feed service Smile.

For more information:

WP Theme & Icons by N.Design Studio
Entries RSS Comments RSS Log in
Creative Commons License