Best Practices for Catalogs

The following best practices are for the site developer, system administrator, and business manager to implement.

Developing with Catalog Components

Designing a Catalog

Assigning Product Identifiers and Variant Identifiers

Importing a Catalog

Deploying Catalogs

Developing with Catalog Components

The following best practices are for the site developer to implement:

• When you use as a join key the product ID, variant ID, or another indexed column in the CatalogProducts table, it is recommended that you index the join key column in the external joined table.

  For example, if you use the GetProductProperties API (in which the product ID is the join key), you get better performance when the join key is indexed in the external table. Performance is similar to that of not using a joined table.

  In this scenario, when the product ID is not an index in the external table, the performance is slower. For more information about join keys, see Code to Join an External Table to Product Recordsets.

• Use join keys to link to external tables to retrieve inventory data at run time, and to associate multiple values with a product.

  Two common scenarios for using join keys are:

  • Retrieving volatile catalog data at run time, such as inventory data. You should no include inventory data in the catalog itself because the site never writes to the catalog database. In addition, by treating the catalog database as read-only, you can use Network Load Balancing of the catalog database so you can scale your site.
  • Associating multiple values with a product, such as a list of song titles for a CD, a cast list for a movie title, or a list of customer reviews for a product.

Note

• If you use a join key to get real time external data such as inventory, you must also update the inventory and cached data for the updated product/variant. Ask your system administrator for assistance.

  For more information about join keys, see Code to Join an External Table to Product Recordsets, CatalogManager.SetJoinTable Method, and CatalogContext.SetJoinTable Method (BCL).

• When developing a .NET-based site, implement tracing by using CatalogContext.DebugContext.

  For information about using DebugContext, see CatalogContext.DebugContext.

Designing a Catalog

The following best practices are for the site developer and business manager to implement:

• When designing a catalog for future growth, keep in mind the following scale points:

  • Multiple product catalogs can share a single SQL Server full-text catalog.
  • Commerce Server supports at least 10,000 catalogs: 10,000 base catalogs, 10,000 virtual catalogs, or a combination of both base and virtual catalogs.
  • A catalog supports at least five million products. (Commerce Server has not been tested with 10,000 catalogs each containing five million products.)
  • A catalog can support up to 1,000 property definitions.
  • A virtual catalog can include content from up to 80 base catalogs; it cannot include content from other virtual catalogs.
  • A virtual catalog can include up to one million products.
  • A virtual catalog can support up to 10,000 price rules.
  • A category can support up to 10,000 products.
  • Categories can be nested five layers deep.
  • Commerce Server supports the SQL text data type, which is not subject to the SQL Server row size limit of 8,060 bytes.

• Use unique names for each catalog, category, and product. Verify that all category and product name combinations are unique.

  If you create a catalog for a site, and you have non-unique product names within categories, the wrong product may be added to the shopping basket when the user selects a product for purchase.

  For example, product 1A in category B and product A in category B1 are both stored as "B1A."

• After you create a product, you cannot assign a different product definition to it.

• You cannot change a data type of a property after the property is created.

  For example, assume you create the following:

  • ProductDefinition1, and it contains Property1 and Property2
  • ProductDefinition2, and it contains Property2 and Property3

  If you create a product based on ProductDefinition1, at a later date you cannot change the product to be based on ProductDefinition2. You can, however, change ProductDefinition1, for example, by adding Property4.

  You cannot assign a new product definition, or change a data type, after it is in use because other products or properties might be affected by the change.

• Verify that a property name does not have mismatched parentheses in its name.

  For example:

  • Correct: shoe size (mens)
  • Incorrect: shoe size mens)

  Commerce Server does not verify the parentheses for you. You can create a property name that is missing a parenthesis, but you will not be able to retrieve it from the Commerce Server database.

