Exploring an Unfamiliar Database with DatabaseSpy

Software developers working on a new app, data professionals in a variety of enterprises, and even database administrators often encounter unfamiliar databases and need a database tool to quickly explore tables and relationships.

Altova DatabaseSpy is a unique multi-database query, design, and comparison tool with a graphical database design editor that empowers users exploring an unfamiliar database to quickly visualize tables, relationships, and even datatype definitions that may be unique among database types.

Read more…

Tags: ,

Update to Altova’s Database Tool Adds Important New Features

DatabaseSpy is the unique database tool that supports all major databases and facilitates database query, design, structure comparison, table content editing and comparison, and even generates elegant charts from query results.

The recent update of DatabaseSpy to version 2017 Release 3 adds several new features, including the ability to automatically generate a complete DDL script for any database schema.

Read more…

Tags: , ,

Mastering Paid Keywords

Anyone who manages paid keyword search knows it is hard work! You can look at vast reports of raw statistics and quickly get lost in trivia. At Altova we designed a better way to analyze and manage the performance data for our Google Adwords campaigns. We can creatively query the numbers to: · Quickly aggregate results for subcategories of campaigns, for instance by product, geographical region, or any other grouping · Easily identify trends over time The chart below illustrates these advantages by collecting data for a single Altova product – SemanticWorks – from multiple campaigns over six individual months. Keyword performance chart created with DatabaseSpy Starting Out Like many keyword advertisers, we were viewing statistics in Adwords, downloading CSV files, then spending hours massaging and manipulating the data in spreadsheets to identify and format the information we required. We wanted more immediate and in-depth reporting of keyword performance while retaining full control of the process and managing everything internally. SQL queries of a database of keyword statistics offer a powerful and flexible alternative. In the remainder of this post we explain how the database design, data mapping, and reporting features of the Altova MissionKit can be applied to create an architecture to efficiently track paid keyword performance. Database Design Our choices were to implement a keywords database on an existing database platform already running in the company, an express edition of a commercial database, or an open-source database, since the Altova MissionKit works with SQL Server®, MySQL®, Oracle®, IBM DB2®, PostgreSQL®, Sybase®, and Microsoft® Access®. We chose SQL Server for our database platform. We connected with DatabaseSpy and used the graphical database Design Editor to create the table shown below. DatabaseSpy graphical table design Most columns correspond to fields in a keywords report. In order to store multiple rows for each individual keyword – one row for every month of statistics – the table also includes columns for the month and year. Populating the Table The Google Adwords online interface lets users create reports of keyword statistics of specific date ranges and download them as CSV files. We downloaded individual CSV files containing our performance data for each unique month. We used MapForce to map values from the CSV files to columns in the database table and insert the month and year data for each row. Keyword report mapping in MapForce The string functions at the bottom center of the mapping diagram remove percent signs and commas from fields we want to treat as numerical data. By doing this in the mapping, we don’t have to massage the columns of data in the CSV files before importing them. Since the CSV files for each month all have the same structure, the mapping needs only minor revisions to import each new month’s data: update the constants at the top that define the starting row id, month, and year. MapForce processes the mapping with its built-in execution engine, reading the CSV input and generating SQL INSERT statements for each row of data. MapForce then allows users to execute the entire generated SQL script by clicking a toolbar icon or from a selection in the Output menu: MapForce database insert script Querying the Database Back in DatabaseSpy, we can query the database from the SQL Editor window. This query reports the top ten performing keywords for SemanticWorks in October 2011. For data privacy, some fields in the Results chart are hidden. Results with table To get additional interesting results, the SQL statement can be easily modified. For instance, the ORDER BY line can sort for highest cost, most clicks, or any other characteristic. The WHERE statement combines data from multiple campaigns. The LIKE keyword treats the percent signs around SemanticWorks as wildcard characters to match any campaign with SemanticWorks anywhere in its name. Other queries could add a geographic identifier such as US or EU, or match on an entirely different column such as adgroup. Of course, all these options depend on a consistent and predictable campaign and adgroup naming system. We created a DatabaseSpy Project to collect all our favorite SQL queries for sharing and convenient reuse. Here is the query we used to generate the chart right in DatabaseSpy that appears at the top of this post: ChartQueryCapture This query goes beyond simple SQL reporting to perform calculations on a subset of the data and format the results. Database Reports We designed reports for the executive team using Altova StyleVision, based on the queries and charts we had already designed in DatabaseSpy. We simply copied our queries from the DatabaseSpy SQL Editor window and added them as sources in the StyleVision Design Overview window. Saving our report design in a StyleVision SPS stylesheet makes it is easy to regenerate an updated version every month. Here is the HTML output for a SemanticWorks Keyword Trends report based on the query above, displayed in the StyleVision Preview window: clip_image009 If you follow the conventional wisdom for building your own paid keyword campaigns, you will develop segmented campaigns with many small, highly specialized ad groups, and you may also find yourself overwhelmed by the data in Adwords reports. If you’d like to try managing your own keywords the way we describe here, a fully functional trial of the Altova MissionKit is available.

Tags: , , , , ,

Creating XML from Relational Databases

Sometimes following an example someone else created is a good way to get a quick start on a project. The downside is you might miss a better, more efficient solution. In our recent post on XML in the Cloud, we used DatabaseSpy to connect to a local MySQL database and to the Amazon Relational Database Service in the cloud. We used the Concat( ) function in a SQL SELECT statement to create XML formatted output from non-XML data as shown below. DatabaseSpy SQL query and result Our SELECT statement was based on an example in the MySQL documentation on XML support. Let’s take a little deeper look at the problem this statement tries to address. You can copy a DatabaseSpy Results table like the one displayed above and paste it into an editing window in XMLSpy, but the Results table alone does not create a well-formed XML document. To be well formed according to the W3C definition, an XML document must contain a root element. All other elements and logical structures must nest within the root. You can also think of the root element as a wrapper around the entire XML content, the same way the element <city></city> encloses each line in our original results. A Better Way to Create XML from Relational Data We don’t need to manually edit the results to add a root element, nor do we need to adapt our already-complicated SQL query to add the root. DatabaseSpy lets us easily export well-formed XML documents from database tables that contain ordinary data like our cities table. In the DatabaseSpy Export dialog we can choose XML Structure as the output format, click the cities table to select it from the database hierarchy, and choose XMLSpy as the destination. The Preview section at the bottom of the Export dialog shows a view into the table contents. DatabaseSpy Export Dialog When we click the Export button, DatabaseSpy formats the relational data with XML element names derived from the column names of the table and sends the resulting output directly to XMLSpy. The screenshot below shows a portion of the file in XMLSpy. The Message window at the bottom verifies the file is well formed. XMLSpy Editing window and Message window Note that DatabaseSpy supplied the root element <Import name = “cities”> and added comments to describe the datatypes of the database table columns. And, we did not have to construct a SQL statement with a cumbersome Concat( ) function. We began this post to address the simple requirement for a root element to complete the output of the Concat ( ) function we described earlier. When real-world projects require converting from relational databases to XML, the requirements are likely to be much more complex. Altova XMLSpy connects directly to all popular databases to work with XML technologies and relational data. XMLSpy lets you easily create an XML Schema from a database structure, or create a database schema from an XML Schema. XMLSpy also includes advanced editors and debuggers for XQuery and XPath for XML stored directly in databases, along with specialized support for XML features in Microsoft SQL Server, IBM DB2, and Oracle databases. As more industries adopt and evolve XML-based standards for information interchange, a common need is to convert data stored in legacy databases to XML. Altova MapForce connects to databases and allows you to map and transform relational data to be compatible with one or more XML Schemas. You can use your mapping to perform a one-time data conversion, you can save and re-open your mapping to perform another conversion later, or you can instruct MapForce to generate royalty-free source code from your mapping to include in your own project when repeated conversions are required. If you’d like to see for yourself how well Altova tools can generate well-formatted XML from relational databases, download a free trial of the Altova MissionKit.

Tags: , , , , ,

Using Charts to Effectively Communicate Data

Altova first added support for charts and reporting the Altova MissionKit with the launch of Version 2011 last September. The v2011 reporting functionality includes options for line charts, 2D and 3D bar charts, 2D and 3D pie charts, round gauge and bar gauge charts. Here are a few examples: Charts created with the Altova MissionKit v2011

Advanced chart features in v2011r2

Version 2011 Release 2 of the Altova MissionKit, introduced on February 16, adds an exciting group of enhancements to the chart and reporting features in XMLSpy, StyleVision, and DatabaseSpy. The chart design options and user interface work the same way in all three applications, so MissionKit users can work intuitively and productively as they move from processing XML data in XMLSpy, to preparing charts for a business intelligence report with StyleVision, and even when they create graphical displays directly from SQL query results in DatabaseSpy. The wide range of new customizable charting features introduced in version 2011 release 2 includes:

  • Stacked Bar charts
  • Area charts
  • Stacked Area charts
  • Candlestick charts
  • Chart overlays
  • Background images and color gradients
  • Ability to change position of axis labels
  • And more!

Now you can create attractive and informative charts to represent a wide variety of data sets without exporting data to a dedicated charting application. Charts created using the Altova MissionKit are not limited to any specific presentation technology – for instance you can use StyleVision to include charts in HTML, Microsoft Word, RTF, or PDF documents, or you can save charts created in DatabaseSpy in a variety of image formats at the custom resolution you specify. In this post we will show some examples of the new charts and features available in all three MissionKit reporting and charting applications – XMLSpy, StyleVision, and DatabaseSpy.

Stacked bar charts

Stacked bar charts are a variation on bar chart presentation and are especially useful when multiple ranges of data need to be illustrated. Stacked bar charts are also useful to more clearly illustrate data in a smaller area. The image below shows a stacked bar chart to illustrate the performance of a sales team by region over two years Stacked bar chart Note that the combined height of each stack in the Stacked Bar Chart represents the total sales over the two-year period for each Territory, since the sales for Last Year are added above the Year To Date numbers. Stacked bar charts complement regular bar charts and 3-D bar charts to offer users the greatest flexibility in illustrating SQL query results. If the user prefers horizontal bars, a checkbox labeled Draw X and Y exchanged in the Change Appearance tab selects that orientation. Chart orientation option Horiztonal stacked bar chart This orientation option is also available for other 2-D bar charts, line charts, area charts, and candlestick charts.

Area charts

Area charts are similar to line charts, with shading applied to make a more graphically appealing display. The area chart below shows a record of temperature and humidity changes by hour over the course of one day. Creative application of color can emphasize the point! Area chart To successfully build an area chart, the analyst must consider the values in each data category. As the area chart is constructed, each category forms an opaque layer on top of the layers for data retrieved previously. In the case illustrated above, Temperature was always a larger number than Humidity, so a SQL query was constructed in DatabaseSpy to retrieve the Temperature value before Humidity to prevent Temperature from acting like a curtain to hide the Humidity data. However, if the data columns appear in a sequence with values in increasing order, the last layer would overlap and hide all the preceding layers. In that case, the chart tab heading titled Select Data lets the user add and delete columns from the results to re-sequence the data correctly. The Select Data column also lets the user edit the names assigned to each column on the X-axis label. Select Data dialog As alternative solution, the Transparency option in the Change Appearance tab lets the user adjust color levels to allow hidden layers to show through.

Transparency dialog

Stacked area charts

As implied by their name, Stacked Area charts layer the columns of a data set to illustrate the overall sum of a data series. Stacked Area charts also eliminate the potential overlapping data problem that can occur with regular area charts. The chart below shows a table of air passenger revenue miles traveled by month, with individual regions for domestic and international travel. Stacked area chart The Stacked Area chart creates a graphical representation of the total of Domestic and International miles, even though the total miles value was not part of the provided data. This is apparent at the top of the January entry, where the International region intersects the Y axis just below 600 (the original data showed 392 million Domestic miles and 181 million International miles, for a total of 573). A strategic data analyst will always consider the nature of the data to be reported when choosing any particular chart type. For instance in the weather example we used above, adding temperature and humidity values in a stacked bar chart would not be logical!

Candlestick charts

Candlestick charts were originally developed by a wealthy Japanese businessman who began trading at the local rice exchange around the year 1750. He kept records of the local market psychology, learning to boost his profits by carefully monitoring prices and not rushing into trades. Today, charts are used to represent financial data such as stock prices over a period of time. Every day the market is open, each stock has four relevant data points that can be rendered in a candlestick chart: the price at market opening, the price when the market closed, the high price during the day, and the low price during the day. Investors and financial analysts like to view these indicators to gauge the stock’s performance over a period of time. In the candlestick chart below, each solid bar represents the range between the opening and closing price and the thin vertical line through each bar shows the extent of the high and low prices for the day. Candlestick chart In this version of the chart, following common convention, the color of each bar signals whether the stock was up or down for the day. If the bar is green, the stock was up for the day– it opened at the price indicated by the bottom of the bar and closed at the price indicated by the top. If the stock was down for the day, the bar is red and the symbolism is reversed – the stock opened at the price indicated at the top of the bar and closed at the price shown by the bottom. Numerous options are available to set line and fill colors, the Y-axis range and values, and more. Because they were intended to be printed in black and white, the original candlestick charts used empty bars to indicate the price increased and solid bars to indicate price decreases. The Altova MissionKit offers this option: Candlestick chart in black and white Another candlestick chart variation omits the opening price and simply illustrates the range by a vertical line and the closing price by a horizontal line. This option is automatically supported when a data set only includes the high, low, and closing prices. Candlestick chart without opening price

Chart overlays

The Overlays feature lets you combine multiple charts in a single image. Each overlay chart has unique settings and can even be generated from a separate data file. The image below shows a candlestick chart of a stock’s daily prices with the daily sales volume in a bar chart overlay. Candlestick chart with bar chart overlay

Support for background images & color gradients

The ability to specify background color gradients and background images gives you even more flexibility for creating customized, eye-catching charts. Overlaying one chart on another lets you visualize multiple data sets with different Y-axes and types. Area chart with a background image The Change Appearance dialog lets users select a background image, as in the Winter Games chart above, or apply a background color gradient, as in the Summer 2010 chart below. Change Appearance dialog Bar chart with a line chart overlay and background color gradient If you’d like to see for yourself how easy it is to use Altova tools to create attractive charts from XML and database data, download a free trial of the Altova MissionKit.

Tags: , , , , , , ,

What Do Industry Authors Have to Say About Altova?

Authors of various industry reference books ranging from SOA and Web services to XML continue to use and recommended Altova tools. The latest update to the Cold Fusion book series – “ColdFusion 9 Developer Tutorial” is an update to John Farrar’s “ColdFusion 8 Developer Tutorial”. In this latest update, Farrar uses the Altova MissionKit, our suite of XML, database, and UML tools to do all his XML work for the book. According to Farrar, “I have a suite of tools from Altova and find they do what I want. I can create XPath, XML Schemas, and more from their tools and don’t ever feel the need to look for a new tool.” ColdFusion9_Farrar Farrar, a ColdFusion expert, teaches the basics of ColdFusion programming, application architecture, and object reuse. He then shows off a range of topics including AJAX library integration, RESTful Web Services, PDF creation and manipulation, and dynamically generated presentation files. So whether you need an overview of XML technologies, the latest information on working with ColdFusion, or want to delve into Web services, you’ll want to check out the Altova Reference Books page on our Web site.

Tags: , , , , , , , , , , , ,

XML in the Cloud

Working with Altova Tools and the Amazon Relational Database Service (Amazon RDS)

More and more enterprises are discovering the advantages of implementing database applications in the cloud:

  • High availability and reliability
  • Automatic scaling
  • Freedom from hardware costs and maintenance requirements

In this blog post we demonstrate how to connect to the Amazon Relational Database Service (Amazon RDS) and build a small database using Altova DatabaseSpy. Since the database Connection Wizard is consistent across the Altova MissionKit, you can connect the same way using XMLSpy, MapForce, or StyleVision. If you would like to follow the steps described below for yourself, you will need to sign up for an Amazon Web Services (AWS) account at: You can also download a fully-functional free trial of the Altova MissionKit or any individual Altova application at:

Build a Local Prototype

The Amazon RDS is based on MySQL, so we will build a small local database in the MySQL Community Edition, then migrate to the Amazon RDS and test our database in the cloud. Although MySQL does not support XML as a data type for database columns, MySQL 5.1 and 6.0 do support some operations for XML data stored as text. For this exercise we will adapt and extend some of the MySQL XML examples at the MySQL reference resources listed here: First, we launched DatabaseSpy and connected to our local MySQL Community Edition. We created a new data source named LocalPrototype, and created a new database schema that we named XMLtest. The DatabaseSpy Online Browser and Properties windows are shown here: DatabaseSpy Project and Properties windows Next, we created two tables called books and cities and inserted data by following the examples in the MySQL documentation. Here is a DatabaseSpy Design View of our tables: DatabaseSpy Design View We can run select queries and display the contents of our tables in stacked results windows: DatabaseSpy stacked results windows Note that the doc column of the books table contains XML data, although it was defined as varchar(150). MySQL supports two functions for working with XML in text fields, ExtractValue() and UpdateXML() that can operate on individual elements via XPath expressions. Below is a simple ExtractValue() query to return only the author initials from every row in the books table: ExtractValue( ) function The UpdateXML() function can be used to modify the contents of individual XML elements using a SQL expression. In the screen shot below, the query on line 1 updates the every row of our books table, and the query on line 2 returns the new values: UpdateXML( ) function We can also use the Concat( ) function to add XML elements to non-XML data such as the cities table, as shown below: Concat( ) function So far, our XML queries have operated on all rows of each table. To facilitate queries for a single row, it’s handy to add a column top the table to hold a unique row index. We can make a copy of our books table and add a column called id to hold the row index. The id column also makes a convenient foreign key to reference an individual XML document in our table from a row in another table. For instance, you might define one table to contain names of job candidates, with a foreign key to reference the XML-formatted resume for each candidate, stored in a separate table. You can use the SQL Editor in DatabaseSpy to generate a CREATE statement for the existing books table and edit it directly, or you can use the DatabaseSpy Design Editor to build the table graphically. (For more information, see the DatabaseSpy section of the Altova Web site.) Since we are planning to run the same queries later in the Amazon RDS, we combined a SQL CREATE statement and SQL INSERT statements into one script for the books2 table. The screen shot below shows part of the script for books2: Create table script We can run a query of the books2 table that shows the unique id column for each row: SQL SELECT query Now we can enhance our UpdateXML() and ExtractValue() queries to act on an individual row: blogSnap8 blogSnap9 This gives us a good baseline set of examples to take to the cloud and test in an Amazon RDS.

