Share via


How to Create a Custom Data Warehouse Event

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.

We strongly recommend that you back up your current Data Warehouse schema before you extend it. If you encounter difficulties when you extend the schema you can replace the changed Data Warehouse schema with the original schema to roll back changes. If you do not want to create a backup of your original schema, see the procedure "To create the Wishlist class" later in this topic.

To back up the original Data Warehouse schema

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

  2. Move to the location where you want to create the new folder that will contain all the files that are used in this sample and back up copies of the files that will be modified. 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 then click Stop.

    If the Stop Other Services dialog box appears, alerting you that MSSQLSERVER and SQLSERVERAGENT are about to be stopped, click Yes to stop both services.

  7. In Windows Explorer, locate the folder that contains the .ldf and .mdf files for the site that will be extended. For example, c:\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. Locate the Wishlist folder.

  11. On the File menu, click Paste.

    You now have backup copies of your original schema. If you decide not to keep changes that are 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.

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

  14. 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 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 CSharpSite_datawarehouse for the CSharp 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=CSharpSite_datawarehouse: 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 in the code as appropriate. See the comments in the code for more information.

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

  5. In the Save As dialog box, in the Save in box, locate the Wishlist folder. In the Filename box, type wishlist_class.vbs, and then click Save.

  6. On the File menu, click Exit.

  7. Click Start, and then click Run.

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

    CSDWDbgSrv.exe is a utility that is included with Commerce Server 2009 that lets you 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 that has a timestamp appended to the log file name. We recommend that you use CSDWDbgSrv.exe whenever you modify the OLE DB schema.

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

  10. At the command prompt, locate the Wishlist folder.

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

  12. When wishlist_class.vbs is completed 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
    
  13. Close both Command Prompt windows.

To extend the CommerceEvent table in the Data Warehouse

  1. Click Start, point to Programs, point to Microsoft SQL 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 that the computer that is running SQL Server uses.

    Login name

    Use only for SQL Server authentication. Type the SQL Server logon 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 the Execute button on the toolbar.

    The CommerceEvent table appears on the Grid tab of the Results screen.

  5. Delete the previous query by selecting the current contents of the Editor screen and pressing the DELETE key.

  6. To update the CommerceEvent table so that it will send the specially formatted strings and import them into the Wishlist class, set SQL Query Analyzer to your Data Warehouse database and copy the following code into an Analyzer window:

    INSERT INTO [CommerceEvent]([CommerceEventID], [CommerceEventClassName], [CommerceEventInternalFlag], [CommerceEventMemberName], [StorageClassName], [StorageMemberName])
    VALUES(0x000000001000, 'WISH', 0, 'EVT', 'WishList01', 'Event')
    INSERT INTO.[CommerceEvent]([CommerceEventID], [CommerceEventClassName], [CommerceEventInternalFlag], [CommerceEventMemberName], [StorageClassName], [StorageMemberName])
    VALUES(0x000000001001, 'WISH', 0, 'PRID', 'WishList01', 'ProductNameCat')
    INSERT INTO.[CommerceEvent]([CommerceEventID], [CommerceEventClassName], [CommerceEventInternalFlag], [CommerceEventMemberName], [StorageClassName], [StorageMemberName])
    VALUES(0x000000001002, 'WISH', 0, 'QTY', 'WishList01', 'Quantity')
    INSERT INTO.[CommerceEvent]([CommerceEventID], [CommerceEventClassName], [CommerceEventInternalFlag], [CommerceEventMemberName], [StorageClassName], [StorageMemberName])
    VALUES(0x000000001003, 'WISH', 0, 'LN', 'WishList01', 'ListName')
    
  7. Click the Execute button on the toolbar.

    The Messages tab in the Results screen shows the following messages. They indicate that the schema changes have completed:

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

  8. Select the current contents of the Editor screen and press the DELETE key.

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

  10. Click the Execute button 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 the Execute button on the toolbar.

    The EventsDimensionView appears on the Grid tab of the Results screen.

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

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

  4. Click Start, point to Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.

  5. In the SQL Server Management Studio, expand Databases, expand the <site>_datawarehouse database, and then expand Views.

  6. Right- click EventsDimensionView and select Modify.

In the view modification area to the right, select all the SQL text in the window, and then press CTRL+C to copy the text.

  1. Use the New Query button to open a new SQL query window.

  2. In the Edit View screen of SQL Query Analyzer, press CTRL+V to paste the contents into the window.

  3. Add the new event description to the list of event descriptions. This requires modifying the CASE statement in 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
    
  4. Add paragraph marks after EventsDimensionView and go, as in the earlier example.

  5. Click the Execute button on the toolbar.

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

    The command(s) completed successfully.

See Also

Other Resources

Extending the Data Warehouse Logical Schema

Extending the Data Warehouse

Extending Data Warehouse Events