As you may have heard last week, we have a new version of Oracle SQL Developer Data Modeler now available as an Early Adopter release. Version 3.3 has quite a few new features and I’ll be previewing them here.

Today’s topic is our new Excel integration. It builds off of last week’s lesson: Search, so you may want to go read that first.

They say it takes a village to raise a child. I say it takes a team to build a data model. You have your techie folks, your business folks, your in-betweeners, and your database geeks. Who gets to define how customers are represented and stored in your database? That data lives forever, so you better get it right from the beginning, or you’ll be living in a hacker’s paradise for years to come. Lots of good rantings, ravings, and advice on this topic in general on Karen Lopez’s (@datachick) blog.

But let’s say you are the primary modeler on a project. You dutifully interview the business folks for their requirements. You sit down and start to model and think you’re pretty close. Now you need someone to confirm your assumptions and provide some feedback. Do you send your model over? Take a screenshot and blow it up on a whiteboard? Export to HTML and let them take a magic marker to their monitors? Or maybe you bite the bullet and install your modeling software on their desktops and take the hours or days required to train them up on how to use the the tool.

Wouldn’t it be nice if they could just mark up their corrections in Excel and let you suck the updates back in?

This is what we have started to build in Oracle SQL Developer Data Modeler.

Let’s say you have a new table called ‘UT_STARTUPS.’ It looks a little something like this:

What I would like to do is have my team or co-worker review how I have defined those columns. Perhaps TIMESTAMP is overkill or maybe the column names themselves aren’t up to snuff.

What I am going to do is now search for all the columns in my table, then export that to Excel.

So do a search for UT_STARTUPS.

With the filter set to ‘Columns,’ if I do a report I’ll be only getting the columns that are resolving to my search term. So as long as my table name is unique in the model, I should get what I’m looking for.

Here’s what I see when I click on the Report button:

I want to decide how the Column data is exported to Excel though, so I’m going to create a report template that I can use going forward.

So click the ‘Manage’ button and setup a new template. I’m going to call mine ‘CollaborativeDevelopment.’

Once this is set, I’ll have the XLS file generated, and get to work

Now let the Excel junkies do their stuff

Note that not ALL of the report properties are update-able (yes, I made up a new word there) via Excel. We’ll have the full list of properties documented going forward, but in my Excel sheet, note that I can’t change the table name or the data types for the columns.

Here’s my input for the designer/architect/database dude:

Save the file, email it back to your modeler.

Update the model from Excel

If everything goes right, you’ll see a nice confirmation message:

Another to-do item on tap – making this dialog more informative. We’ll be showing exactly what in your model was updated from Excel.

Let’s take another look at the model now

Why are we doing this again?

The goal is to reduce the number of round-trips from the modeler and the business process owner. One is used to working with Excel – why not allow them to mark up their changes in the tool they already know?

This is an early adopter release and I anticipate this feature getting a good bit of tuning up before we release. Why don’t you download 3.3, give it a whirl, and let us know what you think?