Gael Duhamel [MVP]

A couple of weeks ago, Kerry Havas, Microsoft Commerce Server Product Architect, has released the second version of CSMetal.

What is CSMetal? It’s a free tool for Commerce Server which generates strongly typed classes called by a dedicated Linq provider based on the Commerce Foundation API => ie, that do not replace the Commerce Foundation API!

Kerry did a very well job with CSMetal as the development becomes very easy for all developers who don’t know Commerce Server!

Before using the Linq provider for Commerce Server we have to generate the entities with the CSMetal command line.

You need to have:

• Le ChannelConfiguration.config file correctly configured
  • Please, check that the useSharedCommerceContexts attribute is set to false.
  • Add to the message handler node with an attribute name called "CommerceQueryOperation_CommerceEntityDefinition" the below nodes:

   1: <!-- Note that RepositoryMetadataLoader is replaced by a slightly customized one -->

   2: <Component name="RepositoryMetadataLoader" type="Microsoft.Commerce.Metal.Generator.OperationSequenceComponents.RepositoryMetadataLoader, Microsoft.Commerce.Metal.Generator, Version=1.0.0.0, Culture=neutral" />

   3: <!-- Note that this is added to add overlay capability to the Metadata repository -->

   4: <Component name="MetadataOverlayProcessor" type="Microsoft.Commerce.Metal.Generator.OperationSequenceComponents.MetadataOverlayProcessor, Microsoft.Commerce.Metal.Generator, Version=1.0.0.0, Culture=neutral" />

• Note that the last child node allows you roughly to generate new entity or add new attribute to existing entity. More details below.
• MetadataDefinitions.xml file correctly configured 
  • Add to the RelationshipTypes node, a child one:
    • <RelationshipType name="Inherits"/>
  • Review the “CSMetal_Beta2_ReadMe.rtf” file, and see that we have to add 2 new entities:
    • Basket
      • Order status enumeration
    • Ads
      • Size enumeration for the ads
• The famous OrderObjectMappings.xml and OrderPipelineMappings.xml files, properly configured.

Put all the beyond dedicated Commerce Server files in a directory, and add some files to the proper execution of CSMetal:

• CSMetal.exe
• CSMetal.exe.config which is roughly a copy of the web.config of your web.application
• GeneratorSettings.xml which allows you to define the output directory for the generated assemblies. And cherry on the cake with your own namespace!
• Microsoft.Commerce.Metal.Generator.dll for the code generation.

So, at the end you will have:

As explained beyond, Kerry introduces a new concept called “MetaDataOverlays”:

• You can extand your metadata with an external file and do not apply any modification to the MetaDataDefinition.xml file. That allows you to keep the MetaDataDefinition as a repository and the overlays files. This provides a way to manage the changes you have made to the metadata by keeping it in a separate file.