• To display a single catalog that contains multiple currencies, create a virtual catalog for each currency.

  Use the currency properties; do not use multilingual properties for multiple currencies.

  You can use one of the following approaches:

  • Create a virtual catalog for each country/region, and then use price rules to define the price of each product for that country/region.
  • Create a virtual catalog for each country/region, use price rules to apply a currency conversion rate (percentage) to all products.
  • Create a currency property for each currency that you support, for example, cy_list_price_USD, cy_list_price_CDN, and cy_list_price_JPY. Write code that queries the catalog to select the appropriate currency property for the current country/region.

  Base pricing is applied to products in a virtual catalog unless otherwise specified using price rules.

  (Language-specific values cannot be applied automatically to other languages. If you need this functionality, you can create your own runtime code to retrieve the default value if the returned value is empty. Or, you can write code to populate empty values in catalogs with the default values.)

• Use the "Display in products" attribute of a property definition to determine whether a property is displayed in the products list in Commerce Server Business Desk.

  The Display in products attribute does not affect your Web site.

• Use the built-in property "OriginalPrice" to view the first price assigned to a product.

  OriginalPrice is set when the price of the product is initialized, and it is never changed. Commerce Server does not maintain any history of the price of a product.

  For more information, see****Using Current Product Prices vs Original Product Prices.

• Add your own user-defined attributes at the catalog level and/or property level, as needed.

  Business Desk includes catalog attributes, such as currency, unit of weight measure, locale, and catalog name. It includes property attributes, such as minimum length/value, maximum length/value, default value, and display name. You might want to add your own catalog attributes, for example, to store information about the vendor that supplies the products in a catalog. You might want to add your own property attributes, for example, to describe how a particular property should be rendered on the Web pages (for example, drop-down list, button, or label). You can also add a new unit of weight measure (for example, kilograms (kg)).

  To add user-defined catalog attributes and/or property attributes, your site developer must first add the attributes to Commerce Server using the Catalog API. After that is done, you can import an XML catalog file that includes the new catalog attributes, and then use the Catalog Definition Designer module to set values for the attributes.

• Extract data from a catalog for use in a printed catalog.

  • You can use the CatalogManager.Export CSV in your custom application to export the Catalog into a comma-separated file. You can then use Microsoft Excel to open the file, or print the file as is.
  • You can Export the catalog as an XML file and then write an XSLT template to transform the XML file into a printable version. For more information on XSLT and its usage, see: https://msdn.microsoft.com/xml.
  • You can also extract data for use in a printed catalog by using a custom application that extracts the Web pages from the server. Using the Commerce Server objects, you can enumerate all products and information by using COM+ interfacing.
  • You can also use publishing software that has an XML interface designed for this exact purpose. When you import XML files, the publishing software produces glossy brochures.

Assigning Product Identifiers and Variant Identifiers

For base catalogs, you must assign product identifiers and variant identifiers. In a virtual catalog the product ID will always be "ProductID" and the variant ID will always be "VariantID." When creating a virtual catalog, you do not have to specify the product ID and variant ID.

The following best practices are for the site developer and business manager to implement:

• In each catalog, use the same property for the unique ID.

  In Business Desk, when you list the products in a catalog, Commerce Server creates one column for every unique ID property. If you include 20 catalogs in a virtual catalog, for example, and each catalog uses a different property for the unique ID, then Commerce Server displays 20 unique ID columns. If each catalog uses the same property for the unique ID, then only one column is displayed.

• When assigning a product ID to a product, use either an integer or text.

  Do not use a date, time, or currency.

• Do not add a product variant property to any properties that are being used as a Product Identifier property in a catalog.

  For example, if you use ModelNumber as the identifier for products in your catalog, do not create a product based on a product definition in which ModelNumber appears as a product variant property. If you do, you will not be able to save variant information based on the product definition in that catalog.

• Do not use the same value for product unique identifiers and variant unique identifiers.

  If a variant property of a product is the product unique identifier for the catalog, you cannot save the product. For example, you cannot assign stockkeeping unit (SKU) as the product unique identifier and the product variant unique identifier.

Importing a Catalog

The following best practices are for the business manager to implement:

