DTM Data Generator Project Report

Summary
Rule #1 clearing 13 table(s)
Rule #2 (dbo.Categories)
Rule #3 (dbo.CustomerDemographics)
Rule #4 (dbo.Customers)
Rule #5 (dbo.Employees)
Rule #6 (dbo.Region)
Rule #7 (dbo.Shippers)
Rule #8 (dbo.Suppliers)
Rule #9 (dbo.Territories)
Rule #10 (dbo.CustomerCustomerDemo)
Rule #11 (dbo.EmployeeTerritories)
Rule #12 (dbo.Orders)
Rule #13 (dbo.Products)
Rule #14 (dbo.[Order Details])
Named Generators
Variables

Summary

Report Attribute	Value
Report Created	05-Nov-2016 13:11
DTM Data Generator	2.00.12, Unicode, Enterprise
Project Description	Sales DB1
Project Author	Mike and Ann
Connection	SQL Server Native Client 11.0: .///NorthwindOld [Local Northwind DB]
Total Rules	14
Prologue SQL Script	--DBCC CHECKIDENT ('[AWBuildVersion]', RESEED, 0); --DBCC CHECKIDENT ('[HumanResources].[Shift]', RESEED, 0);
Execution mode	Create output files only

Rule #1, top

Clear Rule, 13 table(s)

Rule Item	Description
Table: dbo.[Order Details]	Clear Table
Table: dbo.Products	Clear Table
Table: dbo.Orders	Clear Table
Table: dbo.EmployeeTerritories	Clear Table
Table: dbo.CustomerCustomerDemo	Clear Table
Table: dbo.Territories	Clear Table
Table: dbo.Suppliers	Clear Table
Table: dbo.Shippers	Clear Table
Table: dbo.Region	Clear Table
Table: dbo.Employees	Clear Table
Table: dbo.Customers	Clear Table
Table: dbo.CustomerDemographics	Clear Table
Table: dbo.Categories	Clear Table

Rule #2, top

Generator for 'dbo.Categories', append
Rows mode: records="50000"
Transaction size: 500

Rule Item	Data Type	Description
Field: CategoryID	int identity	Incremental: start with 1, step is 1, each value use 1 times, engine call: $Inc(1,1,,,1)
Field: CategoryName	nvarchar(15)	By random data [1;15], engine call: $RString(1,15,5,0,0)
Field: Description	ntext	By random data [1;40], 10% nulls, engine call: $IfR(10,\NULL,$RString(1,40,5,0,0))
Field: Picture	image	By random data [1;40], 10% nulls, engine call: 0xX{1:80}

Rule #3, top

Generator for 'dbo.CustomerDemographics', append
Rows mode: records="50000"
Transaction size: 500

Rule Item	Data Type	Description
Field: CustomerTypeID	nchar(10)	By random data [1;10], Unique, engine call: $Unique($RString(1,10,5,0,0))
Field: CustomerDesc	ntext	By random data [1;4096], 10% nulls, engine call: $IfR(10,\NULL,$RString(1,4096,5,0,0))

Rule #4, top

Generator for 'dbo.Customers', append
Rows mode: records="50000"
Transaction size: 500

Rule Item	Data Type	Description
Field: CustomerID	nchar(5)	By random data [1;5], Unique, engine call: $Unique($RString(1,5,5,0,0))
Field: CompanyName	nvarchar(40)	From Value Library [Companies], engine call: $Lib(Companies,Name,40,0)
Field: ContactName	nvarchar(30)	By random data [1;30], 10% nulls, engine call: $IfR(10,\NULL,$RString(1,30,5,0,0))
Field: ContactTitle	nvarchar(30)	By random data [1;30], 10% nulls, engine call: $IfR(10,\NULL,$RString(1,30,5,0,0))
Field: Address	nvarchar(60)	By pattern 'N{4} $Lib(Streets)', 10% nulls
Field: City	nvarchar(15)	From Value Library [Cities], 10% nulls, engine call: $IfR(10,\NULL,$Lib(Cities,Name,15,0))
Field: Region	nvarchar(15)	From Value Library [Region], 10% nulls, engine call: $IfR(10,\NULL,$Lib(Region,Name,15,0))
Field: PostalCode	nvarchar(10)	By pattern 'NNNNN', 10% nulls
Field: Country	nvarchar(15)	From Value Library [Countries], 10% nulls, engine call: $IfR(10,\NULL,$Lib(Countries,Name,15,0))
Field: Phone	nvarchar(24)	By pattern '$NNN$ NNN-NNNN', 10% nulls
Field: Fax	nvarchar(24)	By pattern '$NNN$ NNN-NNNN', 10% nulls

