Cloud analytics provide a business intelligence (BI) reporting engine without the overhead and cost to install and maintain a traditional reporting engine. They are also accessible to a wider audience, because they don't require specialized skills to set up and maintain them. Unlike traditional reporting engines, the data used for cloud analytics can reside in a number of locations outside the reporting engine.

The primary challenge with cloud analytics, then, is the efficient transfer of the data into the reporting engine. Many cloud analytics platforms allow you to import the data from a spreadsheet or a comma-separated values (CSV) file. They also provide a programmatic interface through Web services.

Zoho® Reports is an example of an application that integrates a traditional application with a cloud service to provide analytics in the cloud (see Resources). The example in this article shows how a traditional application might use Zoho Reports for data storage and retrieval as well as reporting purposes, plus provide the ability to embed the resulting graphs in your Web pages.

Zoho Reports provides a hosted database for storing data and a Representational State Transfer (REST)-ful API to allow for interaction with the data. A REST-style architecture is an ideal solution, because it allows for simple messaging over HTTP. With simple HTTP messages, you can communicate with the server, and the server can return the data in XML format.

Get to know Zoho Reports

Zoho Reports is an online reporting tool that provides a hosted solution for business analytics. Data is stored in a familiar database fashion, organized by tables. The interface for defining the data structure is a mix between spreadsheets and databases. You can also define the structure of the tables through the uploaded file structure.

You can enter data into Zoho Reports in a number of ways, including: uploading from formats such as CSV, Microsoft® Office Excel® (XLS), and HTML; manually in a spreadsheet-like interface; or by using the Reports API. For reporting, Zoho Reports provides components such as those found in traditional reporting applications—charts, pivot tables, and a tabular view of the data. It provides the ability to do visual analysis of the data with a drag-and-drop interface, and it can also use the familiar SQL database language to query data.

After you define your reports in Zoho Reports, you can embed them anywhere on the Internet, including blogs, custom applications, or other Web sites. In this way, you have the flexibility of reusing the same reports in a variety of places.

Back to top

Using the Zoho API

The Zoho API provides a REST-style interface for interacting with the data in the database over HTTP. Calls to the API include the database name and the table to use. The capabilities of the API include:

• Adding or modifying the data.
• Bulk adding or updating of data using CSV files.
• Bulk updating or deleting of data based on specified criteria.
• Exporting data and reports in formats such as PDF, HTML, CSV, JavaScript Object Notation (JSON), and images.
• Fetching data from the database using the SQL SELECT query.

To use the API, you must first request a key from http://writer.zoho.com/apikey.htm. This key is passed with all API calls.

The second important part of each Zoho Reports report is the ticket parameter. The ticket is generated when you first log on. The same ticket is then used with all subsequent API calls.

The request format uses an HTTP POST request format, and specifies the action to take in the parameters along with the API key, ticket, API version, and output format. The data for the request is passed in the body of the request. Details of the request format are available on the Zoho wiki (see Resources).

The response format specified in the request can be either XML or JSON. The XML format specifies a <response> tag with an enclosing <result> tag. The <response> tag specifies the URI that was called and the action taken:

<?xml version="1.0" encoding="UTF-8" ?> 
<response uri="/api/loginname/databasename/tablename" action="specifiedaction">
<result> 
<!-- The specific XML response based on the action goes here -->
</result> 
</response> 

To facilitate interacting with the Zoho API, Zoho Reports provides client libraries in the Java™ language and Python; additionally, a Google App Engine client is provided as an extension to the Python library. The example in this article uses the Java language libraries, which provide a fairly simple interface for interacting with Zoho Reports.

The main class used is ReportClient, which represents the main client for interacting with Zoho Reports. To begin, log in to the client and then get the URI that you will to use to interact with the server:

             ReportClient reportClient = ZohoConfig.getReportClient();
        reportClient.login(ZohoConfig.LOGINNAME, ZohoConfig.PASSWORD);

        String uri = reportClient.getURI(ZohoConfig.LOGINNAME,
            ZohoConfig.DATABASENAME, "WordFrequency");

