Wide World Importers & AdventureWorks – Meet the Senior-Manager Boss

Well, I should first note that I intended to blog more than I have, but was struggling to find the right topics that didn’t involve any ‘woe is me’ complaints struggles. However, then I found inspiration in talking to others about steps when I got started in trying to work on my technical skills in my time away from work (mostly T-SQL and some Python would be covered), and how to cover the basics for a lot of people looking to get started.

One situation I run into more frequently than I previously wanted to admit is being unable to explain the most basic of concepts after I do them. Part of it, as I came to realize, was not allowing myself to practice what I learned outside of the office anymore. By the end of last year, I figured out that I needed to actually use a home database if I was going to perfect my technical chops, let alone speak on a subject in front of an audience where examples are crucial. So my first thought is…what about the constructs of Microsoft’s own sample databases? There’s a new one for 2016, and I had to get it, and post some rudimentary thoughts.

Get the database file, of course

I figured this was a time to trace my steps and add my first database in my shiny new Developer edition instance. So where can you find Wide World Importers? Here it is on the shiny GitHub page. There is both a transaction backup file and an analytical backup file (WideWorldImportersDW-Full.bak). I downloaded both, and moved the extended backup files to my local backup folder. In my case the extension was Microsoft SQL Server\MSSQL13.[instance name]\MSSQL\Backup. My thought is that it’s an easier spot to keep the originals. I read that some folks advised to place it directly in the C: drive, however, and there may be a good reason for me to do so in the future.

So within SQL Server Management Studio, I decided to use the commands rather than the RESTORE DATABASE command. I am not at all DBA level (show compassion for we little developers, peeps), but pretended to be one by asking to restore a database. There are instructions on the Microsoft site, but I’ve got pictures for how I followed along (also because I couldn’t get video to happen).

WWImporters - Restore Database
Yes, the Restore Database command was the droid I was looking for in this particular instance.

Once at the backup screen, I got the database loaded pretty quickly. The key is looking for a file after clicking the Device radio button. Also, the backup folder appeared instantly when I clicked Add and it allowed me to easily choose the database.

WWImporters - Select Device.jpg

Well, hitting okay a bunch of time allowed for a very quick “restore” of a database I never had to start. Then I was able to do the same for the DW/analytical version, and I even put AdventureWorks2014 in there solely to be experimental. Had no problems with a 2014 database brought into a 2016 system, in case some of you were like me some time ago and thought compatibility issues could occur if you don’t set it to 2016 in advance.

I should also note that I used the -full version because I have Developer edition, and -full works on that and everything Enterprise. If you don’t have one of those editions, you’ll need to stick with -standard.

Is Wide World Importers special in any way?

It’s hard for me to say while toying around with the DB so far. The business has changed slightly, including more tables based on delivery locations. However, some of the big differences to me are more about the DB practices and configuration for new SQL Server 2016 features.

I immediately noticed many more system-versioned tables (temporal, maybe?) in this edition. Those are the clocks in the corner of each table. maximized the next level, and there was a history archive. Pretty cool that it’s finally come over this way.

WWImporters - System Version Tables
What’s up with the clocks? Now they can find out where I messed around and I can’t cover my tracks anymore?

Even some of the code itself is more detailed and also slightly different in format, in the way lines are split up. I do still notice cursors, which will show others how to do it…but I wonder if all those folks who convinced me of the badness of cursors would take issue. The views are pretty simplistic to say the least, and may have some use considering they are concentrated to three areas.

The analytical database this time uses dimension, fact, and integration (staging tables) as the prefixes. I think it’s an easier way to teach folks about the data warehouse schema by using these tables. The schema is also set up that way, with fact tables having many a foreign key and the dimensions having identities across the board that link accurately. I even saw a proc called GetLastETLCutoffTime, which gave me ideas that I can bring to my day job for some of what we run during our off hours.

AdventureWorks won’t be updated any more, but it really feels more so like WideWorldImporters is a promotion of sorts, with more integrated features and better key systems. The documentation is about the same, but the data itself is improved. For people using this edition longer than I have, fresh data is a good thing, I’m sure.

Hold up…weren’t you going to mention more about 2016 features?

Oh yeah…those 2016 features. I notice that I haven’t yet tried to stretch the database, but it appears this one is configured so that it can be done. Same goes with R Services, which I’ve only scratched the surface on when using a release candidate.

I’ll have to go into more detail in a second part once I play around with these features, and maybe do a comparison against other public databases that are SQL Server compliant. Always learning, you know.