Joined: 25 Mar 2009
|Posted: Fri Aug 02, 2013 10:42 pm Post subject: Foreign Key Generator - request
I'm trying to use SQL Data Generator (version 220.127.116.11) to generate extra data for the FactResellerSales table in the AdventureWorksDW2012 sample database. But in order to make it match the pattern of the rest, I find that some features are missing from Data Generator.
One of them is for generating the columns OrderDateKey, ShipDateKey, and DueDateKey. These are foreign keys into the DimDate table, but they are always equal to their datetime counterparts (OrderDate, ShipDate, DueDate) - except the foreign keys are represented as an integer in yyyymmdd format.
These values are very easy to compute from the OrderDate, ShipDate, and DueDate columns. But there is no option for this in Data Generator. In fact, there is no choice for the generation method at all for foreign key
columns - even if I were able to write Python, I'd still be unable to use that skill for these columns.
I first thought this is a very unusual request and was hesitant to make it, but then I realized that in a data warehouse, this is not an uncommon design. So everyone who needs to generate test data for a DW would run into this problem.
Maybe RedGate can consider adding a feature for this?
Thanks in advance!
(SQL Server MVP and Friend of Red Gate)