This code logs in to the database specified in ZohoConfig, which is BlogAnalyzer. It then specifies which table you are going to use—in this case, WordFrequency.

After you are done working with the report client, log out of the client using the call:

reportClient.logout();

Back to top

Analyzing an RSS feed with Zoho Reports

As a sample application, let's analyze a number of RSS feeds to find the frequency of each word. Because RSS is specified using XML, it is easy to read and parse RSS in the Java language. The example application uses Rome (see Resources), a Java library for parsing, generating, and publishing RSS feeds. Rome uses the JDOM XML parser to support parsing and converting the feeds to and from different formats.

Reading an RSS feed with Rome is extremely simple. Given the URL of the RSS feed to read, construct an XMLReader, then read the feed:

        URL feedUrl = new URL(url);
        XmlReader xmlReader = new XmlReader(feedUrl);
        SyndFeedInput input = new SyndFeedInput();
        SyndFeed feed = input.build(xmlReader);

This code stores a list of entries in the feed object. You can then iterate through the entries and parse the list of words out of the entry, as in Listing 1.

        List entriesList = feed.getEntries();
        for (Object obj : entriesList) {
            SyndEntryImpl syndEntry = (SyndEntryImpl) obj;
            String title = syndEntry.getTitle();
            parseWordsFromString(title);
            SyndContent description = syndEntry.getDescription();
            parseWordsFromString(description.getValue());
        }

The parseWordsFromString() method updates a word map that counts the frequency of each word. After you have parsed all the entries for each feed you get, loop through the key set, looking at the frequency of each word. For example, the word and appeared 1015 times, the word Apple appeared 45 times, and the word Adobe appeared two times.

Parsing words out of a string

The method shown in Listing 2 for parsing the words out of a string was modified from the sample on the Java2s Web site (see Resources):

 public void parseWordsFromString(String nxtString) throws CharacterCodingException {

        // Create line pattern
        Pattern linePattern = Pattern.compile(".*$", Pattern.MULTILINE);

        // Create word pattern
        Pattern wordBreakPattern = Pattern.compile("[\\p{Punct}\\s}]");

        // Match line pattern to buffer
        Matcher lineMatcher = linePattern.matcher(nxtString);


        Integer ONE = new Integer(1);

        // For each line
        while (lineMatcher.find()) {
            // Get line
            CharSequence line = lineMatcher.group();

            // Get array of words on line
            String words[] = wordBreakPattern.split(line);

            // For each word
            for (int i = 0, n = words.length; i < n; i++) {
                String nxtWord = words[i].trim();
                if (nxtWord.matches("[a-zA-Z]{2,}?")) {
                    nxtWord = nxtWord.toLowerCase();
                    Integer frequency = (Integer) map.get(nxtWord);
                    if (frequency == null) {
                        frequency = ONE;
                    } else {
                        int value = frequency.intValue();
                        frequency = new Integer(value + 1);
                    }
                    map.put(nxtWord, frequency);
                }
            }
        }

    }

The Java2s sample, parseWordsFromString, uses Java regular expressions to parse a string. First it creates a regular expression pattern that looks for the end of sentences and one for individual words. It then loops through each sentence and breaks that into individual words based on the word break pattern. For each word that is longer than two characters, it stores the count in the frequency map.

Now that you have the frequency of each word in the RSS feed, you can upload this data to Zoho Reports for analysis. To do this, create a hash map as in Listing 3 that contains the row data you want to store in the table.

        SimpleDateFormat dtFmt = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
        Set wordSet = map.keySet();
        for (Object objs : wordSet) {
            String nxtWord = (String) objs;
            Integer frequency = (Integer) map.get(nxtWord);

            HashMap rowValsMap = new HashMap();
            rowValsMap.put("Date", dtFmt.format(new Date()));
            rowValsMap.put("Word", nxtWord);
            rowValsMap.put("Frequency", frequency);
            Map result = reportClient.addRow(uri, rowValsMap, null);;
        }