Rule #5, top

Generator for 'dbo.Employees', append
Rows mode: records="50000"
Transaction size: 500

Rule Item	Data Type	Description
Field: EmployeeID	int identity	Ignore
Field: LastName	nvarchar(20)	From Value Library [LastNames], engine call: $Lib(LastNames,Name,20,0)
Field: FirstName	nvarchar(10)	From Value Library [FirstNames], engine call: $Lib(FirstNames,Name,10,0)
Field: Title	nvarchar(30)	By random data [1;30], 10% nulls, engine call: $IfR(10,\NULL,$RString(1,30,5,0,0))
Field: TitleOfCourtesy	nvarchar(25)	By random data [1;25], 10% nulls, engine call: $IfR(10,\NULL,$RString(1,25,5,0,0))
Field: BirthDate	datetime	By random data [1950-01-01;2020-12-31], 10% nulls, engine call: $RDate(YYYY-MM-DD,1950-01-01,2020-12-31) $RTime(HH:MM:SS)
Field: HireDate	datetime	By random data [1950-01-01;2020-12-31], 10% nulls, engine call: $RDate(YYYY-MM-DD,1950-01-01,2020-12-31) $RTime(HH:MM:SS)
Field: Address	nvarchar(60)	By pattern 'N{4} $Lib(Streets)', 10% nulls
Field: City	nvarchar(15)	From Value Library [Cities], 10% nulls, engine call: $IfR(10,\NULL,$Lib(Cities,Name,15,0))
Field: Region	nvarchar(15)	From Value Library [Region], 10% nulls, engine call: $IfR(10,\NULL,$Lib(Region,Name,15,0))
Field: PostalCode	nvarchar(10)	By pattern 'NNNNN', 10% nulls
Field: Country	nvarchar(15)	From Value Library [Countries], 10% nulls, engine call: $IfR(10,\NULL,$Lib(Countries,Name,15,0))
Field: HomePhone	nvarchar(24)	By pattern '$NNN$ NNN-NNNN', 10% nulls
Field: Extension	nvarchar(4)	By random data [1;4], 10% nulls, engine call: $IfR(10,\NULL,$RString(1,4,5,0,0))
Field: Photo	image	By random data [1;40], 10% nulls, engine call: 0xX{1:80}
Field: Notes	ntext	By random data [1;40], 10% nulls, engine call: $IfR(10,\NULL,$RString(1,40,5,0,0))
Field: ReportsTo	int	From table dbo.Employees.EmployeeID, 10% nulls, engine call: $IfR(10,\NULL,$Table(dbo.Employees,EmployeeID,0))
Field: PhotoPath	nvarchar(255)	By random data [1;255], 10% nulls, engine call: $IfR(10,\NULL,$RString(1,255,5,0,0))

Rule #6, top

Generator for 'dbo.Region', append
Rows mode: records="50000"
Transaction size: 500

Rule Item	Data Type	Description
Field: RegionID	int	By random data, Unique, engine call: $Unique($Rint(,,))
Field: RegionDescription	nchar(50)	From Value Library [Region], engine call: $Lib(Region,Name,50,0)

Rule #7, top

Generator for 'dbo.Shippers', append
Rows mode: records="50000"
Transaction size: 500

Rule Item	Data Type	Description
Field: ShipperID	int identity	Ignore
Field: CompanyName	nvarchar(40)	From Value Library [Companies], engine call: $Lib(Companies,Name,40,0)
Field: Phone	nvarchar(24)	By pattern '$NNN$ NNN-NNNN', 10% nulls

Rule #8, top

