Name: Data Intelligence 13 of 21 - Joining Data
Uploaded: 2023-9-6
Duration: 531 s

Share feedback

<p>Planned maintenance of Practice Systems</p>

Planned maintenance of Practice Systems

Why create an SAP account?

Enhance your learning experience

SAP Learning

Subscribe now

Learn more

Live Sessions

Hands-on Practice

Certification

My Learning

Ready to start learning?

<div><div><div>In this video series, we'll introduce some of the applications when using Data Intelligence. In this video, we'll go through using the Modeler to perform some data transformation by join 2 different datasets using the Data Transform Operator.<br><br>0:00 - Introduction<br>1:00 - Adding Workflow Trigger and Structured File Consumer operators<br>3:00 - Join Data Transforms<br>7:00 - Save and execute graph<br>7:30 - View results in Metadata Explorer<br><br><br>Please note:<br>                     This video was originally published on the SAP HANA Academy YouTube Channel                      and therefore may contain references to SAP HANA Academy and YouTube.                      The tech world is always evolving and the SAP HANA Academy YouTube channel                      is set to close on December 31, 2023.                      At that time, references will no longer be valid                      and will gradually be removed when the videos are updated.<br>Also, this video includes automatically generated captions                      and therefore might misrepresent the spoken content or display incorrect product names.</div></div></div>

Data Intelligence 13 of 21 - Joining Data

Welcome to the SAP HANA Academy. My name is Bob, and in this series of videos by Digital Partner Engineering and the SAP HANA Academy, we'll be looking at Data Intelligence 3.1. This video covers how to join data when using the Modeler. In the previous video we built this graph using the Data Intelligence Modeler. Quite simply what it does is it connects to a file running on our semantic data lake. It's a CSV file with four columns and it loads it into a HANA table running in HANA Cloud. Now we didn't execute it because it only connects to one CSV file. We have another CSV file that we want to join to the CSV file located within the data transform operator within this graph. So we're going to cover how to do that in this video as well as run the actual graph. So the first thing we need to do is add ourselves a workflow trigger because we're going to connect to that other CSV within the structured file consumer. Now, not to get confused, what we can do is rename the existing structured file consumer, because this is actually pointing to our performance CSV. So for the label, I'll change this to the word performance. Now what I'll do is I'll add, if I do a search for structured, we'll add another structured file consumer and we'll configure this to point to that other file running in our semantic data lake. This file is the configuration.csv file, so I'll change that label to configuration. Now we need to join the workflow trigger to the structured file consumer. And of course we've already configured the first structured file consumer, so we'll need to configure this other one. Now before I do, I'll just rearrange the screen. So I'll put the HANA cell status object to the top right hand side alongside the workflow terminator. Because these are going to essentially be running in parallel until we perform the join. So within configuration again I'll go into my configuration and we'll go and select the semantic data layer. Just like before it's going to be the same connection so if I go to my SDL connection we're going to choose that DI data lake and click on save. Of course then we need to choose that SDL source file, so if I select it and go to the shared SHA folder, we're going to choose the configuration.csv file. Again we can run the data preview just to make sure that the connection is working and we can see it is. So now we want to perform the join and that join is done within the data transform operator. So what I need to do is select the output and join it to the input of the data transform. When we've done that now we can double click on the transform and deal with the join. So you can see here we've got another input, input 2. This of course is that configuration.csv file. So like before what we want to do is use a projection to output the data. So I'll double click on that projection 2. We'll map all the columns, and what we'll do is we'll only rename them columns. So again, it's going to be date, then we're going to have cell ID. This will be an int 32. Then we're going to have here we're going to have a name which will be nom underscore key one and this will be a float32 and again the same for c3 which will be a nom underscore key two again it will be a float32. So then I'll click on OK. Now that we've done that, we can exit our projection 2. And we can deal with the join, which we get from our data operations. So what we want to do now is join the two data sets together. But at the moment we have the aggregation outputting to our projection one. Which goes ultimately to the output one. So we need to remove this and what we are essentially going to do is join the aggregation into the second input to the Join. This of course means that we're going to join the output from projection 2 to the first input of the join. Now when we double click we can actually deal with the join properties. So you can see the columns we've got, you've got definition and you've got columns. So you can do this manually by dragging, or you can actually write some syntax if you want. So for rules we're going to join the date to the date in the other table. But the join isn't yet finished, so we'll also need to join the cell ID to cell ID. Now the simple way to do that is double click on the join between the two tables and we can manually edit it. So I'm going to do an AND and then we'll put the input 1 cell ID equals the input 2 cell ID. Now in terms of the output columns, we don't need all of them. We can auto-map to simply get them all there, but now we've got the key1, the key2 and the num key1 and the num key2, but we've got date and cell ID duplicated. So from input 2, what I'm going to do is I'm going to remove the date and remove the cell ID like so. So lastly, we can go out of the join and we can finish. So now all we need to do is join the join to projection 1. Again, to be sure of the output columns, we can check this within our projection. So if we right click and go to open configuration and go to columns, these are the columns that will be outputted. So we've actually finished dealing with the join. If I exit my data transform, we'll save. Then all we need to do is, if I save again, we can see that it's saved. If I go to my log, we can see that the graph saved OK. So all I need to do now is execute and run this graph. We can see in the log that it's scheduled to execute, and if I go to my status, again we can see it's pending, now we see it's running, here we can see it's stopping, which should mean it's completed. There we go. So of course, now what we want to do is check the results. So again, we need to go back to our Metadata Explorer. We'll browse our connections. Of course, we wrote this to HANA Cloud. So if I do a search for C Cloud, and again, all of these connections were set up in the Connection Manager. Within there, we've got a schema called SAP IT and here we've got a table called SHA underscore cell status. And of course if I do a data preview on that table and go to the data preview tab, here we can see the combined data set. So for each date and device ID or cell ID we'll have four values, two from the performance CSV file and two from the configuration.csv file. So now that we've looked at structured file consumers and also data transforms, as well as writing to Hannah, what we're going to do in the next video is look at how we can perform some data quality. You can find more tutorials on our YouTube channel, and if you'd like to be informed as soon as new video tutorials are published, then please subscribe. Thanks for watching.