Creating a Custom Commerce Event

  • Article

This section of the sample creates a new wish list class in the Data Warehouse and extends the Data Warehouse CommerceEvent table to include wish list events. This sample works with both the Retail and Supplier Solution Sites.

Ee783792.note(en-US,CS.20).gif Note

  • In this sample, before you can extend the Data Warehouse, you must have the Retail Solution Site or the Retail2002 – International Retail Site and a Data Warehouse installed on your computer.
  • Commerce Server Solution Sites are available at https://go.microsoft.com/fwlink/?linkid=6491.
  • The Retail2002 – International Retail Site is available in the Commerce Server 2002 SDK at <drive>:\Program Files\Microsoft Commerce Server 2002\SDK\Samples\ASPNET\Retail2002.

Ee783792.note(en-US,CS.20).gif Important

  • We recommend that you do not develop a production-quality site based on the Retail2002 Site. We recommend that you use the Commerce Server 2002 Starter Site to develop a production quality site. You can download the Starter Site from https://go.microsoft.com/fwlink/?linkid=37800.

To back up the original Data Warehouse schema

To create the Wishlist class

To extend the CommerceEvent table in the Data Warehouse

To back up the original Data Warehouse schema

It is highly recommended that you back up your current Data Warehouse schema before extending it. If you encounter difficulties extending the schema you can replace the altered Data Warehouse schema with the original schema to roll back changes. If you do not wish to create a backup of your original schema, go to To create the Wishlist class.

  1. Click Start, point to Programs, point to Accessories, and then click Windows Explorer.

  2. Navigate to the location where you want to create the new folder that will contain all the files used in this sample and back up copies of the files that will be modified. Then on the File menu, point to New, and then click Folder.

  3. Name the new folder Wishlist.

  4. Click Start, point to Programs, point to Administrative Tools, and then click Component Services.

  5. In the Component Services dialog box, on the Tree tab, click Services (Local).

  6. In the Services(Local) screen, right-click MSSQLSERVER, and click Stop.

    If the Stop Other Services dialog box appears, alerting you that if MSSQLSERVER is stopped, SQLSERVERAGENT is stopped as well, click Yes to stop both services.

  7. In Windows Explorer, navigate to the folder containing the .ldf and .mdf files for the site that will be extended. For example, <drive letter>:\Program Files\Microsoft SQL Server\MSSQL\Data.

  8. While pressing CTRL, select the following files: Sitename_commerce.mdf, Sitename_commerce_log.LDF, Sitename_dw.mdf, Sitename_dw_log.LDF, master.mdf, and mastlog.ldf, where Sitename is the name of the site that will enable the new commerce events.

  9. On the File menu, click Copy.

  10. Navigate to the Wishlist folder.

  11. On the File menu, click Paste.

    You now have backup copies of your original schema. If you choose not to keep changes made in the rest of this sample, you can replace changed versions with the original versions stored in the Wishlist folder.

  12. On the File menu, click Close.

  13. In the Component Services dialog box, in the Services section, right-click MSSQLSERVER, and then click Start. SQL Server is restarted.

  14. In the Services section, right click MSSQLSERVER, and click Start. SQL Server is restarted.

    If the SQLSERVERAGENT was stopped in step 6, right-click SQLSERVERAGENT, and then click Start. SQL Server Agent is restarted.

  15. On the Console menu, click Exit.

To create the Wishlist class

  1. Click Start, point to Programs, point to Accessories, and then click Notepad.

  2. Copy the following code to the Untitled-Notepad document:

        '------------------------------------------------
    '  Creates the Wishlist01 class for use with the 
    '  Extending Commerce Events sample and the 
    '  Commerce Server 2002 Data Warehouse.
    '------------------------------------------------

Public Const fPrimaryKey = 1
Public Const fMultiValued = 2
Public Const fHasDefaultVal = 4
Public Const fIsRequired = 8
Public Const fIsJoinKey = 16
Public Const fDontClear = 32
Public Const fGenerateColDef = 64
Public Const fIsUniqueKey = 128
Public Const fIsIdentityMember = 256

