How to Create a Custom Data Warehouse Event

Article
01/18/2011

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

Click Start, point to Programs, point to Accessories, and then click Windows Explorer.
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.
Name the new folder Wishlist.
Click Start, point to Programs, point to Administrative Tools, and then click Component Services.
In the Component Services dialog box, on the Tree tab, click Services (Local).
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.
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.
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.
On the File menu, click Copy.
Locate the Wishlist folder.
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.
On the File menu, click Close.
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.
On the Console menu, click Exit.

To create the Wishlist class

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

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

Modify the connection string in the code as appropriate. See the comments in the code for more information.
In Notepad, on the File menu, click Save As.
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.
On the File menu, click Exit.
Click Start, and then click Run.
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.
To open an additional Command Prompt window, click Start, point to Programs, point to Accessories, and then click Command Prompt.
At the command prompt, locate the Wishlist folder.
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.

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

Close both Command Prompt windows.

To extend the CommerceEvent table in the Data Warehouse

Click Start, point to Programs, point to Microsoft SQL Server, and then click Query Analyzer.

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.

In SQL Query Analyzer, select the Data Warehouse for the site from the database drop-down list.
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.
Delete the previous query by selecting the current contents of the Editor screen and pressing the DELETE key.

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')

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)
Select the current contents of the Editor screen and press the DELETE key.
To view the new wishlist events in the CommerceEvent table, in the Editor screen type select * from commerceevent where commerceeventclassname = 'wish'.
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

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.
Select the current contents of the Editor screen and press the DELETE key.
In the Editor screen, type drop view EventsDimensionView go.
Click Start, point to Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.
In the SQL Server Management Studio, expand Databases, expand the <site>_datawarehouse database, and then expand Views.
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.

Use the New Query button to open a new SQL query window.
In the Edit View screen of SQL Query Analyzer, press CTRL+V to paste the contents into the window.

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

Add paragraph marks after EventsDimensionView and go, as in the earlier example.
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.

Share via

How to Create a Custom Data Warehouse Event

To back up the original Data Warehouse schema

To create the Wishlist class

To extend the CommerceEvent table in the Data Warehouse

To add a new event type description

See Also

Other Resources

Additional resources