Feedback on SDG 1.0
jnahmias
Posts: 12
Well, we've got the 14-day trial in our shop and have been using it for almost a week now to try to generate realistic-looking data for some sample reports that we'd like to include in our sales/marketing materials for one of our new products. Overall, this was a very good 1.0 release -- of course there were bugs and features missing that would make doing "real work" much easier -- but there was enough advantage/power with the tool that we didn't just shelve it and do it manually -- so kudos to the developers and managers for achieving the necessary balance!
Here are some of the things we ran into (some with workarounds) in no particular order:
01 - If a foreign key is defined for a column, the generator is locked and I can't weight the values that get used.
02 - No way to coordinate name with sex/gender. That is, we'd like the sex column to be M if the name is a man's name and vice-versa.
03 - When doing a manual foreign-key lookup, the generator only uses each value once. Ended up using the SQL generator to do the lookup manually and used the loop option.
04 - Address generator doesn't auto-truncate the generated values. Ended up modifying the regexp to remove the larger values so that all values had a length <= 25 chars.
05 - Date/Time generator will only create values based on a column in the same table. I wanted it to look up a date in a related table and add between two and five days to it. Ended up using a post-script to do the lookup with DATEADD(Hour, RAND() * 72, col2).
06 - Needed to generate a sequence of numbers with a constant (string) prefix. Ended up using the regexp generator with 'PREFIX[0-9]{3}' and setting the column to unique and generating exactly 1000 rows.
07 - Wanted to have a column be one of two values based on the value of another column in that table. For example if c1 = 1, then c2 = 'A'; else if c1 = 2, then c2 = 'B'. Ended up hardcoding c2 to one value and using post-scripts to modify c2 based on c1 values.
08 - When you're typing a script into the "Edit Project" window, pressing ENTER exits/closes the entire window! Of course, ctrl-enter does what I want, but having enter close the window just leads to much frustration.
09 - The number of the scripts is off by one when viewing in the "Edit Project" window (starts at 1) and the post-generation report (starts at 0).
10 - The number generator fails with "Parameter value '1082.00' is out of range" for the following case: start = 999.00, end = 29999.99, increment = 83.00, column is type decimal(9,2).
11 - Wanted to use the number generator to generate values like c2 = c1 + rand(). Ended up doing this in a script.
That's all for now. We're close, but not done yet, so there may be a follow-up to this.
Kudos again on what seems to be a decent product and I hope this helps make it even better!
Here are some of the things we ran into (some with workarounds) in no particular order:
01 - If a foreign key is defined for a column, the generator is locked and I can't weight the values that get used.
02 - No way to coordinate name with sex/gender. That is, we'd like the sex column to be M if the name is a man's name and vice-versa.
03 - When doing a manual foreign-key lookup, the generator only uses each value once. Ended up using the SQL generator to do the lookup manually and used the loop option.
04 - Address generator doesn't auto-truncate the generated values. Ended up modifying the regexp to remove the larger values so that all values had a length <= 25 chars.
05 - Date/Time generator will only create values based on a column in the same table. I wanted it to look up a date in a related table and add between two and five days to it. Ended up using a post-script to do the lookup with DATEADD(Hour, RAND() * 72, col2).
06 - Needed to generate a sequence of numbers with a constant (string) prefix. Ended up using the regexp generator with 'PREFIX[0-9]{3}' and setting the column to unique and generating exactly 1000 rows.
07 - Wanted to have a column be one of two values based on the value of another column in that table. For example if c1 = 1, then c2 = 'A'; else if c1 = 2, then c2 = 'B'. Ended up hardcoding c2 to one value and using post-scripts to modify c2 based on c1 values.
08 - When you're typing a script into the "Edit Project" window, pressing ENTER exits/closes the entire window! Of course, ctrl-enter does what I want, but having enter close the window just leads to much frustration.
09 - The number of the scripts is off by one when viewing in the "Edit Project" window (starts at 1) and the post-generation report (starts at 0).
10 - The number generator fails with "Parameter value '1082.00' is out of range" for the following case: start = 999.00, end = 29999.99, increment = 83.00, column is type decimal(9,2).
11 - Wanted to use the number generator to generate values like c2 = c1 + rand(). Ended up doing this in a script.
That's all for now. We're close, but not done yet, so there may be a follow-up to this.
Kudos again on what seems to be a decent product and I hope this helps make it even better!
Comments
If I create/save a project, then add tables to the database, then reopen the project, the new tables are automatically selected for generating random data. This is BAD!
13 - After selecting the SQL Statement generator from the dropdown, the application freezes for some non-trivial amount of time (20 seconds or so). My guess is that it's trying to validate the sample text you have in there against proper SQL syntax.
14 - We're having a problem with the both the manual and regular Foreign Key Generators inserting NULLs into the referencing table. It's very seldom, doesn't affect every column that uses the generator and only about 1 in 1000 rows gets a NULL when it does, but we've had to add post-generation scripts to clean this up in a few places.
15 - The XML that is generated by SDG in the SQLGEN project files is all on one line. This makes it very unfriendly to deal with using text-based tools such as diff. I have to run it through to get anything remotely useful.
Lionel
Red Gate Software
I probably am having dificulty as it is 19:52 on a Friday
I will try to have a look into some of these issues next week ni a little more depth as some of the issues like number 14 should not happen at all. Have you definatly got the 'Allow null values' checkbox unchecked?
Lionel
Red Gate Software
Yes, exactly -- and if you reopen the project that was created before the tables existed in the db, and then click generate without noticing that they were automatically selected, SDG will cheerfully overwrite the data that I placed into the new tables when I created them.
Sorry, wrong generator. I meant the SQL Statement generator. That one doesn't even have a checkbox/percentage for NULLs.
16 - When using the Foreign Key Generator on a composite key, the "Set Unique" option is not working properly. We are getting multiple occurrences of the key being generated even though there are more than enough records in the parent table to provide unique tuples for the child table.
Thank you very much for all of the feedback! It has been really useful.
As Lionel mentioned, 1.1 should be out sometime this week, as a result a number of the items you have mentioned has already been fixed. For the others, we will look at each issue again when we are planning our next release.
I just wanted to comment on the other issues.
4) On the table generation settings, you can define what should happen if the data is invalid - for example too large for the column. I recommend that you switch this to truncate data instead of skip row which will solve your problem without having to modify the regex.
6) Could you suggest any alternatives on how you would have liked to solve this scenario? Do you think a different type of generator is required for example?
12) You can configure this behaviour in the application options, under project setup however I'm not sure this is the best place for the setting.
13) I am unable to replicate this. Have you configure the generator with any SQL text or connection setting?
16) Could you please confirm which Set unique option you mean? Is is the "All key values unique" option?
Once again thankyou for the feedback.
Ben
Glad to help.
Yes, but that would affect the entire row, correct? Regardless, that's probably a good idea.
I'd like to be able to specify a constant prefix (maybe, for bonus points, a selection of prefixes with weights) which would then have a number appended to it. The number should have the same options as your current number generator (sequential, random, step size, etc...) with the option to zero-pad it out to a certain length.
This could generate data like:
HMS00001
HMS00002
HMS00003
...
Never noticed that. However, I would argue that you probably don't need a setting. Just do it once when creating a new project, and then offer to do it when the "refresh schema" button is pressed.
No, I don't believe so.
Yes.