Just add a new xml file called Metadata_CSMetal.xml in a new directory called MetadataOverlays

   1: <?xml version="1.0" encoding="utf-8" ?>

   2: <!-- 

   3: This file contains the Metadata Repository

   4: -->

   5: <MetadataDefinitions xmlns="urn:schemas-microsoft-multi-channel-commerce-foundation-metadata">

   6:     <DefaultChannel>

   7:         <CommerceEntities>

   8:             <!-- Added for Linq based Search -->

   9:             <CommerceEntity name="CatalogEntity">

  10:                 <DisplayName value="Catalog Entity" />

  11:                 <Properties>

  12:                     <Property name="Phrase"    dataType="String">

  13:                         <DisplayName value="Phrase"/>

  14:                     </Property>

  15:                     <Property name="ReturnTypes"    dataType="Integer">

  16:                         <DisplayName value="Return Types"/>

  17:                     </Property>

  18:                     <Property name="FirstItem"    dataType="Integer">

  19:                         <DisplayName value="First Item"/>

  20:                     </Property>

  21:                     <Property name="WhereClause"    dataType="String">

  22:                         <DisplayName value="Where Clause"/>

  23:                     </Property>

  24:                     <Property name="TotalItemCount"    dataType="Integer">

  25:                         <DisplayName value="Total Item Count"/>

  26:                     </Property>

  27:                     <Property name="ResultsPerPage"    dataType="Integer">

  28:                         <DisplayName value="Results Per Page"/>

  29:                     </Property>

  30:                 </Properties>

  31:             </CommerceEntity>

  32:             <!-- Added for Linq based Search -->

  33:  

  34:             <!-- Catalog Start -->

  35:             <CommerceEntity name="Catalog">

  36:                 <Relationships>

  37:                     <Relationship name="DependantCatalogs" type="Relationship" modelName="Catalog" isMultipleItems="true" >

  38:                         <DisplayName value="Dependant Catalogs" />

  39:                     </Relationship>

  40:                     <Relationship name="SourceCatalogs" type="Relationship" modelName="Catalog" isMultipleItems="true" >

  41:                         <DisplayName value="Source Catalogs" />

  42:                     </Relationship>

  43:                 </Relationships>

  44:             </CommerceEntity>

  45:  

  46:             <!-- CreditCard Start -->

  47:             <CommerceEntity name="CreditCard">

  48:                 <Relationships>

  49:                     <Relationship name="PaymentAccount" type="Inherits" modelName="PaymentAccount" isMultipleItems="false">

  50:                     </Relationship>

  51:                 </Relationships>

  52:             </CommerceEntity>

  53:  

  54:             <!-- Basket Start -->

  55:             <CommerceEntity name="Basket">

  56:                 <Relationships>

  57:                     <Relationship name="LineItems" modelName="LineItem" type="Relationship" isMultipleItems="true" />

  58:                     <Relationship name="Addresses" modelName="Address" type="Relationship" isMultipleItems="true" />

  59:                     <Relationship name="Payments" modelName="Payment" type="Relationship" isMultipleItems="true" />

  60:                     <Relationship name="Shipments" modelName="Shipment" type="Relationship" isMultipleItems="true" />

  61:                     <Relationship name="RequestedPromoCodes" modelName="RequestedPromoCode" type="Relationship" isMultipleItems="true" />

  62:                 </Relationships>

  63:             </CommerceEntity>

  64:  

  65:             <!-- ShopperList Start -->

  66:             <CommerceEntity name="ShopperList">

  67:                 <Relationships>

  68:                     <Relationship name="LineItems" modelName="LineItem" type="Relationship" isMultipleItems="true" />

  69:                 </Relationships>

  70:             </CommerceEntity>

  71:  

  72:             <!-- LineItem Start -->

  73:             <CommerceEntity name="LineItem">

  74:                 <Relationships>

  75:                     <Relationship name="ItemLevelDiscounts" modelName="Discount" type="Relationship" isMultipleItems="true" />

  76:                 </Relationships>

  77:             </CommerceEntity>

  78:  

  79:             <!-- CashCard Start -->

  80:             <CommerceEntity name="CashCard">

  81:                 <Relationships>

  82:                     <Relationship name="PaymentAccount" type="Inherits" modelName="PaymentAccount" isMultipleItems="false">

  83:                     </Relationship>

  84:                 </Relationships>

  85:             </CommerceEntity>

  86:  

  87:             <!-- GiftCertificate Start -->

  88:             <CommerceEntity name="GiftCertificate">

  89:                 <Relationships>

  90:                     <Relationship name="PaymentAccount" type="Inherits" modelName="PaymentAccount" isMultipleItems="false">

  91:                     </Relationship>

  92:                 </Relationships>

  93:             </CommerceEntity>

  94:  

  95:             <!-- Shipment Start -->

  96:             <CommerceEntity name="Shipment">

  97:                 <Relationships>

  98:                     <Relationship name="ShippingMethod" modelName="ShippingMethod" type="Relationship" isMultipleItems="false" />

  99:                 </Relationships>

 100:             </CommerceEntity>

 101:  

 102:             <!-- Payment Start -->

 103:  

 104:             <!-- PaymentAccount Start -->

 105:             <CommerceEntity name="PaymentAccount">

 106:                 <Properties>

 107:                     <Property name="Id" dataType="String">

 108:                         <DisplayName value="Id"/>

 109:                         <Description value="Unique ID of the payment." />

 110:                     </Property>

 111:                     <Property name="CustomerName" dataType="String">

 112:                         <DisplayName value="CustomerName"/>

 113:                         <Description value="Name of Customer." />

 114:                     </Property>

 115:                     <Property name="BillingAddressId" dataType="String">

 116:                         <DisplayName value="BillingAddressId"/>

 117:                         <Description value="Id of billing address" />

 118:                     </Property>

 119:                     <Property name="DisplayName" dataType="String">

 120:                         <DisplayName value="DisplayName"/>

 121:                         <Description value="DisplayName." />

 122:                     </Property>

 123:                 </Properties>

 124:                 <Relationships>

 125:                     <Relationship name="PaymentMethod" modelName="PaymentMethod" type="Relationship" isMultipleItems="false" />

 126:                 </Relationships>

 127:             </CommerceEntity>

 128:             

 129:             <CommerceEntity name="Payment">

 130:                 <Relationships>

 131:                     <Relationship name="PaymentAccount" modelName="PaymentAccount" type="Relationship" isMultipleItems="false" />

 132:                 </Relationships>

 133:             </CommerceEntity>

 134:         </CommerceEntities>

 135:     </DefaultChannel>

 136: </MetadataDefinitions>

