Feedback on SDG 1.0

The simplest way to populate your database with test data.

Moderators: David Atkinson, Anu Deshpande, Lionel

Feedback on SDG 1.0

Postby jnahmias » Thu May 01, 2008 3:55 pm

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!
jnahmias
 
Posts: 11
Joined: Wed Apr 30, 2008 6:33 pm
Location: NYC

Another problem

Postby jnahmias » Thu May 01, 2008 5:53 pm

I just ran into another thing:

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! :)
jnahmias
 
Posts: 11
Joined: Wed Apr 30, 2008 6:33 pm
Location: NYC

One more issue

Postby jnahmias » Thu May 01, 2008 10:23 pm

I guess I'm up to 13 now...

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.
jnahmias
 
Posts: 11
Joined: Wed Apr 30, 2008 6:33 pm
Location: NYC

Yet more issues (one a BUG)

Postby jnahmias » Fri May 02, 2008 7:22 pm

OK, two more:

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
Code: Select all
sed -re "s/<[A-Za-z]/\\n&/g"
to get anything remotely useful.
jnahmias
 
Posts: 11
Joined: Wed Apr 30, 2008 6:33 pm
Location: NYC

Postby Lionel » Fri May 02, 2008 7:45 pm

Thanks a lot for the feedback. I will get thouse entered into our bug tracking system and hopefully we will be able to get a whole lot of them fixed. If you find any other problems them please add them to the list. Again thanks a lot for reporting all thouse issues. It really helps us know what things to work on next in the product.

Lionel
Software Engineer
Red Gate Software
Lionel
 
Posts: 155
Joined: Fri Dec 16, 2005 11:09 am

Postby Lionel » Fri May 02, 2008 8:00 pm

I should add that we will be shipping a 1.1 version probably sometime next week that will fix issues 1 and 9. Also sorry but I did not understand what you mean by.

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! Smile


I probably am having dificulty as it is 19:52 on a Friday :). Do you mean when you add new tables they are selected as part of the generation?

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
Software Engineer
Red Gate Software
Lionel
 
Posts: 155
Joined: Fri Dec 16, 2005 11:09 am

Re: Feedback on SDG 1.0

Postby jnahmias » Fri May 02, 2008 8:57 pm

Lionel wrote:Also sorry but I did not understand what you mean by.

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!


I probably am having dificulty as it is 19:52 on a Friday :). Do you mean when you add new tables they are selected as part of the generation?


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. :(

Lionel wrote: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?


Sorry, wrong generator. I meant the SQL Statement generator. That one doesn't even have a checkbox/percentage for NULLs.
jnahmias
 
Posts: 11
Joined: Wed Apr 30, 2008 6:33 pm
Location: NYC

Another BUG

Postby jnahmias » Fri May 02, 2008 9:56 pm

Just noticed another problem:

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.
jnahmias
 
Posts: 11
Joined: Wed Apr 30, 2008 6:33 pm
Location: NYC

Postby benhall » Tue May 06, 2008 3:07 pm

Hello,

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
benhall
 
Posts: 69
Joined: Fri Dec 14, 2007 11:15 am

Re:

Postby jnahmias » Tue May 06, 2008 5:42 pm

Hi Ben,

benhall wrote:Thank you very much for all of the feedback! It has been really useful.


Glad to help.

benhall wrote: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.


Yes, but that would affect the entire row, correct? Regardless, that's probably a good idea.

benhall wrote: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?


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
...

benhall wrote: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.


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.

benhall wrote:13) I am unable to replicate this. Have you configure the generator with any SQL text or connection setting?


No, I don't believe so.

benhall wrote:16) Could you please confirm which Set unique option you mean? Is is the "All key values unique" option?


Yes.
jnahmias
 
Posts: 11
Joined: Wed Apr 30, 2008 6:33 pm
Location: NYC


Return to SQL Data Generator 1

Who is online

Users browsing this forum: No registered users and 0 guests