How to Create a Custom Data Warehouse Event
For the latest version of Commerce Server 2007 Help, see the Microsoft Web site.
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 SubModify 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 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 : TaskHistoryWishlist01RelClose 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 eventtypeAdd 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.