Alan Faulkner's Forefront Security Blog

How to extend the Data Warehouse to include a custom Commerce Server event (Part 2)

This is the second part of a three part series in extending the Data Warehouse.  To recap the first part, take a look at this post here. 

This part will focus on adding a new Data Warehouse class for the MusicDownload event and adding the event to the CommerceEvent table in the Data Warehouse database.

Let's get right to it:

• First, we need to create the MusicDownload.  We will do this by executing the below script (you can take this script and modify it for your specific class/event):
• '------------------------------------------------
  ' Creates the Wishlist01 class for use with the
  ' Extending Commerce Events sample and the
  ' Commerce Server 2007 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")
'Create an ADO command object.
Dim cmdCommand
Set cmdCommand = CreateObject("ADODB.Command")
'Create an ADO record object.
Dim recNew
Set recNew = CreateObject("ADODB.Record")
'Open a connection to the provider.
'Modify the connection string to match your configuration.
objConn.Open "URL=mscop://InProcConnect/Server=localhost:" & _
"Catalog=DWSchema:Database=Startersite_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
' This is the event name
strClsDef="MusicDownload"
'Create a class.
'Always set SourceDefName to test_Source for new classes.
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 = "Artist"
strMemDefType = "WSTR"
strDefVal = "0"
CreateMemberDef objConn, strClsDef, strMemDef, strMemDefType, _
lMemFlags, strDefVal,strAggrExp
lMemFlags = fGenerateColDef + fHasDefaultVal
strMemDef = "Album"
strMemDefType = "WSTR"
strDefVal = "0"
CreateMemberDef objConn, strClsDef, strMemDef, strMemDefType, _
lMemFlags , strDefVal,strAggrExp
'-- MEM2 : create non key member
lMemFlags = fGenerateColDef + fHasDefaultVal
strMemDef = "Title"
strMemDefType = "WSTR"
strDefVal = "0"
CreateMemberDef objConn, strClsDef, strMemDef, strMemDefType, _
lMemFlags, strDefVal,strAggrExp

'The follwoing 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
'-- MEM8 : create non key member
lMemFlags = fGenerateColDef + fHasDefaultVal
strMemDef = "VisitNum"
strMemDefType = "INT64"
strDefVal = "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 relattionship

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

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

• Now that we have the event class in the Data Warehouse, you will notice that the Data Warehouse database has a new table created, which is the following:
• MusicDownload    - This is where the events will be stored.  The events are parsed from the IIS logs, resolved, processed and added to this table. 
• Now we need to add the event information to the CommerceEvent table, we do this by running the below Sql commands.  You can modify these commands for your specific event and database.
• INSERT INTO [Startersite_datawarehouse].[dbo].[CommerceEvent]([CommerceEventID], [CommerceEventClassName], [CommerceEventInternalFlag], [CommerceEventMemberName], [StorageClassName], [StorageMemberName])
  VALUES(0x000000001000, 'MusicDownload', 0, 'Artist', 'MusicDownload', 'Artist')
  INSERT INTO [Startersite_datawarehouse].[dbo].[CommerceEvent]([CommerceEventID], [CommerceEventClassName], [CommerceEventInternalFlag], [CommerceEventMemberName], [StorageClassName], [StorageMemberName])
  VALUES(0x000000001001, 'MusicDownload', 0, 'Album', 'MusicDownload', 'Album')
  INSERT INTO [Startersite_datawarehouse].[dbo].[CommerceEvent]([CommerceEventID], [CommerceEventClassName], [CommerceEventInternalFlag], [CommerceEventMemberName], [StorageClassName], [StorageMemberName])
  VALUES(0x000000001002, 'MusicDownload', 0, 'Title', 'MusicDownload', 'Title')

If you want to see the sample project and also the scripts used, I have uploaded the project as a zip file in our GotDotNet Workspace located here. 

For our next part of the series, I will show you how to create a new report that utilizes the MusicDownload event. 

Hope this helps!

Alan