Web Service as a Look-Up Table to Refine GPS Data


Elevation data recorded by GPS devices is notoriously inaccurate, especially in hilly terrain like the Russian River Valley example from our earlier post.

The final elevation track plotted from the Russian River Valley GPX file is suspicious for several reasons. First, the graph shows we descended almost 50 feet below sea level. That’s hard to believe, since we were travelling along the bank of the river, only about 10 miles from the Pacific Ocean.

Altova StyleVision ChartSecondly, we were headed mostly west, following the river downstream, but the track shows a predominantly uphill trend.

We can evaluate the recorded GPS elevation data by comparing it to information available from the United States Geological Survey (USGS). The USGS operates a Web service that accepts latitude and longitude coordinates, and returns elevation data measured by NASA and assessed for accuracy based on over 13,000 control points in the continental United States.

Using the elevation Web service in an Altova MapForce mapping will let us extract each point from the GPX file, send the coordinates to the USGS Web service, and build a new GPX file with corrected elevation data.

Read more…

Tags: , , , , , , , , ,

New Case Study: Automating XBRL Data Collection and Processing


Case studies are a great way to see how other organizations use Altova® technologies to develop unique projects that meet their business goals. We’re often asked, however, what comes next. Did the project take off? Has it grown since the case study was published?

We’re happy to bring you a follow up to a case study we published last year about the not-for-profit Maryland Association of Certified Public Accountants (MACPA).
In the original case study, MACPA converted General Ledger and other financial data from siloed systems into XBRL – in house and on a budget – with the Altova MissionKit®. At that point, they were mapping the XBRL file to multiple external targets, including an Excel spreadsheet used to feed their Key Performance Indicators (KPI) system. (You can read the original XBRL case study here.)

Since that time, they’ve built on the foundation they created and developed a business intelligence dashboard driven by the XBRL files they generate in MapForce®. They used Altova FlowForce®, an application designed to automate the execution of MapForce data transformations and other tasks on servers, virtual machines, and workstations, to automate the XBRL data collection and processing. Now the most recent data is available across the entire organization for custom reporting.

You can read the follow-up to the original XBRL case study here.

clip_image001

Read more about how they did it. Read more…

Tags: ,

Resist Data Integration Redundancy


The Internet makes massive amounts of data available for lots of interesting applications. But whenever you design a unique analysis and presentation of information you don’t privately control, you risk that the owner will offer the same view at some point in the future, instantly making your application redundant.

That’s exactly what happened to the Groupon API data-mining project we originally wrote about in August, 2011. Fortunately, the core of our project is a MapForce graphical data mapping. We can quickly and easily tweak the mapping and repurpose it to present an entirely different data set that provides new value.

HTML output from MapForce and StyleVision

Read more…

Tags: , , ,

Data Exchange for the Mobile Workforce