Generator for 'dbo.Suppliers', append
Rows mode: records="50000"
Transaction size: 500

Rule Item	Data Type	Description
Field: SupplierID	int identity	Ignore
Field: CompanyName	nvarchar(40)	From Value Library [Companies], engine call: $Lib(Companies,Name,40,0)
Field: ContactName	nvarchar(30)	By random data [1;30], 10% nulls, engine call: $IfR(10,\NULL,$RString(1,30,5,0,0))
Field: ContactTitle	nvarchar(30)	By random data [1;30], 10% nulls, engine call: $IfR(10,\NULL,$RString(1,30,5,0,0))
Field: Address	nvarchar(60)	By pattern 'N{4} $Lib(Streets)', 10% nulls
Field: City	nvarchar(15)	From Value Library [Cities], 10% nulls, engine call: $IfR(10,\NULL,$Lib(Cities,Name,15,0))
Field: Region	nvarchar(15)	From Value Library [Region], 10% nulls, engine call: $IfR(10,\NULL,$Lib(Region,Name,15,0))
Field: PostalCode	nvarchar(10)	By pattern 'NNNNN', 10% nulls
Field: Country	nvarchar(15)	From Value Library [Countries], 10% nulls, engine call: $IfR(10,\NULL,$Lib(Countries,Name,15,0))
Field: Phone	nvarchar(24)	By pattern '$NNN$ NNN-NNNN', 10% nulls
Field: Fax	nvarchar(24)	By pattern '$NNN$ NNN-NNNN', 10% nulls
Field: HomePage	ntext	By pattern 'www.aa{8}.com', 10% nulls

Rule #9, top

Generator for 'dbo.Territories', append
Rows mode: records="50000"
Transaction size: 500

Rule Item	Data Type	Description
Field: TerritoryID	nvarchar(20)	By random data [1;20], Unique, engine call: $Unique($RString(1,20,5,0,0))
Field: TerritoryDescription	nchar(50)	By random data [1;50], engine call: $RString(1,50,5,0,0)
Field: RegionID	int	From table dbo.Region.RegionID, engine call: $Table(dbo.Region,RegionID,0)

Rule #10, top

Generator for 'dbo.CustomerCustomerDemo', append
Rows mode: records="50000"
Transaction size: 500

Rule Item	Data Type	Description
Field: CustomerID	nchar(5)	From table dbo.Customers.CustomerID, engine call: $Table(dbo.Customers,CustomerID,0)
Field: CustomerTypeID	nchar(10)	From table dbo.CustomerDemographics.CustomerTypeID, engine call: $Table(dbo.CustomerDemographics,CustomerTypeID,0)

Rule #11, top

Generator for 'dbo.EmployeeTerritories', append
Rows mode: records="50000"
Transaction size: 500

Rule Item	Data Type	Description
Field: EmployeeID	int	From table dbo.Employees.EmployeeID, engine call: $Table(dbo.Employees,EmployeeID,0)
Field: TerritoryID	nvarchar(20)	From table dbo.Territories.TerritoryID, engine call: $Table(dbo.Territories,TerritoryID,0)

Rule #12, top

Generator for 'dbo.Orders', append
Rows mode: records="50000"
Transaction size: 500

