 here we go. Hello and welcome. My name is Shannon Kemp and I'm the Chief Digital Manager of DataVersity. We'd like to thank you for joining this month's webinar, Managing Complex Data Environments, sponsored by IDERA. Just a couple of points to get us started. Due to the large number of people that attend these sessions, he will be muted during the webinar. For questions, we will be collecting them by the Q&A in the bottom right-hand corner of your screen. Or if you'd like to tweet, we encourage you to share highlights of questions via Twitter using hashtag DataVersity. As always, we will send a follow-up email within two business days containing links to the slides, the recording of this session, and additional information requested throughout the webinar. Now let me introduce to our speaker for today, Lisa Waugh. Lisa is a Senior Product Manager at IDERA Software for the Aqua Data Studio Database IDE Tool. She has over 15 years of database industry experience, including speaking engagements and presentations on database tools and technologies, and enjoys defining the direction for database development solutions. And with that, I will give the floor to Lisa to get today's webinar started. Hello and welcome. Thank you. Thank you for having me. Today, we're going to be discussing Managing Complex Data Environments. It's rare to have companies that use a single platform anywhere, so companies and I-teams must adapt. The dates of different disciplines for different roles are gone and multiple disciplines must work together. The lack of appropriate skill set can slow this process down. As teams are gathering for a DevOps and you're becoming part of a team, you have the different levels of skill sets included in that, and you also have different complex environments for working with your data. So here's a typical example of a cloud infrastructure. And keep in mind, this is everything in the cloud. So this is pristine. So you have your application, your collaboration, or your financial manufacturing distribution, or whatever application you guys are using. You're having your database and your platform stored in the cloud. You're probably having your services manages and your infrastructure down and your networking being able to connect. And then you have the peripherals of your laptops or desktops and everybody connecting to the cloud. And this is a pristine of just a cloud environment. But what happens today in these complex environments is people are dealing with hybrid infrastructures. They're having their data center locally or could be in house. They have their application layer could be on premise or in the cloud. They have their database layer could be on premise or in cloud. They could have legacy systems and now they're moving to the cloud. They have a data warehouse that's up in the cloud or the other data warehouse stored locally. They're getting information from the internet and they might have a hosting region or not. And somehow they're getting into these webs, be private. Now, I could have made this screen pretty busy because that's the environments that people are dealing with today. But this kind of gives you an overall structure of what a hybrid infrastructure example is. And when you're setting up your infrastructure or what the thoughts were setting up the infrastructure when you're dealing with databases and getting to the data is scalability and complexity. When you talk about SQL, SQL is structured query languages. But when you talk about relational databases and how they get in, these users, they have to scale a relational database on powerful servers. The data had to be distributed handling tables across different servers. And for the no SQL, their scalability, it spreads your data onto multiple servers and servers can be added or removed from the data layer at any time. The complexity was for SQL is that the design of the data needed to fit into tables and rows. And the database structure could be complex and again, difficult to handle in large volumes if you're dealing with tons of data. The no SQL was automatically spread your data onto multiple servers. And it didn't require a defined structure, it could cache data and system memory, therefore making it faster to retrieve. But the key is you still have a structure and a SQL platform that you have to deal with the rules about particular database and what you're dealing with. So the difference between the relational and the no SQL platform is relational is like my SQL Postgres Oracle SQL server. They're based on a branch of algebraic set theory known as relational database, meaning you have relationships, you have primary keys and foreign keys and relationships on how you pull the data out. Meanwhile, the non relational no SQL like MongoDB collections are stored in JSON documents. You also have data warehouses that infer the tables and rows added on to that. And we're going to talk about all those as we get into them. You also have distributions that integrate in so people that have adopted the Apache Hive, which is a data warehousing project built on top of that, excuse me, Apache Hadoop. They provided a way of querying the data and providing analysis. And it gets like a SQL interface to get the data out and to these various databases and their file systems that integrate in with Hadoop. So I'm going to show you a schematic kind of a little bit of how it works. So you have Hive and Paula and Spark connectors with these distributions and you're getting to Amazon, EMR, Apache, Cloud Deer and Hortonworks. So pretty much you'll have your client for a way to get in to their particular services, you'll have your JDBC connections, your Spark connections or your different distributions are in Palo Hive. Depending on how you're getting in, you could have been getting in through ODBC. It depends on how you're tapping in. And then you have your servers that you're connecting to, you have some sort of driver that they're all connected. And you're either trying to get to a metastore depending on what type of a platform you're getting to or the file systems of where it's stored in order to retrieve the data. So it gets pretty complex as you think of these hybrid environments and how you can get to these, these complex data sets. So I wanted to give you access to or give you something that I read it's rules for managing hybrid cloud environments by database trends. It's a PDF file that you can download. And it talks about their eight business processes that they believe you should follow. I'm going to cover three of those in my talking point today, ensure the flexibility of data movement, embrace the range of tools. I'm going to cover one tool for Aqua Data Studio, which is called Aqua Data Studio, and prepare for new skill sets. As we talk about these teams that are involved, there's different variances of skill sets and how do you manage those particular skill sets? It used to be when you were dealing with databases or you were a part of these teams that there was about nine personas I have enlisted here. You had an application developer, an application database administrator, a database developer, database administrator, you have business analysts or four types of analysts, a business analyst, the data analyst, the data architect and the data scientist would do your algorithms. And those people pulled the data out and made sense of what you were looking at with the data. And then you had a data modeler who sometimes was in its own silo or would be on the DVA team. And they would create the databases and model them from scratch or tap into the database and reverse engineer. And then you have your people who are administering the database on how you get access to it. It could be, you know, how you're tapping in if you're getting across to network. And then a lot of times you have your IT consultants that come in if you're working on big projects like your or ops or or some type of Salesforce or some types management contact management system that you're getting in, you have your IT consultants with different various levels that come in. So I also attached an interesting article I thought on DBAs are changing but not going away. I was talking to one of our customers who would have a DBA title and he was telling me his title has now changed to a data analyst. Although he's doing the roles of the DBA is creating objects. He's figuring out the space for the database. He's manipulating that he's also working with a hosting service to manage that relationship. Now that they have an on premise database and they have a database on the cloud. So he's managing all that, but he's providing reports so that they can make business decisions. So since he's dealing with the data at different levels, they decided to make his title data analyst. So as all these roles have certain key functionality that they need to contribute to the DevOps theory or they need to contribute to what your company is doing, they need to they cross over and a lot of roles. So what Aqua Data Studio does is Aqua Data Studio is a universal IDE and visual analytics tool for dealing with data and databases. It supports over 30 data platforms and it works on all major operating systems. The database platforms can be relational no SQL or cloud databases and it allows you to manage your data and access import and export and visually analyze that data. So we're going to get into how Aqua Data Studio will help you manage these complex environments. So as you can see, we support over 30 database platforms where there's SQL, no SQL or cloud. We also support the distributions of Hive and Poly and Spark connectors to be able to get into these different distributions and the complexity of that. And then we're going to talk about how we help you to query query and more queries because there's different kinds of queries as you're dealing with these different database platforms. You have different disciplines for each database and you also have different roles and how you're getting the data out and different roles for those databases. So we try to make that standard and enable you to query the data and get your data out pretty easy. So like for example, Google Big Tor query has legacy SQL that they use and then they have standard SQL and legacy is a little bit different. It returns an approximate amount where the standard returns an exact amount. And that's just kind of a little example of the variances you can get. You can run a single query or multi database instance query where you're querying two databases at once and how you go about doing that with the join. And then you have scripts which are multiple queries to be saved scheduled. Scripts are just a set of rules that you use or a set of commands within a file and you're pulling data back and forth and you have those set of commands, whatever they could be, could be insert, updating them, whatever, but we're going to get into the queries and what you could do. The relational versus no SQL platforms. Here's some SQL terms for structure query languages, mostly how you pull the data out. And then for MongoDB, here's some concepts of the JSON documents and how you get it different. They're both databases. One has tables and rows and one has the collection. The row is considered a document by son, a binary JSON document, and the columns are filled, the indexes are the same. Table joins that used to have in the relationship. When MongoDB first came out, you didn't have the option of doing the lookup field, which just kind of allows you to do some table joins. You didn't have that capability. It was stored in memory and it didn't work that way. You have primary keys on how you do it. So I attached a SQL to aggregate mapping for anybody who's on Mongo, but we're going to get into some different types of no SQL. We have a snowflake and we have some different ways of data warehousing and different options for you. The languages of SQL, here's a couple of examples of the differences of those as well. You also have the people who are programming on the database and they have to, the language of the particular database platform that you're using. You have PL SQL, which is a proprietary procedural language used by Oracle. You have T SQL, which is used by SQL Server, PL PG SQL, which is used by Postgres SQL. The reason why I pointed out these three is that I just wanted to show you that as we're creating these queries and how we help you with the particular syntax. So they're all pretty much doing the same thing, but they do it in different ways. If you look at the three examples that I give you, it's just a syntax. This is a little bit different, but you have to follow the syntax in order to run your queries for them to work when you're working on a particular platform. So if you guys are going to a new platform, let's say you're on Oracle or SQL Server and you're going to go to Snowflake or you're going to go to Postgres and you want to know what's the syntax, how do I go about manipulating this? We're going to help you with doing that with Aquadata Studio. So what Aquadata Studio's primary features are, is they allow you to register your server in your database. They provide a database navigation exploratory. You build that when you register your servers. And then we have some query analyzer or SQL editors, so the SQL editor or a JSON editor depending on what platform you're using. And then a query builder that we offer that allows you to build these queries quickly and to build these queries with the drag and drop interface. So if you're not familiar with writing queries, because as we talked about, teams have different levels of complexity or different levels of learning. And so somebody who's not familiar with writing queries can use a query builder to help them do that. We have a table data editor for editing the data right on the fly. Once you get your results such, you can edit it. And then a visual analytics to tell your business story. So you can do some analytics on where you were and where you want to go. And then some tool sets for importing, exporting your data and DGDL, comparing the data that you have. You have schema and data and file comparison. And then we have an ER modeling tool that allows you to reverse engineer your database to get a graphical representation or to infer that representation if there are no relationships. So you can kind of see what you have. And then a database administration for being able to create objects and manipulate objects, see some statistics on some objects. You have two developers locking each other out. There's a way for you to unlock those particular locks. So how do we get to all these servers and how do we help the one interface? I showed you on the prior screen, prior couple screens that we offer 30 database platforms. We also offer generic JDBC and generic ODBC for connecting in. And then you can make your Excel spreadsheets as a data source as well. And I'm going to show you how that's pretty easy to do. I do that all the time because I have a boss that works on its Excel spreadsheet over the weekend and he's updated the numbers and then he wants me to put those in the database and run particular reports on it. So there's ways that we can do that. This particular connection file that we're building for connection to these databases is extremely powerful. And I'm going to go through how this really helps the complexity of what you're trying to manage in these hybrid environments. So as you can see, I have Google BigQuery selected in the middle. And to the right, it's contact sensitive with what I have selected over here. So it's going to, you know, what do you want to name your database and the type. I have a type of selection production test or my production test box here. And then you'll see that it will change the icons that I'll see as it's building my navigation tree. One thing that I think is extremely important to people kind of gloss over is this tab color. I use this tab color to visually see when I'm using production. So if I make this tab color red, when I'm in the SQL editor and I do an insert update or delete statement, I can see that I'm on a production box and do I really want to do that when I'm looking at it. I have my full migration path set up. I usually have my development for blue test, path is test, it's green and production is red. That way I can clearly see graphically where I am when I'm in the SQL editor and I'm running scripts or I'm manipulating these scripts or I'm updating and doing things. So as I put in my authentication, this will change if I'm on SQL server, it'll change the authentication to reflect that if I'm in Oracle or any one of these databases authentication will say, and then I put it on my information, I'm going to text my connection and I'm going to save it. The minute that I click this save, it adds this connection over here to the tree. It's going to add the Google BigQuery to the left-hand side and what that allows me to do is it allows me to get to one of any of the main screens that I have within Aqua Data Studio. Whether I'm creating a query, I'm helping to automate that query or I'm building a new query or I want to do something with those particular objects or database and I want to build some analytics on it. So it's going to build this tree and we're going to talk about the options that you have. When I'm creating this connection file, it saves this connection file in my user home directory the minute I click save. But I have some options here. I have general, filter, advanced, driver, permission, script, fluid shell, connection monitor. I'm going to go through these options because here's where the complexity comes in when you don't realize with using Aqua Data Studio and how you can manipulate this. So when I'm setting up this particular database or this particular connection to the server, I can include the filter catalogs. I can say only connect to this particular, let's say I'm working on an Aura Apps application and it's stored on this database, but I only want to get to this particular schema or I only want to include what I'm doing there. I know that's going to load 56,000 objects, so I don't want to load everything that has to do. So I can include how I'm getting to the filter catalogs and I can exclude what I don't want to see. So if I want it to be faster, I can use this way of connecting and manipulate that. Not only do I have the way I'm connecting in and what I want to see, I can also see the folders and how I want to use them as well so I can control my folders. I also can specify if it's a data warehouse, whether I want read, write, if I'm in the advanced, or what I want to display. Do I want to see all the system default players? Do I want to apply the DB filters like I had? And how many connections are pulled? Depending on memory and what I'm doing, what's my fetch size going to be? So I can control this down to the level I need to. I can also, at the time of connection, how I showed you the screen with the distributions of how we're connecting into drivers. Well, we use the native drivers and we use the JDBC connection to get in. You can use the generic ones if you want, but you can also put parameters. I have one customer who connects into the client driver, but then they have added security where they're loading particular parameters on top of this if they want to get in. So right after the way you're connecting, you can load the parameters and on upon this connect particular connection, I can specify what I want to do with that. I also have permission. So let's say I have an analyst on my team. I'm a DBA and I'm actually going to roll out the deployment that I don't want this person. I want them to see the data and be able to analyze and query the data, but I don't want to allow them to insert update these particular statements that are on that database. It's a production database and I don't want my analyst to be able to update it to that level unless they send it to test. We verify it and it goes back. It just depends on the disciplines of your company on how you can control these complex data sets. You don't want that analyst updating or you do. So maybe you would give them, yes, they can insert statements. This is automatically all turned on and you can turn these off and it gets very complex on what you can do with this particular connection. You can also run a script like I talked about at the beginning of a script. So if I want to query certain data information or let's say my boss did give me that template file and I want to update certain rows that I created in the script based on the query then I'm running or based on the Excel spreadsheet, I can run a particular script upon this connection as well. I also, we have a command line driven for people who are really advanced. We have people who like to use a command driven tool. I can turn on the echo commands to see exactly what I'm doing as I'm running them, what went through and then provide me a log. I can enter a shell script to be executed when opening it as well. Let's say I want to connect to certain servers and I run my my TCP and I have a connection file that I use and I connect into those servers and there's some things I do at the time. So this can be pretty advanced as to how you're getting connected in to those database platforms based on what you're doing. I also have server properties over here where I can idle the time and I can say, okay, a maximum snooze time to be interval of whatever I'm doing or the idle time to commit after so many after I've been open and say I'm going through a lot of hops to get to my server and I know it's going to take a long time to run this particular script that I have. Okay, don't kick me out at the time. Don't don't listen to what the security tells me. Let me have a little bit more time. So these connection files can be extremely advanced as to how you're using them and you have different connection files for different servers. I could use that same connection and clone it but I could add more connections for one person. Let's say I want one developer on my team to be connected to two schemas but I have the other developer on me want to see one schema and so I can share that connection file with him and I can include and exclude what I'm connecting to or what they're seeing. So it gets pretty advanced to what you can do. Now the only thing we don't do on the connection file is we don't pass the passwords. So you'll always have to enter your password. But for deployment purposes you can share those connection files. I share our connection files all the time within our company and I'm connected as you can see to Mongo on the left, MySQL and all that. Okay. So there's three parts to Aquadata Studios you get pretty advanced. There's the servers which I built my tree over here on the left. There's files which are the files I have over here to the right and then I have my working area here. We have projects as well and I'm going to get into how you use your projects to your advantage a little bit later. So what you have here is a particular way you're working which is really nice. This is the way I work. But these are models. You can create this desktop or you can create your environment however you want. You can drag and drop. They can be high or low above. However you are used to working, you can build it. Also within Aquadata Studio you have options for allowing different syntax changes. So let's say we're creating these scripts and our company has standardized to make sure that our comments have the slash star star slash and I want to infer so as we're doing our code reviews I know that everybody is using this particular connection file. We're all on the same page as we save our scripts and we manipulate them. So you have that. You also have your code identifiers or your statement separators depending on how you work. I always use a semicolon for me because that's just what I'm familiar with when I'm writing my queries. So you can change these options based on the different syntax for that particular database. It does go and play with how you are connected like I said to the left. So I gave you two examples here. So for my SQL to the right you can see I have auto completion turned on. I have auto commit. I have parameterized script but for when I click on MongoDB I also have the JS tree view available to me in the query analyzer and the MongoJS print JSON available as well for Mongo. So every time you click on the options it's context sensitive to what database platform. So you don't have to think about that. We help you with that. So how do we query the data? We have our tree over here to the left. We have our SQL editor, JSON editor depending on what you're doing. As you can see I have some color coding up here like I said. I use green and blue to represent my colors. Red for production I have up here. So what I can do is I can type in information or the way AquaData Studio is when you build your servers list to here you can right click in this menu and pass in the connection information so you don't have to collect every time or you can use the main screens. You can query which is this window right here or the SQL editor or JSON editor depending on what you're using. And then I can automate. Let's say I'm not familiar with how those different disciplines on my team with doing an update statement. So I can say insert an update statement to the query because I have all these automation tools that are available to me and I put it in here format my scripts based on what I selected and my options to make sure that I have my comments a certain way and it's enforcing me to help. And I can see when I'm on the SQL editor what database. So as you can see I'm on green which is my test that's the way I work. So I know that when I update this I'm on test it doesn't matter. The minute I click over to the tab which is another connection to a server it's on red. I know I'm going to update production. So it's very informative to what I can do from running that aspect. So when I query the database this is how I ran a query. So I ran two select statements. I can select star from doctors or select star from invoices. I can have many selects. These are very simple ways of querying the data and you can see I have multiple result sets down here at the bottom. I can double click on the multiple result sets and edit the table data right from within here. If I set up my option to do that. And I can show what I'm doing inside here. I can show an execution plan which is the root. There's tabs down here. This is the grid view for the results but I also have text view. I have an execution plan which will help me decide if that's a good query or the way it ran. I get clients statistics and I'm going to show you what that looks like on the next screen when I get in here. But any time I'm on the tree view on the left hand side and I click it's context sensitive to where I am. So if I click on databases here it'll ask me what do you want to do with that database? Do you want to drop a database? Create a database? If I'm on a table it'll ask me what do you want to do with this table? Create a table. Do you want to select the top 100,000 rows? I can set the parameters to whatever I want. Do you want the SQL? It'll pass in the information and load it in my SQL editor. So it becomes very powerful as to what I can do with what I have loaded on the left hand side based on what I'm connecting to. So on the result set I called up the client statistics. So for this particular select start from job I can see the statistics that I'm running. The number of insert statements there are none. This is very basic statements. The rows affected by this select statement 73 rows. What I'm getting from this particular statement over here, the value and the average if I want to run. So how much time did it take? There's some pretty detailed things I can do with each one of these platforms that I'm connected into. So it can get pretty advanced. I talked about how you query with the structured query language with the standard SQL. There's also ways for querying MongoDB with MongoJS for querying the database for doing for that specific JSON files. When you query it, it takes on the way I set this up for this particular Mongo. So when I connect into my MongoDB, it sees that it's a MongoDB and it does a typical syntax. So you don't have to think of the syntax that is required like I showed you the PL SQL. Same thing for SQL. You don't have to think about the syntax and what you need to do. We kind of guide you in that directory. If this was select star from table, instead of orders in SQL, it would pop up the tables that are available to me. If I did DB orders and I press the period here, it would pop up the find and it would pop in what I have from within here in my collections. If I just did, you know, select star, I could also change this to SQL contacts and do the SQL from the editors as well. So it gets pretty advanced. Let's say that I'm querying the data and I'm getting data out, but I'm not sure exactly what I did a week ago. I'm like, oh my gosh, I queried the data that my boss updated it. We ran the table that he wanted. What happened? So I can keep a SQL history and I can actually click on two of these history that it gives me the date that I ran, the server that I ran. As you can see, I did select statements from SQL server. I ran an Excel file that I got from my boss. I did an Oracle and a Sybase. I'm using all of these manipulations, but I'm not exactly sure. So I want to compare these two Sybase versions. So I could highlight these and right click and run the compare tool or I could double click and load this particular SQL statement in and get the result set again and I can do multiple things with that result set. So the SQL history can store maximum numbers, you know, statements per 100. Maximum history is 200. These intervals are set and you can archive them. They're just files that are kept off in your user home directory. So you can control what you want to do from within here. Also when you're running SQL statements and you're learning or you're pretty advanced and you don't want to do a lot of typing, not only do we guide you through the process of how to do the SQL for a particular platform because it's complex. We also allow you to have a multi-serial mini. What we do is we have a query builder that will build these queries for you. And what it is, this is a graphical you connect to your particular database. You right click and say I want to do the query analyzer in the query builder or you highlight a couple of things, right click and load it in the query builder or you can go to the main menu and select query builder from new. What it'll do is it'll see your connection. It'll pass in your connection. You can see I'm connected to databases here. I have my client table and I dragged over my broker information and my client information. So all they have to do is select it and drag it over here. It automatically builds the SQL from within here to show me how to build that. And then I can run it and see what I get. I can apply where clauses. I'm not familiar with writing them. I can say, okay, where broker ID is greater than 100. It infers a relationship if it's a relational data data type. Or if I selected on that connection for it to try and infer the relationships based on the naming connections, I could do that. So I could do order advisor having it automatically. It's a fast way for somebody who's applying this inside of your scripts or somebody who's new to the system of querying the data. You can also run same vendor query, meaning that I have two different connections here to two different servers and I can do an outer join or an inner join and I can try and see if I can make sense of the data somewhere else. You can't do one database platform to another database platform like SQL server to Oracle that won't work because the disciplines are different for the databases. So what I could do for creating my script to help me with getting faster for these multiple environments is that I can select multiple tables at once and then I can say script object to windows as create statements for these. Script them as to a template script. If I wanted to script these files as one single file or allow them into multiple script files. And there's a lot of things I could do with this generating script for me. So it helps me to be a lot quicker with managing these scripts and doing them. I could also infer the relationships. We have an entity relationship diagram. So let's say my company is on Oracle. I used to the example and they purchased Postgreps and I'm exactly sure how Postgreps works. I can connect to that Postgreps and I can reverse engineer that. This happens to be an inner base one. Company has inner base. I'm not familiar with the system so I go okay connect to that inner base and reverse engineer it. Then it'll give me a graphical representation of what those tables look like. What is included. I could do this for the metadata. Then I could start clicking and building relationships if I wanted to within here. Associate them however I want. Or if I'm building a database from scratch and I want to do that. I can redo those. I can also build a database from scratch here and then create the scripts to manage them from within there. So one particular area that I didn't talk about and I wanted to get into is using the project tabs from within AquaData Studio. Not only do we have a way for you to create your queries and to create scripts which scripts like again are multiple commands for querying data out or doing multiple commands. Sometimes you have to begin and end and then you have embedded SQL statements. Well AquaData Studio well AquaData Studio has AquaScript so we create proprietary scripting language that we use from within AquaData Studio. We have a project tab which is kind of a grouping like you would consider projects. You can put user files or connections or connections in there and then you have these scripting files that you can use from within there as well. So what they provide is a self-contained programming unit that allows you to create a set of scripts that work on set of database or servers. So you can manage multiple database servers and create these scripts all within a project which is really nice. So one project is completed and it may be exported and shared with others so if you want to be on the same project you can do that. We deliver some canned AquaScripts. So one we give you the template for creating your own if you're very familiar with Java you'll probably understand this. Create an email and Excel file as an example I'm going to use here. Data schema and data exporter if you just want to pull subsets of data and run those off and schedule those. File transfer or remote command line execution if you want to transfer files you can do that. Multi-server script that's good. This is really nice because I said you can create multi-scripts and individual files or consolidate them all in one and then you can execute them on multiple servers that much so we help you build that and do that. Random table and generation I use this all the time. Let's say that you just received a connection to a database and you're not familiar with the database but you are but there's no data there yet and you want to see if you gave the instructions to the DBA or you want to see if you had enough space on there and what it could handle what you could do is you can random table generate on that particular database like 56,000 rows or whatever you want to have it select the tables for you and then you have some data that you can play with until it's up and running and until you've actually moved data in there that you want to play with or you can specify the naming conventions of that particular data like start with AO or start with whatever you want you can specify the naming conventions so that you know it's random table and data generation what you've done. You also have a way of comparing schemas if you want these are some of the tool sets that we're going to get into that we deliver but what you do is you set up your connection so this one I'm using SQL Server on the right-hand side I'm selecting from DBO doctors and I'm going to create an email and Excel file to myself I gave the path a word it's going to build it I'm going to select to its tooth I want to send this to my boss and copy me or whatever I specify who it's from and then I text the Excel file and I email it and I'm going to create the Excel file and I'm going to do it now there's a blog that I created out there using projects for multi scripting environment you can read that blog if you want to get more details or we have tons of videos and resources out there for being able to do this so what appears in the query analyzer again the query analyzer is our SQL editor this is the template that's going to appear within there it put in the the server name that I selected which was SQL Server it selected the database that I specified and it select what I'm pulling and now it's going to email the file to me and it's going to set it up what's more important is that I have a directory structure now of a project that I created so I have these aqua scripts and I have projects that I can create from scratch I don't have to use what we have here but it created this aqua script create an email Excel file this is the folder that created based on what I'm doing it's automatically going to load the server which was a SQL server version that I have here and it's going to load the aqua scripts that I have decided to run from within here so I could have files in here as any one of the files so if I've created scripts in the past and I just want to organize them it's just a container and where I can load these files I can put them in different directory structures and share that that project so there's three ways of working you can use with an aqua data studio there's the servers tab which was the tree view that we built there's the files way of organizing your files which could be Excel files like I told you I'm going to talk about and then the projects that keeping it in the contained spot or using it from within here and loading the aqua script and how I do that I can also integrate these in with source control here I'm configuring source control for version control we offer CVS, SVN, Git and Perforce the top five to integrate in with I can control the file system check them in and check out and enforce our developers to use that again we said you can enforce the scripting if you have certain standards to have comments look a certain way to have your parameters look a certain way you can do that as well and then tools to help you manage all these environments as these environments get more complex and more as you have more platforms you're adding to these environments you need some tools for doing this you need some compare tools for running your schema compares you need to compare your dev to test let's say I loaded the schema on test and then on my testing box from my production box and I forgot to create a table or something I can run comparison it won't give me a difference between the two I could also do file comparison if I'm running comparing two scripts I can either highlight those scripts from within the SQL editor window that I ran run a compare from there or I can select two files from the directory structure that I had used and copy and compare I can also run SQL statements that I've loaded so I never have to leave in my results compare so if I get the results I can results compare on those particular scripts that I ran or those particular individual SQL statements so if I'm not familiar with writing the SQL statements it gave me a SQL statement when I use the query builder I can compare that SQL statement to a basic one as I put in a where clause or as I'm getting more familiar as I get more advanced those compares I know exactly what I'm looking for those scripts can be extremely long and I can load and compare those schema script generator is also used to be able to generate those schemas and being able to to have me compare those and then import and export data how many times you import data in from variable sources you import data from CSV files and from you know from Excel files and you're always importing data as somebody says hey I need this data loaded over here I made some changes please import this into the test box and you're constantly doing that so this is a quick way of doing the mapping and importing that in within your connections so again you have your projects you have the projects you created here some that I did the BigQuery create an email test project when I was testing it so I organize all my projects and these are the tool sets that help me manage it there's an example of synchronizing the schema so I have my source and target here it color code makes the differences between the two let's say I did my source and target backwards and I needed to switch them I could just click this arrow and it would change the direction of how I wanted to synchronize this particular schema with what I have again you're synchronizing the schema with the same database platform so now that you have all this data and you're manipulating it and we're doing things how do you report on it so what you want to do is you want to provide some analytics right you want to broaden your access to a wide variety of data sources we're doing that with Aqua Data Studio we're managing with one IDE even though they have different disciplines that looks and feels the same we're simplifying our querying analysis for doing that our query builder looks the same our SQL editor looks the same and we're going to analyze our results and updates that we got Aqua Data Studio provides a robust palette of graphs and charts for creating these visualizations now that we've tapped into the data and it provides an environment for development collaboration and integration we talked about the big DevOps team or if you don't have a team and you just need to communicate with the co-worker hey I updated these files or actually the data looks a little off to me can I send you what I want I had asked my guy that works with us who has a degree in business intelligence and he's the biggest BI guy and I was asking him what are the differences between business intelligence and visual analytics I could ask this question all the time and this is what he gave me he says that the business intelligence is your review mirror what happened when who and why and how many you take that data and you want to see what happened to it you want to report your KPIs on it you want to have dashboards ad hoc query you want to be able to see what you're doing and the advanced analytics is what you want to predict for in the future what will happen this happened five years ago what if I make this one change this year what's it going to do for me how do I get in to get my statistical quantitative analysis from what I'm doing they both are dealing with big data they both are structured in some of the structured data and the knowledge generation is manual or automatic I think these crossover and then the business users I think these crossover too you have business users who are the business VI intelligence guy but now those are kind of some of the analytics those personas crossover you have your data scientists your business analyst your IT and business users these are the reports that you're going to provide team management on what we want to do with our particular analytical so the business intelligent is reactive and then you have the advanced analytics for features is proactive that was his definition so how does aqua data studio and how do we manage these complex environments to provide this business analytics how do we tell a story with what we want to do well one we queried the data and we got the results said and now we want to deliver that result set or make sense of it so we're going to run the result set and based on how we create this is where I said I use an Excel file but it could be anyone of these data sources you can do anything that you can query any one of these servers that you set up as long as you can write a query to it you can create analytics on it so if I click on you know this little icon it's going to say new visual analytics workbook or attitude this team workbook if I click on this what happens is it pulls up the visual analytics and like the query builder it shows a graphical representation of the analytics and it divides them up based on the type of data types that I have so it'll say this is for dimension and measures and it'll load it differently then I can drag and drop and build these particular worksheets based on that so what I've done is I created three worksheets profit by product category the example that I showed below here is from a bike shop so I'm loading a bike shop bike shop I own a bike shop business and I want to see what's selling the most so there's over 50 visual visualizations such as you can use with aqua data studio and you can click and change them right on the fly it's pretty easy it takes two minutes I did this less than three minutes doing this visual analytics so by looking at this I can make sense for this particular bike shop I can see that my locks are selling the most over here by a graph chart and I can drill down and get the data I can also have a pie chart so what I did was I just copied this and I loaded it into a pie chart and then I highlighted the area that I wanted to see which was like profit by state so I have profit by product category profit by product state and then I have weekly sales over time so I can start thinking of where we want to go so this is what we did before now if I make a change to one of these data points then where do we want to go next or what do we want to do what is California doing that we need to implement and the other regions that we have here so are the other states that we have here so what I can do is I can drill down in the data now as I create these particular worksheets I add them to a dashboard and I can share this with my boss I can have this dashboard not only am I giving in the dashboard but I'm getting the data that's backing up each one of these analytics that I have done so far and said this is what we've done and here's the data to prove it now he can make a change to the data that you can have links and he can update this and say hey I made a change and this is how it reflects it so you can do some pretty advanced analytics with what we have with Bakuigeta Studio so what did we cover today in today's database environment they're more complicated you have a hybrid environment you have some cloud environments you have the no SQL you have the SQL you have your data warehousing and it's not just a simple type of data store anymore so how are you managing the different platforms Bakuigeta Studio will allow you to manage those platforms with one interface so I can clearly set up those connection files and those servers different based on where I'm connecting so my oracle could be different than my SQL server I could have two oracles pointing to the same schema but have them different disciplines for those connection files so DBA could be you know getting everything from there but my analysts could only see in a subset of data I also can control how who's doing upside and how I'm running those and then I can manage where I'm running the queries from so each one of those queries I can load them I could also create them into a project and manage the projects across those servers if I want and then I have the aqua scripts to allow me to advance that into building more advanced scripts for running offline or scheduling or what I want to do with that then I take all that data from all these multiple environments and I create a data story with it I build analytics to what are we doing what are we done with this with this information how are we able to collect as much data as we can from different resources I can connect it from Excel I can connect it to the different resources I can gather data off the web I can infer you know the regions and what we're doing just by connecting to what I'm doing so Aqua Data Studio makes it easier to make these business decisions and to connect to these advance and multiple environments with these complex environments now that we're getting into in the future and what we're doing with today and how our personas are crossing over so that's the end of my presentation I hope that it helps I'm going to open it up to questions Lisa thank you so much for this presentation if you have any questions for Lisa feel free to submit them in the bottom right hand corner of your screen in the Q&A section and just to answer the most commonly asked questions just a reminder I will send a follow-up email to all registrants by end of day Thursday for this presentation with links to the slides and links to the recording of the session and everybody's very quiet right now Lisa no questions coming in yet everyone's having a holiday lunch so just when will you offer Amazon RDS support oh that that will be out in January so we're at we're offering the Amazon RDS support for MariaDB MySQL SQL Server Oracle and Postgres and that'll be in the next release which will be GA in January 30 past Q8 so we're really excited about that nice I love it so can this still connect to mainframe databases any data sources if you can connect to ODBC or JDBC if it's not a supported platform that we have then you might not get everything that is associated with that particular like the new because we offer native connectivity some of the objects not be might not be available to you but we have some people that are connecting to different data sources that we don't support and they get in through ODBC or JDBC connections and they're able to work from the tree query them fabulous again everybody's very quiet oh is there any to any connection to MS data catalog I'm not exactly sure we have the Microsoft I'm not exactly sure the catalog like I said if you can connect in via ODBC or if you can get in the JDBC drivers and you can try and connect to them but the database platforms that we have support are here there we go these are the database platforms that we've supported and tested and run through a rigorous QA but we do have people that are connecting to different different data sources for sure I love it that's quite the variety any other questions we've got going on again it's a nice holiday season everyone's very quiet everyone's full new features coming out for Snowflake yeah we do we have secure channels secure tunnel support and we have portable boarding for Snowflake also if you go to I'm going to show you on the screen let's see let's see I'm going to take you to AquaFold we have a list of resources out here so if you go to AquaData Studio and you go to resources I like the way this is organized is that if you're looking for resources you can sort by resource type so if you want to see data sheets or you just want to watch a video because you're not exactly sure how something works you can take you can take a a tutorial and you could go through and do a video let's say I want to do a video on video database administration or database development I can click on video and then I can do database development from within here and it'll sort to the right of what exactly I'm doing if I just want to learn about visual analytics or like I said I did database videos there's one for interbase Snowflake there's AquaData Studio overview demonstration and it has everything that has to do with development so if I'm a developer and I'm trying to figure out how I develop on the database then I can watch one of these videos with AquaData Studio it's really nice on being able to sort and being able to do that so you can go out and take a trial of AquaData Studio it's a 14-day trial so if you go and download then you have it for 14 days and you can trial the functionality that we have separate important export that's on the trial that's the only thing that we don't have on the trial nice and is this tool available on different cloud environments as your AWS etc? yes all the cloud environments are listed on that sheet but there's a lot of cloud environments there's Snowflake, Mongo, JS there's a bunch of cloud environments that are supported with that you'll have the RDS is coming out you have a bunch of cloud environments that are available with AquaData Studio and you can see the list of stuff here and that looks again to me all the questions we have for today right thank you for having me oh thank you so much for the presentation and thanks to all of our attendees and again just a reminder I will send a follow-up email by end of a Thursday for this presentation with links to the slides and the recording of the session I hope you all have a great day thank you bye bye