v15 - Database builder - best practice?

[Paolo F Cantoni]

Sorry for the extremely long post, but hopefully the topic will help others.

I've only recently (within the last month) started using the Database Builder.  I've only used it with v15.
I'm striking some issues (possibly EAUI) that are of concern.  So I'm asking for people's thoughts on what might be best practice.

The last time I did serious DB work in EA was nearly a decade ago and I built my own add-in to get around many issues present in EA at the time.

In my new enterprise, we are going to use EA to help model and manage our Datawarehouse.  The warehouse design predates my arrival but is a sound design.  On the same server, we have three DBs (SQL Server)  the Raw (Landing DB) the Staging DB and the final Dimensional Warehouse.  Data flows into the Landing DB as clones of the source system tables. The data is then moved to the Staging DB where business logic is applied and (Staging versions of) the Fact and Dimension tables are created and finally, the Staged data is moved to the final dimension warehouse.  The Landing and Final DBs are "dumb" having very few data manipulations.  ALL the heavy lifting is done by the Staging DB.'

The table structures and schemas are the same in the Staging and Final DBs (the schemas are qualified by [stg\] and [\] respectively.  Landing (Raw) DB schemas (which mirror the source systems) are prefaced by [raw\].  All this is to paint the picture of the environment we intend to use the Database Builder.

Because all the DBs are on the same server the connection in the Database Builder appears the same.  It can't seem to distinguish between the three DBs.

In our EA repository we have set up 4 sets of DB structures:  The Conceptual Model, the Logical Model and two Physical models, the Design Model (to forward engineer) and the Implementation Model reverse-engineered from the actual DB via ODBC.

In creating this repository we started by using DB Builder to create the Implementation Model.  We reverse-engineered each schema separately - into its own folder. Since Database Builder couldn't distinguish between each DB, we ended up with some 50 schemas - each with its unique name.

This was a laborious task.  But once done, we thought we'd be able to make good progress.  We exported the Implementation Model and re-imported it as the Design Model. Then as the Logical Model and finally as the Design Model.  We made appropriate transformations to the datatypes in the Logical and Conceptual Models and linked the tables across the 4 models.  Thus if you select one table in one model and "insert related elements", you'll get links to the other three.  So far, so very good!

For a week or so we worked both via the EA UI (but primarily) by direct SQL queries on the repository to achieve our ends.  We were able to use the Database Builder to look at each Model as a separate "entity".  We could select one of the models and "Database Builder" was available on the context menu.  We put the repository aside for a week while we got on with other tasks.  In the meantime, I think we went from beta/release candidate to official release of v15.

Today we opened the repository and to our horror, when we opened Database Builder, we got ALL four models aggregated as a single browsable list.  Because all the schemas are replicated to all four models, they appeared in quadruplicate!  All you see is the schema name. To find out which model it belongs to you have to load the schema then right-click (there's no keyboard shortcut as far as I can see) to get "Find in Project Browser" THEN scroll up (remember there are 50 of these) to find which model the one you've just loaded is in!

To add insult to injury, each set of "quads" appears to have been ordered differently in the Database Builder browser!  You potentially have to open each one of the 4 to find the one you're after!

Should the Database BUilder behave this way?  Is our use case a valid use?  It seemed to work before.  What might we have done (say, behind the scenes) that might have caused this behaviour?  Remember as fas as I can recall, it was working after we made all our manipulation, but before we went to the production release of v15.

Any assistance REALLY appreciated...

Paolo

[Geert Bellekens]

Paolo,

Why can't the database builder distinguish the different databases on your server? Are all tables actually in one database but with another schema name? (standard tables are in the "dbo" schema). I'm not sure what the benefit would be to put three databases into one, with different schema's, but I'm not a DBA.

Because when you have actually separate databases then EA will import them as separate, even if they are on the same server.

Maybe that can be a workaround. If you create separate databases (only the structure will suffice) for each of the schema's then you can import and manage them separately in EA.  Schema's are indeed somewhat hidden in EA.

Geert

[Paolo F Cantoni]

Paolo,

Why can't the database builder distinguish the different databases on your server? Are all tables actually in one database but with another schema name? (standard tables are in the "dbo" schema). I'm not sure what the benefit would be to put three databases into one, with different schema's, but I'm not a DBA.

Because when you have actually separate databases then EA will import them as separate, even if they are on the same server.

Maybe that can be a workaround. If you create separate databases (only the structure will suffice) for each of the schema's then you can import and manage them separately in EA.  Schemas are indeed somewhat hidden in EA.

Geert

Hi Geert,

THAT's one of the issues.  They ARE separate DBs on the Server!  I was as surprised as you, but since I'd never used DBB before I assumed that's the way it worked.  We ARE discussing EA(UI).  The only "funny" is that the server is in Azure.

Somewhat annoying as that is, that's not the major problem.  We could live with that.  It's the quadrupling of the 4 models (with inconsistent ordering) into one list that's the REAL issue here.

Paolo

[EDIT:  Looks as though we may have solved the issue of the "Database Builder" option not being available on the context menu.  Somehow (I'm pretty sure it wasn't us - at least NOT intentionally) the EAUML::Database stereotype was removed from the Database level folder.  Restoring that has restored the context menu option.  Doesn't seem to change the DBB display though. ]

[Edit2:  Spoke too soon, Batman!  The inclusion of the "Database Builder" option on the folder context menu (seems) is inconsistent!  I have replaced the stereotype on the four model folders but only two of them show the option.  The two that don't are grouped under an intervening grouping folder (Physical Data Models).  The grouping folder doesn't show the stereotype and yet shows the "Database Builder" option!  EAUI?  Confirmed!  Moving the physical model folder up one level restores the "Database Builder" option!  The option appears to be applied to the folder level below the EAUML::DataModel folder.  It's a bug, !]

[Paolo F Cantoni]

With some assistance from Sparx, we've realised what we did wrong and why we are seeing what we are seeing.

I'm developing a mechanism to convert what we currently have to what we need to manage the database models on an on-going basis.

However, one question that eludes me.  How does the Database Builder (DBB)  determine that a folder is (contains) a Database and include it on its list?

We've removed the stereotypes from some of our DB items and some DB have now "dropped off" the list, but others, where we have seemingly done the same process, have not.  Can anyone enlighten us?

TIA,
Paolo