Connect DatabaseSpy to the Amazon RDS Cloud

After you follow the instructions at the AWS Management Console to create a database instance on Amazon RDS, the Connection Wizard makes it easy to get started with DatabaseSpy. Simply choose the MySQL option as shown here: DatabaseSpy Connection Wizard The first time you connect, you will need to create a new DSN. After the first time, you will be able to select the DSN from a list by choosing the “Use an existing Data Source Name” option. You can even use the original DSN when you go back to connect from XMLSpy, MapForce, or StyleVision. Connecting to MySQL In the connector dialog, fill in the following information:

  • Data Source Name: This is the name that will be listed in the DatabaseSpy Project. window and in the list of existing data sources when you connect again.
  • Description: Information for your own reference.
  • Server: This is the Endpoint name listed in your Amazon RDS account dashboard.
  • Port: 3306 – make sure your IT department isn’t blocking this port with a firewall!
  • User / Password: This is a user you set up in Amazon RDS.
  • Database: The default database name you configured when launching your RDS instance.

MySQL Connector/ODBC We connected to our Amazon RDS cloud database in the same DatabaseSpy project we built for the local prototype. Here is a screen shot of the project window showing both Data Source Names and the working SQL files we added to our project: DatabaseSpy Project window with cloud connection Before we build our tables and run the queries, it will be interesting to check the versions of each system. The screen shots below show a query that requests version information for each system. Note that the gray bar directly above each query indicates which data connection the SQKL statement is assigned to. Version reported by the local server Version reported by the cloud server The Amazon RDS reports it is running version 5.1 of the MySQL Community Server, the same as our local prototype – a promising omen!

Migrate the Local Project to the Cloud

We can open each of our original table creation scripts and run them in the cloud database by re-assigning the execution target  in the Properties window: Data Source selection in the DatabaseSpy Properties window The gray Execution Target bar near the top of the SQL Editor window identifies the cloud Amazon RDS database as the query target: DatabaseSpy SQL Editor window After similarly creating the books and books2 tables, we can run each of the SQL queries in the cloud database. ExtractValue() function for all rows example: ExtractValue( ) function Concat() query to create XML output from non-XML data in a table: Using the Concat( ) function to add XML elements to data from a non-XML table UpdateXML() example for a single row in a table. Using the UpdateXML( ) function in the Amazon RDS cloud ExtractValue() for a single row: blogSnap23


In every test we performed, Amazon RDS behaved exactly like the local MySQL community edition. This behavior it much more efficient for developers to build and test new cloud database applications, or enhancements to existing applications, without incurring the cost of cloud resources for development iterations. We also verified the operation of MySQL XML functions for XML data stored in text columns in the cloud databases. Our XML data was very limited – the text column in our books table was limited to 150 characters. However, MySQL lets you store much larger XML documents in a single column. Every table has a maximum row size of 65,535 bytes. Even if your table uses an index column, this means a varchar column for one XML entry could be over 64k bytes. If you need to store even larger XML documents, MySQL offers MediumText and LongText data types, similar to BLOBs. MediumText can hold over 16 million single-byte characters and LongText can hold up to 4 GB. Although not illustrated in this blog post, we have successfully tested ExtractValue() and UpdateXML() functions with MediumText and LongText data types. When you need to store XML data files that large, writing XPath expressions to resolve individual elements can become a development challenge. The XPath Analyzer included with XMLSpy is an invaluable tool that facilitates the testing and debugging of XPath 1.0 and 2.0 expressions. As you type an XPath expression into the analyzer, XMLSpy evaluates it and returns the resulting node set in real time. This can save hours of debugging time spent trying to understand and track down XPath problems. In future blog posts we’ll explore other ways XMLSpy, MapForce, DiffDog, and DatabaseSpy can help developers accelerate creation of cloud application with Amazon RDS. We look forward to seeing you back soon! If you’d like to see for yourself how well Altova tools work with Amazon RDS, download a free trial of the Altova MissionKit.

Tags: , ,