Rule Item	Data Type	Description
Field: OrderID	int identity	Ignore
Field: CustomerID	nchar(5)	From table dbo.Customers.CustomerID, 10% nulls, engine call: $IfR(10,\NULL,$Table(dbo.Customers,CustomerID,0))
Field: EmployeeID	int	From table dbo.Employees.EmployeeID, 10% nulls, engine call: $IfR(10,\NULL,$Table(dbo.Employees,EmployeeID,0))
Field: OrderDate	datetime	By random data [1950-01-01;2020-12-31], 10% nulls, engine call: $RDate(YYYY-MM-DD,1950-01-01,2020-12-31) $RTime(HH:MM:SS)
Field: RequiredDate	datetime	By random data [1950-01-01;2020-12-31], 10% nulls, engine call: $RDate(YYYY-MM-DD,1950-01-01,2020-12-31) $RTime(HH:MM:SS)
Field: ShippedDate	datetime	By random data [1950-01-01;2020-12-31], 10% nulls, engine call: $RDate(YYYY-MM-DD,1950-01-01,2020-12-31) $RTime(HH:MM:SS)
Field: ShipVia	int	From table dbo.Shippers.ShipperID, 10% nulls, engine call: $IfR(10,\NULL,$Table(dbo.Shippers,ShipperID,0))
Field: Freight	money(19,4)	By random data, format '%19.4f', 10% nulls, engine call: $IfR(10,\NULL,$Rfloat(,,4,%19.4f))
Field: ShipName	nvarchar(40)	By random data [1;40], 10% nulls, engine call: $IfR(10,\NULL,$RString(1,40,5,0,0))
Field: ShipAddress	nvarchar(60)	By pattern 'N{4} $Lib(Streets)', 10% nulls
Field: ShipCity	nvarchar(15)	From Value Library [Cities], 10% nulls, engine call: $IfR(10,\NULL,$Lib(Cities,Name,15,0))
Field: ShipRegion	nvarchar(15)	From Value Library [Region], 10% nulls, engine call: $IfR(10,\NULL,$Lib(Region,Name,15,0))
Field: ShipPostalCode	nvarchar(10)	By pattern 'NNNNN', 10% nulls
Field: ShipCountry	nvarchar(15)	From Value Library [Countries], 10% nulls, engine call: $IfR(10,\NULL,$Lib(Countries,Name,15,0))

Rule #13, top

Generator for 'dbo.Products', append
Rows mode: records="50000"
Transaction size: 500

Rule Item	Data Type	Description
Field: ProductID	int identity	Ignore
Field: ProductName	nvarchar(40)	By random data [1;40], engine call: $RString(1,40,5,0,0)
Field: SupplierID	int	From table Table1.F1 [TestAccess/], 10% nulls, engine call: $IfR(10,\NULL,$Table(Table1,F1,0,,TestAccess,,))
Field: CategoryID	int	From table dbo.Categories.CategoryID, 10% nulls, engine call: $IfR(10,\NULL,$Table(dbo.Categories,CategoryID,0))
Field: QuantityPerUnit	nvarchar(20)	By random data [1;20], 10% nulls, engine call: $IfR(10,\NULL,$RString(1,20,5,0,0))
Field: UnitPrice	money(19,4)	By random data, format '%19.4f', 10% nulls, engine call: $IfR(10,\NULL,$Rfloat(,,4,%19.4f))
Field: UnitsInStock	smallint	By random data [;32766], format '%d', 10% nulls, engine call: $IfR(10,\NULL,$Rint(,32766,%d))
Field: UnitsOnOrder	smallint	By random data [;32766], format '%d', 10% nulls, engine call: $IfR(10,\NULL,$Rint(,32766,%d))
Field: ReorderLevel	smallint	By random data [;32766], format '%d', 10% nulls, engine call: $IfR(10,\NULL,$Rint(,32766,%d))
Field: Discontinued	bit	By random data [;1], format '%d', engine call: $Rint(,1,%d)

Rule #14, top

Generator for 'dbo.[Order Details]', append
Rows mode: records="50000"
Transaction size: 500

Rule Item	Data Type	Description
Field: OrderID	int	From table dbo.Orders.OrderID, engine call: $Table(dbo.Orders,OrderID,0)
Field: ProductID	int	From table dbo.Products.ProductID, engine call: $Table(dbo.Products,ProductID,0)
Field: UnitPrice	money(19,4)	By random data, format '%19.4f', engine call: $Rfloat(,,4,%19.4f)
Field: Quantity	smallint	By pattern '$$($Vars(@V2TO10)*$Vars(@V2TO10))'
Field: Discount	real	By random data [0;1], format '%f', engine call: $Rfloat(0,1,3,%f)

Named Generators

Generator Name	Description
CATEGORY	By random data [1;15]
DESCRIPTIONS	By random data [1;4096], 10% nulls
IMAGES	By random data [1;4096], 10% nulls

Variables

Variable Name	Description
#FROM	Constant = 10
#TO	Constant = 50
#STEP	Constant = 3
@V2TO10	Pattern = $Rint(2,10)