'--- flags for clsdef
Public Const fGenerateIdentity = &H1
Public Const fGenerateTableDef = &H2
Public Const fGenerateKeyDef = &H4
Public Const fGeneratePartDef = &H8
Public Const fIsAbstract = &H10

Public Const fHasAggrExp = &H100  'member is aggregate
Public Const fIsDimension = &H1000
Public Const fIsMeasure = &H10000

Main
Sub Main()

    'Create an ADO connection object.
    Dim objConn
    Set objConn = CreateObject("ADODB.Connection.2.5")

    'Create an ADO command object.
    Dim cmdCommand
    Set cmdCommand = CreateObject("ADODB.Command.2.5")

    'Create an ADO record object.
    Dim recNew
    Set recNew = CreateObject("ADODB.Record.2.5")

    'Open a connection to the provider.
    'Modify the connection string to match your configuration. The
    'database should be set to Retail_dw for the Retail site, or
    'Retail2002_dw for the Retail2002 site. If you are not using
    'Windows Authentication, replace Trusted_Connection=Yes with your
    'username and password. Be aware that storing your password in this
    'file is a security risk.
    objConn.Open "URL=mscop://InProcConnect/Server=servername:" & _
    "Catalog=DWSchema:Database=Retail_dw: Trusted_Connection=Yes:" _
    & "FastLoad=True"

    'Set the connection in the command object.
    Set cmdCommand.ActiveConnection = objConn

    'Turn on "Schema Change" mode.
    cmdCommand.CommandText = "SchemaMode=1"
    cmdCommand.Execute

    strClsDef="WishList01"

    'Create a class.
    CreateClassDef objConn, strClsDef, "DWSchema", "test_Source", "", _
    False


    '-- MEM1 : create non key member
    lMemFlags =  fGenerateColDef +  fHasDefaultVal
    strMemDef = "Event" 
    strMemDefType = "WSTR"
    strDefVal = "0"

    CreateMemberDef objConn, strClsDef, strMemDef, strMemDefType , _
    lMemFlags, strDefVal, strAggrExp 

    '-- MEM1 : create non key member
    lMemFlags =  fGenerateColDef +  fHasDefaultVal
    strMemDef = "ProductNameCat" 
    strMemDefType = "WSTR"
    strDefVal = "0"


    CreateMemberDef objConn, strClsDef, strMemDef, strMemDefType, _
    lMemFlags, strDefVal,strAggrExp 

    lMemFlags =  fGenerateColDef +  fHasDefaultVal
    strMemDef = "ListName" 
    strMemDefType = "WSTR"
    strDefVal = "0"

    CreateMemberDef objConn, strClsDef, strMemDef, strMemDefType, _
    lMemFlags ,  strDefVal,strAggrExp 

    '-- MEM2 : create non key member
    lMemFlags =  fGenerateColDef +  fHasDefaultVal
    strMemDef = "Quantity" 
    strMemDefType = "LONG"
    strDefVal = "0"

    CreateMemberDef objConn, strClsDef, strMemDef, strMemDefType, _
    lMemFlags, strDefVal,strAggrExp 

    '-- MEM3 : create non key member
    lMemFlags =  fGenerateColDef +  fHasDefaultVal
    strMemDef = "VisitNum" 
    strMemDefType = "IDENTITY"
    strDefVal = "0"

    CreateMemberDef objConn, strClsDef, strMemDef, strMemDefType, _
    lMemFlags, strDefVal,strAggrExp 

    'The following members are the required for every Commerce Event.

    '-- MEM4 : create non key member
    lMemFlags =  fGenerateColDef +  fHasDefaultVal
    strMemDef = "DTimeStamp" 
    strMemDefType = "FILETIME"
    strDefVal = "1900-1-1 0:0:0.0"

    CreateMemberDef objConn, strClsDef, strMemDef, strMemDefType, _
    lMemFlags, strDefVal, strAggrExp 

    '-- MEM5 : create non key member
    lMemFlags =  fGenerateColDef +  fHasDefaultVal
    strMemDef = "RequestIndex" 
    strMemDefType = "SHORT"
    strDefVal = "0"

    CreateMemberDef objConn, strClsDef, strMemDef, strMemDefType, _
    lMemFlags, strDefVal, strAggrExp 

    '-- MEM6 : create non key member
    lMemFlags =  fGenerateColDef +  fHasDefaultVal
    strMemDef = "UriKey" 
    strMemDefType = "INT64"
    strDefVal = "0"

    CreateMemberDef objConn, strClsDef, strMemDef, strMemDefType, _
    lMemFlags, strDefVal, strAggrExp 

    '-- MEM7 : create non key member
    lMemFlags =  fGenerateColDef +  fHasDefaultVal
    strMemDef = "UserKey" 
    strMemDefType = "INT64"
    strDefVal = "0"

    CreateMemberDef objConn, strClsDef, strMemDef, strMemDefType, _
    lMemFlags, strDefVal, strAggrExp 

    strChildClassName  = strClsDef
    iRelType = 5 'indicating virtual relationship for performance
    'reasons

    '-- Rel1 : create virtual relationship to uri
    strParentClassName = "URI"
    strRelDefName      = strParentClassName & strChildClassName & "Rel"
    strParentClassKey  = strParentClassName & "Key"

    CreateRelDef objConn, strRelDefName, strParentClassName, _
    strChildClassName, strParentClassKey, iRelType    

    '-- Rel2 : create virtual relationship to loguser
    strParentClassName = "LogUser"
    strRelDefName      = strParentClassName & strChildClassName & "Rel"
    strParentClassKey  = strParentClassName & "Key"

    CreateRelDef objConn, strRelDefName, strParentClassName, _
    strChildClassName, strParentClassKey, iRelType    

    iRelType = 2 'indicating real relationship

    '-- Rel3 : create relationship to Registereduser
    strParentClassName = "RegisteredUser"
    strRelDefName      = strParentClassName & strChildClassName & "Rel"
    strParentClassKey  = strParentClassName & "Key"

    CreateRelDef objConn, strRelDefName, strParentClassName, _
    strChildClassName, strParentClassKey, iRelType    

    '-- Rel4 : create relationship to Site
    strParentClassName = "Site"
    strRelDefName      = strParentClassName & strChildClassName & "Rel"
    strParentClassKey  = strParentClassName & "Key"

    CreateRelDef objConn, strRelDefName, strParentClassName, _
    strChildClassName, strParentClassKey, iRelType    

    '-- Rel5 : create relationship to TaskHistory
    strParentClassName = "TaskHistory"
    strRelDefName      = strParentClassName & strChildClassName & "Rel"
    strParentClassKey  = strParentClassName & "Key"

    CreateRelDef objConn, strRelDefName, strParentClassName, _
    strChildClassName, strParentClassKey, iRelType    

    '-- Rel6 : create relationship to Date
    'strParentClassName = "Date"
    'strRelDefName      = strParentClassName & strChildClassName & _
    '"Rel"
    'strParentClassKey  = strParentClassName & "Key"

    'CreateRelDef objConn, strRelDefName, strParentClassName, _
    'strChildClassName, strParentClassKey, iRelType    

    '------------------------------------------------
    '  Commit Schema
    '------------------------------------------------
    cmdCommand.CommandText = "CommitSchema"
    cmdCommand.Execute

    'Turn on "Schema Change" mode
    cmdCommand.CommandText = "SchemaMode=0"
    cmdCommand.Execute