Data Exchange for the Mobile WorkforceOrganizations have been forced to adapt many of their internal business processes to accommodate an increasingly mobile workforce. Although there are technological solutions that address many of today’s communication needs, the plethora of different document formats in use – even within the same organization – means that some tasks remain vexing. For example, how does an organization remain flexible enough to facilitate the exchange of data among mobile workers yet retain the ability to bring that data into internal IT systems? Altova offers an inexpensive solution with StyleVision®, a graphical stylesheet and report designer with electronic forms capability, and Authentic®, a WYSIWYG XML and database editor in which end users can view and edit electronic forms created in StyleVision. The Authentic Community Edition is available from the Altova Web site as a free download so that anyone can be brought into your workflow, whether they are internal or external to your organization.clip_image002In this post we’ll present a sample case showing you how to create an electronic form that mirrors an existing paper form (in our example it is a reimbursement form) and then we’ll follow it as it makes its way around a fictional organization. Please note that the example we use here is simplistic and was designed only to illustrate the process of developing and deploying an electronic form. Although you can add additional data sources and perform validation and other complex functions in StyleVision, we have not illustrated these here.It is extremely easy to design electronic forms in StyleVision. To start, we simply select New – New from XML Schema/DTD/XML … from the File menu, browse to an XML Schema file, and select the type of design we’d like to create. For this example we created an XML Schema and instance file in XMLSpy, Altova’s XML editor and development environment, based on the fields on the paper reimbursement form. You can also base a StyleVision design on a database or XBRL taxonomy.Below is a copy of the reimbursement form we will be using along with the XML Schema we created.clip_image002clip_image003Once we select the XML file in StyleVision, we are prompted to select either a free-flow or form-based document. In a form-based document all design elements (e.g., text boxes for user input, images, buttons) are fixed in position – ideal for data entry forms.When we create a form-based document, we can upload a “blueprint image” so that we can recreate a paper-based form exactly as it was originally designed. This is the option we’ve selected below. The image will appear in the background of the design window and we will simply place design elements on top of corresponding elements on the form. Of course, the blueprint image overlay does not appear in the final output.clip_image004The screenshot below shows the blueprint image as it appears in the design window in StyleVision – how cool is that? clip_image005Now, using the Insert menu at the top, we can simply insert design elements onto the blueprint image in the design window.Available design elements include form controls (e.g., input boxes, combo boxes, radio buttons), images, tables, charts, and “layout containers” for exact positioning.We’ll start by adding input fields to capture employee information (i.e., First [Name], Last [Name], Title, etc. from the top part of the form). Once we click Insert – Insert Form Controls and select Input Field, the Insert Design Element dialog box appears.We have highlighted the First [Name] element in the dialog box below – the input field will now be associated with the First element. This way, when the end user types data into the input field and saves the form, this information will populate the First [Name] element in the XML file.clip_image006We now add design elements throughout the rest of the form, associating input fields with their respective elements from the XML file.When we are finished adding input fields, a logo, lines, a table for the expense items, and labels, the design looks like this – we’ve set the opacity attribute for the blueprint image to 0 to make it easier to view the design elements. Please note also that we’ve done some additional design work such as adding calendars in date fields, drop down boxes, and a currency sign that changes according to user input. For more information about fine tuning your form please see the StyleVision User and Reference Manual in the StyleVision application.clip_image007We can preview how the end user will see the form we designed in StyleVision by clicking on the Authentic eForm tab at the bottom of the design window (below). Note that the end user is prompted to enter data directly into each data input field. We accomplished this by placing the prompts (e.g., Insert First Name) in between the relevant tags in the XML file associated with our design. The end user simply highlights the prompt and replaces it with text.clip_image008Once we are finished designing the reimbursement form we can save the entire design – including the XML Schema and instance files, images, and any other associated files – in a single PXF® (Portable XML Form®). Saving the design as a PXF will enable us to email the form along with data updated in the underlying XML form among people both inside and outside the company’s LAN.clip_image009Once we hit OK we are prompted to select the files to include in the PXF. Notice that we’ve selected HTML, RTF, PDF, and Word 2007+ under the Generate and store XSLT files … heading. This will allow an end user to generate the form – with data – in these formats directly from Authentic.clip_image010Now that we’ve saved it in a PXF, the electronic form we designed in StyleVision is ready to be deployed in a business environment.In our example, we have a team of salespeople working across the globe who need to request reimbursement for business and travel expenses incurred. The salespeople complete expense reports, forward them to their managers for approval, and then send approved reports to the corporate office so that the information can be added into the accounting system.The PXF makes this easy.Once a salesperson is ready to complete a reimbursement request, she simply opens the PXF in Authentic and can immediately begin entering information onto the form. Below is a screenshot of a reimbursement form that has been completed in Authentic – notice that the form still needs a manager’s signature.clip_image011Now the salesperson must send it to her manager for approval. It’s easy to initiate an email with the form attached directly from Authentic. clip_image013Once the manager receives the email, she can simply double click the attachment and it will open in Authentic. Here the manager has clicked the Approved check box and added her name and the date.clip_image014The manager can then email the updated PXF back to the salesperson, who in turn emails it to the corporate office so it can be imported into the accounting system for processing. Our fictitious corporate office of course receives hundreds of reimbursement requests each day and has established a process for importing them into the relevant Oracle databases in the accounting system.We’ll use Altova MapForce, a graphical any-to-any data mapping, conversion, and transformation tool, to populate the corporate database with the data from the quotations. After setting up the mapping, we’ll automatically generate code from MapForce so that we can automate the transformation either through batch processing or a real-time conversion.First we’ll set up the mapping.We’ve inserted the XML file ExpRpt which we’ve extracted from the PXF into the left side of the MapForce design window and then inserted the Oracle database on the right side of the design window.Now we can drag and drop fields from the XML file with the reimbursement data into the Oracle database. We can also transform data, as we’ve done with the Approved element. Here we’ve used the built-in boolean function to convert the string value stored in the XML file (“true” or “false”) into the numeric equivalents (1 or 0). We can also create our own functions.The mapping we’ve created appears below.clip_image015Please note that this post offers a very broad overview of how to use both StyleVision and MapForce. Please visit the online training section of the Altova website for more in-depth instructions on how to use these and other Altova products.And there you have it. With the PXF, the fictitious Nanonull Corporation allows a group of far flung sales reps and their managers to easily exchange and edit information via electronic form. The PXF also provides a way for Nanonull to populate the accounting database without offering these employees direct access to company IT systems. All without busting the IT budget.