Now it’s time to run the CSMetal.exe:

In the directories, you will find 3 cs files (if you configure the generation for odata), one for the context, one for the entities and one for odata:

Copy the files GeneratedContexts.cs and GeneratedTypes.cs in the project CSMetalLinqProvider and re run a new compilation for this project.

Your Commerce Entities are now available through your Linq provider for Commerce Server

To get a category, just write down these lines of code:

var category = new CategoryContext(commerceServiceProxy)
                  .IncludeChildCategories()
                  .Single(o => o.Id=="Departments" && o.CatalogId==DefaultCatalog);
var childCategories = category.ChildCategories;

or for a product :

var product = new ProductContext(commerceServiceProxy)
                 .Single(o=>o.CatalogId==DefaultCatalog && o.Id=="AW200-12");
var productCode = product.ProductCode;

That’s just a simple sample of what the Commerce Server Linq Provider is able to do and I’ll try to show you more in my next posts. But, I’m sure that will simplify the life for your developer

As you should know, Microsoft Commerce Server allows you to manage your marketing campaigns of your e-Commerce website through 2 modules of the Marketing System :

• Discounts
• Advertisements

These two modules are all schedulable. Which means that you can set a start and end date for the display of an ad or on a discount application.

By default, the Marketing Business Tools allows you to preview the rendering of an ad:

But nothing on a discount… and my two cents, that like my customers, you will tell :”Hey, Gaël, it’s not enough! I need to view my ads or my discounts in my website. I have to be sure that everything is set up correctly…”

Ok, so it’s pretty easy to see the result of your marketing campaign items if they are all applicable right now. Just open your web application and that’s it However, don’t forget that you might have constrains on your ads or discounts like for example:

• the ad can be displayable on a particular type of pages (home, products, basket, …),
• the ad or the discount is eligible if the connected user meets the requirement like to be a man or a woman
• etc, etc, … (if you have questions on all the different condition, do not hesitate to contact me )

But what can you do if your marketing campaign items are expected in 2 or 3 months? Do you have to wait the d day like you was expecting your first party? So, for sure the answer is…

… really depending of the quality of your Commerce Server Consultant/Expert Let’s pick up one of the below answers:

• “That’s not possible at all! Commerce Server don’t do those kind of feature!”
  • Meaning that’s time for you to change your consultant/expert => Are you looking for a Commerce Server expert?
• “Change the server’s date and refresh your cache to see the result” (truthful!)
• Handy if you have staging and live on the same box, isn’t it?  
• Most of the time it’s an IT task, and I guess they have something else to do… (I do hope )
• Don’t forget to rollback the date, so again an IT task…
• “Deactivate your eligible marketing campaign items and activate only the ones you want to test by changing the start date to the current day time.”
  • Need to have a staging environment
  • Painful, I have customer who has more than 100 advertisements and at least the same amount of discount…
  • Confusion when you have to reactivate the true eligible marketing campaign items
    • Have to be sure which have to be reactivate…
  • Possible errors when you modify the begin and end date
  • Some guys can tell you that a custom development for activating /deactivating a list of campaign items can be the solution. Right, but that’s still not an acceptable answer as you still have to tick/untick the campaign items…