End Sub

'----------------------------------------------------------------------
'  Procedure : CreateClassDef
'  Parameters : objConn
'               strClsDef - name of classdef to be created
'               strCatalog - catalog in which we want to create
'               strSrcDef - source definition name
'               strBaseClsName - this is useful for aggregations
'                               simple classes can pass ""
'               bGenKeyDef = true - generate key definition 
'                     automatically
'   Notes : The schema change mode should have been set to true prior
'           to calling this function.
'           
'----------------------------------------------------------------------
Sub CreateClassDef(objConn, strClsDef, strCatalog, strSrcDef, _
    strBaseClsName, bGenKeyDef)

    WScript.Echo "DBG: Class : " & strClsDef
    Dim rec
    Set rec = CreateObject("ADODB.Record.2.5")

    rec.Open "Class/" & strClsDef, objConn, 3, adCreateOverwrite
    rec("IsPersistent") = 1
    rec("ClassDefName") = strClsDef
    rec("SourceDefName") = strSrcDef
    rec("GeneratePartitionDef") = 1
    rec("GenerateTableDef") = 1
    If bGenKeyDef Then
       rec("GenerateKeyDef") = 1
    Else
       rec("GenerateKeyDef") = 0

    End If

    'to create an aggregate class --
    If strBaseClsName <> "" Then
       rec("BaseClassName") = strBaseClsName
    End If
    rec("GenerateIdentity") = 1
    rec("__Commit") = 1
    rec.Fields.Update
    rec.Close

