Video : ER/Studio Data Architect

Transcript

00:08

Enterprise Data Architecture

Hello everyone and welcome to this webinar on connecting the developer community in an Enterprise Data Architecture. The developer community at large has always been an invaluable part of the data architecture business powering. With a lot of the data modeling techniques that I’ve built over the course of the years.

ER/Studio Data Architect

In this webinar, we tend to go ahead and present how ER/Studio Data Architect can help you with the basics of developing a logical data model and also going ahead and leveraging this to a design-based principle in your enterprise architecture needs. Let me go ahead and kick-start this.

Enterprise Data Model

What we’re understanding here is how we can bridge the gap between developing an enterprise logical model and also being able to tie that to an enterprise data architecture. With your developers here in mind, you have a couple of things. The first and foremost is where you would actually go ahead and kick-start a brand new data model from scratch.

01:23

Logical Data Model

Here, I’m just going to go ahead and kick-start a brand-new logical data model. One of the first things that you will do is you’ll go ahead and insert a title block. Here you’ll just give it a name. Here I’ll just call it E-commerce. There’s some metadata here on what the diagram is going to be about. Here I’m just going to go ahead and start adding a couple of entities. Here, if I double-click on this, I can kick-start things off by using the orders table entity. I can go ahead and do that.

05:46

Entity Relationship Diagram

Now here in the other entity actually not this one, let me go to the other one and let me go into the repository login. Here if I edit the entity here, I can actually call this as a product. Here what I’ll do is I’ll just call it a product name and here I’ll just call it Orders or Customers actually. What I’ll do is I’ll go ahead and insert a relationship from the order ID here to the product. What I’ll do is I’ll do the customer ID here. Lastly, I’ll take the product ID here as well. I’ll do this as a different one and from here from the order ID, I’ll put this here.

07:14

Physical Data Model

Now I’m actually slowly building out my e-commerce application. What I’ll do is I’ll go ahead and generate a physical data model. As a developer, we need to then choose what database platform we’re going to target here. I’m just going to target Microsoft SQL Server 2019 and SQL Server. Now what we can do is we can actually then go ahead and generate our database and we can generate our SQL scripts that we can actually go ahead and put in as part of the deployment. Here I’ll just call it the deployment script Next. What I can do is I can actually choose if I want to deploy this to an actual database or create a brand new SQL Server database. I will just ignore that. You also have the ability to look at the SQL preview here that you can actually go ahead and click here. Once that is done, you can then go and choose Next and hit Finish.

08:32
Now the script is ready, I’ll go ahead and open it up in Notepad. Here I can open it up here on the desktop. Here if I go under modified look at my SQL script actually if I go back here. Here if I go under the deployment script and then I can say edit. This is where this deployment script is now ready for me as a developer to go and deploy into my favorite CI/CD pipeline and then have that generated for my end users in the database. Next, what we’ll do is as part of any kind of dev to database application lifecycle, you’re always challenged as an architect to also go ahead and make sure that the changes reflect if there are any newer entities being added or if there is anything that needs to be changed. Here if I go ahead and make a change in the entity of customers, for example, I can actually go ahead and edit the entity now.

09:58

Database Design

Here I can just go ahead and add, let’s say I want to add the customer address. So go ahead and do that. Now that I’ve added the customer address, I would like to go ahead and save this model first. Here what I can do is I can right-click on the customer entity, I can use the Compare merge utility that will actually go ahead and compare this with the actual file. Here I want to compare this against this different physical data model. Here I’m interested in only the customer and entity and the table. So I’ll compare those two here. You’ll see, ER/Studio has automatically gone ahead and identified the change. All you have to do is choose to merge into Target, into getting that into the table and hit okay. Now if I come back to the customer’s table here, I get the address. Now, in order to push again any kind of changes back to your database system, you can go ahead and generate the database. You can compare it with that and synchronize the changes into your local database.

11:15

Enterprise Architecture

