Import Table using Basic Query Transform

Junaid Ahmed
A free video tutorial from Junaid Ahmed
Entrepreneur, Product Manager, Architect and Trainer
4.1 instructor rating • 9 courses • 33,882 students

Lecture description

Learn how to setup SAP BODS and use query transform to bring data into SAP HANA

Learn more from the full course

SAP HANA Implementation, Modeling and Reporting Course

Learn how to build a Real Time Platform using SAP HANA the best selling product in the history of SAP.

09:29:52 of on-demand video • Updated April 2020

  • At the end of the course you will have a strong understanding and practical knowledge about SAP HANA and you will be able to develop an end to end scenario on SAP HANA.
English [Auto] Hello. And this topic we're going to see how to use big ideas to bring data and to accept Rihanna. My data shows like HP ERP. We've already demonstrated how to access here. I want to go and open the data services design and we have to authenticate OK. I want to use this repository called buildings underscore OCD and there's an additional authentication or the repository there can be different repositories on an HP services application to that. All right. So this is the entry point and there are three distinct areas. One is the product area. One is a local object area and then one is the working area. Currently you see a big lunch and would be most used functionalities. Step number one. Let's go ahead and create a data source to talk to this whole system so you can use this feature here to create that I want to name it later underscore a safety and the data store type is an SMP application. So let's go there and the name of the database server. In other words the name of the application seller you can find it in the asset we love on we can right click on the system a link is in properties and this is your application and this process and pick copy paste it here and need to give your user name and you need to go advanced and make sure you have the right system number. Our system is 0 7. So you can you can say applying and you can see if your connection is working. Looks like you have an issue. Looks like the RNC log on failed and the password is incorrect. So let's corrected let's again blind but this time we don't have any prompt that means this is a successful connection let's say. OK. And now let's create a data source to talk to the target. So we don't not create a data source to talk to. And we'll name this live underscore Hannah and this is a database somewhere only with a database and a database type is SFP Hana. The question is one X database. You can database you can edit it statically give the database server name or you could use a liaison. It is also indisputably created. And when I use I'm not going to give the Southern what is OK and the password and the user name is to be provided and I want to click apply to check so everything works OK. All right let's go ahead and create the final straw. So now we have two retail stores connected one connecting to this whole system and one connecting to the target system. What we want to do now is we are going to import the table call and see one on this whole system to the Italian system. So we have to import metadata into these data shows. So that's do that really go to the recipe. They're open options. We're going to import a table. So let's right click on the table currently if you see the table Holder will be empty. So you want to click on it and you want to say import by name. We are going to bring C1. You need to give the exact table name and then say import. OK. So the table has been imported to fill up on the tables is not an easy option. Now you can double click on this to understand the column names of the table and the different data types and the linked operator types. You can also view the data by clicking on view here. OK. So this is the data we have in the source system and we want to push this through the target system very well. Let's close all laws out from all this and let's come back to the quick lunch. Now what we want to do is we're going to create a project so that you can come all the way to the project area right on top. You have something new. We can say like or something else we can say exercise and this is an empty projection. Someone to right click and want to say we are going to create a new batch job. You can click on my job and say X your name and then inside a bad job you need to create it if not in fact. So the first thing is we want to create a data flow so you can just click on it and then click on the canvas. You don't need to brag it puts it in your name. And we're not we don't have other data for us to be connected to the Senate floor. So be sure to go inside the data floor to create a confirmation for that. Let's drag out this table from the source system which you just imported quality one idea on the canvas. Then we will use this query plan from for this is a tool pallet here. And we're going to use the weight control. And now we may need a similar structure on the table to create in the target. So either you can import the table structure from your delegate system into your data stored here or you can use a template table. So let's go ahead and create a template table so you can use it and play table from a template to and you can give it a name. So this is the table name which has to be created at the target. Want to give the same name. Let's use our own data source which is like you want to score a hammer and the owner name is nothing but your schema name so we can go on. We're going to give training. Now let's quickly hop onto the it'll be on our database and take a look at what kind of tables we have in the training schema here. So this is a training scheme and these are the tables. That's me real quick refresh it just to understand what papers we have currently. All right. So these are the data which we have and we're going to bring C1 in the schema. So I'm going to say OK. And now what we have to do is we have to connect these different pieces so you can the source to the body and then get it to the target. You double click on the query transform and this will open up. So you have three distinct areas. One is the source area. This is gonna be the target area. This is where you want to program your target. Here you can add additional functions and filters. OK. So if you open the sourced metadata These are the different columns and you can choose what columns you need. So let's say we need all these columns and let's say we need these two columns. These two are the keys on keys. We'll talk more about what these columns are and things like that when you're actually doing the modelling. And let's pick up some measures so. All right. So this is the. This is how your target table is going to look like and you can program your you can program your column names and you can program your data types so for number like you can change it to a general understanding to one and you can change the length for example you can make it five and different things like that you can change to the primary key and make a primary key. I don't want to say all right. And then what we'll do is we'll get out of here. So the X out of here and X out of here. Okay. Now let's go ahead and execute the job. So job has to make a year from the job level of each. This is a job level. So right click on the job right click on the job right out here and execute. It's a good practice to save and validate. You can do that from about here. OK. So these are the standard settings. Let it be. And we want to say OK now the current job is running and you see that it's based on the different activities of the job. And if you just score next it talks about the different draw counts and the State of the job. So currently it's in process and they're all counted seven data 73000 three. So this kind of state of the job a stop and I don't see the red mark here. This is the edit actually if we had any errors you would see the errors in the step. Now let's go back to the job itself and if you see that it says job has been completed successfully. Let's go to Hanna and check if this job has been completed successfully. So naturally you want to see the table you have to refresh it let's say refresh. Now you see that we have the idea but the first thing you want to do is you want to check the definition. So you're going to go right click and say open definition. So you see that the name has been changed. The data I mentioned has been the length has been increased to find and only the columns which we need are available here. If you see this is in sitting in the schema called training and this is a column store you need to keep that in mind that any table that comes inside is by default item stuff.