clip_image016

What could your organization do with a flexible, portable interactive document? Please share your ideas with other users by commenting on this blog post. Have you used StyleVision or other Altova products in an interesting project and think it would make a great case study? Email us at marketing@altova.com. We’d love to hear from you!

Tags: , , ,

XML Development with Database Integration


Did you know that XMLSpy connects to relational databases? One of the most compelling features of the Altova MissionKit is that numerous tools in the suite include offer deep integration with relational databases, providing seamless access to back end data for bi-directional conversion, integration, analysis, and reporting. image Let’s take a look at what you can do when you connect XMLSpy to your databases. Other database-enabled MissionKit tools will be covered in subsequent posts.All popular relational databases are supported in XMLSpy:

  • Microsoft® SQL Server® 2000, 2005, 2008
  • IBM DB2® 8, 9
  • Oracle® 9i, 10g, 11g
  • Sybase® 12
  • MySQL® 4, 5
  • PostgreSQL 8
  • Microsoft Access 2003, 2007

First step: Connect to and query the database

When you select Query Database from the DB menu, XMLSpy helps you connect to your database with the step-by-step Database Connection Wizard. Then, the DB Query window makes it easy to explore and/or edit data in the database you’re working with, either by opening existing SQL files or creating SQL scripts from scratch using drag-and-drop and auto-complete functionality. Once you execute your query, you can edit the database data in the results window, review changed fields (highlighted in pink), and commit the changes back to the database. Querying a database in XMLSpy

Next: Convert between XML and databases

Another common requirement is converting between XML and database models, and XMLSpy supports this in both directions. You can easily export database data to XML. If no schema is required, you can simply export the data to XML in its basic tabular format. Or, you can use the Create XML Schema from DB Structure option first, then import database data maintaining all the relationships and dependencies defined in the content model. Numerous options are available to specify the format of the schema, whether columns should be imported as elements or attributes, and the database constraints that should be generated in the XML Schema. Get schema from DB data Or, to go in the other direction, it’s just as easy to go from XML to a relational model in XMLSpy. The Export to Database dialog (accessed via the Convert menu) allows you to specify where to start the export, how to handle export fields, and which elements to include. Then, the data is instantly converted and stored in your database. image For times when you want to define a database with the same rules as an existing XML Schema, the Create DB Structure from XML Schema dialog lets you do so with numerous options. Any identity constraints included in the schema will automatically transfer to the database structure. Alternatively, it’s easy to define relationships between elements manually. Learn more about all these features for working with XML and databases in XMLSpy, or check out all the database tools available in the MissionKit.