• “No worries, we have the solution!”…

The solution is pretty simple. Fist, you have to know that the Commerce Server runtime loads the different caches and context information during the first load of the application or each time the cache is refreshing. That’s of course for better performance considerations.

In the case of discouts and advertising the caching system is loaded by two stored procedures (and an another one called by these 2 stored procedures) which is located in the marketing system database:

• mktg_spRuntimeLoadAdvertisements
• mktg_spRuntimeLoadDiscounts
• mktg_spRuntimeLoadReferenceTables

=> These stored procedures are called at the first load or each time the caches are refreshing.

The Commerce Server team did a really great job with the caching system on discounts and advertisements. Indeed, it’s possible to tell to the runtime which stored procedures to load and so, we are now able to include our own logic within them.

The idea, here, is to have two applications. One for the preview and another one for the default behavior. In the preview web application, we will load our two new stored procedures by the runtime.

In order to do that, we will tell to the runtime to load our 2 stored procedures by modifying the web.config file, in the caches section web.config > CommerceServer > Caches (see MSDN: caches Element).

In the preview application web.config’s file we will changing the default caching section by:

<cache
    name="Advertising"
    type="Advertising"
    refreshInterval="900"
    retryInterval="30"
    loaderProgId="Commerce.CSFLoadAdvertisements"
    writerProgId="Commerce.CSFWriteEvents">
    <config
        key="LoadAdvertisementsProcedure"
        value="spGael_staging_mktg_spRuntimeLoadAdvertisements" />
</cache>
<cache
    name="Discounts"
    type="Discounts"
    refreshInterval="0"
    retryInterval="30"
    loaderProgId="Commerce.CSFLoadDiscounts"
    writerProgId="Commerce.CSFWriteEvents">
    <config
        key="LoadDiscountsProcedure"
        value="spGael_staging_mktg_spRuntimeLoadDiscounts" />
</cache>

The default behavior for the pipeline is to compare the start and end date with the current local time, but as you may know, changing that means re develop all the pipeline components and that’s of course not my solution . To workaround that, I will move back the start and end date by calculating the difference between the current date time and the preview date, and so retrieve it on all the scheduled discounts and advertisements.

Let’s execute the default stored procedure to load the advertisements caching system “mktg_spRuntimeLoadAdvertisements”:

Only the advertisements with the start and end date in the time frame of the 8th of July came up. For my post, I’ve created a new ad only visible between the 1st and the 31st of August:

If I execute again the stored procedure “mktg_spRuntimeLoadAdvertisements”, I got the exactly same result. Surprised? No! As my ad is not eligible. The start date is on the 1st of August and we are the 8th of July 2010:

Now, copy and paste the “mktg_spRuntimeLoadAdvertisements” stored procedure code and create a new one call “spGael_staging_mktg_spRuntimeLoadAdvertisements”. Just change the start and end date by retrieving the days number between the preview date and now.

You will note that the preview date is stored in a table called “altima_doPreview”. Like that, I can have a simple web part to change the preview date on the fly (don’t forget to refresh your cache).

If we now execute the stored procedure, we got just one result, because the preview date is on the 15th of August and only my ad is eligible:

Note that in my result, the start and end date have switched to 37 days back:

• Start date: 1st August 2010 – 37 days => 25 June 2010
• End date:, 31 August 2010 – 37 days => 25 July 2010

So, now time to run our preview web application (don’t forget to update the web.config) and miracle… my new ad is now displayed

Start the default behavior web application and note that my ad is not displayed

To finish, I give you here the code for the discounts stored procedure:

Hope this help!

Want to know more about Commerce Server 2009? How Commerce Server looks like? What can it bring to your e-Commerce strategy? Meet Matthieu and I at Wygday 2010 for a dedicated Commerce Server session.

So, what’s the Wygday?

WygDay 2010 : The Lille’s edition of the Microsoft Days

The wygday is a day organized by Wygwam and Microsoft. The Wydgay is fully dedicated to the new technologies. You’ll can meet several experts from Wygwam or Microsoft who will show you an overview on the new key features and feedbacks from their own experience. That’s the best moment to talk with them!

