Alan Faulkner's Forefront Security Blog : DW/A Class Creation Script from Webcast

Recent Posts

Tags

No tags have been created or used yet.

News

These postings are provided "AS IS" with no warranties, and confer no rights. Use of included script and code samples are subject to the terms specified here.

Commerce Server Related Bloggers

Archives

DW/A Class Creation Script from Webcast

If you have seen our DW/A WebCast Series, I have placed the script that was used to create the DiscountsApplied DW/A Class. Enjoy!

'Create an ADO connection object.
Dim cnnConnection
Set cnnConnection = 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 (bind) to the provider.
'Modify the values for Catalog, Database, User, and Password to match your
'resources.
   cnnConnection.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 = cnnConnection

'Turn on "Schema Change" mode.
'SchemaMode can be set to On, True, or 1 to turn the
' schema change mode on.
cmdCommand.CommandText = "SchemaMode=1"
cmdCommand.Execute

'Create a new class in the Class Definition table.
recNew.Open "Class/DiscountsApplied", cnnConnection, _
adModeWrite, adCreateOverwrite

'Set the attributes.
   recNew("ClassDefName") = "DiscountsApplied"
'Always set SourceDefName to test_Source for new classes.
   recNew("SourceDefName") = "test_Source"
   recNew("Description") = "Discount Applied."
   recNew("GenerateTableDef") = 1
   recNew("GeneratePartitionDef") = 1

'Create your own key.
recNew("GenerateKeyDef") = 0

recNew("GenerateIdentity") = 1

'Save the new row.
   recNew("__Commit") = 1
   recNew.Fields.Update
   recNew.Close

'Create an instance in the Member Definition table for the first member.
recNew.Open "Member/DiscountsApplied/OrderGroupId", cnnConnection, _
adModeWrite, adCreateOverwrite

'Set the attributes.
   recNew("ClassDefName") = DiscountsApplied
   recNew("TypeName") = "UUID"
   recNew("Description") = "Identifier for an Order Group."
   recNew("IsPrimaryKey") = 0
   recNew("DefaultValueAsStr") = "{00000000-0000-0000-0000-000000000000}"
   recNew("MemberDefName") = "OrderGroupId"
   recNew("GenerateColumnDef") = 1

'Save the new row.
   recNew("__Commit") = 1
   recNew.Fields.Update
   recNew.Close

'Create an instance in the Member Definition table for the first member.
recNew.Open "Member/DiscountsApplied/LineItemId", cnnConnection, _
adModeWrite, adCreateOverwrite

'Set the attributes.
   recNew("ClassDefName") = DiscountsApplied
   recNew("TypeName") = "UUID"
   recNew("Description") = "Identifier for an Line Item."
   recNew("IsPrimaryKey") = 0
   recNew("DefaultValueAsStr") = "{00000000-0000-0000-0000-000000000000}"
   recNew("MemberDefName") = "LineItemId"
   recNew("GenerateColumnDef") = 1

'Save the new row.
   recNew("__Commit") = 1
   recNew.Fields.Update
   recNew.Close

'Create an instance in the Class Key Definition table.
recNew.Open "Key/DiscountsAppliedKey", cnnConnection, _
adModeWrite, adCreateOverwrite

'Set the attributes.
recNew("ClassDefName") = "DiscountsApplied"
recNew("Description") = "Unique key for the DiscountsApplied class."

'Save the new row.
   recNew("__Commit") = 1
   recNew.Fields.Update
   recNew.Close

'Create an instance in the Key Member table for the first member.
recNew.Open "KeyMember/DiscountsAppliedKey/ordergroup_id", _
cnnConnection, adModeWrite, adCreateOverwrite

'Set the attributes for the first key member.
   recnew("MemDefName") = "OrderGroupId"
   recNew("OrdinalPosInKey") = 0
   recNew("Description") = "First member of DiscountsAppliedKey."
   recNew("KeyDefName") = DiscountsAppliedKey

'Save the new row.
   recNew("__Commit") = 1
   recNew.Fields.Update
   recNew.Close

'Create an instance in the Key Member table for the second member.
recNew.Open "KeyMember/DiscountsAppliedKey/OrderForm_Id", _
cnnConnection, adModeWrite, adCreateOverwrite

'Set the attributes for the second key member.
   recnew("MemDefName") = "LineItemId"
   recNew("OrdinalPosInKey") = 1
   recNew("Description") = "Second member of DiscountsAppliedKey."
   recNew("KeyDefName") = DiscountsAppliedKey

'Save the new row.
   recNew("__Commit") = 1
   recNew.Fields.Update
   recNew.Close

'Commit the changes to the Data Warehouse schema.
cmdCommand.CommandText = "CommitSchema"
cmdCommand.Execute

'Turn off "Schema Change" mode.
'If SchemaMode is set to a value other than On, True, or 1
' it is turned off.
   cmdCommand.CommandText = "SchemaMode=0"

   cmdCommand.Execute

'Close the connection.
cnnConnection.Close

Hope this helps!

Alan

Posted: Friday, November 03, 2006 10:29 AM by akfaulkner

Comments

Commerce Team Blog said:

We added three new webcasts as part of a series on extending the Data Warehouse. Here are the details,

# November 3, 2006 1:34 PM

Willian Leite said:

Hi Alan,

I watched your webcast and I developed the Classes and Views for my Weekly Typed Properties in my Purchase Orders(after do the OrderObjectMappings.xml).

All this works fine in my Commerce Developer Environment (WebServer and SQL Server in the same machine). Now I have to do this in an Enterprise Cenario.

The DTS Import Wizard fails in the start, showing this message : "Faild to retrive DTS Task Information". In my cenario I have 2 WebServer (ProductionServer and StagingServer) and two SQL Server 2K5 (Commerce DataBase and DW/BI Database).

In the WebServer I dont have any SQL Server installed.

What I need to run the DTS Task in this environment?

# November 23, 2006 6:04 PM

akfaulkner said:

You need to install Commerce Server on the machine that you plan to run the DTS Task on. So you need to have Sql Server installed and Commerce Server installed on that machine in order to run the DTS task.

Hope this helps and if you have additional questions, I would prefer you to post those in our forums here:

http://forums.microsoft.com/MSDN/default.aspx?ForumGroupID=294&SiteID=1

Hope this helps!

Alan

# November 29, 2006 2:38 PM

Willian Leite said:

Hi Alan,

To solve my problem I deleted and re-install the DW in default instace on SQL 2K5.

In my first installation all Database were installed in Named Instances, and I did the same to the relational DW database.

Now I have the relation DW and the analysis Databases on default instances and all works fine.

Thankz for your help,

See you in the Forum.

# November 30, 2006 9:44 AM

gamonie said:

I haven't gotten much done these days. So it goes. What can I say? I've just been letting everything pass me by. Basically not much going on lately, but it's not important. I've basically been doing nothing worth mentioning.

# April 10, 2007 10:01 PM

Leave a Comment

Title (required)

Name (required)

Your URL (optional)

Comments (required)

Remember Me?

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS