Transcript of the webcast:
Why Toad Data Point is the Perfect Replacement for Brio
[00:00:01.77] I think it's going, so we're good. All right. Well, welcome, everybody. This is Michael Micalizzi with Quest Software. Thank you for joining us for our webcast this morning, Why Toad Data Point is the Perfect Replacement for Brio. And I wanted to say good morning to all of you here on the west coast. And those of you in the central and mountain time zones, good afternoon. Thank you for joining us today.
[00:00:28.10] Our session today is scheduled to last an hour. We are recording the session, so I'll make sure that we do send a copy of the recording out to all of those who are attending our session today. And so you'll have a copy of the recording to share with any colleagues or co-workers who might also be interested in this information. Just real briefly, an overview of our agenda today.
[00:00:51.19] We'll just make a couple of introductions so you know who we are. And then we're going to go through a brief overview of Toad Data Point and then Matthew Phan will be taking you through a product demonstration. And then we'll have questions and answers as we wrap up towards the end of the hour today. Although, I do want to encourage you folks, if you do have questions, there's a Q&A window down at the bottom right hand side of your screen.
[00:01:16.24] You can type your questions in there. I will be monitoring the Q&A session and I'll try to interrupt as we can, so that we can get those questions answered for you. And you also will be able to unleash your lines. Everyone's lines are muted currently, but we'll meet your lines that you can ask questions directly over the phone at the end of the presentation. We do encourage that the questions-- we really want to make this as organic as possible for all of you on the phone.
[00:01:44.02] So who are we? So a couple of quick introductions. So myself, my name is Michael Micalizzi. I'm the Regional Director of North American Sales for Western United States here, responsible for database tools, solutions. Primarily, our Toad products, as well as our ApexSQL acquisition that Quest made earlier this year. I've been with quest for 14 years in sales and marketing, mostly with our Toad and Database Performance Monitoring Solutions.
[00:02:12.58] And our presenter today-- our product expert, Matthew Phan is a systems consultant here with Quest Software, focused primarily on our Toad solutions as well. And like myself, Matthew has been with Quest for 14 years working as a support engineer and as a systems consultant for Toad. And in a previous life, Matthew spent some time as an Oracle database developer as well. So he has a high technical acumen and a very deep knowledge of our solutions. So he'll be able to answer your questions and show you what Toad's all about.
[00:02:45.88] So with that being said, I would like to go ahead and turn it over to Matthew. Matthew, take it away.
[00:02:51.44] Thanks, Michael, for an introduction. Hi, everybody. My name is Matthew Phan, and as Michael mentioned, I'm a systems consultant at Quest Software. I specialize in all of our Toad Database Solutions. Thanks again for joining us today. You probably got invited to this webinar, because you're either a current or former Toad user. Or maybe you currently use Brio, and you've been evaluating Toad as an alternative or replacement for Brio. Or maybe you've found us on LinkedIn or to an email campaign.
[00:03:18.61] However you found us, I appreciate you spending some time with us today. If you've used Toad before or seen Toad Data Point before, or if you tried it out before in the past and I wanted to see what was the session was all about, and what it's about is that we've recently released a new version of Toad Data Point that has a very special UI. My too long, didn't read summary of it is, Toad Data Point has a brand new UI that's developed in partnership with our ex-Brio users to make two data point look and feel and behave more like Brio or Hyperion IR.
[00:03:55.33] So I'm going to go through a few slides to set up that discussion points. Then I'll demonstrate the new UI. And then, like Michael mentioned, we'll take some questions towards the end. Again, if you have questions, feel free to submit them into the chat window, and we'll get those addressed along the way if we can. So why would you want to replace Brio? Well, this is based on real feedback that I myself and my colleagues get from customers all over the US. Excuse me.
[00:04:23.89] Brio is a great tool, but it's gone through a lot, and it's starting to show its age. Brio was bought out by Hyperion, then Hyperion was bought out by Oracle. And then Oracle ended support in around 2015 or so. And then Oracle has been trying to push customers to their BI solutions, which hasn't been a great fit for a lot of people. A lot of folks have been kind of stuck on Brio since then. And due to its end of life, there's no support beyond other tools like support integration with things like Internet Explorer 10, Windows 8, and so on.
[00:05:00.97] So I speak from experience that many of our customers have been trying to, for example, move on to Windows 10, but they can't because of incompatibility issues with Brio or it's not certified anymore by their IT departments, or something like that. So they're kind of stuck on old versions of Windows, stuck on old versions of Internet Explorer, and so on. Brio also doesn't connect to some of the more modern data sources, cloud sources, things like Azure and Amazon. And no support for big data sources like Hadoop or NoSQL data sources like MongoDB and so forth.
[00:05:40.39] And also a lot of people have invested time into learning Brio. And learning a new tool can be hard, but the database market is constantly evolving, especially in the last few years. And users really need to adapt to new tooling to take advantage of new functionality and new opportunity and new performance that these new platforms offer. So document databases, in-memory databases, cloud sources, and things like that. So if you've been using Brio for 10 years-- 10 plus years, and then you move on to a new company that doesn't have Brio, or now uses Azure SQL or Caldera, how are you going to adapt if you don't have a replacement in line for your Brio? Those are some things that you might want to think about and consider when thinking about maybe moving on from Brio.
[00:06:33.95] Here's some other reasons why you might be looking for a new solution like Toad Data Point. Although you might be an expert in SQL on one platform, you might not know all the ins and outs of another platform. Some functions that work on SQL Server might not work on Oracle, or the call to that function is different. And you definitely don't want to have to get another tool just to learn another platform. You probably already have enough tools. You probably already have too many tools as it is.
[00:07:01.11] You can also avoid spreadsheet sprawl or Excel proliferation. So it's really easy to create an Excel file, and then send it off to somebody, but you have to be mindful. Is this the best way to share data just sending Excel files around? Am I endangering data governance rules? Am I endangering privacy rules of my clients? So try to keep the amount of Excel files you create to a minimum. And to do that you need to have the right tooling.
[00:07:27.88] If you work in the line of business, sometimes you have to go to IT to get the data or request data. If you work in IT, sometimes you have to provide data to the business folks. So Toad Data Point can help bridge the gap and enable users to be more self-sufficient in their data prep needs, while maintaining your data governance. And users can fix data quality issues using wizards and automated processes instead of manual processes. And this can be sped up by using things like scheduling and automated features, like scheduling, using our wizards, things that are built directly in Toad Data Point itself.
[00:08:10.49] So I also want to point out that Toad-- and here at Quest, we have a lot of experience in this area. We've helped a lot of customers move from Hyperion or Brio to a more modern solution. For example, a major US airlines has replaced Brio with Toad Data Point, as well as many-- or multiple Fortune 500 companies. One of the largest universities in the US has replaced Brio with Toad and it is actually one of our partners in making improvements to Toad, making that transition more comfortable for their Brio users.
[00:08:43.14] Probably the most prominent technology research and advisory firms uses Toad for their data prep. And I'll give you a couple of guesses who that might be. And all three of the top three telecommunication companies use Toad Data Point in some fashion or another. But one of them completely replaced Brio with Toad Data Point.
[00:09:08.39] So like I mentioned on our last slide, we treat our customers as partners. And they've been helping us improve Toad Data Point and make it better. They suggest things like putting parameters into the scripting processes, which we've added in version 4.2. They suggested that we have improvements in our pivot grids, so we rebuilt that in version 4.3. An inversion 5.0, we've come out with a brand new UI-- a user interface, working together with our partners-- with their suggestions and how we can make that transition from Brio and Hyperion more comfortable. So that's what I'm going to be demonstrating today, this brand new UI that we released this year to help bridge that gap.
[00:09:53.07] All right. With that, I'm going to go ahead and start doing a product demonstration and switch over to my desktop. So a quick note, when you launch Toad Data Point now, you actually have two entry points. You can use the traditional Toad Data Point UI. So if you use Toad before in the past, this might be more comfortable for you. But if you've used a Brio or Hyperion before in the past, you might want to launch Toad Workbook instead. Both UIs are included in your Toad Data Point Professional Edition. You can pick one or the other, or you can switch between them freely.
[00:10:31.56] So today, we're going to be focused on the Toad Workbook UI. This is Toad Workbook 5.1. And this is what you'll see when you launch that. So the very clean, modern interface. When you started off, it has a blank workbook. It also has a couple of video animated tutorials right here in the window itself. And you can follow along there. You can also click on these links down here below, and that'll open up a web browser to show you even more videos about using Toad Data Point in general.
[00:11:05.17] So it already has a blank workbook for me to work on, so I'm going to starts here. You could think of a workbook like an Excel file. And inside Excel file, you have multiple worksheets. So this workbook-- I'm going to rename this to Webinar Demo. So this is give me the name of my workbook. And inside of a workbook, I have workflows. So I'm going to add a workflow to this workbook.
[00:11:33.02] So the first workflow, I'm going to rename this to GetCustomerData-- or maybe just CustomerData. And then I get the option to say build a result set. I'll click on Build Result Set, this will then ask me, how do I want to get my results set? Where do I want to build my base query? So I have two options. I can either open up a query editor window, and this is great if I know how to write SQL-- I'm proficient running SQL, or I already have a .SQL all file that I want to open up inside of Toad.
[00:12:08.06] But you could also open up a Query Builder, which lets you visually drag and drop tables together and construct a SQL query visually. So I'm going to use this option here. This might be more comfortable for folks who've used tools like Brio or Hyperion before. And so the next screen I'm going to get, because this is a brand new workbook, it's going to ask me for what connections do I want to use when creating this query? You can see all my previous connections here, but if you want to create a new connection, there's a new connection screen up here along the top.
[00:12:40.42] And this is another great value of Toad Data Point, is that it can connect to all kinds of databases. Connect out to traditional relational databases, like your Oracles, your SQL Server. Your cloud sources like Amazon Redshift or SQL Azure. Your data warehouse systems like Teradata, Vertica, Greenplum. It can also connect out to business intelligence data sources. So if you need to query your OBIEE-- Oracle BI Enterprise or your SAP Business Objects, you can get that from in here as well.
[00:13:11.70] You can also connect out to cloud sources, again, like salesforce.com and get queries-- results from Salesforce. And down here below, you can also see we support NoSQL data sources. So if you needed to query a Hadoop database that has Hive on top, you can do that. If you needed to query a MongoDB document database, you could do that. We'll translate your SQL query into the query of that NoSQL platform and give you a tabular results set back.
[00:13:38.24] You can also connect out to files. So if you need to query an Excel file, an Access file, or join these data sets together with maybe a table that you have in a relational database, you could do so as well. So if I select something like Oracle, you can see that we leverage the Oracle Native Client. So your going to get very close to native performance, if not exactly native performance here. There's other dropdowns, select my Oracle platform, fill in my Oracle user name, passwords, schemas, and so forth. If I got multiple clients installed, I can drop that down and select a different client as well.
[00:14:13.27] Or if I'm working with SQL Server, I can dropdown, select SQL Server, and we'll support your SQL Server host. [INAUDIBLE] kinds of authentication, so you can see here that sometimes Brio has issues with [INAUDIBLE] and different authentication methods. Here we support all the modern methods of connecting to a SQL server database. So I'm going to cancel out of here, and I'm actually going to connect to an existing SQL Server database I already have. I'm going to click on Connect.
[00:14:42.75] So now I get a browser here towards the center of my SQL server database-- I should say my SQL server host and all the databases on that host. In this particular instance, I'm going to connect out to the Contoso Retail Data Warehouse, and then I can see the tables, views , and also functions that I can run on the database as well. But in this case, I'm just going to grab some customer data. That's going to be the purpose of this first workflow.
[00:15:11.06] So I'm going to find customer data. I've got a dimensional table of customers here. And I can right click on it and perform some actions. I can view the details of it. I'm just going to drag and drop this customer table onto my Query Builder. And I get this visual representation of this table. So now I could just proceed to check off what I want from this table. I want his first name, middle name, and last name. Maybe I want birth dates, gender, income. And down here below, maybe I want their education as well.
[00:15:45.57] So if I go to the Query tab now, I can see that Toad has forward engineered this query based upon my selection in the diagram. So select these columns from this table, from this database. And I can run this and get a results set inside the same window. Now, I realize that maybe there's some more data that I need, so I can go back to my diagram and I can add in some additional tables from this database. So I'm going to go down here and select Geography and add geography to this Query Builder.
[00:16:17.51] Now, since there's referential integrity behind the scenes-- there's some foreign key constraints in here, it automatically says that, hey, this geography key here maps to geography key over here. That's really helpful. And if that wasn't defined in the database, I can also drag and drop one key onto the other to map them together and draw this line. So it doesn't have to be defined in the database. But when it is, it's helpful that Toad automatically picks that up for me.
[00:16:44.31] Now, on this table-- the geography table, I want continent, city, state, and region. And I can also do additional modifications down here like I can in something like Microsoft Access. But I'm going to use a right click up here. I'm going to filter-- I'm going to add a Where condition. And I'm going to say, where the continent is, and then I can dropdown and select my qualifiers in here. I'm going to say, where the continent is equal to, and then I can leave it like this so I could be prompted for a variable. Or I can dropdown and select actual data from my table inside a SQL server.
[00:17:20.72] I want the North America. Click on OK. And now fetch this data. And now I get my result set without having to type anything on my keyboard. I built this query-- I built this results that just by dragging and dropping some tables together, checking some boxes, filling out some forms, and I have my first data set. All right, so maybe this is good to go so far. But I want to start working with this data. Maybe I need to do some data manipulation on it. Maybe I need to reuse this again later.
[00:17:49.99] So maybe the next step I want to do is I'm going to add the step to this query here. So Add Step, and then select what I want to do with this query result. In this case, first thing I want to do is I'm going to output this to my local storage. I want to save this locally just in case I want to reuse this again later, which I will. You can also publish this. So you can share this on a server, if I want to collaborate and work with my colleagues and they can use the same data set as well. And I can also store it as a view, which I can talk about a little bit later.
[00:18:24.24] So for now, I'm going to store it in my local storage. I'm going to put on a temporary database called the Webinar Demo here. And it's abbreviated CD local storage because this data set is called CustomerData-- or CD, and it's called local storage. That's how we've come up with that name. We'll say OK here. So now I've got this stored in local storage. And I can reuse this again and again if I needed to.
[00:18:49.08] So I'm going to add another step here. Let's say I want to build a pivot grid off of this. I'm going to go ahead and do that. So you can see that everything I do based upon this query is shown right below it. And so if I ever modify this parent query, the changes I do here will be applied to all the child objects down below. And you'll see that in just a moment.
[00:19:14.41] So let's say I'm going to build out a pivot grid here, and I want to get a calculation of maybe the education. And then I want to do something with maybe their income, I'm going to get that. But then I realized I want an additional field in here that I didn't include before. Maybe I want marital status for some reason. And I didn't have that in my parent query. So I can go back to my parent query here, and I can go back to the diagram. And I can add in marital status now. And then I can check my results here to see that now I've got marital status on the far right.
[00:20:03.53] And you see here on the left hand side, it says, the results that updated. Do you want to update all the steps below? I'm going to say, yes. And now, marital status will be available to both the local storage of this data set, as well as this pivot grid that I've created here. So I go back to my pivot now, I could see that I have marital status listed in here now. And I can add that to my pivot grid. And then let's say-- let's add another step at this point and create an Excel file based on this.
[00:20:32.88] So let's go down to report, create an Excel file, and then let's point this Excel file to my documents folder called CDExcel. And I'll say, OK. So if ever I changed the query, or if I need to get the new results set today, I can always hit Refresh and it will run the new query, save the new data, create the new pivot grid, and save a new Excel file all in one go. And that can save me a lot of time from having to open up multiple files and running things individually. This is a great workflow.
[00:21:09.27] Now let's say I want to add some more data, or I want to maybe manipulate the data before it goes into local storage. So I can say, Add Another Step. And this time, I want to do a transform and cleanse. So it opens up this UI for me to perform a transform and cleanse. So I'm going to give myself a little bit more real estate. I'm going to hide this workbook map here. I'm going to find maybe and calculate a new column.
[00:21:42.29] So there's all kinds of things you can do up here. You can create a find and replace, you can do a calculate column. You could filter, format, dedupe, and so on and so on. There's a lot of great stuff in here. I'm going to use this Group Columns here. So I'm going to create a new column, it's going to be based upon the Education column here. So I'm going to call it Education Group. If the education is containing either a bachelor's degree or a graduate degree or partial college, I'm going to say they have higher Ed.
[00:22:18.02] And then I'm going to go over here and they Add Another Criteria. If the education is high school, partial high school, OK, I'm going to say they have no higher Ed. Otherwise, the value is unknown. So I can add calculated columns derive from my current results. So this row here-- I'm sorry, this column here doesn't exist inside of SQL Server. It is a calculator column that I'm generating using Toad Data Point .
[00:22:54.39] And using this wizard is really nice, because I don't have to learn how to do this specifically in Oracle, and then specifically in SQL Server. I use the wizard in that kind of abstracts that for me, and I can just use the same tool across multiple platforms. All right, so now I've got this new calculated column here. I can pin this. And let's go ahead and run that transform and cleanse. And then after I do the cleanse, maybe I want to say that as a new local storage. So let's go down to Output, local storage, and this could be my clean CD local storage. And OK.
[00:23:39.89] So, again, from one base query, I've got all these other subroutines running, and I can refresh that as I need to. All right, so now let's say I want to join this data with data from another platform. It can be another SQL Server database, it can be an Oracle database, it can be an Excel file. I'm going to use another Oracle database as an example. So let's add another workflow to my workbook. And now, I've got workflow number two. Maybe this is going to be, get the application data from my Oracle application. So this could be AppGeoData.
[00:24:17.36] And I'm OK with that. So maybe I have an Oracle application that has the latest geographical information of my customers. So I want to combine my data warehouse data with my application data. So I'm going to say, Build Result Set. I'm going to use the Query Builder again. And this time I'm going to connect out to my Oracle database. And now, I can see-- I can also see multiple database connections on the same Object Explorer. But I'm going to keep it simple. I'm just going to browse into my Oracle database here. Look into my application-- let's say it's called Matthew, and take a look at the GeoCode table in here.
[00:24:57.99] And maybe this is the table that has the newest geography information I want to get for my customers. I'll add that to my Query Builder here. And I want the continent, city, state, and region from here. And let's say I don't need to do any cleanup. I'm just going to say, Add Step. It's asking me that I have to execute it first. So let's go ahead and execute it. And then let's Add Step. And let's go to Output, local storage.
[00:25:29.00] So this is going to be my application geo data, also in local storage. I'm going to say, OK. And then I'm going to add a third step, where I'm going to combine the data from Oracle and SQL Server together. And so I'm going to say, it can be my final report. Build my result set at Query Builder. And this time the data's going to be in my local storage, so I'm going to go up here, connect to local storage, select. It's already connected, so I'll just go up here and browse into local storage.
[00:26:08.20] The database is called Webinar Demo. Let's go down there. And I can see the clean-- oh, this one actually hasn't ran, so let me run it. Now I can see I've got the clean customer data and the application geo data, and I can combine that together into a Query Builder. So let's go to my diagram. Let's go to this Query Builder here. Let's go to Application Data, and let's go to Clean here.
[00:26:46.99] And then I realized, hey, you know what? I forgot to include the geography key, so I can't map these two data sets together. I don't have anything that would make sense here to combine this. I mean, I could probably join the continent name to content name, city name to city name, and so on. But what I want to do is I want to include a geo key that I forgot to add here. So let's remove these.
[00:27:15.34] Let's go back to the parent queries. Let's go to this query here, go to the diagram, add in the geographical key here, and run this again. So now, geo key will be applied to all these items. And then let's go here and also include your key here, and apply that. Now, let's go to the file report and go to my query, and grab the new artifacts in here. I think I need to do a quick refresh.
[00:28:07.84] Now, you see there's a geo key here, and I'll combine this with the clean data that I made. And there's a geo key here, so I'm gong to say, geo key here to geo key here. And this one, I want the-- let's do the names first. Birth date, income, education. Now, up here, I want the continent, city, state, and region from the actual application, not the data warehouse. And I can execute this, and I get my result set that had the data from SQL Server cleansed with the data from Oracle. For completeness sake, let's go back here and add the calculated column that I added. And put that calculated column towards the front, and run this.
[00:29:00.21] So now I've got this SQL Server data, the calculated column I've added, combined with the application data from Oracle. And now, that's ready for a final report. I can say, Add Step. Let's report this out as an Excel file. Let's change the settings here. Let's call this the final report, FRExcel. OK. Now, if I ever need to run this whole thing again, I could just hit the Refresh button at the top and it'll go down this list, update all the objects, update the pivot grid, update this Excel file report, update my saved data, and create a new final report. And then I can also open this report up here and take a look at it in Excel.
[00:29:49.10] Here you go. SQL Server data, the calculated column, the Oracle Data all in one Excel. And then I can also enable sharing, create an email, and say, here's the cleanse data, and here is the final report Excel file. And then include your email information here. So, hopefully, what I've done is shown you something that's very similar to what you've been doing before in Brio and Hyperion.
[00:30:21.06] I think that you have a question.
[00:30:23.33] Yeah, go ahead.
[00:30:23.88] Is there a way to see and or save off the SQL that's used in the transform and cleanse operation?
[00:30:30.69] Sure. Sometimes you can. Sometimes we do something that's outside of just a single query, so we can't show those. So if you want to see that, let me go back to my transform and cleanse job here. Oftentimes, if you open this window here, you can see a value. Let me get to my steps here. So this operation had one step, which is where I created the grouping.
[00:30:58.57] Sometimes you'll see a tab here, and in this tab, shows you what SQL we used to generate this change. Sometimes we showed it to you, sometimes we don't. Again, it depends on what actions were performed and what database platform you're connected to. So you would see that here. There would be a Show SQL tab right here.
[00:31:26.48] Michael were there any other question?
[00:31:27.60] Another question. Another question about using local storage. Customer asks that-- or says that they've moved away from utilizing local storage, because after a set period of time, the temp files can get cleared by Windows. Is there any way to avoid this?
[00:31:44.30] Sure. That's a great question. So in Toad, you have the option of local storage, but you also have the option to publish to the Toad Server. And the Toad Server is called the Toad Intelligence Central Server. I just call it Toad Server for short. So you don't have to store it locally. Let's say, for example, this geo data here, in addition to storing it locally, I want to store it on a server. This would be like a Windows Server. This would have a Windows Server would be on 24/7. It'd probably be housed maybe by the IT department or maybe it's just a PC that we leave on in our department 24/7.
[00:32:21.26] So instead of saying output local storage, I can say output publish and this will save the results to the Toad Server. So on my Toad Server, I've got two connections here, I've have got to Matthew and John because they share data with each other. I'm going to connect that with Matthew. And this also gives me the ability to publish and make this available through a web browser as well. And when I publish to the Intelligence Central, other people can also grab the data and put that into their queries too.
[00:32:52.45] So when I do save to local storage, only I see it, but when I say to the Toad Server, my colleagues can see it and use it too. So here I'm just going to do a quick save here of this. I'm going to put it on the server. I'm going to create a new folder on the server. I'm going to call this Webinar. And I'm going to put this Oracle data there. It's going to be a data set. It's going to be called the Application Geo Data Published. And I can put descriptions and tags on it, and I can specify who I want to share this with, and then I click on OK.
[00:33:28.84] So now you'll see there's another step here is to publish this Oracle Data. And I'll just hit Refresh here. And so other people can grab this, they can connect, they'll see it in here. So I'm connected as Matthew right now. Once this is done publishing, I'll browse to that folder and you'll see the data is available on the server. There it goes in here, default, Webinar, AGD Published. So I can reuse it off the server instead of you using it off my local storage. And my colleagues, like John, who I shared this with, he'll see it when he logged into Toad. He can use it too.
[00:34:08.96] And finally, that Toad Server has a web portal too. So let's say I'm John and I don't use Toad. John can go to the web server, click on LogIn here, and John can see the report that I have created and he can use it in other ways. He can just download it into Excel directly. This Toad Server is also compatible with things like Tableau, or Qlik, or Power BI and can feed those tools directly. So I can create a report, save it on Toad Server that feeds Tableau for example.
[00:34:44.12] But here, I'm just going to go through the web portal as John this time. I can go to the Webinar folder, and I can see AGD Published, this was from Matthew, this was an Oracle database. But maybe I don't even need to know that. Maybe as John, I'm a business user who just want to get the report. I can say download the data. I can apply filtering. I can also apply variables here too if I wanted to enable something like that. So I could see the geographical data based upon what Matthew has published.
[00:35:14.82] And I could just download this through the web browser without having to launch Toad at all. And I can open this up into something like an Excel file. Download and open an Excel, or open it up in Tableau, or something like that. So that's a way around not having to save it locally. Saving it to the server gives you a lot of benefits like sharing, off loading the query, processing to the server, offloading and giving you the ability to set up automation and things like that. So there's really lots of nice benefits on having the Toad Server as well. Other questions, Michael?
[00:36:00.29] Yeah, there's another question about the table fact. Have you implemented the table fact that is currently in Brio as it handles a one too many problem? Not sure if that makes sense to you or not.
[00:36:14.51] I'm not sure I understand that question. If you could perhaps rephrase it, or we can pin that and address that offline. I'd like to take a look at that offline, if we can? OK, any other questions?
[00:36:33.11] Some one else asked, is Toad Intelligence Central the only other way to share with others those views outside of the local storage? Is there other options that we could connect to other than Toad Intelligence Central?
[00:36:49.80] That's a good question. In this interface, the only sharing capabilities is local storage or the Intelligence Central. If you've used the more advanced Toad UI, you can save data back to any other database that you have write permissions. To the more powerful UI gives you some more flexibility. So I could run a query on one database, save the results on another database, and that third database can be the shared server. It doesn't have to be the Toad server.
[00:37:22.68] You can also save files, and then just put files onto a shared drive. If you want to do something like that, you can do that. But the built-in local storage and the built-in sharing to a Toad Server, that's what's available in this UI. Again, we have that more advanced UI. There's some more flexibility there.
[00:37:49.49] Thanks, Matthew. That's all the questions we have now.
[00:37:54.24] All right. So let's see. I think-- yeah, you know what? Let's just take a quick look at the advanced UI just to point out some additional features that you get in that interface above and beyond what you see here. Again, this is the UI that was designed to kind of mimic the Brio, Hyperion UI. If you are building queries, and then building artifacts on top of those queries, and other queries on top of that query, this is the UI for that sort of workflow.
[00:38:30.91] I'm going to close out of here and switch to the other UI. There is an option that you can enable so you can launch both the UI at the same time. I didn't have that enabled, so I'm going to have to shut down completely and launch into the other UI real quick. And so, again, you had the option of using either or, or both. And so this is the more traditional UI if you've used a Toad for Oracle or something like that before in the past, this might look and feel more comfortable.
[00:39:06.94] This is more like direct access to data, do one or two things with that, and then maybe automate it. Whereas, the other one, again, you kind of build on top of your previous work. So let's do a similar example. Let me go and connect. Again, I've got some connections saved here, I can go ahead and connect out to this user interface here. Connect out to my-- open up a Query Builder. Let's build a quick example again using the same sort of data here-- customer data and geography data. And then I'm going to get the first name, middle name, last name. Let's get birthdate.
[00:39:50.24] Let's do email, education, and income. Over here I want continent, city, state, region. And for a similar process here, where the continent is North America, OK. And then execute this. You get your result set. So in this advanced UI, you can right click a data set and there's some more options available to you in this advanced UI. You can go through an export wizard. So you say, OK, read all the data, and then put that in another database. Dump it into an Excel file, make it a HTML table out of it, make an Access file out of it, create this as a new table somewhere else. Make this to a SharePoint file, a CSV file.
[00:40:37.49] You can see there's a lot more options available here. From here, you can also say, jump right in to-- send to the transform and cleanse engine, but before you do that, maybe you want to do some profiling on the data. You want to understand what kind of data you have here. You want to run some basic statistics on this data. I can say, data profile and I can just-- with a right click and say data profile, I get some really great information about my data set. I can change a sample size here. The sample size by default is the first 1,000 rows.
[00:41:12.03] So I can edit my profile sample size. I can say, all the rows, x number of rows, or random 10% of the rows. And I can give my summary here of how well my columns are distributed, what's the population, what's the distinct uniqueness, duplicates, how many minimums-- how many minute missing or null values I might have. What are the minimum values, medians, and maximum values? For example, I've got a lot of null values for middle names, and that might raise some questions. Did I run the right query? Did I join the right tables? Is this data accurate? It might help me flag some invalid data immediately before I start my transformation in cleansing process.
[00:41:52.86] I can jump over to Statistics tab here. And I can quickly see things like, OK, what is the distribution like for my yearly income? And I can get a distribution graph. I can get a statistics about minimum values, averages, quarterly percentiles, standard deviation. And this is interactive too. So if I want to see what my high income customers are, I can click on this bar graph, and then below, I'll see those users who fall within that bar graph. So I can see that April here is one of my high income customers that maybe I want to send some sort of promotion for that.
[00:42:30.61] Frequency, what is the most frequent city or state my customers are in? So in my sample size, 46% of my customers are in California. What are the bottom values? Minnesota, Wyoming, Ohio, I only have a couple customers there. So do I need to do some more marketing there? Do I need to do some more outreach for those states?
[00:42:51.85] Pattern recognition, for the middle names, what is the format of the middle names here? So most people just have single letters in their middle names. Jasmine A Taylor, Rihanna A Hughes, for example. But there's a couple people here with the dot. Jordan C. King, Wyatt L. Hill, is that OK, or do I need to go here and scrub and trim the dot off of these middle names before I do some sort of report on this? If this was going to-- if this column was going to be using a calculation, then I might want to reformat this so that I can do my calculations properly.
[00:43:26.57] I can check for languages. So if this is going to be fed into a tool that doesn't support UTF-8 characters, I want to make sure they're all ASCII characters, I can certainly see that. Most of my character most of my users here have ASCII first name and last names. But there's a couple folks in here who have Latin characters-- UTF-8 characters in their name. So Jose here, is that going to be OK for wherever I'm sending this report off, or does this need to be reformatted so I take the accent mark off the e?
[00:43:55.36] I can also use it to quickly check for duplicates, first name, middle name, last name, and birth. Because if they have the same combination of those four factors, it's probably the same person somehow entered into my data set as a duplicate. So I can check for duplicate and see that. So far I'm good. But just for fun, does anybody have the same birthdate here? Check with duplicates, and there's a couple folks here born in July 15th of 1944, Jasmine and Lawrence here. So really helpful utilities.
[00:44:26.02] Other things like importing and exporting tools. Compare tools, so I can compare the data in a SQL Server database to the data in an Oracle database and compare that to maybe data in an Excel file. The ability to do automation. So if I wanted to say, take this report and generate it and send out an email or update a script, we've had some really great automations built in. So run this query, save it to this file on my desktop. Toad Report, save that. And next here, email your weekly report. See attached next here. Add the report, and then finish this off.
[00:45:18.39] And you can see in automation inside of Toad, it's very powerful. You can say, OK, three things you need do. These are the settings, and then this is a query you want to run, and this is where you want to save it. And then this is the email you want to send off. But let's say you want to create some more files, you just add select to a file. You can add it to this little tree here. If you want to do some importing and exporting of data as part of this workflow, I can also import some data, then run my query. Then save the results to an Excel file, then email it out.
[00:45:47.15] Or maybe now that I've got two files, I want to zip them together. I can go down here to my activities. Let's then zip the files together, then attach the zip file to my email instead. So a very powerful automation here. Again, just drag and drop. I don't have to learn a whole lot of programming or scripting here. A UI for everything. You do everything from uploading FTP files, setting variables, if/then/else conditions, looping through data sets, kicking off other automations, kicking off other programs from in here. Then I can schedule this to run locally, or I can publish just to run automatically off the Toad Server.
[00:46:23.29] So a lot more feature rich capabilities inside this traditional advanced UI. We're moving a lot of this stuff also into the workflow UI-- Workbook UI, I should say. But as it stands, this user interface has some more functionality that the other user interface doesn't have. So that's something to keep in mind. But, again, you get both UIs, you can pick and choose what suits you. If you are more of a power user or there's just one or two things you need to do with the data set, this is the way to go. If you are a Brio user or Hyperion user, the other UI might be better suited.
[00:47:01.46] Or if you have the workflow of having to get a query, and then build multiple artifacts, and then build off that, the Workbook UI is better for that. All right. So before I kind of open up for general questions, a call [INAUDIBLE] to actions. Well, at this point, let's go ahead and just open it up to questions. We've got about 13 minutes left. And then if there are no more questions, we can go over some of these additional resources here. So, Michael, do we have any more questions in the queue?
[00:47:30.63] Yes. Yeah, Matt. There's a question about Toad. Does Toad Data Point require a server to run?
[00:47:38.10] Good question. No, Toad Data Point is a server-- I'm sorry. Toad Data Point is a client install. You install this on your own computer, your own laptop, desktop. It runs, it does not require a server. You use it to connect to database servers, whether it's a server locally or if it's a server in the cloud or something like that. The Toad Data Point, again, is installed on your local machine. But if you want to share your data, you want to collaborate with other people, there is the optional Toad Intelligence Central Server.
[00:48:11.20] So that would be installed on a server, it's optional. But you can store your data there. You can store your automations there. You can store a variety of things there. And you can use that as a way to share and collaborate. So, no, Toad Data Point does not require a server. It runs locally on your own machine. It connects out to servers, though. And we also have an optional server that you can use.
[00:48:35.41] OK. And then there was a question of automating reports. But I think you showed how to automate the reports in that last example. But are there any standard or default reports that are available in Toad Data Point?
[00:48:50.55] OK. Good question. So the Toad Data Point Workbook, it's a pretty new UI. It just came out this year, so the reports you get in the Workbook is going to be an Excel file, a PDF file, or a CSV flat text file. So those are three kinds of things you can generate from the traditional UI. The Toad Data Point UI-- the more advanced UI, or the traditional UI got a lot more options in here.
[00:49:13.97] Once again, if I go to export, Export Wizard, you can see there's some more file types that you can export to, including SharePoint and so on. So this has a few more options in it. Again, in terms of automation, the automation right now is mostly going to be in the advanced UI. But we are, in the next release, going to add more automation capability to the Workbook UI. So stay tuned for that.
[00:49:41.12] And then there's a question about cross platform queries, Matthew. Does the performance of the cross platform queries rely on the user's workstation computer computing power? Or could there be other things that could affect positively or negatively the performance when you're doing a cross platform type of query? Combining data from two different sources, I guess.
[00:50:06.75] Yeah, OK. That's a great question. That's actually a whole session we can spend time on. My tips for that, what I did earlier in the workbook method is I queried from SQL Server, I saved that to local storage. Then I queried from Oracle, and then I saved that to local storage. Then I ran a third query off of local storage to get my final result set. Oftentimes, you're going to get the best performance if you can do that. If you could put everything into a third server, whether it's a local storage, the Intelligence Central, or third database, you get the best performance that way.
[00:50:44.53] However, Toad has this ability called Cross Connection Querying, which lets you do-- lets you just grab two tables and, on the fly, combine them in memory. So if the data set isn't too huge, you can do something like this where, for example-- let me go back to this diagram. Let me say, this geography key doesn't exist here. Or maybe I instead, I want the Oracle version of this geography key-- geography information. I can go directly to the Oracle database and not save this at all to local storage, but it does have to serialize the data somewhere. And it serializes that in RAM.
[00:51:22.23] So as long as you have enough RAM-- and even if you start filling up your RAM, we'll start writing to disk. But you are going to get a performance hit if you fill up your RAM. And, again, we can go on and on about different ways and techniques you can optimize this, different tricks you can do, like using subqueries to offload as much of the work onto the database as you can. But in this example here, I'm to get data from a SQL Server database and an Oracle database without saving it to a local storage.
[00:51:51.92] So I can just say, OK, I want the geography key here, map to geography key over here, continent, city, state, and region. And then I'm going to apply WHERE clause here. In North America. OK, so this method here is what we call the Cross Connection Query, or a heterogeneous query. This will generate a result set that combines the two, but it does this utilizing our-- basically, we do some things in the back-end and combine the data together.
[00:52:25.04] So this is data from SQL Server and data from Oracle all in one go and I didn't have to store it in local storage temporarily or anything like that. So this works. You might have some performance issues depending on a variety of factors, your network speed, the type of query you're running, the amount of data you're getting. So if you can save to it third location, whether it's the local storage or a third database, that would give you the best results. Especially, if you've got huge amounts of data.
[00:52:59.71] But if you're just going to do a prototype or something a million rows or less, something like that, you can probably get away with it doing it on the fly like I'm doing here. And then just taking this and saving this out to a report. So that might require some further discussion. You can play around with it and talk to us. We can set up some webinars with you and kind of go over your specific use cases and what you have available. And we can kind of tailor a solution for you to get you the best performance queries possible.
[00:53:37.91] OK. Seemed to like your answer on that one, Matt. So thanks.
[00:53:44.20] Any other questions, Michael?
[00:53:46.73] I don't have any other questions here in the chat window-- in the Q&A window.
[00:53:53.12] All right. Well, we can open up the phone lines and see if there are any questions. And if it does get too loud, we might have to go ahead and mute all the lines again. But if you want to do that, Michael, we can get at that now.
[00:54:07.97] OK. I'll go ahead and do that. So phone lines should be open. So if you do have a question you want to ask Matthew directly, feel free.
[00:54:30.93] You answered the only questions, Matthew.
[00:54:34.24] Well, we'll keep it open for now and I'll just kind of close off on this last slide here. If you will learn more specifically about the Workbook method I used today-- the UI that I showed you predominantly today, you can go to this URL, toad.com/guide to using toad data point workbook. You might be better off just going directly to the ToadWorld website, which is that second link there, www.toadworld.com. ToadWorld is our community website for everything Toad, but you can go in there click on Blogs, and then you just browse the Toad Data Point blogs. You can also download the free 30-day trial from there as well.
[00:55:10.61] Alternatively, you can also reach out to your account rep. I'm not sure if this is the exact URL to use for that, this might be the URL for an older webinar. But I'm sure the link will still work, but you can also contact us directly. If you've got technical questions, you can send that over to me, mathhew.phan@quest.com. If you've got a business question, a licensing question, a sales question, you can address that to michael.micalizzi@quest.com.
[00:55:35.23] But, hopefully, those will get you going. And if you've used Toad before, I thank you for your loyalty to Toad. And, hopefully, that this enables you to do more, perhaps move away from Brio or Hyperion or have something to look forward to in the future. And one more request for questions.
[00:56:00.40] Any other questions out there?
[00:56:10.22] All right. Once again, you can also send us an email and we'll be happy to answer your questions via email. I know there's one question that we have pinned, we'll try to get that back to you. Other than that, thank you so much. Check out toadworld.com and contact your rep.
[00:56:26.00] Thank you.
[00:56:27.99] Thanks everybody.
[00:56:28.52] Thank you, Matthew. Everyone on the line will be getting an email shortly this afternoon with a link to this webcast so you could download it, rewatch it, and also share it. So thank you again for your time and questions and have a great day everybody. Thank you, Matt.
[00:56:47.38] Thanks, Michael, for setting us up. Have a great week everybody.
[00:56:52.21] You too.
This is a companion discussion topic for the original entry at https://blog.toadworld.com/why-toad-data-point-is-the-perfect-replacement-for-brio