You can either do it through scripts or you can just directly connect to that database and apply the changes. Next, what we have is as part of any kind of enterprise architecture, you want to do some kind of enforcement of naming standards. That’s where our naming standards templates come into play. This is where you can actually go ahead and start building your custom naming fields. Here, if I just give a sample and I’ll give it at the logical level, I’ll give it in uppercase, and here I can give it in uppercase here as well. Here I’ll just make it lowercase. There you go. Under the mapping section, you can actually go ahead and add your own mapping document, so to speak, right? You can actually go ahead and choose that and add that. Here you also have the order, logical order and physical order of parts that you can go ahead and leverage.

12:17
You have the general option here, and you can choose the delimiter options here as well. I can go into my binding information and here if I look under the entity, I can actually just go ahead and select all of them. Now, in order to run this and validate, I’ll just choose the physical data model here. I can then go excuse me, I can then choose the naming standards utility here. I can select all, and choose my naming standards template. I can choose Fiscal and run the translation. There you can see the output, but here it translates into that as well. Now that we have worked on this, the next thing we might want to do as part of our developer enterprise framework is to actually link this to our repository. I’ll go ahead and add this diagram to our repository here. Before I do that, I wanted to bring your attention to this data dictionary.

13:29

Data Repository

This data dictionary right now is tied to this local file. Now with this local file now going into the enterprise into the repository, I’m going to convert that to an enterprise data dictionary so that anybody who’s got access to the repository and the model can make a change only in one single place. So now I’ll go ahead and.

13:56

Enterprise Data Model

To the repository and here I’ll go to the project center and here I can go ahead and click on the Projects tab. And here I’ll choose an e-commerce project. Okay. Now I’ll go ahead and add the diagram, this current diagram. I’ll go into the project for e-commerce. You’ll notice that I do have the Enterprise Data model here. So I’ll just choose ecom_Edd. Just hit okay. Now here you can see how we can leverage this to our advantage by just focusing on just the Enterprise Data Dictionary. The local data dictionary is now empty. So what’s your next step? Now once we have gathered the Enterprise Data dictionary here we have what is known as a name release. I can actually go ahead and set this. If I want to create a snapshot of this model for my next development framework in terms of putting it into a CI/CD pipeline, I can actually have this as a version control area.

15:26

Version History

So let’s call this e-commerce first. Call it baseline and there you go. Next, what I can do is I can actually click on Get name release. Here if I go under my e-commerce model, you will see that I have a camera and an icon here that actually goes ahead and signifies the name release. Now I can just open it up and there you go. These are all the snapshots if you will. If you come from a database background, you’ll be familiar with the concept of snapshots. This is basically like a mirror image before you go ahead and make any changes. That’s where the next thing you might want to do is to actually look at your version history to see who did what. Right now the admin username added an object. I set a name release and right now the object is not being checked out.

16:29
If I want to go ahead and check it out, I can check out the diagram itself. Let me just check out the diagram. Now if I wanted to go under repository and version history, you’ll see that the checkout status has been given by my username and by machine name. Now that we are done with this, we can actually go ahead and check the diagram. Excellent. Next, let’s take a look at how you can generate a report out of this. This is one of the final stages that you will do as a developer. Here we can go ahead and choose an HTML report. Here if I double-click, you can see on the left-hand side we have our e-commerce model. On the right-hand side is something that we have here. One of the other things that we have is we can look at any of these other metadata items that are to the right.

17:37

ER/Studio

Here we have our data dictionary, naming streams, template and of course, lastly, we have the image that we need to go ahead and generate. Now we can go ahead and hit finish. It goes and generates the diagram for us. Now, if I expand this look at the model image, I have this. Nice. Here I can actually go ahead and show you. Here we have the DDL, et cetera. This is at a very high level of how you can take ER/Studio, data architect from a logical data model all the way up to a physical data model. Generate the code, then also go ahead and generate and add it to a version control repository, and make some changes. Again, check back those changes, create a version that you have like a baseline plus and finally be able to generate a report out of this. All right, well, with that, we would like to thank you for attending another awesome ER/Studio presentation.