Tags: , , ,

Leverage Your Financial Data with the XBRL Chart Wizard–Part 1


Extensible Business Reporting Language (XBRL), an XML-based language for financial data, is increasingly being used by both public and private organizations across the globe – in fact it is mandated for some companies in countries including the United Kingdom and the United States. Altova provides comprehensive support for XBRL tagging and XBRL reporting with the MissionKit, a suite of our most popular software. Among the MissionKit tools is StyleVision, a graphical stylesheet designer and report builder, which can be used to support a host of internal reporting and analysis activities for companies that use XBRL. clip_image001 In the next post we’ll focus on StyleVision’s XBRL Chart Wizard, a powerful XBRL visualization tool that can turn your XBRL-tagged financial data into powerful charts and graphs – if a picture is worth 1,000 words then StyleVision is worth its weight in gold. Calling the XBRL Chart Wizard You invoke the XBRL Chart Wizard as you do the XBRL Table Wizard and other StyleVision capabilities. Once you’ve started a design by selecting New – New from XBRL Taxonomy from the File menu and selected a taxonomy and working XBRL file, all concepts are populated to the Schema Tree. From here you simply select a concept from the XBRL taxonomy in the Schema Tree and drag it into the design window. For this example we’ll be using the Carnival Corporation quarterly report for 2009 that they have published with the SEC, but you can apply the same techniques to any XBRL instance document – be it a publicly available filing with the SEC or an internally generated XBRL file. As a first step, we will look at how the revenues are composed by creating a pie chart that shows the revenue breakdown. Here we’ve dragged the Revenues concept (highlighted in the Schema Tree in the left sidebar) into the design window and selected Create XBRL Chart.   clip_image003   Once you select Create XBRL Chart the XBRL Chart Wizard dialog box will open automatically.   clip_image004   Once you click the ellipses in the corner of the Concepts tab in the Series pane, the Concept Properties dialog box (below) will open and you can select concepts to appear in the chart. Carnival Corp breaks out revenues for their cruises between Passenger tickets and the Onboard and other. We will select those two concepts, and also the Other category to capture all elements that make up the total revenues.   clip_image005 Pie Charts Pie charts are useful when you wish to see the relative contribution of individual elements to the whole. Placing Onboard and other, Other Sales Revenue Net, and Passenger Tickets in a pie chart provides us with a visual representation of the relative contributions of each source of income to total revenue. We are now ready to make changes in the XBRL Chart Wizard dialog box so that our pie chart reflects the information we need in a format conducive to strategic decision making. First we must change the chart type under Chart Settings from Bar Chart to Pie Chart 3D via the Change type… button, which brings up the Change Type dialog box (below).   clip_image006   In pie charts, the concepts that will form the segments of the pie (in this instance the Onboard and other, Other Sales Revenue Net, and Passenger Tickets concepts that we selected above) are placed in the Categories pane and the values in the Series pane. Therefore we will need to move the Concepts tab to the Categories pane and the Period tab to the Series pane. We’d like to segment the revenue data from the XBRL file based on quarter. We do this by dragging the User-Defined Grouping (by Quarter) tab from the Available pane to the Categories pane. We’ll make the necessary changes in this tab in the next step. We will also check the Remove empty categories and Remove empty series boxes so that a value or label will not be generated if no data exists and change the size of the chart to 350 pixels x 350 pixels in the Chart Settings section of the XBRL Chart Wizard dialog box. After we make these changes, the dialog box looks like this:   clip_image007   Now we are ready to select the data that appears in the chart. First we’ll segment the data by quarter. We invoke the User-defined Grouping Properties dialog box pictured below by clicking the ellipses in the corner of the User-defined Grouping (by quarter) tab in the Categories pane. The grouping feature provides you with maximum flexibility by allowing you to segment data based on variables identified in the taxonomy (e.g., reporting period, geographical area, division). Now we can use XPath in the Group By field to group the data by quarter, filter it based on the group we created (in this example only the second quarters will appear in the chart), and add a dynamic label. We want the chart to reflect all second quarter data for each of the revenue concepts we selected so we toggle Do not filter under Group key filter.   clip_image008   We can further filter the data by clicking on the ellipses on the Period tab in the Series pane to bring up the Period Properties dialog box. Here we’ve selected only duration periods (i.e., those with a start date and end date – instant periods have a single date reflecting the date that the “snapshot” was taken) and filtered based on year. In this example only data from the second quarter of 2009 will appear in the chart.   clip_image009   Finally we can fine tune the chart’s appearance by clicking on the All Settings tab under Chart Settings, which brings up the Change Appearance dialog box. Here we’ve opted to show the concept labels, values, and percent of total. We can also select color schema, chart size, font types and sizes for each section of the chart (e.g., chart title, labels, legend), and background colors.   clip_image010   After making all of these changes we hit OK in the XBRL Chart Wizard dialog box and the pie chart reflecting these changes is created. Please note that after the chart is created you can go back and edit the chart settings.   clip_image011   As you can see, the biggest source of revenues is Passenger tickets, which produced 75.02% of total revenues for Carnival Corp in the second quarter of 2009. As is the case with all StyleVision designs, output can be rendered in HTML, RTF, PDF, and Word 2007+ formats and an XSLT stylesheet for each format is automatically generated. And this was just one example of what kind of data you can extract from an XBRL filing and visualize in a chart. Next week we’ll look at creating bar charts and line charts from XBRL financial data. clip_image022 Have you created something really great with the XBRL Chart Wizard? Or developed an interesting project using StyleVision or another of our tools? Please share your story with other Altova users by commenting on this blog post. Think it would make a great case study? Email us at marketing@altova.com – if we choose to use your story you’ll receive a $200 Amazon gift card as well as some free press for you and your organization. We’d love to hear from you!