End Sub

'----------------------------------------------------------------------
'  Procedure  : CreateMemberDef
'  Purpose : Utility to create members
'  Parameters : objConn
'               strClsDef - name of classdef
'               strMemDef - name of member
'               strMemDefType - type of the memberdef
'               lMemFlags - MemberDef Creation flags 
'
'  Notes : User is supposed to set the schema mode to updatable and
'          reset after the member is created.
'----------------------------------------------------------------------
Sub CreateMemberDef(objConn, strClsDef, strMemDef, strMemDefType, _
    lMemFlags ,  strDefVal,strAggrExp )
Dim rec 
' On Error Resume Next
    WScript.Echo "DBG: Mem : " & strMemDef
    set rec = CreateObject("ADODB.Record.2.5")
    rec.Open "Member/" & strClsDef & "/" & strMemDef, objConn, _
        adModeReadWrite, adCreateOverwrite
    rec("MemberDefName") = strMemDef
    If (lMemFlags And fGenerateColDef) > 0 Then  'default case
        rec("GenerateColumnDef") = 1
    End If

    If (lMemFlags And fPrimaryKey) > 0 Then
        rec("IsPrimaryKey") = 1
    End If
    If (lMemFlags And fMultiValued) > 0 Then
        rec("IsMultiValued") = 1
    End If
    If (lMemFlags And fHasDefaultVal) > 0 Then
        rec("DefaultValueAsStr") = strDefVal
    End If
    If (lMemFlags And fIsUniqueKey) > 0 Then
        rec("IsUniqueKey") = 1
    End If
    If (lMemFlags And fIsIdentityMember) > 0 Then
        rec("IsIsIdentityMember") = 1
    End If

'Aggregate member, then set the aggregate expressions. 
if (lMemFlags And fHasAggrExp) > 0 then
         rec("ExpressionStr") = strAggrExp
End if
if (lMemFlags And fIsDimension) > 0 then
rec("IsDimension") =1
End if
if (lMemFlags And fIsMeasure ) > 0 then
rec("IsMeasure") = 1
End if

    rec("TypeName") = strMemDefType
    rec("__Commit") = 1
    rec.Fields.Update

    rec.Close

End Sub


'----------------------------------------------------------------------
'  Procedure:    CreateRelationDef
'  Purpose:      Create a  Relation Definition
'  Notes :       The classes that the parent and child referred to must
'                exist. The Keydefinition for the parent must exist 
'                (1-M).
'
'----------------------------------------------------------------------
 Sub CreateRelDef(objConn, strRelName, strClsParent, strClsChild, _
     strKeyParent, iRelType)
 Dim rec 
 'On Error Resume Next
    WScript.Echo "DBG: Rel : " & strRelName
        set rec = CreateObject("ADODB.Record")
    rec.Open "Relation/" & strRelName, objConn, adModeWrite, _
        adCreateOverwrite
    rec("ParentClassName") = strClsParent
    rec("ParentClasskey") = strKeyParent
    rec("ChildClassName") = strClsChild
    rec("RelType") = iRelType
    rec("__Commit") = 1
    rec.Fields.Update

    rec.Close
    Set rec = Nothing

