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:
- Recommended way: Use the XML data feeds we provide via BizTalk Server 2006 adapters (we provide with CS2007: catalog, inventory, orders, and profile adapters)
- In the case where you don't wish/have BizTalk Server 2006 you can get the XML data feed from our web services
- 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.