Design Data Movement With ER/Studio

ER/Studio Data Movement

And this video, we provide a deep dive behind the scene on designing data movement in your studio. We cover how you design source to target mappings manually, as well as visually. Then we discuss the values you get as a data architect when documenting you data movement transformations, and defining data movement tools between disparate systems. Finally, we will show you how you can share that information across the team.

Data Mapping

We are looking currently at yesterday, data architect, I have already opened a data model. This is what we call the source target database, which is measured from different source and target systems. I see it a logical data model and then knows when physical model, and this is the old model. So I would like to define the source target mapping or go to the data lineage tab and just right click on data sources.I can import a new source as, and the manual process, or I can just create an important new sources, what I'm going to do kind of post from another DM one file repository based file SQL file. We'll reverse engineer from a live database.

Compare and Merge with the Physical Data Model

Yeah, we just pick up my source data model in this case and go next. I pick up the physical model, select all tables and it shows me now compare much difference between this suit or like to merge it into the current and say, just finish. And the sources now I see the data model. The best thing would be just to rename it and call it. Source model. Okay. If you look into this source model, you will see the cust table consisting of first name and last name, which is now concatenated in the new physical model. And the customers, if I double click on customers takes me to the Coleman. Now it is moved as a contact name. So this is the mapping, which I would like to define.

Data Lineage Mapping

So there are different ways of doing it. So before doing that, what I'm going to do is I would just create a data movement tool. Miss I define what is going to happen with the data. When I move it, I would define staging archive. Period room. You have of course, different rule types. So under the rule test, I would just copy some information I need here. And I say, okay, now we go back to the data model and define the so-called manual that the limit is on the table. And Coleman left.

Define data movement

The first step to do that is just right. Click and define the so-called data movement properties.

So we can have different source levels of that Alinea and target lips of that time. You need you kind of self-correct in secondary as well. He might have just one data source. This is my source physical model. Pick that up and say, okay, so once this is defined, now we can go to the customer table and define the mapping that would click on customers, go to the data lineage tab. And on the table level, we can define how often the data source it just as an example, weekly. And I say, the date is whatever it is. So this is the information I can type in all the table level on the Commonwealth level. I can double click on the contact name and go again to the data lineage tab.

Define source codes

Now I can define that on the, that the source codes, in my case, the customer first name and last name are the source coalmines. And it stands for mission. Logic is something I'm going to copy here is bleak and put any script here. And you can also define the business logic behind the description here. So basically this is the way we defined that. And this is tied to the source. Coleman's mission, logic and information is available.

Data Flow

Is it what we call model data lineage? The second type of data lineage is on the data lineage tab. Let me see. We defined the so-called a data flow and let me re right, and just create a new act of love and we call it activate data flow. Okay? So under this style of flow, you can use different transformations and potent ships. Of course you can.

Documenting data movement transformation

Meritable death associate for the components. We just drag and drop the objects which are involved with, for example, my customer table here on this side, from the source in the customer table, where it is going into running the middle, we can type in basically the transformation. What is we need here, click here, transformation drop. Here can the data stream can be defined from customer to transformation from transformation to customers. Once this is set up, we can double click on the transformation and give it a good name. It may call it contacting it names. The type is maybe kind of selecting two in this case and economists, we can specify here, the input currency, this case first name and last name on the target side. It flows into the contact name and the definitions. Of course, I can use my business definition back again.

And go to the code as well and take this code into it's just so under that the movement tools, I can still use that and move more through a switch I've already defined. And so basically once this information is set up, you can basically define what information you would like to see. For example, you like to see the drawing names, all that kind of things can be specified here. So use beautifying methods, for example, can you ships in order to show something like knots on it? For example, he, I can put not double click on that note. Shep text could be something like matching first name, last name. This can use some colors and fonts. Somebody use a background, color change, the text, phone size, maybe 16 or 14 in any kind of information can be added into it.

Basically, if you lack, it can also use an arrow line to define, okay, customer name, last name is flowing into this one. So you can use basically here, for example, web praises, in order to define those kinds of properties. That means if I double click an object, you can source the styles, being defined, moved to kind of say, use. And as a web press, for example, whatever you like, you know, no definition you can of course change the colors, for example, for your mapping in a different colors than, than the line systems. So, so once these things have been defined, you would like to share that information, of course, with as a 10 members for that purpose, we can use a number of marks. Was a tool.

Exporting Data Lineage to Development

For example, we can use the data lineage export to Excel MI, you can use it. For example, I can just select this customer's table only in go to that limit export to Excel, and that's run the macro selected objects, direct source mappings. So the macro walks through the tables in January. It's an Excel sheet and that Excel sheet can be shared with your team. For example, your team members are able for example, to go here and analyze the data, to look into this table and change it as they like it. And once they are finished, they consented back to you and you are always able to import the data language import from Excel as well. What we have is also for the visual data lineage, you are also able to export that as well.

He and I, visual data lineage export to Excel for run this macro. The same kind of job is done here to be exported to Excel sheet here. In this case, you see all information, what we have already defined, and it can also be imported back into IES studio as well.

Reporting Your Data Movement

And the last thing for sharing, what I'm gonna share with you is degenerate reports. I can create HTML or RTF reports in this case, I'm just generating an HTML report. I select all objects here in this case, the data also all objects, so that I'm not missing anything on the ships course. Let me generate the ships as well. Go next step. Maybe select all the diagrams or was it that Alinea dark rooms as well. And it got finally safe finished.

So the product creates now an HTML report fully clickable and interesting for us now is maybe the data flows. This is a, concatenations a diagram which has been generated, is available and all components are here visible. You see the different data sources, which have been here as a source physical model. So you see all the table objects as well, recreated those for the customer tables under customer's contact name here. For example, we created the source mapping before on the column 11. This is also available for sharing across the team as well. Just to recap, we're able to show you, I can use the same mappings from social target using based on table and Coleman level. And we discuss how you can define data flows. The visually Parkland, the ETL data lineage between different systems I used to get up first.

And the data architect is able to easily document the source and target mappings transformations in data movement tools in with between different source and target systems in second variable to export and share the data lineage information across development and operation teams via integrated Microsoft reporting tools. For more information, please visit [email protected] and contact sets.