End Sub

  3. Modify the connection string on line 50 as appropriate. See the comments above line 50 for detailed information.

  4. If you are using the Retail2002 site, or a site other than the Retail Solution site, replace all instances of Retail_dw with Retail2002_dw, or the name of your Data Warehouse database.

  5. In Notepad, on the File menu, click Save As.

  6. In the Save As dialog box, in the Save in box, navigate to the Wishlist folder. In the Filename box, type wishlist_class.vbs, and then click Save.

  7. On the File menu, click Exit.

  8. Click Start, and then click Run.

  9. In the Run dialog box, in the Open box, type "<drive>:\Program Files\Microsoft Commerce Server 2002\CSDWDbgSrv.exe" -f traceoutput, and then click OK.

    CSDWDbgSrv.exe is a utility that ships with Commerce Server 2002 that allows you to see trace output from the OLE DB Provider for Commerce Server. Appending –f traceoutput to the file name will log all output to a file with a timestamp appended to the log file name. It is recommended that you use CSDWDbgSrv.exe whenever you modify the OLE DB schema.

  10. To open an additional command prompt window, click Start, point to Programs, point to Accessories, and then click Command Prompt.

  11. In the Command Prompt window, navigate to the Wishlist folder.

  12. To extend the Data Warehouse schema and run the Visual Basic Scripting Edition (VBScript) file you just created, at the command prompt, type cscript wishlist_class.vbs.

  13. When wishlist_class.vbs completes successfully, the command prompt window will display the following:

    DBG: Class : Wishlist01
DBG: Mem : Event
DBG: Mem : ProductNameCat
DBG: Mem : ListName
DBG: Mem : Quantity
DBG: Mem : VisitNum
DBG: Mem : DTimeStamp
DBG: Mem: RequestIndex
DBG: Mem : UriKey
DBG: Mem : UserKey
DBG: Rel : URIWishlist01Rel
DBG: Rel : LogUserWishlist01Rel
DBG: Rel : RegisteredUserWishlist01Rel
DBG: Rel : SiteWishlist01Rel
DBG: Rel : TaskHistoryWishlist01Rel

  14. Close both command prompt windows.

To extend the CommerceEvent table in the Data Warehouse

  1. Click Start, point to Programs, point to MicrosoftSQL Server, and then click Query Analyzer.

  2. In the Connect to SQL Server dialog box, do the following:

    Use this To do this
    SQL Server Type the name of the server in which the Data Warehouse resides.
    Connect Using Select the type of authentication the SQL Server computer uses.
    Login name Use only for SQL Server authentication. Type the SQL Server login name.
    Password Use only for SQL Server authentication. Type the SQL Server password.

  3. In SQL Query Analyzer, select the Data Warehouse for the site from the database drop-down list.

  4. To view the current contents of the CommerceEvent table in the Data Warehouse, in the Editor screen, type select * from commerceevent, and then click Execute icon on the toolbar.

    The CommerceEvent table is shown on the Grid tab of the Results screen.

  5. Delete the previous query by selecting the current contents of the Editor screen and press DELETE on the keyboard.

  6. To update the CommerceEvent table so that it will send the specially formatted strings and import them into the Wishlist class created in To create the Wishlist class, copy the following code into SQL Query Analyzer:

    INSERT INTO [Retail_dw].[dbo].[CommerceEvent]([CommerceEventID], [CommerceEventClassName], [CommerceEventInternalFlag], [CommerceEventMemberName], [StorageClassName], [StorageMemberName])
