Change Management With ER/Studio Data Architect

ER/Studio Data Architect

Hello, and welcome to this demonstration on managing change using ER studio data architect to begin, we will connect to the ER studio repository and check out our model. We will then create a named release. This will give us a safe point in case we need to roll back the change. At any time, we will make changes to the logical model. Migrate those changes to the development physical model, generate our change script for enacting the change on the development database and use the included SQL utility to enact that change on the development database and then generate a change report and SQL change file between the development model and the production model for sign-off. After getting approval, we will update the production model with the change. So let's get started.

Data Repository

We have a model already checked into the repository and to get that we simply need to connect to our repository and check out our diagram. Just click repository in the main menu and select login and to your login, credentials and press. OK. Now we can get our diagram from the repository we select the diagram we wish to alter and press. Okay.

Logical Data Model

Now we can check out the model by right clicking on the model name and selecting checkout diagram and optional checkout diagram. Dialogue comes up, giving us the ability to enter a change, record and description. If we choose, if our repository is connected to JIRA, we can also select the appropriate tasks created for this change. Once done, click, okay. All the lock icons have been changed to unlock icons showing us that everything has been checked out successfully.

And we can now make our change. We'll create a named release, which is the same as creating a version that we can later roll back to in case the change tests out or the requirements change type in the release name, and maybe a description and press okay. In the logical model, we will add a title field to the middle of the department table. Now we can push that change to the physical model by right-clicking the model and choosing compare and merge. We will leave the default of compare against another model in this DM one file and click next.

Compare Data Models

Now we select the model we wish to compare. And in this case, it's the main model in HR DB dev click. Next defaults are fine here. So click next. They're also fine here. So click next again.

Now we'll be showing the differences between all of the objects. As you can see, the department table is one of those with changes and drilling down. We can see exactly what is different and we can then choose which direction we wish to push that change. Click finish when done

Migrate Changes to the

Physical Data Model

now our logical and physical HR DB dev models are up to date with the change clicking the HR DB dev model. We can see that the new field is represented. Now we can push that change into our HR DB dev database on the server to do this. We simply right click the HRD dev model, and again, choose, compare and merge this time. We will be sure that the radio button aligning with compare and merge against the live databases selected click. Next, we will enter our database connection, credentials and click next.

Now we can select the appropriate database and click. Next defaults are fine here. So click next. They're also fine here. So click next again. As before we'll be shown the differences between all of the objects and as before the department table is also shown and drilling down, we can see exactly what is different and we can then choose which direction we wish to push that change. Once that is finished, we have two options to execute the cul. We can execute it directly from the included SQL utility, or save the file to a file location and run it from our database interface like SQL management studio, as this is a development database, I have jurisdiction over it. So I will execute this change directly from the SQL utility. I've previously created a DNS file for connecting. So we only just need to select it.

Run SQL Code

And after checking that the code is good, run the SQL code. Now my logical model development, physical model and subsequent databases are all aligned. The testing can be performed on this database. If the testing has gone well, we can then compare the development model to the physical model, to generate a change report. This can then be shared with the project leaders for oversight and sign off on the proposed change. We will, again, right-click the development model and select compare and merge this time. We will be sure that the radio button next to compare against another model in this DM one file is selected and click next. Now we select the model we wish to compare. And in this case, it's the main model in HRD DB prod click.

Next this time, we will click the checkbox to generate SQL for changes, merged to target model. And since we don't want the change in the production model yet we'll uncheck the box to update target model. When merge is executed, click next. Since we know that we may be doing this again, to move the change into the production model, we're going to save the settings to make the second time much faster click use file based quick launch settings and click save as select the file location and specify a name for the file. When finished click save click next. Now we'll be shown the differences between all of the objects. Again, the department table is shown and drilling down. We can see exactly what is different and we can then choose which direction we wish to push that change.

Generating a SQL Report

Once done, we click the report, choose location and name of the report and the report type. I prefer the look of the HTML document. So let's choose that and click, okay. Let's take a look at the report. Looks good. Now I can put that out on a share or email it up the chain of command for approval. Getting back to the compare and merge utility. We press next to generate the actual SQL code for an acting the change on the database, which we will also submit for DBA sign off. Once approval has been given. We can then move the change into the production model. This time we will use the quick launch file. We created to move things along a bit faster. Click next click next on check the generate SQL for changes.

Since we already created the SQL file for the DBA click next and click next again, and set all resolutions that were approved to merge into the target. Click finish now provided the DBA, ran the SQL file. We submitted all changes have been migrated from the logical data model through the other model and into their subsequent databases for this version. 1.0 release, you should now be comfortable managing changes to your models and subsequent databases using ER studio, happy modeling.