How-To Guide WPC Data Model Version 0.2
How-To Guide WPC Data Model Version 0.2 Noman Mohammad WebSphere Product Center Solution Management Services TABLE OF CONTENT OVERVIEW ................................................................................................................................... 3 PRODUCT DEFINITION ............................................................................................................. 4 WHAT IS A ‘PRODUCT’? .................................................................................................................... 4 WHAT SHOULD BE MODELED IN WPC? ............................................................................................ 4 FACTORS TO CONSIDER DURING DATA MODELING ......................................................................... 7 CORE .............................................................................................................................................. 9 ITEM IDENTIFIERS ............................................................................................................................. 9 ITEM TYPES .................................................................................................................................... 12 COMMON ATTRIBUTES .................................................................................................................... 12 ITEM STATUS .................................................................................................................................. 14 PRICING AND COST ......................................................................................................................... 16 IMAGE LINKS .................................................................................................................................. 18 EXTENSION ................................................................................................................................ 19 ITEM-TYPE ATTRIBUTES................................................................................................................. 19 ITEM-CATEGORY ATTRIBUTES ....................................................................................................... 20 ITEM-CHARACTERISTIC ATTRIBUTES ............................................................................................. 21 RELATIONSHIPS ....................................................................................................................... 22 MODELING RELATIONSHIPS IN WPC.............................................................................................. 22 ITEM TO ITEM.................................................................................................................................. 23 ITEM TO OTHER ENTITIES ................................................................................................................ 26 LOOKUP VALUES (LOV) ......................................................................................................... 30 OTHER.......................................................................................................................................... 32 EFFECTIVE DATES .......................................................................................................................... 32 SEASONAL ITEM ASSORTMENTS..................................................................................................... 32 DATA MODEL VALIDATIONS ............................................................................................... 33 HIERARCHY MODELING........................................................................................................ 33 DESIGNING AND DEPLOYING THE DATA MODEL......................................................... 33 APPENDIX ................................................................................................................................... 34 LINKS TO OTHER RESOURCES ........................................................................................................ 34 Document Control The change history for this document is listed below. Version 0.1 0.2 Date 12/7/2006 12/21/2006 Change Description Initial version by Noman Mohammad Incorporated feedback from Review Panel This document is an asset of the IBM WPC Solution Management Team. Please do not disseminate or make document changes without permission of the Solution Management team. ([email protected]) WebSphere Product Center Knowledgebase – Data Model How-To Guide 2 Overview Given the flexibility of WebSphere Product Center (WPC), the data modeling aspect is always a challenge. There can be many ways of achieving the same end result. However, the solution delivery timelines as well as product performance can vary drastically if the data model is not designed correctly. Hence, good data modeling is the key to the success of the implementation both in terms of meeting the client’s needs as well as laying a solid foundation for other WPC components to be built upon. This document tries to list out some of the common design questions that we come across during the data modeling exercise, and recommends an approach which has proven to work well in customer engagements. This is a working document and will be updated as we continue to learn more from the field. WebSphere Product Center Knowledgebase – Data Model How-To Guide 3 Product Definition What is a ‘Product’? This is perhaps the single most fundamental question that one should try and get answered during the early stages of the engagement. Depending on the industry vertical and the customer requirements the product can be defined in different ways. ‘Product’ for a Consumer Electronics retailer would include laptops, MP3 players etc. Whereas for a Clothing retailer the ‘Product’ would comprise shirts, pants, caps etc. of varying color and sizes. Moreover if you go into another vertical such as Automotive, the product may be defined as the Part itself e.g. xyz radiator, or from the perspective of the Part application e.g. 1994 Nissan Altima radiator. WPC provides the capability to model all such variants in product definitions leveraging the same platform. Hence, the product modeled within WPC is usually more closer to the way the business understands it, as opposed to being locked down into a rigid model provided by a package solution. However, this flexibility may lead towards a slippery path and one should be clear on what should and should not be modeled within WPC. What should be modeled in WPC? To determine what components should be modeled in WPC, it is important to have a good understanding of the PIM Domain. One should understand the Do’s and Dont’s otherwise theoretically everything can be modeled in WPC given the flexible nature of the platform. The following sections outline the different components encountered in a typical PIM engagement. Product Attributes These are the set of attributes that define a product. These are usually grouped into a set of core and extension attributes. Core attributes are common across all of the enterprise products e.g. UPC. Extension attributes are specific to certain product types or category e.g. screen size. WPC can also support relationship data e.g. cross-sell, up-sell, etc. WPC typically serves as a system of record for referential attributes. Attributes that are transactional or volatile in nature are best handled outside of PIM by the appropriate consuming applications e.g. current price being handled by a pricing engine. Also attributes whose values are derived by business logic in external applications should not be modeled in PIM. Such data may be kept in PIM as read only but then it necessitates an update mechanism to keep the data in sync and may also put unnecessary load and high availability requirements. Hence it is best to leave these types of transactional or volatile types of attributes outside of PIM. The following are some common attribute sets that are encountered in a typical engagement, and are classified as either Core or Extension data, Relationship data, or Not Applicable (N/A) to a WPC application. WebSphere Product Center Knowledgebase – Data Model How-To Guide 4 Function Product Identifiers System of Record PIM Attribute Group Core Item Types PIM Core Common attributes PIM Core Item Status PIM Core Pricing and Cost Pricing Engine N/A Image Links PIM Core Item-Type Attributes PIM Extension Item- Category Specific Attributes PIM Extension Product Bundles PIM Relationship Product Variants PIM Relationship Static Cross-Sell & UpSell Dynamic Cross-Sell & Up-Sell PIM Relationship Personalization Engine/CMS N/A Comments Uniquely identifies a product e.g. UPC, GTIN, SKU. Logical grouping of items to share common traits regardless of where it gets categorized. Usually one or two attributes: item type, item sub-type. Description, Dimension UOM, etc Tracking an item’s lifecycle within PIM Less volatile data such as initial price and initial cost can be maintained in PIM Links to images. Actual images should be stored in an external image repository Attributes specific to a product type e.g. screen size. This is part of product definition. Should not be used for maintaining external attributes specific to upstream/downstream applications Attributes specific to a product based on the category it is classified in Part of product definition. Bundles usually modeled as separate products Variants such as size, color etc are part of product definition. The variant product may be modeled as a separate product than the base and linked together Products that are always sold together Data depends on user activity and business analytics WebSphere Product Center Knowledgebase – Data Model How-To Guide 5 Accessories PIM Relationship Substitute/Replacement PIM Relationship Packaging PIM Relationship Promotions Pricing/Promoti ons Engine PIM N/A Effective Dating / Seasonality Other Products that are always sold together The relationship is stored in PIM. Action is taken by Replenishment system based on inventory levels. Attributes around packaging such as pack size, case dimensions etc. Provides input to downstream systems such as Ordering/Fulfillment Volatile data. Owned by the Pricing/Promotions Engine In roadmap for future WPC release. Simple scenario such as Seasonal catalogs can be supported. Comprehensive framework for adding date effectivity to attributes such as pricing, description etc. for items in same catalog is not supported currently. Product Classifications Defines how the products are grouped together, whether serving a specific business purpose such as organizational structure or for ease of navigation. There can be multiple ways in which a Product can be categorized. Function Primary Product Classifications Secondary Product Classifications System of Record PIM Comments Core Product Classification PIM Sales Channel Hierarchy Sales Channel System Packaging Hierarchy PIM Only for enterprise-wide secondary classifications. Consuming application specific hierarchies should be outside of PIM In an enterprise-wide PIM solution, Sales channel dependent hierarchies such as Print Catalog and Online should be maintained outside preferably within their own system. There is an exception for cases in which WPC is sold as the PIM solution specific to the Sales channel e.g. PIM for eCommerce Part of item definition WebSphere Product Center Knowledgebase – Data Model How-To Guide 6 Foundation Data Foundation data encompasses any supporting entities and attribute values that are needed for defining a product e.g. list of suppliers, locations, product brands etc. Entity Vendors System of Record Financial system / CDI Customers Financial system / CDI Locations Images Merchandising/ERP system Imaging application Product Type / Sub Type PIM Packaging Types PIM Product specific attribute values PIM Comments Better positioned to model rich vendor data and its application Better positioned to model rich customer data and its application Embedded location-based functionality and mass updates Image generation, storage and types List of possible values to select from e.g. simple, complex, bundle etc List of possible values to select from e.g. Each, Case etc List of possible values for various product attributes such as unit of measures (in, pound etc) Factors to Consider during Data Modeling During data model design it is also important to take into consideration whether the approach will enforce any constraints on the native WPC functionality or result in poor performance of the platform as a whole. Usually we have seen it boiling down to the following key components: User Interface, Workflows, Search. User Interface WPC provides out-of-box item and hierarchy screens. The UI is auto-generated based on the metadata. This is a combination of WPC objects such as attribute collections, views etc along with an application of user roles and security. When a user clicks on a particular item or hierarchy for viewing, there is a lot that goes in the background to generate the screen. One would not want to replicate this kind of functionality. Hence, the goal during implementation is to leverage as much of the native UI as possible. Any customizations should be seen more as enablers to business processes such as for dashboard-like monitoring capability. To achieve this one should keep in mind how the data model is being designed. If we have items spread across two catalogs e.g. core data and detail item data, then that itself would warrant a custom UI for editing and another for viewing. Instead we could try and WebSphere Product Center Knowledgebase – Data Model How-To Guide 7 model it such that both are modeled as attribute groupings on the same item spec. The multi-edit feature should also be tested against the data model design. Based on the data modeling certain attributes may not be available for multi-edit and if those are important for the customer from a multi-edit perspective it may require rethinking the design or else one will have to create a custom UI. Therefore, to minimize User Interface customization, it is important to understand and validate that the data model design will support native capabilities around: Single item edit use cases Multi-item edit use cases Workflow One can create an end-to-end business process with step-based views and user roles using the WPC workflow framework. There are two aspects to the framework. One is the actual layout of the steps and user navigation. The second is the UI for each step view. Both of these may demand customization requirements based on the data model design. The user navigation is driven from the Collaboration Area which is tied to a single Catalog. The UI is auto-generated based on the metadata as defined in the section above. Therefore at the time of data model design it is recommended to test a prototype of the typical business processes, or do a conceptual dry run, to see if the data model design itself is constraining the use of native workflows by any way. Search Searching for items based on their attributes is one of the most common use cases in WPC. The users can search for the items from the left navigation, using primary key / display name, or they can use Advanced search for searching on particular attributes. The data model design has a direct impact on the search functionality and hence it is very important to take this into consideration. Let’s say one models enterprise items such that different item types are in separate catalogs with their own attribute spec. In this case to find a particular GTIN/UPC the user will have to go to each individual catalog and search for them, resulting in a tedious process. This would force one towards a custom search tool spanning multiple catalogs. Instead if one were to model it in such a way that all item types are in the same catalog but instead have an attribute ‘item type’ to differentiate them along with a secondary spec representing item type specific attributes, then the native search capabilities can be leveraged. Therefore, when considering different approaches to data modeling, it is necessary to have an understanding of how end users will be searching for product data, and ensure that the data model can support this through minimal to no UI customization. WebSphere Product Center Knowledgebase – Data Model How-To Guide 8 Core Item Identifiers Item identifiers are used to uniquely distinguish an item. These could be based on industry standards such as GTIN, UPC or enterprise specific such as SKU, Corporate ID etc. Usually enterprises may have one or more such identifiers per item. The best way is to identify a Primary Identifier and then group the rest into Alternate Identifiers. Primary Identifier Ideally there should be one Corporate Item Identifier across the enterprise to facilitate data exchange and avoid duplicate resolutions. This strategy should be discussed with the customer upfront as it may require coming up with a uniform identifier across all product lines. It may also have some implications to legacy systems. An identifier can be modeled as a single attribute or a combination of attributes. However, WPC requires that as part of item definition there should be a single attribute that is assigned as the Primary Key. This is best modeled as a sequence attribute. WPC enforces the uniqueness of the Primary Key and keeps it indexed. Following are different options of storing the sequence number. The first option is recommended for simple sequence IDs. Option Sequence Pros Generated automatically Atomic operation ensures no concurrency issues Lookup Table Attribute Value String key requiring sequence manipulation Tighter control on IDs being issued Easier to reset value Cons If new item is discarded without saving still ‘burns’ the sequence Two items created sequentially may have IDs far apart Harder to reset sequence object. Has to be dropped and recreated. Can cause concurrency issues Requires coding to access and increment lookup value May run into caching issues WebSphere Product Center Knowledgebase – Data Model How-To Guide 9 SEQUENCE - Item Primary Spec SEQUENCE - Item View LOOKUP – Lookup table WebSphere Product Center Knowledgebase – Data Model How-To Guide 10 LOOKUP – Item Primary Spec Alternate Identifiers These are used to search for and identify the products using different values. Usually standards based identifiers such as GTIN, UPC or how end users are used to remembering and referencing items for example supplier-model combination as opposed to a 10 digit sequence. Keep in mind the following while modeling alternate identifiers: Make sure identifier attributes are marked as ‘searchable’. This will cause WPC to create indexes on them for faster searching. If the identifier is a combination of attributes please make sure all of them are marked as ‘searchable’. Enforce integrity constraints. If the identifier is unique across all items, similar to the primary key, then mark it as ‘unique’. Otherwise, or for multi-attribute identifiers, one will have to write custom validations to enforce uniqueness based on business logic. For details on implementing custom validations please refer to the Validation Frameworks document listed in the Appendix. WebSphere Product Center Knowledgebase – Data Model How-To Guide 11 Item Types Typically an enterprise would group their products into subsets either representing different lines of business (e.g. consumer electronics, entertainment, services etc) or varying product types (e.g. pants, shirts, shoes). During requirements gathering one should try and get a good grasp on the complete range of items an enterprise carries regardless of which phase they will be made available in WPC. It may be helpful to have breakout sessions to identify the item types and sub types that they would like to see in WPC. This will help in extracting the attribute set that is common to all items, as well as the set of attributes that are applicable to items of a particular type. The objective of defining item types is to be able to identify an item regardless of where it gets categorized. Sometimes a customer may get confused between item types and item classifications. The difference is clear. Item type is an attribute on the item itself and remains intact no matter where the item is classified and however many hierarchies it gets classified under. For example a ‘shirt’ will always remain a ‘shirt’ whether it appears under ‘Casual Shirts’, ‘Dress Shirts’ or ‘Holiday Deals’. Similarly a ‘Consumer Electronics’ item will always remain so whether it falls under ‘MP3 players’, ‘Phones’ or ‘Special Offers’. The following table depicts the different constructs when identifying attributes for an item: Component Item Type Data Model Implemented as an item attribute Item Sub Type Implemented as an item attribute Common Attributes Implemented as the Primary Spec Implemented via Secondary Specs Item-Type Attributes Purpose Helps identify item regardless of categorization. For e.g. clothing, electronics, toys. Drives the attachment of secondary specs Further identifies an item type. Helps during search on item sub types. For e.g. shirts within clothing, audio within electronics etc. Common for all enterprise items Separate spec for each item type Type of Extension Attribute. For more details refer to section on ‘Extension’ Common attributes These are the attributes that are common to all item types across the enterprise. The common attributes would form the Primary Spec of the Item Master Catalog, and would enable one to model all item types within the same catalog. Typical attributes found in this set include: item identifier, description, item status, supplier, cost and price attributes. WebSphere Product Center Knowledgebase – Data Model How-To Guide 12 Common Attributes – Item Primary Spec Common Attributes – Item View WebSphere Product Center Knowledgebase – Data Model How-To Guide 13 Item Status Typically an item's complete lifecycle is managed within a Product Lifecycle Management (PLM) solution. The item goes through various stages from planning to active to retirement. Being able to identify the current status of an item is core to product definition and should be captured in WPC. Based on the status there are usually a set of functionality such as specific validations tied to the product. Once the Planning & Forecasting teams have decided on the item assortment, the next step is to get the items into WPC. It may also be likely that WPC is used to create the planned items, as dummy items, to feed the Planning & Forecasting system. In this case the items already exist in WPC in ‘Planning’ status, and once selected need to change their status to ‘Active’ and then pass through the appropriate business processes. To capture the various stages of an item a 'Status' attribute should be maintained. From a modeling perspective this is quite simple. A string enumeration or string enumeration rule with values such as: Planning Active Delete Archive The changes to these values are restricted through a business process, either electronic or manual. Usually the status changes are initiated in the PLM system and get tricked down, electronically or manually, into WPC. It is best practice to keep only active items in WPC’s Master Catalog and move the deleted/archived ones into a separate repository. This prevents the item count in the Master Catalog from growing too large, and causing a lot of clutter in the main catalog. Also, it is suggested to store only a select set of attributes for a deleted/archived items, leveraging solutions such as User-Defined Logs (UDL) for managing archives to gain performance. [Please refer to the Archive paper] Item Status Attribute – Item Primary Spec WebSphere Product Center Knowledgebase – Data Model How-To Guide 14 Item Status Attribute – Item View WebSphere Product Center Knowledgebase – Data Model How-To Guide 15 Pricing and Cost Typically a Pricing/Merchandising system should own the management of the Pricing and Cost attributes. However there are certain attributes that may come into the PIM system through external feeds which may not change that frequently but serve as inputs to the Pricing engine. Some examples are Initial Price Initial Cost It is best practice not to store the ‘current price’ of an item in PIM, as it does not provide a lot of value as users can always view it in the Pricing/Merchandising systems. Instead it can lead to a lot of unnecessary overhead to maintain the value to be always in sync increasing the SLA/Performance requirements. However, if the requirement is such that the current/promotion price be visible in PIM then the ‘current price’ attribute can be created as read only and an interface exposed to update these values from the Pricing Engine. No processing of these prices and or promotions happens within WPC. In effect all WPC is doing is to display these prices and export them as needed. The data exchange should happen on a periodic basis and no realtime requirements should be imposed. Also, WPC should not store any history of price and cost changes. Pricing Attributes – Item View Cost Attributes – Item/Supplier View WebSphere Product Center Knowledgebase – Data Model How-To Guide 16 WebSphere Product Center Knowledgebase – Data Model How-To Guide 17 Image Links As we move towards an information rich age, images are becoming integral to item definition. For businesses that operate eCommerce sites, the quality and different types of the product images available become the determining factor of enhancing a user’s experience and helping them make a purchase decision. For other applications such as the PIM system itself, the images serve more of a referential purpose. Different media such as print / web have specific requirements for the usage of images and drive the color, size, resolution and type of image. We are seeing imaging requirements come up more in recent engagements, driven mainly by retailers with an eCommerce or Print Catalog system that consumes data from PIM. Typical business requirements around images include: Rule Definition: size, color, resolution, compression Creation: source image capture (high resolution) Derivative Generation: creation of images through transformation rules on source image Image Association: assigning images to the products Enterprises typically have a Creative/Imaging team that has set business processes for generating images. Once the high resolution shot is taken, an Image Server (such as Adobe Graphics Server) processes this to generate multiple images based on the defined set of business rules. The final images are placed in the image repository and the imaging team is notified of the location of the generated images along with the metadata. Once the images are ready, the Creative team can access the PIM system to perform the image association. For more details please refer to the Image Management document listed in the Appendix. From a data modeling perspective an item would contain a multi-occurrence of images with the following item-image attributes Image Type: swatch, primary, alternate, room view, etc. Image URL: pointer to image location in repository. Preferably thumbnail image so it can display within WPC UI Sample Item View (with Images) WebSphere Product Center Knowledgebase – Data Model How-To Guide 18 Extension Item-Type Attributes These set of attributes are only applicable of items of the same type. For example the dimensions ‘collar size’ and ‘sleeve size’ are attributes specific to shirts whereas ‘waist’ and ‘length’ are specific to pants. There are two ways to model item-type attributes: 1. Use of secondary specs that are attached through a post save script based on the selection of ‘item type’. 2. Creation of a secondary hierarchy with the categories depicting item types. The item-type specific attributes can be attached as item category specs. During item setup one can categorize the item in this hierarchy hence obtaining the additional set of attributes. This also provides a different way to navigate the item assortment in the catalog. Option 2 is recommended as it allows one to model item types and sub types as a hierarchical structure and provides capability to attach item-category specs at the type and sub type level. For attaching other extension attributes such as those based on itemcharacteristics, see following section, Option 1 is recommended as you can attach the secondary spec directly without the need to first define a separate hierarchy. When defining item types one should not get carried away and model every single variation as a different item type. This would increase the development time and result in maintenance issues later. Item-Type Attributes – Secondary Hierarchy WebSphere Product Center Knowledgebase – Data Model How-To Guide 19 Item-Type Attributes – Secondary Spec Item-Type Attributes – Item View Item-Category Attributes These attributes are similar to the concept of item-type specific attributes. They are used to extend an item's attribute set. Instead of being associated to a type, they are tied to an item’s classification. If an item is classified under 'Perishable goods' then perhaps a 'best before date' etc is added. This allows for the dynamic extension of an item's attribute set as opposed to one big Primary spec across all items. The data modeling is similar to Item-Type attributes mentioned above. This is always modeled as Option 2 as the classification hierarchy already exists. Each category within it has its own item-category spec, which gets assigned to the item upon classification. WebSphere Product Center Knowledgebase – Data Model How-To Guide 20 Item-Category Attributes – Secondary Spec Item-Category Attributes – Item View Item-Characteristic Attributes These attributes are similar to the concept of item-type and item-category attributes. They are used to extend an item's attribute set, but are based on a particular characteristic as opposed to an item’s type or classification. For example if an item is marked as 'sellable' then there may be certain attributes such as 'sales channel' etc that need to be appended. This specific section has been included to acknowledge that such data modeling requirements do come about, however it is discouraged as this goes into a very granular form of defining dynamic attributes. Instead it is recommended to assign such attributes to all items and move the requirements as part of data validations. For example if an item is marked as ‘sellable’ then make sure that at least one sales channel has been selected else throw a validation error. Note in this case all items would see the ‘sales channel’ attribute regardless of whether they are sellable or not. WebSphere Product Center Knowledgebase – Data Model How-To Guide 21 Relationships Modeling Relationships in WPC A very common requirement that arises during data modeling is the ability to relate entities within WPC. It may be relating two items together under a certain relationship such as up-sell, or relating an item to its suppliers. There maybe a set of attributes defined on top of a relationship, for example ‘supplier cost’ would be an attribute on the itemsupplier relationship as it signifies the supplier cost of that particular item. In particular the sheer volume that may arise should be taken note of. Let’s take the case of 1 million items where an item can have a max of 20 suppliers. We are now looking at a possibility of 20 million item-supplier records. If another dimension such as locations were added to the mix, say 50 locations per item, the number quickly jumps to 1 billion item-supplier-location records. Item N N N Supplier N N N Location As you can see by the numbers, how one models such relationships can make-or-break the implementation. Such volume is typically seen when doing any relationships that form a 3-way join, item-supplier-location being one of the primary examples. Hence, a lot of thought process should be put into this kind of modeling. Since there is no clear right/wrong approach, one should seek advice from WPC Services, Engineering and Architecture on whether the proposed model will be supported by WPC and if there are any other ways of fulfilling the requirement. 2-way relationships, however, can be modeled in various ways in WPC: Use of relationship attributes Linked Catalogs Hierarchies Multi-Occurring attributes WebSphere Product Center Knowledgebase – Data Model How-To Guide 22 The next few sections will describe each type of 2-way relationship, and the best approach to modeling it. Item to Item Bundle A Bundle is a collection of individual products that can be sold separately, but are sold together for promotional purposes. For example a digital camera ‘Get More’ bundle could include – A digital camera, 1 gig memory stick, and the camera case. All these items could be sold individually, but they can also be sold together for a discount to entice the buyer to purchase three products, instead of just one of the products. A bundle is a product itself. It has its own attributes such as ID and description. In addition to the regular items, it has pointers to the individual products it comprises, which can be seen as an item-to-item relationship. The individual products themselves are items in the catalog and have their own IDs, descriptions etc. Like any other product, a bundle can also be categorized. Further it may have validation rules for combining certain set of items. For details on implementing custom validations please refer to the Validation Frameworks document listed in the Appendix. Any scenario that requires a grouping of products to be represented by a single entity can be considered a bundle. The names may vary across verticals and serve different purposes e.g. promotions, functional groupings etc. In the automotive industry there is the concept of ‘Kits’ which are a group of repair parts to be shipped with an order. These can be viewed as bundles and modeled similarly. From a modeling perspective the Bundle is defined as a product with a multi-occurrence of relationship links to other constituent products within the same catalog. Depending on the type of bundle, homogeneous / heterogeneous, there can be validations on what type of products can be valid constituents. Variants / Differentiators An enterprise may manufacture or sell items that are essentially the same but have some variation such as color and size. For example, a blue iPod and a red iPod are essentially the same product. To reduce the data entry and increase accuracy of the product information, the enterprise usually groups these items into one item family. This allows reporting and analysis at the base item level. This concept is commonly referred to as item variations or differentiators. WebSphere Product Center Knowledgebase – Data Model How-To Guide 23 Base Item iPod Differentiators N Blue iPod Red iPod The data model design for this is a bit involved. It depends on how much data can vary between the base item and the variant, as well as customer requirements around searching and assigning item identifiers. The following table tries to illustrate some modeling options based on a set of key requirements: Item ID: Does the base or variant item require an independent item identifier? For e.g. a SKU for iPod and a separate SKU for Blue iPod. Search: Should the base items / variants both be searchable? Diff Val: Can the variant have a different set of values, other than the distinguishing characteristics, compared to the base item for e.g. separate description, supplier id etc. Modeling key points Same Catalog Separate items Relationship link between base and variant Variants cannot override base item attribute values Same Catalog Base item has multi-occ attributes for depicting variants Separate Catalog Variants in Item Master catalog Relationship link between base and variant Same Catalog Base Items Item ID Search X X X X Variants Item ID X X Search X Diff Val X X X X X X X X X WebSphere Product Center Knowledgebase – Data Model How-To Guide 24 Separate Items Relationship link between base and variant Variants can override base item attribute values Cross-sell / Up-sell Cross-sells and Up-sells are industry specific terminology that provide context to relating products together. A Cross-sell is another product that may be suggested to the buyer other than the current product being purchased. It maybe in the same category as the current product or a completely different category and has no bearing on the price tag but more on the relevance of being sold together. For example, a merchant can suggest a high-resolution color printer when a digital camera is purchased, or a particular type of batteries when a flashlight is purchased. The Up-sell is a similar relationship but tends to focus on products that are more expensive and belong to the same category. The relationship names can vary from industry to industry but the concept is pretty much the same. The requirement is to relate Product A to Product B and define this relationship to be meaningful to the business and be leveraged by other consuming applications. From a PIM perspective these are seen as static relationships, and hence make sense to be part of the item definition. Following is the recommended data modeling approach: Scenario < 10 related items > 10 related items Data Modeling Key Points Multi-occurrence of Item to Item relationship attributes Non-Persistent Attribute (NPA) on relationship to display item description Custom tool to add, edit and view list of related items with their ids, name/description and link to the item Similar relationships can also be constructed dynamically based on user behavior, sales etc. This would typically require a business rules engine to go through logs of history and determine the correct linkage, and would be managed by the Personalization/CMS applications. Accessory An accessory is a product that will complement the current item. For example, a headset can be offered as an accessory with an MP3 player. There could be one or multiple types of headsets that may be supported for the same MP3 player, all of them being termed as accessories. An accessory is often less expensive than the main product. This is another example of an item-to-item relationship, as the accessory product and the main product are both items. It has been mentioned here as it is a common terminology that comes up across different verticals. The data modeling concepts are similar to those WebSphere Product Center Knowledgebase – Data Model How-To Guide 25 described for Cross-sell and Up-sell, where the main item has pointers to the related item (accessory). Substitute / Replacement The concept of Substitutes and Replacements is widely used across different verticals such as Retail, Automotive and Manufacturing. The terminology may change based on industry standard or business definitions. For example in the Automotive industry the term ‘Interchange’ is used for substitute parts, and ‘Supercession’ is used for replacement parts. A substitute is a product that is comparable and similar in functionality to the current item. They can be used at point-of-sale to suggest alternates when an item is out of stock, and can also be used by the replenishment system for restocking inventory if the max order quantity of the main item has reached capacity. A replacement is a product that supersedes the current item. It is assigned when the current item has become temporarily or permanently unavailable. Any queries to the current item from a purchasing or replenishment perspective, will yield in the replacement product being selected. Again this is an example of an item-to-item relationship, as the current product as well as the substitute and replacement products are all items. The data modeling concepts are similar to those described for Cross-sell and Up-sell, where the main item has pointers to the related item (substitute / replacement). Item to other entities Item to Supplier Supplier is one of the main entities that is modeled in WPC as part of a PIM solution. It is integral to the item’s definition and can be defined in multiple ways: supplier, manufacturer, distributor. Essentially it is the entity that supplies the particular product to the enterprise. Typically a financial or merchandising application holds the Supplier Master and is the system of record for it. Since the item-supplier relationship is defined during item setup, the supplier entity needs to be in WPC for establishing the relationship. This can be modeled as a Supplier Catalog / Lookup Table with a limited set of attributes such as: Supplier ID Supplier Name Supplier Status Supplier Contact The data should be read-only and an interface exposed in WPC to update this data from the Financial/Merchandising application. For example, iPod is supplied by Apple WebSphere Product Center Knowledgebase – Data Model How-To Guide 26 Computer, Inc. This would establish a link from the iPod product to the Apple Computer, Inc entity. On top of the item-supplier relationship there are typically a group of attributes defined such as supplier cost, supplier country, supplier packaging etc. One of the options is to model the Supplier as a multi-occurrence attribute on the item and within that multi-occurrence have a subset of attributes defined. This works well for a limited number of suppliers per item. Another option is to model the Supplier as a Hierarchy and map the item to a particular entity (category). This works well for a large set of suppliers per item, and is efficient in determining the list of items supplied by a particular supplier. However, it is not very intuitive from the UI in determining the list of suppliers for a given item. Therefore, the following criteria should be used to determine the right approach to modeling supplier data: Scenario < 10 suppliers per item Item-Supplier queries < 10 suppliers per item Item-Supplier queries Supplier-Item queries > 10 suppliers per item Item-Supplier queries Supplier-Item queries Data Modeling Key Points Supplier modeled as Catalog / Lookup Table Limited Supplier attributes in WPC Item – Supplier relationship attribute on item Multi-Occurrence of item-supplier relationship attribute Attribute subset defined under item-supplier relationship Attribute subset to include NPA attribute to display Supplier Name on UI Supplier modeled as Hierarchy with each supplier entity as a category. Hierarchy can be used to organize suppliers into groups Limited Supplier (category) attributes in WPC Item is classified under a supplier to establish itemsupplier relationship Item-Supplier attribute subset defined as item hierarchy spec. Supplier modeled as Catalog / Lookup Table Item-Supplier Catalog or UDL for performance Custom tool to create Item-Supplier relationship and enter values for attributes Custom tool to view suppliers for a given item Custom tool to view items for a given supplier Supplier Lookup Table WebSphere Product Center Knowledgebase – Data Model How-To Guide 27 Item/Supplier Attributes – View Item to Customer The need for a customer entity arises when PIM is discussed in context of a manufacturer or distributor. Product assortments get assigned to select customers. For example a Food Distributor needs to assign what customers/businesses get a particular item. From a data modeling perspective within WPC, the concept of the customer entity is the same as the supplier entity. For details please refer to the above section. The only difference being that the third scenario will usually apply for customer entity as it is more common for there to be > 10 customers per item as opposed to 10 suppliers of an item. Item to Locations Location can mean different things to enterprises based on the industry vertical and business requirements. It could depict warehouses, retail outlets, target markets, cities, states etc. Regardless it captures the essence of how an item is related to where it will be going. The location entity is modeled similar to the suppliers. Only the necessary location attributes are modeled within WPC, as the Location Master typically resides outside. WebSphere Product Center Knowledgebase – Data Model How-To Guide 28 Since an item is typically assigned to a large set of locations, usually in the hundreds or thousands, WPC has optimized this requirement by providing an inbuilt Location Hierarchy. It also allows for a subset of attributes to be defined on the item-location relationship. WebSphere Product Center Knowledgebase – Data Model How-To Guide 29 Lookup Values (LOV) Lookup values can be used as a set of predetermined or rule-based list of valid values for a particular attribute. This allows for end users to be able to select a value from the list in the UI. For example the list of valid countries, suppliers, brands etc. There are two options for defining lookup values in WPC. Scenario < 20 values Data Modeling Key Points String Enumeration / String Enumeration Rule No Search by Primary Key Single attribute value selection > 20 values Search by Primary Key Lookup Table Multiple attributes in lookup spec NPA attribute to display lookup value description on UI Reference to other attrib vals before selection LOV – String Enumeration – Item View WebSphere Product Center Knowledgebase – Data Model How-To Guide 30 LOV – String Enumeration – Item View LOV – Lookup Table WebSphere Product Center Knowledgebase – Data Model How-To Guide 31 Other Effective Dates There have been a lot of requests recently around the requirement of having Effectivity Dates for attribute values i.e. the new values being entered for the attributes are applicable only after a specific date or for a limited time duration. Common use cases include Promotions, Holiday/Seasonal Info, Promoting data from staging to production, etc. Unfortunately, at this time WPC does not provide an efficient solution to handle such a requirement. In prior implementations we have seen one of the two approaches: Option 1: Passive approach For each attribute that requires effective dates, add an attribute grouping which contains the attribute value and effective date. In WPC when the user changes the attribute value, they also modify the effective date to reflect when that value is applicable and pass this onto downstream systems The consuming application takes this data from WPC and plugs this into their effective date framework Option 2: Comprehensive / Custom approach Create an Effective Date framework within WPC Attribute names, values and effective dates stored in UDL / Catalog Custom tools to create and view effective date values for selected attributes Scheduled job that runs periodically to check on which attribute values need to be triggered based on the current effective dates It is highly recommended to avoid ‘Option 2’ as much as possible. The future MDM Platform is looking into supporting this requirement natively. However, if the customer insists then make them aware that this is a custom framework which increases the implementation timeline and adds to the maintenance overhead. In the meanwhile we may look at how robust the Custom solution as been and whether this can be turned into a solutions asset. Stay tuned from more on this in the upcoming future. Seasonal Item Assortments Some businesses such as house ware and clothing retailers have the notion of seasonal item assortments. This implies that the items available in each season changes. The change could be in the item assortment or within the item data such as description, images etc. They also have requirements where separate teams are working on multiple seasons at the same time. The planning team is interested in the next season, whereas the item team is responsible for data on the current season. From a data modeling perspective one can tackle this by identifying the number of seasons any team can be working on at a given time, example current season and next WebSphere Product Center Knowledgebase – Data Model How-To Guide 32 season. Create separate item catalogs to hold this data with proper access to each team. This will allow the different teams to login to WPC and work in tandem on separate catalogs. This is one way of getting around the effective dating framework but only applicable to this scenario as the business process and organization structure demands items being duplicated in separate catalogs. May not be an ideal solution for implementations with a very large item volume. Data Model Validations [Future version of How-To Guide] Please refer to Validations Framework listed in the appendix. Hierarchy Modeling [Future version of How-To Guide] Please refer to Hierarchy Management listed in the Appendix. Designing and Deploying the Data Model [Future version of the How-To Guide] WebSphere Product Center Knowledgebase – Data Model How-To Guide 33 Appendix Links to Other Resources Validation Framework WPC Services Repository -> Best Practices -> WebSphere Product Center Validation Framework Image Management WPC Services Repository -> Best Practices -> Best Practices: WPC Image Management Hierarchy Management WPC Services Repository -> Best Practices -> Hierarchy Management in WPC WebSphere Product Center Knowledgebase – Data Model How-To Guide 34
© Copyright 2024