VALUES(0x000000001000, 'WISH', 0, 'EVT', 'WishList01', 'Event')
INSERT INTO [Retail_dw].[dbo].[CommerceEvent]([CommerceEventID], [CommerceEventClassName], [CommerceEventInternalFlag], [CommerceEventMemberName], [StorageClassName], [StorageMemberName])
VALUES(0x000000001001, 'WISH', 0, 'PRID', 'WishList01', 'ProductNameCat')
INSERT INTO [Retail_dw].[dbo].[CommerceEvent]([CommerceEventID], [CommerceEventClassName], [CommerceEventInternalFlag], [CommerceEventMemberName], [StorageClassName], [StorageMemberName])
VALUES(0x000000001002, 'WISH', 0, 'QTY', 'WishList01', 'Quantity')
INSERT INTO [Retail_dw].[dbo].[CommerceEvent]([CommerceEventID], [CommerceEventClassName], [CommerceEventInternalFlag], [CommerceEventMemberName], [StorageClassName], [StorageMemberName])
VALUES(0x000000001003, 'WISH', 0, 'LN', 'WishList01', 'ListName')

  7. If you are using a site other than the Retail Solution site, replace all instances of Retail_dw with Retail2002_dw, or the name of your Data Warehouse database.

  8. Click Execute on the toolbar.

    The Messages tab in the Results screen shows the following message, indicating that the schema changes have completed:

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

  9. Select the current contents of the Editor screen and press DELETE on the keyboard.

  10. To view the new wishlist events in the CommerceEvent table, in the Editor screen type select * from commerceevent where commerceeventclassname = 'wish';.

  11. Click Execute on the toolbar.

    The new wishlist events appear on the Grid tab of the Results screen.

To add a new event type description

  1. To view the current contents of the EventsDimensionView, in the Editor screen, type select * from EventsDimensionView, and then click Execute icon on the toolbar.

    The EventsDimensionView is shown on the Grid tab of the Results screen.

  2. Select the current contents of the Editor screen and press DELETE on the keyboard.

  3. In the Editor screen, type drop view EventsDimensionView go.

  4. Click Start, point to Programs, point to Administrative Tools, and then click Commerce Server Manager.

  5. In Commerce Server Manager, expand the Microsoft SQL Servers node, expand the SQL Server Group node, expand the node for the local server, expand Databases, expand the node for the site containing the new event, and then click Views.

  6. In the Commerce Server Manager content pane, double-click EventsDimensionView.

  7. In the View Properties - EventsDimensionView dialog box, select all of the text in the content window, and then press CTRL+C on the keyboard to copy the text.

  8. In the Editor screen of SQL Query Analyzer, press CTRL+V on the keyboard to paste the contents of the notepad into the Editor screen.

  9. Add the new event description to the list of event descriptions. This requires modifying the CASE statement within the EventsDimensionView to return a display string for the new event types.

    In the following example the descriptions “Add to Wishlist” and "Remove from Wishlist" are added to the default set of recognized event types.

    drop view EventsDimensionView
go
create view EventsDimensionView as select [EventTypeID], [EventName], [EventDescription] = 
case EventName  When 'AIBSK' then N'Add Item' 
When 'RIBSK' then N'Remove Item' 
when 'REQUEST' then N'Request' 
when 'Click' then N'Click' 
when 'Download' then N'Download' 
when 'SUBOR' then N'Submit Order' 
when 'SOLD' then N'Sold'
when 'ADDTOLIST' then N'Add to Wishlist' 
when 'DROPFROMLIST' then N'Drop from Wishlist'
when 'Unknown' then N'Unknown' 
Else isnull([EventDescription],[EventName]) end from eventtype

  10. Place paragraph break marks after EventsDimensionView and go, as done in the example above.

  11. Click Execute on the toolbar.

    The Messages tab of the Results screen shows the following message, indicating that the schema changes have completed:

    The command(s) completed successfully.

  12. On the File menu of SQL Query Analyzer, click Exit.

  13. In the View Properties – EventsDimensionView dialog box, click Cancel.

  14. On the Console menu of the Commerce Server Manager window, click Exit.

See Also

Extending the Data Warehouse Logical Schema

Copyright © 2005 Microsoft Corporation.
All rights reserved.