Commerce Server 2007 Data Warehouse Analytics Physical Schema

I get this question fairly often: "Can you provide us with Commerce Server 2007 physical & logical schema?"

I ask "What is the scenario/need you are trying to address?" and invariably I get something to the effect of "So we can manipulate the data directly for various purposes"

For backup purposes (i.e. IT Admin Operations) we provide a list of databases and tables the systems use. However, we do not provide details beyond that.

The reason is we do not support direct data manipulation of the databases/tables. I have had the personal opportunity to interact with a customer that had done this with their Commerce Server 2002 implementation, resulting in severe data corruption. It was a very sad situation that we were not able to help with.

What about direct reads from the tables? That is not supported either. So what to do?

There are 3 options:

  1. Recommended way: Use the XML data feeds we provide via BizTalk Server 2006 adapters (we provide with CS2007: catalog, inventory, orders, and profile adapters)
  2. In the case where you don't wish/have BizTalk Server 2006 you can get the XML data feed from our web services
  3. Extract the data from CS2007 DWA

The first 2 options are very well documented in our product docs. The last one is also documented but I wasn't able to find the physical schema documentation, so I am attaching those here for your reference. You will have to be a SQL and OLAP expert to understand how to use it, but at least you have something to start with.

Thanks, Caesar.