Sep 15 2009
Good Sample Databases Can Be Hard To Find
It’s really hard to make up reasonable fake data. Or maybe it’s just hard for me, because I have tried and not been very successful. 🙂 For this reason I have typically used eFashion or Island Resorts Marketing for my various technical tips and conference presentations. They’re small enough to be speedy during conference demonstrations, but complex enough to be interesting. For the conference coming next month I am also going to use the “Motors” database provided in the designer training class. I have converted these databases to Oracle because it allows me to have more fun. Microsoft Access is easy and portable but doesn’t provide all of the features that I would like.
Sometimes, though, I want more.
Because of this desire to have more or at least different data to use, I have gone looking for some alternatives. One that I found and started using years ago was the old Oracle demo database for Summit Sporting Goods. I like this database because even though it is very simple and small it still has a lot of good challenges to solve. Would you believe you could have a loop with one table and one join in a database? Can you have a fan trap with only two tables? The answer to both of those questions is “Yes, you can.” That means Summit Sporting Goods makes a great demo database, especially for universe design.
Ultimately, though, I wanted a really big database (bigger than eFashion) that I could use for presentations or technical tips. Instead of trying to make something up, I have captured (with permission) some data from a client. Everything about the database will be changed except for the rows and relationships. Product and customer names as well as any identifying attributes will be altered. General invoice data as far as dates, line items, and as mentioned before the relationships to other dimension tables will remain the same. Quantities and dollars will be modified up or down by a random factor for each row. The final result will be a snowflake schema because I like some of the extra features that I can talk about with that sort of structure. The raw data was in Microsoft Access to start with but the final version will end up in Oracle.
Is anyone wondering yet what the point is for this post? 😉
I am interested in finding out if folks have an opinion as to which – if any – of these databases I should make available for download here on my blog. My original intent was to make them available in Oracle format only. The main reason for that has been given above (it’s a better database for showing various techniques). The secondary reason is that I already have everything in Oracle so I don’t have to do any additional work.
I am not committing to doing anything just yet. 🙂 But if anyone has an opinion or concern one way or another about this idea, please feel free to leave a comment and share your thoughts. Thanks in advance.
What about SQL Server or any other DBMS?
If possible could you publish an database schema script in ANSI92 SQL and some load scripts so other DBMS platforms could use the examples as well?
Sir Dave,
Being a beginner, Im quite satisfied of how eFashion and IRM (Island Resorts Marketing)work for me at first but as the learning process becomes deeper and deeper I find the need for a more large and complicated databases to play with.
I say its a good idea sharing your database but its much better if it is available in different formats (not just in Oracle) to be used by people with different levels of knowledge. (and hope we will not get into some legal trouble with that)
Anyway, I just want to tell you that you have a very good site, and it helped me a lot being a beginner. actually im currently being assigned to create some sample activies (basic to “not so” advance) in Webi for our company’s training purposeswhich I know i shouldnt be because of my lack in experience but because our senior consultants are not available they threw me in. and I used eFashion and IRM databases for those activities so your idea of posting some new DB would be a big help (obcourse i wouldnt use it as a sample database for our activity but rather for my personal use only.)
Thanks Dave and more power 😀
I say leave them as Oracle. Most of the major DBMS vendors have conversion tools that folks who want a different version can use to convert them.
I may use the suggestion from the first comment… getting SQL scripts should not be too much trouble that that way any database is supported.
I’d definitely be interested in more sample data. I’ve started building a demo virtual machine, and one thing that is lacking is better sample data. I use SQL Server 2008 though, but if you’d like, I can do the SQL Server conversion for you.. 🙂
– Josh
Its really apreciatable and being a beginner i would definitely want to experiment with various formats .If there are any effective sample databses for download could some one aware of it , please let me know in here
Add me to supporting the first suggestions – I have easy access to any number of databases, but Oracle is not (regrettably) among them. More importantly, I work in a big shop and am not the DBA, so I don’t have access to conversion tools…
Robert Metzker wrote the scripts that I started with for creating Oracle versions of Island Resorts and eFashion. They were posted in the BOB’s Downloads area over five years ago. If you were not aware that those scripts are available, here’s a link.
Oracle SQL Scripts for Island Resorts and eFashion
Hi,
Rather than a sample database I am more interesting in a blog where you could describe in more details this part:
“Would you believe you could have a loop with one table and one join in a database? Can you have a fan trap with only two tables? The answer to both of those questions is “Yes, you can.””
🙂
Thank you.
Hi, Marek, thanks for the input. I am always looking for ideas for new blog posts.
Great Idea…I am working on a good Data Services Demo..and all of the demo databases are so small..that the jobs finish way too soon..
Something that I have been working on is a true “Rapid Mart” for Sales Performance..starting with a generic transactional system…all the way through to xcelsius dashboards, universes, dashboards, and standard reports…some good depth of invoices/customers/products/territories would be amazing…looking at combining all sources I can find into one … I’ll let you know.
From a source standpoint…Sql Server…or even MySQl…since that is included with Bobj, so everyone should be able to get there hands on it…and migrate to whatever from there using standard tool…or gee…even Data Services..LOL..
Thanks for all your support of BOB…
Stu
I have generated SQL scripts to create and load the “Motors” database. I modified some of the table names and fixed some of the data so that things balance. I have a bit more testing to do and then will post it. The higher volume data is still being worked on.
Hi,
Can you please send me the Oracle data to play around with BO.
Hi, when the data is ready it will be posted on my blog, thanks. You can download Oracle scripts for eFashion and Island Resorts from BOB already.
Dave, I am facing many errors with the Oracle scripts for eFashion available on BOB and the eFashion Universe. Would you mind sharing your eFashion universe sitting on top of Oracle?
Thanks.
i just posted this on BOB but wanted to share it here as well. Hope you don’t mind. http://www.forumtopics.com/busobj/viewtopic.php?t=22133