 Well, it looks like it's 12 o'clock my time, so maybe I better get started, but we'll see I'll go kind of slow and then we'll see if people start showing up. Alrighty, well thank you for showing up today for our presentation on what is SAS data integration studio. So, um, let me first let's see if I can just do. There we go. So, um, today we're going to talk about SAS data integration studio, which is a component of SAS. So just to remind you, you know, SAS comes in like components and like sub languages. But basically what it means is that in your SAS environment, when you purchase SAS or your organization purchases SAS, they can choose different components to be included in the license. And what really happens is if you go to college, and you take a course and you learn one of the components. Okay, sorry about that. I don't know why this person kept requesting control on my screen so I had to get rid of them. But anyway, there wasn't a security thing for that and so. Anyway, so, um, SAS data integration studio is kind of like one of those components that if you used it in a college class when you were in school, and then you went to a workplace you might say hey let's use this. Or if you joined a workplace that already had it you'd be like whoa this is really cool. But if you don't know about it you might not think to include it or you might not think to ask your SAS workplace to include it. It has actually been around a long time it's been around since 2010. It's not like the awesomest thing in the world but it is something you want to know about. In case you want to do data integration, I get simple to say but if you're in a SAS environment where you're connecting in you're bringing in a lot of data from outside the SAS environment. I'm saying you definitely want data integration studio but I'm saying you might have a use case for it. So it's worth looking into that component and that's what I'm going to talk about today. And just to let you know, I am holding a special free workshop. It's free six plus hour workshop in application basics for SAS integration. Now the actual course is already an online course that you can pay for and do. And it's called applications basics. But if you sign up for my free workshop, I'll teach you this class and I'll put a spin on it because we're going to talk about SAS integration. If you just take the online course and pay for it, it's just about applications. It's teaching you like how applications are designed and how they can work together. What we're going to do in the workshop is focus on SAS application basics. SAS is an application but it works with other applications. So please look at the sessions. I know you're all busy. I tried to cram it all in one week. I thought maybe you could handle that. So each session is going to be two to three hours. It depends on how many of you sign up. It's Monday, August 7th, Wednesday, August 9th and Friday, August 11th, three sessions. And then there's a private follow up session for 30 minutes with me. So if you want to go to the free workshop, it's free and it's online. It's on Zoom. I promise I'll get better at Zoom. And you get the course from the course management site. So just go back to the LinkedIn event and click on the link to sign up if you want to be in this workshop. All right, so getting back to SAS data integration. Again, I see some more people just showed up. I just want to repeat. SAS Data Integration Studio is a component of SAS. So you might be in a SAS shop that has it. You might be in a SAS shop that doesn't have it. You might have taken a course where they used it, but then you never used it again. So it's sort of mysterious when you have these SAS components you don't use very often. Like, how do you find out about them? Well, you can talk to people who use them. You can read white papers. I wanted to really look into SAS Data Integration Studio because all of these SAS components have really pretty specific use cases. And if you have that use case, you really want it. And if you don't have it, you're probably not going to be able to use it. So the white paper, today's white paper has been around since 2010 because Data Integration Studio has been around since 2010. So let's go to the next slide and see if I can figure out how to use PowerPoint. OK, so let's see here. So why SAS Data Integration Studio? So it's a challenge to pull together federated data sets for auditing purposes. OK, that's like a really dense sense. What do I mean by that? Federated data sets are data sets about the same thing that are in different data systems. And that if you are in research, like clinical research, you can kind of immediately imagine a situation where, like, maybe you have lab data that's actually in a lab system. Like, or maybe like a, like radiology, you have like imaging, you have data for like a person, like this imaging data. And the patient data is in the patient database, so they're federated, like they connect theoretically, but they aren't physically connected. But let's say you want to analyze, you're going to want to connect them, right? And so for auditing purposes, auditing is one way of looking at it. So let's say that we're doing a clinical trial and we need to do an MRI on everybody at baseline. So I've got my SAS data set maybe of everybody's enrolled, but how do I know if they've got their MRI? You know, I want to connect to that data. So that's basically what you're trying to do in data integration studies. You're trying to connect data sets you know belong together or should match, but they're basically in different environments. Now, let's pretend we weren't SAS users, we weren't talking about SAS. Let's pretend we were talking about SQL and we were going, and we had this problem. And I'm actually going to show you a use case for just having this problem in SQL and or just in SQL plus IBM mainframe plus, you know, some other weird, you know, like usually when you have this problem, and your federated data sets are on all different formats. It's actually a little hard to figure out what are we going to integrate? Like what wins? Like what environment do we put the data into? And you know, where do we start? But we already know that SAS likes everything to be in SAS, right? So data integration studio is a little weird if you're used to SAS or if you're used to SQL integration. Because in SAS, you're always working around the IO limitations of SAS, right? Like SAS likes everything to already be in its environment. If you're bringing something in, it's going to be costly. So how are you going to run a data integration studio? It's sort of interesting. This Jeff Sander, he wrote this white paper. This is the link. And I'm going to maybe even just bring up the white paper. But I think we might dig into the white paper a little. Unfortunately, I hate ever criticizing SAS white papers because there's so much work to write and I'm always grateful somebody did it. But some people are better than others at communicating what they're trying to say. And it's hard, you know. What's difficult about this white paper is the use case that the author chose. It's not really obvious what he's trying to do. So it's not really obvious what he's illustrating in the paper. But I'm going to show you some use cases that are typical use cases. Now, this is one I actually saw, this was presented, this use case, and it wasn't with SAS data integration studio, it was more like a SQL integration. But this was, I think it actually was done in Microsoft Access. This was presented at a university. So what's going on at the university? So there was this university finance data environment. So they were there in the university finance department. Okay, so over on the way right side, you have accounting. So this is data. So see FARS, that's the fund accounting database. That's a database where it's like, if you get a grant, let's say somebody gets a grant from the government to do clinical research. It's all, it's all like kept track of in FARS. Okay. Let's say the grant's running and you're going to pay some people like let's say you pay some SAS bio statisticians, right? Well, over, over on the left side of the slide here, you see the, you know, this, a grant supported employee, this could be a statistician or a doctor or whatever. So this person's getting paid, they're getting paid out of payroll, university payroll, but eventually that's going to have to be reported back to the grant. And so you have these auditors come in and see this yellow part is sort of the special auditing environment. So this auditor comes in and creates a secure analytic environment just for auditing. And this person pulls in just a little bbd that they need from FARS to do this auditing. Okay. And just a little bbd from payroll they need to do this auditing so they aren't digging through every state of our own digging through all this private information and tries to sort of map them together and match them and see if they're reconciled. Okay. And this was actually really, this is very challenging to do. Originally, like 20 years ago, this was, you'd have to really build something for this. But what I want to show you is that actually this data environment, everybody in here is trusted. Okay. And this is not really usually what happens when you're using SAS. Okay, because you're usually not using SAS or something like this. So let's go to the next one. This is what you're usually using SAS for especially in an auditing function. So this is a more like a research institute. Okay. And this bio statisticians over here. And this is actually the SAS server environment. So this yellow part is, it's very costly to move data into this environment. So, if you're going to move data into this environment, you probably want to keep it there and not move it out. So let's put the SAS server in here in the live research data. And now maybe quest, you know, quest diagnostics as a commercial lab. And I know that, you know, you can hire them, they're happy to do your, your panels for your research study if you're, if you don't want to do it in house. So you can, let's say that you have them do your data, do your lab data. But you're going to have to import it and add it to your, to the rest of your participants data. Or maybe maybe you send out a survey monkey survey to them, or like a red cap survey. And you've got data out here in Excel, like an extract. You're going to want to bring that into here. So one of the questions you might be asking is who is missing their lab data who is missing their follow up survey. And this is data integration studio notice how it is in the SAS environment. So this is where you would take this component. And you would do this work where you mapped it to this lab data and pulled that in and mapped it to the server data and pulled that in I'll show you an example from the white paper although it admittedly is not that clear. So that would be your use case for data integration studio so here. So, first of all, I just want to talk to you about this concept of ELT. Now, if you're familiar with databases, you, like, actually I honestly thought this, I was like, Oh, this person's out screwed up it's ETL. Okay, but they're not all screwed up. Okay, so I'm going to explain what's going on here. What is ETL, it's extract, transform, and load. So imagine I only am dealing with a SQL server environment. Okay. And I haven't have two relational databases that are just not they just happen to be on the same server but they're not really connected. I actually saw this at a health insurance. We had, there are membership, like, like our membership database like members could call and figure out like where they could get care. And then we also have this database that was for claims education, and literally the two were not connected because the financial entities and the actually surface entities were not connected right so we had these two SQL databases. So once in a while we had to actually move data from one to the other. So let's say there was a new provider, a new like doctor, in one of the databases, and we had to actually put it in the other database. We would extract that data, transform it into the right shape of the other database, and then load it into the other database. Okay, so ETL. So when I saw this ELT I was like, I think they screwed up, you know, they didn't screw up. Let's just think about every time you try to put data in SAS, what happens is, is it easy. Like, it's possible to edit data on the fly when you're putting it in SAS so it's possible to take, like a TXT with just a few columns, and one of the columns was state. So I had multiple states in there like Massachusetts, Minnesota, Florida, and there are two letters. There is a way of using an, like in file like doing your imports I forget how to program it, where you tell SAS to read over and only read in the states that have a value. And that's a way of reducing your load time if you're loading and extract into SAS, where you don't need the whole thing, like you have a bigger one than you need. But the problem is that's even takes a long time. And also if you have a server it's you really powerful. So what you almost want to do in that situation is just like put the whole thing in SAS, and then start messing around with it and figuring out what you're doing. Because it's just so easier, it's so much easier to mess around with data in SAS than as you're reading it into SAS. So that's why ELT, because it's like you extract it, and then you mess around and load it, and transform it. I think you have to kind of read the paper to get what he needs. But I think, so the screenshot is from the paper and it's actually from the DI environment. Now, those of you, we all know that SAS is command prompt you can program it but also you can have these environments in SAS where you basically drag an object on it and then you can open the object and you can put commands in the object. And that's actually how you use DI Studio. So he's showing over here like some transfers like a pipeline. And unfortunately it's a little hard to understand his use case because it looks like he's working with a customer database. And each of these steps, it looks like he's extracting it from a SAS like data warehouse. And he's trying to just analyze some of these invoices. Again, he didn't really go into like what he was trying to do. So it's a little hard to understand. But here actually, you know what, I'm going to go and actually look at the, here is the this is his paper and this is the ELT. So what he's saying is access any data source or system. Now, think of this from the SAS perspective. Efficiently extract only the data needed but that's not really what's happening. And then so they get that he gets the data needed and then stages the data inside the data space platform and utilize high speed loaders to load the data fast. And then transform the data using resources of the database. So what's actually happening here is the ELT the L is doing some sort of optimized loading into SAS. And it says transform data using the resources of the database use database specific SQL user defined funds that are leveraged database resources. So in other words, you would never think of loading something into SQL this way. And I'll tell you why actually, when you make a query and SQL, you're making a declarative query you're saying return these records to me like, like select all from customers where date is less than June 1 or whatever, right. So you're you're declaring it and actually SQL has optimizers and it calculates it decides how it's going to execute that query. In SAS we tell it pretty much how to execute the query. And so for that reason, it's kind of like we have three little steps here and we can do some manipulation of executing the query during the quick loading. Oh, hi there. Everybody say hi to Ebenezer he's here. We're showing up. He's my marketing assistant everybody if you ever need science marketing go to Ebenezer he'll help you out. Anyway, so this is sort of like, if you're in SAS and then in a SAS environment you need all the help you can get to get data into your environment. I will tell you that if you're worried about getting data into your environment and you haven't tried the DI data integration studio component. I would talk to your SAS provider and see if you could demo it or something see if it could solve your problem. Like if you're having a regular problem, loading data into your SAS environment, just see if this could help. It's because when I see bulk load like there might be just a way to more efficiently load it because of these tools and like I had a data lake and I was always manually loading or we were always manually loading the data. I wanted to automate it, but in order to automate everything you have to actually have designed it really well like you have to really have it well documented know what's going on. Well, once you do that you can build it something like this like we were basically planning to build some sort of automated function we had the code that we would put in each of these steps. So there's nothing wrong with planning or taking a manual process and then building it into DI studio but that's why I went over these use cases first because usually that is the challenge is that people don't really understand the use case or they don't really know if their use case is is one for the data integration studio and by the way if anybody listening to me actually uses SAS DI studio and can tell us an actual use case you're using it for and how it's working. That would be awesome because you know as you know that's not open source and so it's sometimes hard to find people who have been using these things and just hear what they had to say. Here are a few images from here. So one of the things like see see these dialogue boxes. What these are is there from each of these things so you can do all these configurations and how each of these steps executes and like here. And this is really about loading like large amounts of data. One thing that I always talk about and I don't know if it's even possible in this use case. But is that if you're in a load data from a SQL database, see if you can get permission to go into the SQL database using SQL and create a view in SQL that just has the data you want. And then that way you this step doesn't take as much trouble right because you've already done some of that work in SQL for yourself so you don't have to do it in SAS. And that's actually the whole difficulty with integration is that you always have to be diagramming out what you're planning to do and then decide well what queries do we put on what side of the integration. Because usually you have access to everything. So, like here, this is this was, to me, a really interesting diagram that they put. So it says check database processing results and an indication of what will run into database and what will not. When I see this. This is I find this really confusing. Okay, so if I was writing a white paper, I would not do this. I just want to show you. It says here class in all caps. Then this one says class and it's uppercase and then lowercase. This one says class three. And this one says all caps class but it says student data under it. Is, is this the same table, duplicated. Are these different classes, like are these class like, like category, or are these classes like in school. Right. This is a sequel joint. So, like, if you're going to make this, this diagram and this instruction shop, why make it so confusing and so this is it's a little hard to tell what they're doing. Um, what this is product PRD senses, sense of stand for product. So it's product and sales and join. And they're setting something up here. But what I believe is, let's see the sequel join trash can automatically upload data the DBMS before doing the joint. So, I'm trying to figure out if they're trying if they're getting this query to execute on the sequel side or on the SAS side, because it looks like it's happening on the SAS side. It looks like it's happening in the SAS environment in data integration studio. This is something I wanted to show you. This is typical when you're doing a data integration like when I was describing that first use case, where you have that counting database the far as database and then the payroll database, what you'll often do is create some sort of views that are very similar. And then you'll have this crosswalk this cross map. So like see this like daytime here like SAS function name, like these are I guess are SAS functions provided by SAS identity. And this is these are, I don't know which DBMS they're talking about here I think they're configuring this this has to do yeah they're configuring see Oracle here. So they're connecting to an Oracle SQL database, and they're cross mapping to a particular. I think a particular table or no it looks like they're cross mapping functions to particular engine, which is actually kind of cool but when I talk to other people. SAS doesn't play so well with other software sometimes their functions are not the same as like another function like you think it would be. I've heard that SAS has like idiosyncrasies. But anyway so still if you're very organized shop you could use this and you could automate things now here. This looks so complicated doesn't it. What they're talking about in this paper is calculating this. This or bringing in this risk rating I apparently that's what the issue is is they're trying to get some sort of risk rating, or produce a risk rating or I'm not really sure they didn't set it up very easily. But anyway so this like see these diagrams here. The basic point is that before you use SAS data integration studio, or any other data integration. You have to know how to do each of these steps manually, like you need to have the code and make sure it works manually. Right, because if you can't get it to work manually how are you going to automate it like where. How are you going to make decisions about what you put on each side. So a lot of times when I like I do a lot of teaching with students also like we're in school, and often they'll have like a class where they're learning like a SAS component. And they get really confused because they're not even sure what they're doing like the use cases confusing to them. And so what that that tends to be the problem so what I feel like it can be very helpful is if you just can figure out how to manually do it. And then bring that use case to SAS, because then SAS can help you figure out if you have if they have a component for you, or like I don't buy is really powerful I haven't used it but let's say that you can do something manually or in a small scale manually then SAS can help you maybe do an invite or do it, you know, using one of their automated functions but, you know, SAS or any other company can't, you know, figure out what is the best integration for you like you're going to probably have to figure it out yourself. But so thank you for coming. And don't forget to download the slide so you can go read the white paper on your own. Oh, I looked online to see if there are any good videos of people using data SAS data integration studio. There are a couple videos of a person like doing like a like one hour one hour and a half instruction. So you can look at that but I did I didn't want this to be this huge instructor I wanted to just explain it, explain the use case and then you know if it's something you want to like delve into or not. And again, you know my name is Monica Wahee, and these are my you can connect with me and don't forget if you're interested in learning more about applications and application basics. Please join my online workshop. As you can see it's a six plus hour workshop each of our three meetings is about two to three hours depending on how many people show up. You'll have access to the online, the online course management system because it's a course and applications basics, but I'm going to teach it and we're going to have a spin on it about SAS integration. So we're going to focus on ways of integrating SAS with other applications. So thank you for coming today. Don't forget to download the slides and go to the event and go click on the link if you want to sign up for the free workshop. All right. Does anybody have any questions or I'm looking at the chat so I can always. Oh, I see a question. Is there anybody can see for SAS data integration. I have four years of experience SAS base SAS DI SAS administrator. So this is Ashwini Ashwini. Can you tell us what you've been doing in SAS data integration studio, like what kind of projects you've been doing. You don't have to but but if you do then I can put it out there oh you use it for ETL for an ESIC what is ESIC for an ESIC Indian project. So that's great so you use it for ETL. Let me know what ESIC is. Oh, I see employee state corporation employee state. I think you mean like income corporation or something like the like the retirement database right, we have it we have one in Massachusetts, that's a big database. So tell us how when you did you use SAS. Yeah insurance based government project very good wow that's a big database okay very good. So when you use set the DI studio for that. And you were doing ETL. It's the third largest database in India that's amazing. And anybody out there if you've had an experience with either. What have I seen integration software I've mostly seen it in SQL environments what will happen is like you'll have oh I know when I've seen it I've seen it when we've been. Like to medical entities merge, and we're trying to merge the medical records, they'll be like this integration software where you can compare two different records, without integrating them yet so you can decide which one to merge into the other. I imagine SAS data integration studio would be good for that. All right everybody, well thank you so much for coming, and I hope to see you at my free online workshop.