The last call in the loop calls the report client and tells it to add your row values to the table. Log on to Zoho Reports to see the data in the WordFrequency table, shown in Figure 1. (View a larger version.)

For this table to be truly useful, you should further refine the list of words. For example, you might filter out common words such as http and com.

Back to top

Optimizing the upload process

The previous example uploaded the data one row at a time—obviously, not an optimal approach. Running that example, it takes a substantial amount of time to upload the data. A better approach is to use batch uploads to upload all the data at once. Currently, the Zoho API only offers the option to upload CSV files.

The format for the CSV file is simple: the first row contains a list of column headings, and the following rows contain the data. When passing the CSV file, you can specify additional upload parameters in the POST body, including numerous options for uploading the data, such as creating a new table, appending to an existing table, or updating the existing values in a table. The details of the available options are in the Zoho wiki (see Resources).

To optimize the RSS word-analysis program, change the upload process to use the Zoho API import method. The import data call returns an XML document that details the result of the upload—the number of columns uploaded, the total number of rows, and other information. It also has a list of the import errors, such as when a value was the wrong type for a column.

To use the import method, you must first convert the data set to a CSV file using opencsv (see Resources). To populate the file, loop through the values in your word map, writing each to a row in your file (see Listing 4).

        Calendar today = GregorianCalendar.getInstance();
                SimpleDateFormat formatter = new SimpleDateFormat("dd.MM.yy.H.mm.ss");
        String dateStr = formatter.format(today.getTime());
        String csvFileName = "benchmark-results" + dateStr + ".csv";
        CSVWriter writer = new CSVWriter(new FileWriter(csvFileName));
        String[] columns = new String[]{"Date","Word","Frequency"};
        writer.writeNext(columns);

        SimpleDateFormat dtFmt = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
        String crntDateStr = dtFmt.format(new Date());
        Set wordSet = map.keySet();

        for (Object objs : wordSet) {
            String nxtWord = (String) objs;
            Integer frequency = (Integer) map.get(nxtWord);
            String[] values = new String[]{crntDateStr, nxtWord, frequency.toString()};
            writer.writeNext(values);
        }

        writer.close();

After you create the CSV file, simply pass the name of the file as a parameter to the importData() method on the ReportClient. The method parses the return XML document and populates an ImportResult class with the detailed results of the import, as Listing 5 shows.

ReportClient reportClient = ZohoConfig.getReportClient();
        System.out.println(" Logging into the server");
        reportClient.login(ZohoConfig.LOGINNAME, ZohoConfig.PASSWORD);
        System.out.println(" Successfully logged in.");

        String uri = reportClient.getURI(ZohoConfig.LOGINNAME, 
                ZohoConfig.DATABASENAME, "WordFrequency");
        System.out.println("uri = " + uri);

        ImportResult importResult = 
                reportClient.importData(uri,"APPEND",new 
                        File(csvFileName),getImportConfig(), false);
        System.out.println("imported " + importResult.getTotalRowCount() + 
                " rows");

        reportClient.logout();

The results of changing the import process to use a bulk upload are fairly dramatic: The upload process for 6000 rows dropped in time from several minutes to several seconds.

Back to top

Exporting XML from Zoho Reports

You can export the data from Zoho Reports into XML, CSV, or an image format using either of two methods. The first is to specify a direct export with an optional criterion filter. The second method is to use something similar to an SQL SELECT statement.

A direct export specifies the table to export in the URI, and the output can be saved directly to a file or an output stream. Listing 6 provides an example of exporting directly to an XML file using search criteria. For this example, you want to see only the words that have a counter higher than 400.

        String exportUri = reportClient.getURI(ZohoConfig.LOGINNAME, 
                ZohoConfig.DATABASENAME, "WordFrequency");
        File topXmlFile = new File("topFrequencyWords.xml");
        String xmlCriteria = "(\"Frequency\" > 400)";
        reportClient.exportData(exportUri, "XML", 
                topXmlFile, xmlCriteria, null);