• Before you import an XML catalog file using Business Desk, select the Validate XML file option on the Import Catalog dialog box to ensure that all data in your XML file is valid.

  Business Desk will display up to 20 errors found in an XML catalog file import. Do not import the XML catalog file until it is validated without errors.

• Your system administrator must start the full-text indexing service (Microsoft Search service) before you can import a catalog into the catalog database.

  When a catalog is imported into the Commerce Server catalog database, the full-text index is created at the same time.

• If you experience poor performance when importing 100,000 to 1 million products into a base catalog, or publishing a virtual catalog of a similar size, your site developer should use two SQL Server Database Console statements (DBCC) to improve performance.

  Use SQL Server Query Analyzer to run DBCC FREEPROCCACHE and sp_updatestats. These commands improve the performance of Catalog APIs, such as GetProduct and GetProductProperties:

  • DBCC FREEPROCCACHE removes all elements from the procedure cache.

    Syntax: DBCC FREEPROCCACHE

  • sp_updatestats runs UPDATE STATISTICS against all user-defined tables in the current database.

    Syntax: sp_updatestats [[@resample =] 'resample']

  For more information about these commands, see SQL Server Books Online.

• To import a catalog on a regularly scheduled basis, for example, to import catalogs from trading partners, ask your developer to write the appropriate code.

  For information about using the CatalogManager API to write the code for importing a catalog, see CatalogManager Object.

Deploying Catalogs

The following best practices are for the system administrator to implement:

• If your site uses relative URLs to images in catalog file name properties, for those images to be displayed in Catalog Editor, create a virtual directory for the graphics.

  You must create a virtual directory under the BizDesk virtual directory, and map the URL to the directory containing the images.

  To create a virtual directory

  1. Click Start, point to Programs, point to Administrative Tools, and click Internet Services Manager.

  2. In the Internet Information Services screen, expand <server name>, and expand <your Web site>.

  3. Right-click your Business Desk application (for example, retailbizdesk), click New, and then select Virtual Directory.

     The Virtual Directory Creation Wizard starts.

  4. In the Welcome to the Virtual Directory Creation Wizard dialog box, click Next.

  5. In the Virtual Directory Alias dialog box, in the Alias box, type the name of the subdirectory where you store images for your site, and then click Next.

  6. In the Web Site Content Directory dialog box, type the appropriate path in the Directory field (default is C:\inetpub\wwwroot\images), and then click Next.

  7. In the Access Permissions screen, clear the Run scripts (such as ASP) check box, and then click Next.

  8. In the Completing the Virtual Directory Creation Wizard dialog box, click Finish.

• Set up the ProductCatalogs folder under the BizDesk folder on the server as a share.

  When importing or exporting a specifying a file on the server side, if users only specify the file name, the file will be imported from or exported to the ProductCatalogs folder under the BizDesk folder on the server. For example, exporting a file as output.xml will export it on the server as d:\inetpub\wwwroot\retailbizdesk\productcatalogs\output.xml. You can set up this folder as a share.

• Enable the SQL Server change-tracking featureto automatically update the full-text indexes of your catalog tables.

  Using the change-tracking feature, you can update the full-text index with changes immediately, on a schedule, or as they occur, using the background update index option. However, when SQL Server periodically updates the full-text indexes, it will impact the performance of your site.

  By default, the change-tracking feature is disabled on the Commerce Server catalog tables. This means you must update your full-text indexes manually (using Business Desk) every time data in the catalog is changed, but at a time that you choose.

  For information about enabling change tracking, see SQL Server Books Online.

• If you create a .pup package that contains a materialized virtual catalog, after you unpack the virtual catalog, you must materialize the virtual catalog again.

  When Commerce Server unpacks a materialized virtual catalog, it uses the views instead of the materialized tables. For information about using the MaterializeVC.vbs script, see Materializing Virtual Catalogs.

