Jun 29, 2020 8:45:00 AM by Quest Software
Everyone wants data. Here’s a new webcast series on Data Preparation Made Easy.
Extracting and preparing data for transformation and analysis is a critical function for DBAs as they support data scientists and business analysts. Toad® Data Point is a great tool for getting the right data into the hands of the people who need it. That’s why we’ve designed a whole new webcast series on Data Preparation Made Easy.
Our third session features Product Manager Julie Hyman, on July 9 at 11:00 am ET. Julie will be presenting Toad Data Point Workbook 101: The Ins and Outs.
Introduced in Toad Data Point Professional version 5, the Workbook is an alternative and intuitive interface for many product features. If you haven’t tried Workbook yet, give us 30 minutes and we’ll demonstrate how it can help you with data preparation.
In this webcast, Julie Hyman, Product Manager will show you how to:
You’ll come away with an appreciation for the many ways Workbook streamlines building queries, pivot grids and reports.
Be on the lookout for a new program on the second Thursday of every month. Each 30-minute session includes step-by-step guidance on an everyday challenge to simplify and streamline your work, followed by a helpful Q&A. So get ready to learn something new about data preparation with Toad Data Point.
Our September, October and November webinars will focus on Toad Data Point Automation, Excel and pivot grids. Do you need answers to questions you have on these topics? Click the link below and submit your questions.
If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post. Thanks!
Try Toad Data Point for free
Learn how Toad Data Point can help you access and prepare data faster. Seamlessly access more than 50 data sources—both on premises and in the cloud—and switch between these data source with near zero transition times.
Get started with our free 30-day trial.
Julie Hyman makes it abundantly clear that she knows how to get around in Toad® Data Point, kicking off our new Toad Data Point webcast series Data Preparation Made Easy.
Our first session features Julie Hyman, Product Manager. Julie presents How to Navigate Toad Data Point – Tips and Tricks and demonstrates how to get the most value from Toad Data Point.
Julie Hyman makes it abundantly clear that she knows how to get around in Toad Data Point. And she wants the same for all of us, so Julie shows the many ways to navigate through Toad Data Point with helpful tips, settings and color coding to make data preparation easy.
Watch: How to navigate Toad Data Point on demand and view this quick, 30-minute session and sign up for future sessions.
Kicking off the first session in data preparation, Julie first introduces the left-hand Navigation Manager of Toad Data Point version 5.1.6. It consists of three sections where you can view the database connections, explore objects and view the fields within the specified tables. Each section has a dotted bar that allows it to expand or collapse.
Within the connections area, you can organize similar items into groups, such as connections to Excel spreadsheets or different types of databases. You can also rename individual connections to make them more intuitive.
One of the most helpful features is the ability to assign a color code to a particular connection. This code appears as a colored dot beside the connection in the Navigation Manager, and by using option settings, you can have the color extend to the Object Explorer and to tabs in the query editor. This provides a powerful visual signpost that reinforces which connection you’re currently using.
Another handy feature is the ability to import and export connections using an XML file. This is useful if you want to share connection information with another user, or simply keep a backup of connections you’ve already set up.
The Query Builder area also has some powerful navigation features that allow you to see the queries as you’re building them. Using the F9 function key, you can immediately run the query – opening a new tab for each set of results. So if you change a query parameter, you can toggle between tabs to see how the query results change.
Julie also showed the options for changing the entire visual theme of Toad Data Point, including a dark option for those who prefer it. Julie seems to prefer the standard Toad theme!
There are many more useful tips in this presentation that pave the way to easy data preparation that would be helpful for new and experienced Toad Data Point users alike.
Q: Will connections.xml (the connection export) store passwords and are they encrypted?
A: Connections.xml stores encrypted passwords if user has ‘save password’ checked on the connection properties.
Q: How to create a chart from within a data report?
A: To learn how to add charts to reports, you can search inside the help (F1 for help) for “Create Charts Using Chart Designer”
Q: I tried running a procedure to one of my database connections and got an error that the command is not available in read only connection. Is it possible to run procedures or see procedure code in Toad Data Point?
A: You can call procedures within Toad Data Point, however if you only have Read Only rights to the database then the database will not allow you to call procedures OR if you have installed Toad Data Point in “Read Only” mode then you also cannot call procedures.
Q: Does the connection export also export the ODBC information?
A: You can export information about ODBC connections, but it will not include the ODBC drivers or create an ODBC connection with the correct name – you will need to add the ODBC connection to the ODBC manager manually.
Q: Is there a way to export ER diagrams to another tool such as Visio or LucentCharts?
A: No, Toad Data Point only saves diagrams in the Toad Data Point diagram format.
Q: I know when I run wizards the entire instructions are cut off and do display and there is no scroll bar to scroll down to see the entire message. Anyway to adjust that?
A: You can alter your screen settings, but I would suggest calling support so they can walk you through choosing the right settings for your environment.
Q: How can I change the Fetch to default to "Fetch All" rather than "Fetch 1000 rows"?
A: This can be changed at the top of the query window for an individual query or you can change the default settings under Tools > Options > Database > general settings.
In May’s session, Julie Hyman, Product Manager, covers how to navigate easily through Toad Data Point. During June’s session, she demonstrates how easy it is to create a query and automate it.
Using Toad Data Point v5.1.6, Julie first shows how easy it is to select one or more databases to query. They can be any type of database in your environment; for the demo she uses MySQL and SQL Server databases. After selecting the databases, she drags a table from each into the visual query builder area of the application, and from there checks the fields to query and draw connections between the tables.
Knowing beforehand which tables contain the data needed for the query could be hardest part of this process. In large organizations with multiple databases and hundreds of tables, it could be difficult to pinpoint the exact data needed. But once you know it, the rest of the process within Toad Data Point is easy.
Once you identify the tables and fields, the visual query builder lets you specify the number of rows to return and apply filters with WHERE clauses. Then, you just run the query and get the results.
Once you have your query results, you can choose to analyze them further within Toad Data Point using pivot grids and visualizations. Or, you can opt to export the results directly into an Excel spreadsheet. Subsequent query results and pivot grids can be saved to the spreadsheet – either overwriting an existing sheet or appending a new one. Queries and pivot grids can also be saved within Toad Data Point for reuse.
Julie uses the traditional view of Toad Data Point, and calls out an important difference between the traditional and Workbook views. In traditional, all the artifacts, such as pivot grids and reports, are independent of each other. So, a change to the query won't generate an update to an associated pivot grid. In Workbook, the artifacts are connected, so a change will cascade through the connected items automatically.
The last part of the session focuses on automating the query and other associated tasks like creating a pivot grid, exporting results and emailing them to team members. This is done from the automation builder, where you visually select the steps of the automation and configure each one with a variety of settings. Each automation step icon can be renamed for clarity, and of course the entire automation sequence can be saved and scheduled for execution. Because Toad Data Point runs in Windows, it takes advantage of the Windows Scheduler for running automations.
Be sure to join Julie again on July 9 when she’ll demonstrate many of these concepts using the Workbook feature of Toad Data Point. Workbook is an intuitive interface designed for users and business analysts who just want to get at the data without some of the technical overhead.
Toad Data Point 101—The Essentials
Q: When you export a pivot grid, can you also export the graph?
A: You cannot export the chart via the export options on the pivot grid screen, but you can choose to include the chart if you “print” the pivot. Choosing to “Print” the pivot will bring up a print preview screen and from there you can choose to export the print preview (with chart) to Excel or other formats.
Q: When exporting to Excel, how do you append data to a worksheet? And, the reverse of that question, how do you make sure you are not appending data when exporting to Excel?
A: You have a lot of options when you are exporting to Excel through Toad Data Point Automation.
Q: Do I need to be logged into Toad Data Point on a workstation for a scheduled automation to run?
A: Your PC needs to be on, and you need to be logged into your network if your automation requires network access to complete, but you do not need to have Toad Data Point open for your automations to run. Your Windows Scheduler will kick off your automations as a background process at the scheduled time(s).
Q: Is there any benefit to pivoting in Toad Data Point rather than Excel?
A: When you create a pivot in Excel, you need to have all of the detailed data in Excel as well, which can create very large Excel workbooks. Also Toad Data Point offers some aggregation choices like Distinct Count and ListAgg (read about ListAgg here - https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030 ) that are not available in Excel.
Q: How do you disable an automation step?
A: You can disable any automation step you want to skip in a run, by right-mouse clicking on the step and deselecting Enabled.
Q: How I add a variable – like “run this query for today’s date” where “today’s date” is calculated on the fly?
A: For queries in the Toad Data Point Visual Query Builder (VQB) you can select options for relative dates (today, the current month, this year, next year, last year, etc) when you add a where clause to a date field.
You can also add a date prompt or anything kind of variable prompt as well.
You can automate these kinds of queries by adding a “Set variable” action to define how the automation should populate the variable during an scheduled run – for more info on this see https://blog.toadworld.com/2010/01/31/automation-variables-automation-variables-using-datasets
Q: In Automation can you name an Excel sheet anything you want when you write out to Excel?
A: Yes – you have control over the name of the sheets you create in Excel
Q: How can you open multiple databases to query both databases in a single query?
A: in Toad Data Point Pro you can create “Cross Connection Queries” which opens up a visual query builder ready to accept tables from different databases. See https://blog.toadworld.com/performing-cross-connection-queries-with-toad-data-point for more info
Q: Is it possible to use cross connection queries for UPDATE and INSERT or just selects?
A: Cross connection queries can only be SELECT queries.
Q: Do I have to run automations on my desktop or can I run them on a Server?
A: By adding Toad Intelligence Central into your environment you can publish automations from Toad Data Point to Toad Intelligence Central so that they run on a server.
Q: Will Toad Data Point be available for MacOS?
A: At this time we do not have plans for Toad Data Point on MacOS.
Q: When you export to an existing excel spreadsheet that has Excel's conditional formatting, will the conditional formatting be preserved or erased?
A: Yes – in many conditions you can retain your Excel formatting. For example, if you are using and Excel template that has conditional formatting on a Pivot table - you can export data to the ‘Data’ worksheet and retain the Excel conditional formatting.
Written by Quest Software