Save Smart Ssn with airSlate SignNow

Save smart ssn

welcome to the SP Hana Academy my name is Bob and in this series of videos we're going to be looking at some of the new features within sa p Hana SPS zero 9 in this series of videos we're going to focus on two concepts that of smart data quality and smart data integration in this video we're going to go through a simple example of using the address cleanse transformation within smart data quality in s AP Hana SPS zero 9 so to do this we launch our s ap hana studio and I'm going to go to my perspective which is my s AP Hana development perspective you can open it as you know from here so I want to go to my ACP Hana development perspective we've got our list of flow graph so I'm going to build myself a new flow graph called address cleanse underscore basic so right click new I'm going to build an other file it's going to be in database development but it's going to be a flow graph so here I've got flow graph model I'll select next I need to choose where I'm going to place it so I want to place it in this SD Q underscore SDI folder and I'm going to call this one address cleanse underscore basic it's going to be activated as a task plan so we need to select flow graph for activation as tax task plan and then I'll click on finish so what are we going to actually click clean well in the previous video we all we set up the address cleanse directories on our s AP Hana linux server in others so we can use the address cleanse transformation but again what data are we going to clean so I'm going to go to my list of systems and we've got our schema dev 0 1 so I'm going to just create myself a brand new sequel console and there's many ways we could load this data we could connect via a variable but the simple way what simple simply what I'm going to do is create myself a table called and we underscore address which contains the following columns street number Street locality state and country so I'll click on execute it creates the table and of course if I expand my dev one schema and go to my list of tables that employee address table will be empty so I'll open the content so I'm going to load a bad address into this and again in the previous video I loaded the u.s. address directory file so I'm gonna load a I'm gonna import into this table a u.s. address so they just I'm gonna load is the old Business Objects office which was in New York which was actually at 5:55 Madison Avenue so I'm gonna give it the right number but I'm gonna spell Madison wrong it's actually Madison with an S o N and it's Avenue of course I'm only putting a V here it's in New York in the state of New York but I'm also gonna extract I'm not I'm not only gonna fix this column and get the right spelling of Madison but I'm also going to output and enrich the data by outputting the zip for postcode so if I execute that we can see we've loaded that bad address into this table called employee underscore address so now that we've got that address what we need to do of course is build our actual flow graph so the first thing that I need to do is select the data source so again I'm going to place that on my flow graph and it's going to be that employee underscore address table we know that the base objects if I go to my properties has to be in the right schema so I'm going to put this in the devs are all on schema and we know that this employee table also comes from that diverter on one schema so in this example we're going to do a simple address cleanse so all I need to do is go to my list of data provisioning objects and we've got one here called cleanse so I'm going to select the cleanse object and place it on the screen and then of course like normal we connect our output over our data source to the input of our cleanse transform so the way we configure this is the same way as we did with our data cleanse we go to our address cleanse or cleanse object and here we've got if I select the cleanse objects and I go to general we have three tubs the three tabs are the input fields so obviously this is the data that this is the data source what do you want to be outputted so this is where you can enrich the data so in my example I'm going to also include the zip for postcode and also various settings you can change these things here in this simple example what we need to do is specify our input fields so there's many different ways you can do this in this simple example we're going to use what's called a list of composite fields what this means is essentially anywhere in any of our columns we can have address information it doesn't matter where we've got it so for example we might have Street and then Street number or street number and then Street it doesn't really make a difference in our columns in each of those columns we're going to look for the various values in order to cleanse that data so what do I mean well if I expand my address input field and I look at these things called freeform all I'm gonna say here is that in this simple example if I click on the the freeform here I'm going to say this maps to our source column which is street number so that's essentially the first column all I'm going to then do is actually match each of those feel so if I go down and go to the freeform two I'm going to match this the street I'll do the same for three form three locality and what we do need is that address for you is that a country field so of course we've got the state but we'll also need a country field so I'm going to map also that country field as well there we go so we've got those various fields selected in the source so now now that we've done that what we want to do is decide on our output now just to go a bit more into this input field I'm basically saying that with three form two three four and five but any of those columns that we've got in the input could be a street number a street a locality a state and a country you've also got the choice to use what are called hybrid fields and discrete fields as well there's many many different options which we'll cover in other videos so now that we've selected our source fields what we need to do is choose our output field so again if I scroll to the top we've selected our input fields now we need to go to the output fields so firstly what I'm going to do from the address basic option now this is where we can output cleanse and enrich the data if I go to address cleanse I'm going to firstly output the postcode because I want to output the full postcode so if I go to postcode I'm going to change this option to true this means from the data that we've got in the source we also want to output the postcode I remember we didn't have this in our source data there's lots of information that you can output you can output street names building names regions cities you've also got extended information so if I go to address extended you've got all these other options on which you can output so give you a simple example I want to output the cleanse street name so if I go right down I'm going to output both the street name and the street name X and the street name expanded output the street name and also the street name expanded just to show you some of the other options so if I again I go to address extended and drill down I'm going to out also output the street name expanded by selecting the troop so now we've got the postcode in the street name that's kind of really all we need to output but the next thing we need to do is then specify what we want to output into the target into our data sink so again if I go right to the top and in general we've got our data sink template table now just before I cover that you've also got these various settings and again I'm trying to keep the video as short as possible but if I go to my list of addresses you can see here what you want to change so based on the directories you've got you can choose and specify the case obviously you can do the same you can change settings for persons titles so on and so forth again we're going to keep it simple in this first video so now we've selected our input fields and we've got our output fields if we go to the output this is what the actual things going to output you can see here that we've got five we've got lots of columns so to make it simple these five columns were from our source data these three columns with standard address and then post-cold primary name and primary name four are actually from the output so this is where we can enrich the data again if I go to employee address and right-click and go to open content you can see that we don't have a postcode or a zip code and you can see here that Madison Avenue is actually incorrect it should be ma D is a.m. so that's hopefully what we're going to fix so you can choose exactly which columns you can output this is kind of a case of trial and error I'm going to output the original ones and also these additional one the original columns and also alongside that postcode and actually I don't need all of these columns so I'm going to delete one of these by going to my data sync template table so if I go to my data sync of course what I need to do is specify the schema and I'm going to call the output of this table cleanse address and then all I need to do is select the output and join it to the input of that data sink so you can see these are the columns that I'm going to output if I select a column I can specify one on one output so there's lots of different columns you can output I'm actually going to remove this prime name I'm going to use the full prime name so I can select the column and select remove like so so all I'm outputting is the actual States I actually don't need the country as well so I'll actually remove that so now we've got the street number the street and I'll keep this one because it's the this is the original street but this should be New York New York and why is the state but we're outputting there we're enriching the data by putting the postcode and this will be the cleaned version of the street so this should be Madison and this will be Madison Avenue so when you've done that you've got your template if you go to your input here we can actually change their the way the data is displayed so I'm going to call this one here old Street or original Street and that this one as we know is going to be our new streets and our new post-cold which were outputting so all this of course you can change in your data sync template table so when you've done that all we need to do of course as always is save then we need to select the objects and then do a validate if you get no errors back it means it's good everything's good to go and then of course now what we need to do is execute so this is the original data so we can close this table and I'll execute this task plan so we can see it executed okay and if I right-click and refresh we should have a table called cleanse underscore address appear so again if I right-click on that table and go and do an open content this is what we have so of course what you can see here is that this was the original Street Madison Avenue the actual primary full name is Madison Avenue with the O and AV e as Avenue and of course because we have established that this is Madison 555 Madison Avenue in New York were also able also to output the correct zip for postcode which is 1 0 0 2 2-3 3 0 1 so I hope you enjoyed that simple example I've tried to keep the video as short as possible of using the address cleanse or the cleanse transformation in sa P smart data quality to do some address cleansing based on us data within sa p Hana SP s 0 9