Gael Duhamel [MVP]

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! 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:
    • The databases are not expose to public lan
    • You can still secure the databases access (integrated security for exemple)
    • Xml format is based on the standard market:
      • Atom 1.0 Syndication format
      • Atom 1.0 Publishing Protocol
  • 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 asp.net, 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:

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 .

For more information:

• Microsoft Data team has a dedicated web site to the WCF Data Service.
• Data Service team’s blog
• Beginner’s Guide to WCF Data Services

To follow up my post on Power Pivot and Commerce Server, I decided to show you how to achieve three different dashboards and give to you the excel file.

The goal here is not to show you how to create the data source, the relationships and so. You can find very valuable information on a lot of web sites:

• Créer des rapports simples et efficaces pour Commerce Server grâce à PowerPivot (aka Gemini)
• Tutoriel PowerPivot pour Excel – Partie 1/2 : Construction d’un modèle PowerPivot à partir de plusieurs sources de données
• Power Pivot : How To

To achieve theses scenarri you must have:

• Excel 2010
• Addin Power Pivot pour Excel 2010
• A Sql connection to the Commerce Server databases

The databases

To create our different dashboard, we need to connect to two databases:

• Profiles (here SPGael_profiles) : Owns all the profile information for the customers
  • In this table, I have a custom field to store the birthday date. All the others data are native in Commerce Server
• Transactions (here SPGael_transactions) : Owns all the orders information
  • All the data are the native one with Commerce Server.

Select the tables :

• Orders 
  • DiscountsApplied : Owns all the marketing discounts applied on the orders;
  • LineItems : Owns all the line items in the orders;
  • OrderAddresses : Owns all the adresses used for the check out (billing and shipping);
  • OrderForms : Owns the container for the line items (one order has from 1 to n OrderForms and  one OrderForms has from 1 to n LineItems);
  • PurchaseOrderPayments : Owns the different payment methods used during the checkout;
  • PurchaseOrders : Owns the general information about the orders;
  • Shipments : Owns the shipping information used during the checkout;
  • ShippingDiscounts : Owns the shipping discounts applied on an order;
• Customers 
  • UserObjet : Owns the customer information

General dashboard

Order’s dashboard

Customers’ dashboard

Read the rest of this entry »

In one of my previous post, I explained how to configure the analytics reports for Commerce Server, and I’m sure that most of you guys was disappointed by the result (who says everyone?).

Even if the cubes provided by Commerce Server can be a good starting point, the import process is fully out of date and close to a kind of a black box where trying to tune something is an exploit. And I’m not speaking about the reports that have not evolved since 2001…

In short, you will have understood, it is time that the Commerce Server team focuses on this feature to give it a good blow paint!

So, what we can do?

• Develop new reports based on the existing cubes
  • Pros:
    • No cube development;
    • Depends on your needs, not so much development time to add some new information;
  • Cons
    • Hope that the import process running well and rich your goals ;
    • Redesign all of your reports if you want more modern ones;
    • The structure of cubes does not necessarily correspond to your need;
    • Require IT intervention for all adjustments;
• Develop your own cubes
  • Pros
    • You stuck to your goals;
  • Cons
    • Require IT intervention for all adjustments;
    • May be lengthy implementation time;
• Do nothing
  • 

In any cases, you have to work with your IT department and you may not have the time to wait for them. Or you may want to explore the data as you want, because you’re the best person to understand your ecommerce web site . The miracle solution is:

Hello and welcome to Gemini, sorry Power Pivot

Power Pivot is a new tool developed by Microsoft to facilitate and give users the power to create compelling self-service BI solutions. Power Pivot is a data analysis add-in within Excel 2010 (and only with Excel 2010):

• Microsoft Office 2010 Beta
• Power Pivot for Excel 2010

I tested the solution against one of my Commerce Server project and I can confess that I’m excited by getting started ease and speed execution.

It’s really easy with Power Pivot to a data source. Because of the user friendly and guided interfaces:

And voilà, in 5 minutes, I imported my 2Go Commerce Server transactions database. And cherry on the cake, my Excel file is only weight 43 Mo. You don’t trust me?

With the relational and multi-dimensional structure of Power Pivot, you are able to design quickly and simply powerful charts or cross tables in a couple of minutes. Let’s take an example with a chart that displays the number of orders by day:

• First, select in the menu the chart

• Then, add the number of tracking number in the value box and in axis the created field.

• Not readable? I’m agreeing The problem here is the created date contains the date and time when the order was created. We need to have only the date without the time. For that we just have to create a new column in the “Purchase Orders” section by using the DAX language (which is very close to the Excel one): RIGHT("0" &MONTH([Created]),2) &"/" &year([Created])

• Refresh the data source to have the new column available:

Easy isn’t it?

You understand it’s therefore very easy to implement powerful dashboards through Powert Pivot (for data) coupling to Excel (for formatting), example:

For more information:

• Power Pivot for Excel 2010 : Introducing Microsoft Office 2010 Technical Preview
• Site Officiel de Power Pivot
• Official Blog