Processing the Groupon API with MapForce – Part 2


In Part 1 of this series we described how to connect Altova MapForce to the Groupon API. We queried the API for a list of Groupon divisions, then used the list to create API queries for all the current deals from every division. In this part, we will execute the /deals queries and filter the response for the most interesting data. The list of /deals queries we built previously looks like this: List of Groupon /deals queries generated by Altova MapForce To process all the queries, we can connect the list as a dynamic file input to a new mapping component. When we needed a new component last time, we dropped an API /divisions query into the mapping, and let MapForce create an XML Schema automatically. We could do the same thing here by dropping in an API /deals query as an XML input file. There’s just one small issue — although the Groupon API online documentation clearly describes the queries we can make, it is vague about the information that will be returned. Before we send dozens of queries to the API for all the current deals, we probably want to know a little more about the data that will come back.

Let’s Make a Deal Like Yogi Berra said, you can observe a lot just by looking. Let’s start by running a /deals query in XMLSpy. That will let us examine the response to a query for one division before we pull in a potentially unwieldy volume of data. The XMLSpy File / Open menu includes the same Switch to URL option we used in MapForce in the earlier post. If we enter the /deals API query for a division that covers a large metro area – say Dallas – we are likely to get enough deals instances to extrapolate the characteristics of the entire data set. XMLSpy opens the response to the /deals API query in Text view just as if we opened a local file: Example from the response to a Groupon /deals query, shown in XMLSpy As expected, we got quite a bit of data when we requested all the deals for a single division! A fast way to analyze the structure of this data is to use the XMLSpy DTD / Schema menu option to generate an .xsd file from the xml. Shown below is a reduced view of the entire generated .xsd file based on the response to the /deals query for Dallas: An xsd file generated by XMLSpy from the Groupon query We can dig even deeper, following Yogi’s advice like déjà vu all over again. Expanding all the elements to review the XML Schema reveals some curious anomalies. For instance, there are two elements named redemptionLocation with different definitions. The first contains a sequence of child elements: First use of the remdemptionLocation element And the second is defined as a simple string: Second use of the remdemptionLocation element Going back into the xml data for Dallas and searching for redemptionLocation displays these examples: One example of redemptionLocation in the body of the response And: One example of redemptionLocation in the body of the response And: One example of redemptionLocation in the body of the response Now this is really interesting, because redemptionLocation = ”online” identifies deals that can be redeemed from anywhere, instead of by a visit to a bricks and mortar location in the division where they are advertised. What if we ran the /deals API queries for all divisions and extracted a list of all the online deals? That would be one extreme Groupon! Only Ask for What You Need The Groupon /deals API query supports an optional parameter called &show= that allows users to limit the data returned. Applying this parameter can save bandwidth and reduce processing time for the data transformation by removing unwanted data from the API response. We can also simplify our final result by including only the most interesting information, including the link to the Groupon web page for each deal. After we remove unwanted elements from the generated Dallas schema, our final version for the summary of online deals looks like this: XMLSpy Schema diagram of the simplified Groupon xsd file When we add the &show= parameter to our MapForce mapping to request only the elements included in the simplified XML Schema, the queries look like this: Modified list of queries with the &show= parameter Now we can drop the revised .xsd file into the mapping and connect the list of API /deals queries as dynamic input. We don’t need to delete the text file we used to collect the list of queries — that might continue to be helpful for future debugging. Mapforce dynamic input file mapping These changes complete the input side of the data mapping. Defining the Data Transformation Output Back in XMLSpy we can make a couple more revisions to the input XML Schema to design a new version for output: XMLSpy schema diagram of the output file xsd We discarded the response element since it doesn’t add any value, and eliminated the redemptionLocation element that we don’t intend to include in the output. We also added a date element for a timestamp, because our output file will be a snapshot of data that is constantly changing. After saving this version of the .xsd file in XMLSpy, we can drop it into the MapForce mapping. Shown below is the output side of the mapping with the output component partially connected. The filter at the top reads the redemptionLocation element to select only online deals and the now function inserts the date: Partial view of the MapForce output file mapping The last revision we made in the output XML Schema was to change several element types from dateTime, Boolean, and integer to the string data type to allow more descriptive text Here is the complete definition of the mapping with the final connections to the output component: Mapforce data mapping for the Groupon API Now for the Payoff When we click the Output button MapForce processes the entire mapping from beginning to end using the MapForce Built-in execution engine. Here’s a breakdown of the steps:

  • Run the /divisions query to get the current list of divisions
  • Concatenate strings to build the list of /deals queries for all divisions
  • Run the /deals queries to create dynamic data for the input component
  • Filter for online deals to generate the output component, execute the remaining mapping functions, and add the timestamp after all the deals are processed

MapForce takes only a few seconds to complete all those steps and generate an output file with a series of deals that look like this: Output data from the MapForce mapping for the Groupon API In part 3 of this series we’ll design a stylesheet to automatically transform the XML output of our mapping into html for attractive presentation in a web browser and on mobile devices. See ya at the ballpark, Yogi! XMLSpy and MapForce are available together in the specially priced Altova MissionKit. See for yourself how easy it is to use the MissionKit to convert data from a Web API — download a free 30-day trial!
Editor’s Note: Our original series on mapping data from the Groupon API ran in three parts you can see by clicking the links here: Part 1 of Processing the Groupon API with Altova MapForce describes how to create dynamic input by collecting data from multiple URLs. Processing the Groupon API with MapForce – Part 2 describes how we filtered data from the API and defined the output to extract only the most interesting details. Processing the Groupon API – Part 3 describes formatting the output as a single HTML document optimized for desktop and mobile devices, and reviews ways to automate repeat execution.

Tags: , , , , , , ,