This year, we will land three different axes: Visual Studio 2010 development, Architecture/Industrialization, SharePoint 2010, Office 2010, Exchange 2010, Cloud Computing, Bing Maps, Research et Innovation. For more information on the sessions:
http://wygday.wygwam.com/Sessions.aspx

Come to meet us by registering on the  WygDay 2010 website.

Rendez-vous on the 8th of June 2010 at EuraTechnologies
165 avenue de Bretagne 59000 Lille
Métro Canteleu (Direction St Philibert – Ligne 2)
Free secure car park available

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 »

The Altima’s Commerce Server team is more than happy to celebrate the “go live” for the new web site of APART – Fashion. This mark up clothing range for women offers stylish and original collections in distance selling exclusively! Ladies, to your blue card!

APART-Fashion is also on FaceBook: click here to join them!

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

I was contacted by Cyril to help him in a Commerce Server development/optimization and he was kind enough to let me post this mini “case study”.

So, I was there with my fellow colleague Anoir to leverage and bring our expertise around Commerce Server and asp.net for their existing Mathon’s ecommerce web site. Indeed, when the traffic was high, the performance on the web site was so poor that it was not possible to access to one single page. Our intervention was about .Net optimization (caching, good usage of asp.net control, AJAX, …) and apply the best practices for Commerce Server like CatalogItemConfiguration.

The site is now running like a charm and ready to take your orders .

I recommend as well to visit the Cyril’s blog who is a real Microsoft geek…like me

The Commerce Server team has released a new CTP for Commerce Server 2009 R2 and some good stuff was done by the Commerce Server team. So, you understand that my first impressions are very positive

First, you can say good bye to your old pals 32 bits processor, SQL Server 2000, 2005 and windows 2000, 2003, ‘cause now Commerce Server 2009 R2 just running on 64 bits processor, SQL 2008 (R2 or not) and Windows 2008 (R2 or not).

Let’s start with the installation process which bring you more flexibility in the components you want to install:

• The usual one:
  • Direct mailer
  • Staging => for enterprise edition only
  • Health Monitoring Service
  • Volume Shadow Copy Writer Service
  • Commerce Server (rename in Commerce Foundation Components)
  • Biztalk Adapters
• The handy one:
  • Data Warehouse and Reporting (don’t need now to add a ressources, the features can come up with the installation ) => for enterprise edition only
  • The documentation
  • Desktop Business Management Tools
• For the developers
  • Extensibility Kits
• Site out of the box aka Store Front
  • SharePoint template site
  • Web Parts
  • Web Business Development

The Commerce Server configuration has also been improved to drive you through the features configuration. Two things to say here:

• For the moment, you can only choose the basic configuration. The advanced one will be available in the next CTP.
• There is a bug, if you want to configure Commerce Server on a remote SQL box. Only the local one is available and like for the configuration, it will come with the next CTP. The workaround is quite easy, you just have to run the configuration tool with a command line and give as a parameter an XML file location path. This XML describes the features configuration:.

Read the rest of this entry »

These last few weeks, I have been involved with several Commerce Server reviews, and I have noticed that a lot of CS implementations was done without 2 important settings, which are:

• Microsoft.CommerceServer.Catalog.ProductConfiguration
• Microsoft.CommerceServer.Catalog.CategoryConfiguration

These two classes inherit from Microsoft.CommerceServer.Catalog.CatalogItemConfiguration, and they are used to load only the objects you use. Why? Because when you want to display the product price or its description for example, you do not need to get its ancestors, variants, related products so you can have a simple and lighter Product or Category…

Besides, if you have a look into the API via Reflector, you will see that the CategoryConfiguration or ProductConfiguration class is always instantiate if you’re not setting up one (ie, the CS framework choose for you the objects to populate):

To demonstrate to importance of those classes, let’s go through a simple exemple of product implementation where you only need to show the productID, the price and the display name:

• Without a ProductConfiguration, we use:

Product product = catalogCtx.GetProduct("Adventure Works Catalog", "AW029-03");
Console.WriteLine("Pid: " + product.ProductId
                + " - " + product.DisplayName
                + " - " + product.ListPrice);

With the SQL Profil we are able to get the SQL calls and discover that 2 stored procedures are call:

Read the rest of this entry »