hi everyone and welcome to another episode of twister to sky to service now we're up for number 28 and today we're gonna take a look for another orlando feature called the robots transform engine also rté all the extract transform a transform load ETL a lot of operations let's see if we can remember them all but before I go into that one I will try to get my PowerPoint work my name is guru lock twist a Kade witch doctor hopefully you see me before in some other videos so I won't dig into this built point just like a say never video but basically I've been a customer of being a partner working for partner and now working for sir not themselves as a developer try to help out in the community by responding on the community I don't use money blog post more I just try to do the videos being a source now I'm a tea in the p4 fourth year in a row now if you want to connect with me ask questions or just talk about service now or something else you've got my connections over here I finally learn where they are I also wrote a book last year song I guess it's soon time to start ninety about it but it's still valid so just a quick one we took to scout service now I try to write down a lot of my knowledge stuff that I mistakes I did that hopefully you can learn off and don't do the same stuff I did there's a few pools point on what that actually exists in the work and I wrote it from Madrid and we're already on Orlando so time flies when having fun I guess enough about that let's move on to the fun stuff so what is there is robust and yen and what is the ETL stuff how does it work and what's the difference between using dis and good old transform apps that were used to do so let's keep this one and before I start I would like to say on the documentation site Docs there is some really beautiful OTT of information and this funk that is really big and I won't really go through all it I will show you hopefully our working transform and load of data but it's not that big complex one but I think you would get how it works so let me just see the correct screen here we go so if I just type robust and don't ask me where robust comes from I have actually no clue anyway robust the think I'm not going to start and let's just say well because we do always click on a breadcrumb to get to the first one I will say in here so basically this is a new way of blood get data into service now instead of having transferred Maps it's more flexible you can do a lot more tweaking with it it's faster because you come right there as a batch it's also in a way faster because the way is set up you don't need to read multiple times from the insert our import set table I will go through that when I'll show you when I'll do the demo but basically what you can say if you go to move data into multiple tables you have noticed that your insert set rows is one row per table per data so if I have a user that one will come row one if I want to move that user because we or that row because we also have information about the computers in the computer which let me show you an excel sheet and you might understand what I'm talking about so let's say you have an excel sheet like this first name last name email then have a computer name vendor installation date I want this data to actually might want to create a user or at least I would like to fetch the user so I can assign a computer which are inserting a creative table to that user in transfer Maps you need to to transfer maps one probably for user to do the insert if there are one and then one for the computers meaning you have to do to read ones read operations but with new one you don't need it but I've been testing it a little bit and I must say that depending on your situation this might be overkill because it can actually take more time to build this then actually just using a transformer oh look I got an email let me just turn off my emails that was our beautiful healthcare application we have here at source now I don't know if I dare to go in and see the result of for instance anyway let's get back here so basically in this scenario is not so complex but and I won't go true and there we go we had this like channel let me turn off the slack channel as well let's quit that yeah hopefully I won't be disturbing worn out so where was I yeah depending on your situation you might think this as an overkill because right now is it the first release they don't have all the easy things that actually worsen transfer maps and I really hope that real combination first pair is so what's an echo no no you know but anyway let's stop talking about that and let's show you how it's done instead so let's go back and wider look up there so I have pre-built this just so you don't have to click me see me filled in all the names and I'll just walk through have done it I just created a data source I attached the excel file you saw earlier and then as you can see down here we have a new lately it's called robust transformer so I would click on that one so this is the first thing you to create so I created this one and mainly the most important thing to figure out here is the transform definition and there is where you build all the logic that want to do another to finish that might be good to know is you have to baptize this is running bad you can't define how many rows per batch you want to do I haven't really tested to see what's good what's bad and you have a verbose which I can actually uncheck because if you check this one you get a lot more logging done because I had some issues earlier with my my user didn't want to be populated so I turned that one on and actually found the issue so when you create a new robust set transformer the first thing you need to do is actually do the transform this permission so this one is empty and I don't know why but I haven't really found it out here in either can't even spell grounds for me it doesn't seem to pop up there so basically what you need to do is click on this one and click on new and that was what I did so you have let's say oh this car and here it is now what we can say here is you have something called ETL and titles that is basically what you can call the tables you're going to use so you need to create which tables am I going to use in this transform map and as you can see in my case I will only want to create computers so I have one for the computer table I have to define the import a table and I have a temp table that our land first and this is because I'm going to show you there are some really nice functionality where you can without writing a lot of scripting do some transformations and operations on the data before you actually put it in the in the target field which is really nice and what was it normal going to say if you don't do that you actually don't need to create the temp table of course you want you the big thing about this is I create a temporal I can do all the magic there one time and then put all that data into different tables I don't need to redo it I can actually need to do with multiple transfer Maps so how does this entitlements look like let's click on the import set this is basically just and you can see we have all the fields or not all the fields this is the fields I have decided that I actually want to have in this ETL interactive I can't even pronounce an entity ETL entity that's a tricky one oh and this is basically really easy put in a name what you want to call this field then you need the column name not the label of the techs actual field the rest of the stuff are pre field right now we're not going to create stuff in this field because we're loading data from it so here I have basically just defined all those so let's go back when I clicked on the wrong error now let's go to our tent table here you can see oh I've got to show you on the table on the insert import set entity I have of course the table name this is a temp table it would just be here for the transformation so we actually don't have any table to point that we only have a pause and I can say I haven't really figure out if they're useful for anything else but right now you just need to have a unique path for this one and of course a name an inheritance II I just defined the same stuff the names and what the field is going to be named now for the operations and here is the fun part and there are a lot of operations as you can see I have actually three different things here I have used something called convert to date and I have a concatenation and I actually have a lookup operation so let's get through this tree so the convert date is actually I don't know if you saw it but on the excel sheet let me bring that one up you can see where is my excel I hate this why can't I see that there we go you can see I have the date as day now a month day year that's not the same format as we have a service now as which I'm using at least so basically the only thing I need to do is say this is the format in the excel sheet how that one is set up month date year and then I would like to convert that date and put that value in this field which is the same as the so field so let's convert it and save it there that is a pretty simple operation which I would ever need to do some coding to actually be able to do which is nice to do it without coding then of course without a little bit of user error I guess next one is the concatenation create model now you can see depending on what operation you have you will see different fields popping up so in this case I say that the source fields and as you can see I can select which other fields I want you can see the table and the name and I noticed I don't know if it's meant to be or not but remember to click if you have almost the same names click on the one that is in the correct table so here I have selected to compute ranarium vendor concatenation and it will be saved in this field pretty easy as well right and last one which is actually getting the CCD of the user I only have first name last name and email so I'm going for the email so what have I done here I selected the glide lookup operation said that the source field date I'm going to use is in this field I want to put in this case I want to save the email so I'll just have another field in the temp table and put the sis ID in that field and you can see down here I said this is the field going to look for matching the source data in this table and this is what you want to return some of these fields have really good help and of course it won't pop up now don't know why no mind it really has really good lives and this is also done let's go back so does all the free manipulation of data I do before I actually put it in the come pewter table and if you look at computer it basically four fields the feelings is the field name on the computer table of course and I said that the name should be the one that our colors so one thing I've noticed and remember how you're playing around this or it's all new to me as well is the sink rise inserts and I guess that if remember you can actually batch run transforming Maps as well but if you have multiple tables you might not actually have them sink all together I think by checking this one it will make sure that it will only be one record that will be created you can't run into those issues I haven't tested yet but I think that's one one is for before we move on one thing that you've noticed that in transfer Maps you have the count of mapping fields for example it doesn't exist here you need to create it by hand or build a script that does it for you but I bet I know I haven't found it or it will show up in a in a later release as well so that's where I'm talking about if you're just going to move data from one fully in percent into one one table and you don't need to do so much manipulation of data I'm not sure that this one will be the correct choice it's faster to create the transfer map it might be faster performance wise later on if you have a lot of data so that might be the reason you will still go here but I would recommend testing that one out before saying from one way or another so what I would looked at now we looked at the different ETL entities that we have created the tree wants to show you yes having wrong I have a mirror window on my widescreen so sometimes my mouse is hunter run screen so that's why nothing happens when I click around so this is the definition I've built those three entities and in the ten table we have the different operations let me go back to that one I just want to show you that when I click on new here is oh sorry not that one on the operations you will see this is all right now the cool stuff you can do and as you can see there are a lot of stuff that you can actually do the one thing I show your work pretty much the concatenation the glide lookup and the date that's the three ones I've shown you and I have no clue what all of these other stuff do but you can see you can probably do a lot of cool stuff and don't even need to do scripting in most cases so let's go back to the definition we have the tables then we have the last form the mapping so this is pretty much the thing you need to do as well of course but the last thing we need to do so from the import set table to the temp table and you can see your order is hundred and then from the temp table to the computer tape and basically it just looks like normal mapping this field should be in here this is where I would like to have the outer mapping nope nothing there either just clicking around to see if I miss something so I put the import sets to the temp and here you can see you can define which you don't need to put all the fields which also can make the performance and for the computers you can see here are our fields from the temp table and you can see it take advantage of my temp user where the sis ID and put that into the sign too so I have vendor to vendor user to sign to installation date decision date and computer name to name so how does it work let's go back to the data source and you will notice that since I have to find everything here I don't need to move over oops transfer maps and stuff like that I'll just load the data once more or just to show you crater on today there is nothing and my computer started calm there is nothing and now today final moment let's load the data and now we can see there is a run robust transform I have no clue if you can actually make both the transfer sent map and robust transform bathroom wants to do that let's just click on that one want to do it yes and done let's go to computers reload and there we go we have with two users manufacturers the installation date then is pretty created you can see that it's actually now that those were created you didn't think I was cheating or something so this is just our almost all is a fundamental a video of how we can actually use these robots transform engine there is so much more to do and this video could probably take an hour to use going through those as well but if you're on land or going over there I would recommend you take a look at this one because it's really cool a really easy and remembered ETL definitions is reusable as well so about that I will say thank you so much for watching and hopefully we'll see you again soon