Adventure Works DW Script

Adventure Works DW Script

• Smokie

  I am one of the authors for the Data Mining Tutorial. While recent versions of the tutorial have been updated to reference a ProspectiveBuyer table (which does exist in the TOC), the June CTP release did not inlcude this update. I am tracking down the script for that CSV and will post it soon.
  
  Thanks,
  Seth
  
  This posting is provided "AS IS" with no warranties, and confers no rights.
  

  
  

• Juan Wajnerman

  I am a novice sql server 2005 user and I want to go through the data mining tutorial. I have the adventure works db and am attempting to follow the instructions on create datasource view. I sort of understand this email thread, but not enough to make it so that I can complete the steps to accomplish the task.

  I do not understand the following: Just put the location of the csv in @data_path and make sure the name of the csv is correct.
  
  When I execute the create table script in query analyser, I recieve an error that I have an invalid object name: Adventureworksdw.dbo.Prospecitve Buyer
  

  Can you please explain what I need to do so that I can complete the steps for creating the data source view

  Thanks,

  Zena

  
  

• JohnHennesey

  Here is a script that should work. Just put the location of the csv in @data_path and make sure the name of the csv is correct.
  
  CREATE TABLE [dbo].[ProspectiveBuyer] (
      [ProspectAlternateKey] [nvarchar] (15) NULL ,
      [FirstName] [nvarchar] (50) NULL ,
      [MiddleName] [nvarchar] (50) NULL ,
      [LastName] [nvarchar] (50) NULL ,
      [BirthDate] [datetime] NULL ,
      [MaritalStatus] [nchar] (1) NULL ,
      [Gender] [nvarchar] (1) NULL ,
      [EmailAddress] [nvarchar] (50) NULL ,
      [YearlyIncome] [money] NULL ,
      [TotalChildren] [tinyint] NULL ,
      [NumberChildrenAtHome] [tinyint] NULL ,
      [Education] [nvarchar] (40) NULL ,
      [Occupation] [nvarchar] (100) NULL ,
      [HouseOwnerFlag] [nchar] (1) NULL ,
      [NumberCarsOwned] [tinyint] NULL ,
      [AddressLine1] [nvarchar] (120) NULL ,
      [AddressLine2] [nvarchar] (120) NULL ,
      [City] [nvarchar](30) NULL,
      [StateProvinceCode] [nvarchar] (3) NULL,
      [PostalCode] [nvarchar](15) NULL,
      [Phone] [nvarchar] (20) NULL,
      [Salutation] [nvarchar] (8) NULL,
      [Unknown] [int] NULL
  ) ON [PRIMARY];
  GO

  --Import Prospect.csv
  DECLARE @data_path NVARCHAR(520),
          @DatabaseName NVARCHAR(100)

  SET @data_path = 'c:\'
  SET @DatabaseName = 'AdventureWorksDW'

  EXECUTE (N'BULK INSERT ' + @DatabaseName + '.[dbo].[ProspectiveBuyer] FROM ''' + @data_path + N'Prospect.csv''
  WITH (
     CODEPAGE=''ACP'',
     DATAFILETYPE = ''char'',
     FIELDTERMINATOR= '','',
     ROWTERMINATOR = ''\n'' ,
     KEEPIDENTITY,
     TABLOCK  
  )');
  
  Let me know if you have any more questions,
  Seth
  
  This posting is provided "AS IS" with no warranties, and confers no rights.
  
  

  
  

• Prathap

  Thanks for the response. However the data won't load. An example from the csv for the Prospect.csv for the first row follows:
  
  27 403 5120  Barbara   Raje 0 1945-05-17 00:00:00 M  F  20000 4 0 High Schoo Skilled Manual 1 2 3532 Premier Pl.  1 (11) 500 555-0155 
  
  Obviously the FirstName is not the second column as noted in the script to create the table, and the data won't load as a result. The first three columns are any one's guess as to the field names. Also despite the various address fields in the script, there doesn't appear to be any data other than a single address field in the csv etc.
  
  Regards,
  Baz Star
  

• Kathie351

  I have covered the correct steps to get, install and attach AdventureWorksDW database in the thread below, Adventureworksdw.dbo.Prospecitve Buyer is the last table in the 26 tables database. Hope this helps.
  

  http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1894422&SiteID=1

  
  
  
• Adventure Works DW Script