Boite à outils Office

Create simple and effective reports for Commerce Server with PowerPivot (aka Gemini)

Analytics, BI, Commerce Server, Power Pivot, Reporting, SQL Server 1 Comment »

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):

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:


image   image  image  image  image  image

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?

image image

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:

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