• Use the CatalogCleanup.vbs script to delete temporary tables from the MSCS_CatalogScratch database.

  To improve run-time performance, the Product Catalog System uses the MSCS_CatalogScratch database to store temporary catalog tables. These temporary tables are empty when not being used. It is recommended that you run the CatalogCleanup.vbs script on a weekly basis to remove the tables. If you do not run the script, a few temporary tables will remain in the MSCS_CatalogScratch database.

  The CatalogCleanup.vbs script is located in the \Program Files\Microsoft Commerce Server 2002\Support folder. To run CatalogCleanup.vbs, provide the connection string to the MSCS_CatalogScratch database.

• Rebuild a virtual catalog so products that are included from the base catalogs are available to set price rules.

  You must rebuild a virtual catalog in the following scenarios:

  • If a virtual catalog is materialized, you must rebuild the virtual catalog when any data changes in the associated base catalogs.
  • If the virtual catalog is not materialized, you must rebuild the virtual catalog whenever the data in the associated base catalogs changes (for example, pricing changes, and hierarchy and relationship changes).
  • You must rebuild a virtual catalog whenever you change include/exclude rules or pricing rules.

• Do not rebuild a virtual catalog on a production server. Users will not be able to access the catalog.

  Only rebuild virtual catalogs on your staging server. After the catalogs are materialized, you can replicate them to you production servers without impacting user transactions.

• Do not delete products from the catalog when the full-text indexing service (Microsoft Search service) is stopped.

  The Microsoft Search service creates full-text indexes on the content and properties of the catalogs, allowing fast linguistic searches on this data. If the Microsoft Search service is stopped, and then you delete data from the catalogs, the catalogs will become corrupt. Your system administrator will notify you when downtime is scheduled for the Microsoft Search service.

• Use the Updatefulltextcatalogs.vbs script to update the full-text indexes on all product catalogs in one full-text catalog.

  When you have multiple catalogs in one full-text catalog, you can use the Updatefulltextcatalog.vbs script to update the full-text indexes of all the catalogs at the same time. It is more efficient to use Updatefulltextcatalog.vbs script than to use the ProductCatalog.RegenerateFullTextIndex object, which can consume a lot of resources on the SQL Server.

  The Updatefulltextcatalog.vbs script is located in the \Program Files\Microsoft Commerce Server 2002\Support folder. To run Updatefulltextcatalog.vbs, pass it the connection string to the catalog database and two other parameters. You must run Updatefulltextcatalog.vbs on the same computer as SQL Server. For instructions, see Running the Updatefulltextcatalog Script.

• Use the SP_FULLTEXT_SERVICE command to set the resource usage for the full-text service (Microsoft Search service).

  The following command sets the resource usage to 3 (the default):

  EXEC sp_fulltext_service 'resource_usage', '3'
  

  If you set the resource usage to 5, the full-text index population is faster, but the service will use 100 percent of the CPU.

• Use UpdateLanguageInfo.vbs to populate the CatalogLanguageMap table.Configure the word-breaker for each column that is full-text indexed in the neutral language table.

  Given a full-text search phrase such as “This is a new book,” a word-breaker parses the phrase into individual words. The behavior of a word-breaker depends on the language for which it is configured. For example, to support searches of an English catalog, you would specify the word-breaker 1033, where 1033 is the locale identifier for English.

  You might want to configure the word-breaker for the language of a catalog. For example, if you have a Japanese-only catalog, then all text properties would hold Japanese text, regardless of whether the properties were defined as multilingual. In this case, the default word-breaker is not appropriate; configure the word-breaker for Japanese.

  To specify the word-breaker for a catalog, you can manually add a row for the locale in the CatalogLanguageMap table (for example, "en-us", 1033). Or, you can populate the CatalogLanguageMap table using UpdateLanguageInfo.vbs, which is located in the \Program Files\Microsoft Commerce Server 2002\Support folder.

  To configure the word-breaker for the neutral language table, <CatalogName>_CatalogProducts, use a row in the CatalogLanguageMap table {"neutral", <locale identifier>}. When you change the neutral language word-breaker, it affects only catalogs that are created afterward. This change will affect all catalogs, both pre-existing catalogs and new catalogs.

Copyright © 2005 Microsoft Corporation.
All rights reserved.