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:

A table in Oracle SQL Developer Data Modeler

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.

Search, filter, then Report

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:

XLS or XLSX, either format is just fine

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

The templates allow me to define what properties are included in the reports.

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.

Update the model from Excel

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

Voila!

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?

Really useful stuff here, thanks.
Maybe you can help me with a problem i have saving data modeler reports custom templates, i.e. i can’t.
I am using a virtualised 4.0.2.15 instance.
I can create a custom template and it is available so long as i have SQL Developer loaded, but if i exit and restart the tool my template is gone.
I assume SDL Developer is saving them to a folder within the virtualised environment which is destroyed when i exit the tool.
Is there anything i can do to have them saved to a local location.

All the current and new features of SDM are awesome.
Can you please shed some light on a nagging issue in the modeller that is still present in 3.3 EA?

It is that labels for relationship links do not keep their positioning. If you let the modeller ‘auto-route’ or use ‘straighten lines’ the labels align ok’ish, except that the lines end up behind entities or just becoming too unreadable.
So one adds a few elbows and reposition the lines manually. The labels reposition ok’ish at this event.
…BUT, once the model is saved and closed and later re-opened those labels are not where they were when saved. they now revert back to somewhere along the ‘line’ that the relationship link would have taken if ‘auto-routed/straightened’!

So now I have to go and revisit every manually repositioned link and open its properties window, just to click OK and the labels at least go back along the manual route.
Just wasteful that I have to do this EVERY time I open the model, even if it was just to print or or display it to someone.

This has been raised in the forums, but no reply to original poster or mine, yet.

I am using version 4.0.2.840 but the resizing of the labels is not preserved after saving and re-opening the model. They always reappear in their “original” place. Or is there any preference I can set?

I just tried it in 4.0.3 with a single label. I resized it larger, and it moved the label up and over to the left of the relationship line. I closed the design. I re-opened the design. It was where I left it…

It is in the Logical Model that the saved position of the relation is repositioned. When re-opening the file I actually see it respositioning the label. In the Relational Model, the respositoning of the KFK name is pertained. I then installed the 4.0.3 version but the same behavior is repeated. Strange indeed. So it must be a preference somewhere but I cannot see which one.

1 – Yes. I’m currently running 1.7.0_05 – note we officially don’t support Java7 yet, but you should be OK.
2 – I don’t know. It doesn’t require Excel, just a program that supports the XLS and XLSX formats. For this example I used Oracle Open Office Calc, and it worked just fine.