commerce server 2007
December 22, 2010 | 
Author For my current project, I have extended LineItem table to hold few values. These fields are of type NVARCHAR and holds 3 to 4 English words. Our project DBA want to create these fields in VARCHAR as these fields are not multi-lingual (so that DB space can be saved). As per the text from OrderObjectMapping.xml file, VARCHAR is not allowed
“* Table columns defined in this mapping are restricted in their uses of string types. Specifically, NTEXT, NVARCHAR, and NCHAR SQL types must be used, instead of TEXT, VARCHAR, or CHAR.“
The same has been mentioned in MSDN.
http://msdn.microsoft.com/en-us/library/aa545934(v=CS.70).aspx
I tried to change the data type but I end up getting error while generating the scripts using OrderMapping.exe file.
| C:\Program Files (x86)\Microsoft Commerce Server 2007\Tools>OrderMapping.exe /w web.config -i
Microsoft (R) Commerce Server 2007 Order Mapping Tool Copyright (C) Microsoft Corporation Information M0001 – The output file OrdersStorage.sql will be overwritten One or more errors found during validation: Error G0032 – Column LineItems.DataCenter type varchar is not an allowed relational type |
I manipulated generated OrderStorage.sql file and changed the property from NVARCHAR to VARCHAR but I have few issues while saving the order. Since Commerce server has to support multiple languages, I fee, it might be forcing users to go with N<Type>(Unicode, 2 bytes for every character) for string data types. Not sure if there is any other answer to this feature/limitation.
If you like this post, please click on our sponsor advertisement.
Posted in 
.In last two posts, we have seen how to add data warehouse resource to commerce server site and created DTS package using data warehouse wizard. If you haven’t go through the articles, please follow below urls.
http://microsoftblog.co.in/commerceserver/data-warehouse-import-wizard/
In above two steps, data warehouse reports are not installed. In this article, we will configure and install the reports for SQL Reporting services. The report installer command line tool provides the ability to install/deploy the analytical reports for commerce server. Follow below steps to install data warehouse reports.
- click Start => All Programs => Microsoft Commerce Server 2009 => Tools => Commerce Server tools command prompt.
- Type “ReportInstaller.exe” and press enter.
- Reporter installer screen appears. Make sure, data warehouse and reporting server details are specified.
- To test, press “Test Connection” button.
- If connection to reporting server is successful then click on “Install Reports” button. Once reports gets successfully installed, you will get a dialog box (which shows log file location – See C:\ReportInstaller.log for details.). Click OK button.
- Now if you open the url (http://localhost/reports) in internet browser. From commerce server report manager => Click Home => Commerce server Analytics => EN. You can run any available analytical reports.
To know more about these report, refer to below url.
http://microsoftblog.co.in/commerceserver/tasks-to-view-commerce-server-reports/
If you like this post, please click on our sponsor advertisement.
After adding data warehouse resource to commerce server site, we must create the DTS tasks that extract the data, transform and load into data warehouse. Using data warehouse import wizard we can accomplish this. To define a DTS package follow below steps.
- Click on Start => All Programs => Microsoft commerce server 2009 => Tools => Data warehouse import wizard. A dialog box appears. Click on next button.
- Select commerce server site from the dropdown and check the type of imports you would like have and click on Next button.
Web server logs => It consists of data logged from IIS (c:\windows\system32\logFiles). You can find IIS logs in world wide web consortium (W3C) extended log format. This data includes duration of the visit, referring sites, clicks etc.
Resolve IP address => IP addresses are converted to domain names.
Product catalog => Includes dimensional data like product name, size, color, etc.
Transactions => It includes purchase orders and requisition numbers and useful in determining how well products are selling.
Profiles => It includes user information such as user name, visits, etc
Campaigns => includes marketing campaign information.
Note: If you select commerce server site which doesn’t have data warehouse resource installed then you may get this error.
”Failed to connect to the datawarehouse for this site. You must unpup the datawarehouse resource before running the import wizard for this site or choose a different site.” - On mode of import screen, select whether you want to import full data or import data incrementally. After selection, click Next button.
- On log file location screen, you must specify the location of the web server logs(c:\windows\system32\logFiles). Click next button.
- On File selection screen, select the log files that you want to import. Click next button.
- On Import executive options screen. You have option save the configuration or run the import process immediately. Click Next & Finish button to continue.
- If you have selected “Run the import immediately” option in last step, you can see DTS package execution dialog window.
Note: if you didn’t select log files in step no. 5, you may get error while performing web server log import. To overcome this error, you have to unselect “web server logs” option in step no. 2
Once installed you can able to see DTS package in SQL Server. Follow below steps to see
- Click start => All Programs => Microsoft SQL Server 2005 => SQL Server management Studio
- In SQL server management studio, expand management => Legacy => Data Transformation Services.
- Right click the package you wish to run and click open. You may get error “SQL Server 2000 DTS Designer components are required to edit DTS packages. Install the special Web download, "SQL Server 2000 DTS Designer Components" to use this feature.” if you don’t have SQL Server 2000 DTS Designer components installed.
- Click the execute button to execute the DTS package.
If you like this post, please click on our sponsor advertisement.
Installing commerce server site will bring all 5 core systems (catalog, inventory, profile, marketing & orders) but you will not get the sixth core system (i.e., data warehouse). To install data warehouse, we must explicitly add the resource by installing data warehouse package. In this example, I will be adding data warehouse resource to existing starter site.
- Open commerce server manager, right click on the site (in my case, its starter site) and click on New –> Add resource.
- A dialog box appears. Select DW.PUP file and press OK button. Note: You can find this file under %commerce_server_root%\pup packages.
- In the select resource screen, select “Data warehouse” resource and click on add button. Then, Click Next button.
- In global resource pointer screen, you can either create new global resource or attach to an existing global resource. This feature is similar to profile resource while creating commerce server site. For now, we will select “Add new global resource”. Click Next button.
- Now we are in “Database connection” screen. In this screen, you can change either database name or database server name. click Next button.
- In this screen, we an provide data warehouse resource’s analysis server properties. Once done, click on OK button
- That’s all.. now commerce server will install the resource to your site. At the end, you will receive a warning message The dialog screen displays the list of operations it has completed. That means everything is successful.
- Once the unpacking is done, you can able to see the newly added resource under commerce server site in commerce server manager.
Is this enough to see commerce server reports? The answer is NO. You need to perform DTS import package with Data warehouse import wizard and finally install & run analytical reports. We will discuss more in coming articles.
If you like this post, please click on our sponsor advertisement.
In commerce server, a basket is created either when the user logs in to the application or when the user tries to add first product to the cart. In reality, in the background we are creating an entry about user basket in transaction database – OrderTemplatesAndBaskets table. All information related to basket such as products, quantity, addresses, etc are stored in a column named “marshaled_data” as Binary large object (BLOB).
Note: You can’t read this data from select query rather you have to use customer and order manager tool to view this binary information.
All baskets will not become purchase orders. So, in an enterprise application with more than a million customers, you can see many unnecessary baskets created in the system. It is good practice to remove unnecessary baskets periodically. We have few ways to do it.
1. Simple and Ugly way: Delete unwanted data directly from OrderTemplatesAndBaskets table.
|
Delete OrderTemplatesAndBaskets Where LastModified <= DATEADD (d, -30, GetDAte()) |
2. Use PurgeCommerceData.exe tool that comes out of box from commerce server. The syntax is
| PurgeCommerceData <site_name> flag [options] |
For example, the below command purges all baskets that are been unchanged for last 30 days.
| PurgeCommerceData site_name -b -d 30 |
Note: This option only works on the system where commerce server installs
3. Some times, we may have to run this tool from remote system (not on the production server directly) and in those situations, we can use commerce server agent mode API to perform this task. The below code snipped can be used to delete the baskets. You can create a service to do this clean up job regularly or create command line utility and schedule to run that utility on specific intervals.
|
string orderWebServiceUrl = "http://server/OrdersWebService/OrdersWebService.asmx"; // Create a search clause. // Delete baskets that match the conditions. |
If you like this post, please click on our sponsor advertisement.