In this example, you specify that you only want data returned where the frequency value is greater than 400. The output is saved directly to the topFrequencyWords.xml file. Listing 7 provides a snippet of the XML file.

<?xml version="1.0" encoding="UTF-8" ?>
<response uri="/api/flexanvil/BlogAnalyzer/WordFrequency" action="EXPORT">
<criteria>("Frequency" > 400)</criteria>
<result>
<rows>
<row>
<column name="Word">http</column>
<column name="Frequency">2687</column>
<column name="Date">19 May, 2009 16:43:26</column>
</row>
<row>
<column name="Word">com</column>
<column name="Frequency">2587</column>
<column name="Date">19 May, 2009 16:39:05</column>
</row>

In the second example, you use SQL to specify the data you want to export. For this example, you use some basic SQL functions to analyze the data. If you wanted to see the average, count, max, and standard of the frequency values, you can use code similar to Listing 8.

     String sqlUri = reportClient.getURI(ZohoConfig.LOGINNAME, 
             ZohoConfig.DATABASENAME);
        System.out.println("sqlUri = " + sqlUri);
        String sql = "SELECT avg(\"Frequency\") as Average, 
                count(\"Frequency\") as Count, max(\"Frequency\") as Maximum, " +
                "std(\"Frequency\") as Standard  FROM \"WordFrequency\"";
        File xmlFile = new File("frequencyData.xml");
        reportClient.exportDataUsingSQL(sqlUri, "XML", xmlFile, sql, null);

This example specifies the SQL string to use for exporting the data. The SQL code is executed against the database, and the results are stored in the XML file, frequencyData.xml, as in Listing 9.

<?xml version="1.0" encoding="UTF-8" ?>
<response uri="/api/flexanvil/BlogAnalyzer" action="EXPORT">
<result>
<rows>
<row>
<column name="Average">11.796</column>
<column name="Count">5834</column>
<column name="Maximum">2687</column>
<column name="Standard">78.0844</column>
</row>
</rows>
</result>
</response>

The key difference with the SQL method of exporting the data is that the URI does not specify which table to use, which allows the SQL method to combine the data of multiple tables. For example, you might have a separate table with a list of companies you're interested in tracking. You can then use an SQL inner join to get the frequency with which each company in the list is mentioned, as in Listing 10.

        String companySql = "SELECT wf.Frequency, wf.Word, cm.Name 
                From WordFrequency wf inner join Companies cm on 
                wf.word = cm.name";
        File companyXmlFile = new File("companyFrequency.xml");
        reportClient.exportDataUsingSQL(sqlUri, "XML", 
                companyXmlFile, companySql, null);

This example uses a SELECT query that defines the inner join between WordFrequency and Companies, where the word matches the name of the company. Listing 11 shows the XML output from running this query.

<?xml version="1.0" encoding="UTF-8" ?>
<response uri="/api/flexanvil/BlogAnalyzer" action="EXPORT">
<result>
<rows>
<row>
<column name="wf.Frequency">52</column>
<column name="wf.Word">apple</column>
<column name="cm.Name">Apple</column>
</row>
<row>
<column name="wf.Frequency">3</column>
<column name="wf.Word">sun</column>
<column name="cm.Name">Sun</column>
</row>
<row>
<column name="wf.Frequency">17</column>
<column name="wf.Word">intel</column>
<column name="cm.Name">Intel</column>
</row>
<row>
<column name="wf.Frequency">80</column>
<column name="wf.Word">microsoft</column>
<column name="cm.Name">Microsoft</column>
</row>
</rows>
</result>
</response>

Because all of these examples produce XML, you can easily integrate the output back into the application. For example, you might parse this XML in the Java application to be displayed in the application.

Back to top

Creating and exporting graphs

One of the unique features of Zoho Reports is the visual analyzer. The visual analyzer allows you to quickly create charts, graphs, and other common reports of the data. As an example, suppose you wanted to create a graph of the most frequent words. Figure 2 shows the visual designer for creating a basic bar chart. (View a larger version.)

After you create this graph, download an image of the latest version of it by calling the exportData() method and specifying an image format for the output:

        String imgURI = reportClient.getURI(ZohoConfig.LOGINNAME,
                ZohoConfig.DATABASENAME,"FrequencyChart");
        File imgFile = new File("FrequencyChart.png");
        reportClient.exportData(imgURI,"IMAGE",imgFile,null,null);

The exportData() method saves the graph to a PNG file. You can in turn use this PNG file to display the graph in your application.

Back to top

Conclusion

With the REST-ful Web services that Zoho Reports provides, you can add cloud analytics to traditional applications. This in turn allows you to import and export data, export XML reports, and export predefined graphs. The ability to quickly analyze data and generate reports without installing a reporting engine allows you to spend more time on development. Cloud analytics also provide the ability for a broader audience to access the data and perform visual analysis—perhaps the greatest benefit of cloud analytics. Put the power of the cloud to use on your data.

Resources

Learn

• Zoho Reports wiki: Get answers to your Zoho Reports questions.
  
  
• CSV options: Get a list of available options for passing a CSV file into Zoho Reports.
  
  
• Java2s's example code: See Java2s’s example code for parsing the words out of a string.
  
  
• Cloud Computing space: Make this your starting point for the latest IBM, developerWorks and general industry information on cloud computing.
  
  
• IBM XML certification: Find out how you can become an IBM-Certified Developer in XML and related technologies.
  
  
• XML technical library: See the developerWorks XML Zone for a wide range of technical articles and tips, tutorials, standards, and IBM Redbooks.
  
  
• Technology bookstore: Browse for books on these and other technical topics.
  
  
• developerWorks technical events and webcasts: Stay current with technology in these sessions.
  
  
• developerWorks podcasts: Listen to interesting interviews and discussions for software developers.
  
  

Get products and technologies

• Zoho Reports: Download Zoho Reports.
  
  
• Rome Java library: Download the library for use with Zoho Reports.
  
  
• opencsv: Download opencsv, the open source Java CSV library.
  
  
• Download some of the many open source BI tools available:
  • BIRT: An Eclipse-based reporting system that integrates with your Java/Java 2 Platform, Enterprise Edition (J2EE) application to produce compelling reports.
  • Jaspersoft Business Intelligence Suite: Includes JasperReports, iReport, JasperServer, JasperAnalysis, and JasperETL.
  • Pentaho™ Corporation: The commercial open source alternative for BI; provides comprehensive reporting, online analytical processing (OLAP) analysis, dashboards, data integration, data mining, and a BI platform that have made it the world's leading and most widely deployed open source BI suite.
  
  
• IBM product evaluation versions: Download or explore the online trials in the IBM SOA Sandbox and get your hands on application development tools and middleware products from DB2®, Lotus®, Rational®, Tivoli®, and WebSphere®.
  
  

Discuss

• Cloud Computing Central group: Discuss and share what you do around cloud computing, saas, and related topics.
  
  
• XML zone discussion forums: Participate in any of several XML-related discussions.
  
  
• developerWorks XML zone: Share your thoughts: After you read this article, post your comments and thoughts in this forum. The XML zone editors moderate the forum and welcome your input.
  
  
• developerWorks blogs: Check out these blogs and get involved in the developerWorks community.
  
  

About the author

Rate this article

Comments

Back to top

Table of contents

• Get to know Zoho Reports
• Using the Zoho API
• Analyzing an RSS feed with Zoho Reports
• Optimizing the upload process
• Exporting XML from Zoho Reports
• Creating and exporting graphs
• Conclusion
• Resources
• About the author
• Comments

Next steps from IBM

Learn more, join the cloud community

---

• Community: Cloud computing conversations led by IBM community members
• Buy: Try IBM software on IBM SmartCloud Enterprise

Dig deeper into XML on developerWorks

IBM SmartCloud trial. No charge.

Unleash the power of hybrid cloud computing today!

---

Public and private solutions in one trial.

Special offers