InetSoft Product Information: Data Integration Software to Access Multiple Data Sources

InetSoft's business intelligence software includes powerful data integration abilities via support for many types of connectors to various data sources including:

  • Relational databases such as Oracle, SQL Server, PostgreSQL, and MySQL via a JDBC driver
  • Multidimensional databases
  • XML
  • SOAP
  • Java beans (POJO)
  • EJB beans
  • Flat files (CSV)
  • Microsoft Excel spreadsheets
  • OLAP cubes such as Microsoft SQL Server Analysis Services, Hyperion ESSbase, Oracle OLAP, and SAP NetWeaver
  • SAP Business Information Warehouse (SAP BW)
  • Progress
  • PeopleSoft
  • JD Edwards
  • salesforce.com
  • Siebel CRM
  • Google AdWords
  • Google Analytics
  • Microsoft SharePoint
  • Hadoop/HIVE
data integration software example 2nd data integration software

Data Integration with Style Intelligence

The information access needs of each business differ greatly. From the data sources used, to the dashboard or report design requirements, to the security needed; every company has their own unique set of requirements that must be fulfilled. One important topic that is often forgotten is that of data source integration. If data cannot be retrieved, it cannot be used to produce reports.

Style Intelligence provides interfaces for multiple data source types and a flexible Java API that can interface with any custom data source. This article discusses how Style Intelligence interfaces with various data sources and how it can solve data integration problems that are left untouched by other dashboard or reporting tools.

Multiple Data Source Support Problem

There is a need to connect to different types of data sources and retrieve data from each of these sources in each report. Relational databases, XML, SOAP, custom objects, and other data source types may be used in dashboard or report creation. Other dashboard or reporting tools are limited in the data source types they can connect to.

data integration art

The Style Intelligence Solution

Just as the dashboard and reporting requirements differ between each business, so do the data integration requirements. Some businesses need to report off of standard relational databases whereas others need to report off of data sources such as XML or Java beans. Style Intelligence supplies an out-of-the-box interface for every popular data source type and a flexible API that can be used to integrate with any custom data source.

All data source connections are defined in and managed through the Query Builder in the Report Designer and the Visual Composer. The type of source can be selected from the dropdown list and then the particular connection information can be entered. Each data source type requires specific connection information related to that source.

Defining a new data source in the Query Builder

The most commonly used data source type is a relational database. Relational databases can be interfaced through JDBC or ODBC. Any Java compatible driver can be referenced to connect to the database. These drivers are supplied commercially (usually with the database) and are not distributed with Style Intelligence. Since Style Intelligence utilizes JDBC when connecting to a database, it can interface with any new or legacy system such as: Microsoft SQL Server, Oracle, Sybase, Informix, DB2, MySQL, Postgres, Access, Teradata, and InterBase, to name a few.

XML data source definition in the Query Builder

The second most popular data source type is XML. Style Intelligence can connect to any well defined XML data source. An XML file or stream requires a URL (local or remote) and a schema definition (DTD or XSD).

Accessing Custom Data Source (Objects) Problem

Some businesses require reports or dashboards to be created from a custom data store. This data store can be presented as an object but cannot be easily integrated with other dashboard or reporting tools.

The Style Intelligence Solution

Style Intelligence goes beyond reporting against traditional data sources such as relational databases, XML, and flat files by providing a unique object data source interface. More and more businesses are creating object data stores in order to provide a unified data interface and persistent data store for all applications across the entire enterprise. The most common approach is to retrieve data from a relational or hierarchal data source such as an RDBMS or XML file, and then transform it into objects. The problem is accessing these objects. Since Style Intelligence is a 100% Java application, it requires a Java interface to access custom data sources. This Java object layer can retrieve its data from any location so Java is only required at the top level, not at every level.

Let’s look at an example of utilizing an object data layer to produce reports. A particular company needs to retrieve customer information from an object that in turn, retrieves its data from a relational data base when the system is initialized. This information is stored in an Object called Customer that has standard “get” and “set” functions for members called: customerId, companyName, address, city, state, zip, and reseller. The Customer object is accessed through two other classes that must be created.

The “Loader” retrieves the data, populates the objects, and provides the functions to retrieve data into Collections. The “Data Helper” provides information on the data types returned. Below is a code example of the Loader and DataHelper created for the Customer data Object.

---- Begin Loader.java ---

public class Loader {
   public static final String DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";
   public static final String URL = "jdbc:odbc:Orders";
   private Vector loadCustomers(String whereClause)
      throws SQLException, ClassNotFoundException {
      Vector result = new Vector();
      Customer cust = null;
      Class.forName(DRIVER);
      Connection conn = DriverManager.getConnection(URL, null, null);
      Statement st = conn.createStatement();
      ResultSet rs = null;
      if (whereClause == null)
         {rs = st.executeQuery(Customer.SQL);
         } else {
         rs = st.executeQuery(Customer.SQL+whereClause);
         }
      while (rs.next()) {
         cust = new Customer();
         cust.setCustomerId(rs.getInt("customer_id"));
         cust.setCompanyName(rs.getString("company_name"));
         cust.setAddress(rs.getString("address"));
         cust.setCity(rs.getString("city"));
         cust.setState(rs.getString("state"));
         cust.setZip(rs.getString("zip"));
         cust.setReseller((rs.getInt("reseller") == -1));
         result.add(cust);
      }
      rs.close();
      st.close();
      conn.close();
      return result;
   }


      public Vector getAllCustomers() {
      Vector result = null;
      try {
      result = loadCustomers(null);
      } catch (SQLException e) {
      e.printStackTrace();
      } catch (ClassNotFoundException cne) {
      cne.printStackTrace();
      }
      return result;
   }
   public Vector getCustomerById(int customerId) {
      Vector result = null;
      try {
      result = loadCustomers(" WHERE customer_id="+customerId);
      } catch (SQLException e) {
       e.printStackTrace();
      } catch (ClassNotFoundException cne) {
       cne.printStackTrace();
      }
      return result;
   }
   public Vector getCustomerByState(String state) {
      Vector result = null;
      try {
      result = loadCustomers(" WHERE state='"+state+"'");
      } catch (SQLException e) {
      e.printStackTrace();
      } catch (ClassNotFoundException cne) {
      .printStackTrace();
      }
      return result;
   }
}

---- End Loader.java ----

In this example, the Loader retrieves data from a relational database using a single query. This example could be expanded to include many data base tables or other data sources if needed. The functions getAllCustomers(),getCustomerById(), and getCustomerByState(String state) are used to retrieve data from the Query Builder into the report. Once can see that the interface is so generic that any data set can be retrieved from any location

.more data integration art

Custom Object Problem

Business intelligence analysts need a unified meta-data layer to build reports and dashboards without the hassle of creating queries. The end-users also need this meta-data layer to create their own queries via the web.

The Style Intelligence Solution

Any physical database schema that can be represented by a data model can be done entirely within the Query Builder. The first step is to define physical views of your database. These views include the tables and joins. The next step is to create logical models that map to the physical views. The advantage gained by creating a logical model is that it does not have to look anything like the physical view. The various entities and attributes can combine any fields from assorted tables, and alias them with better names.

For example, cycles can be corrected by the addition of weak joins, and table aliasing. Furthermore, once data models have been created, Virtual Private Models can limit the data that is returned based on security permissions.

Summary

Style Intelligence provides more choices for data integration than many other reporting and business intelligence tools on the market. Interfaces for relational databases, XML, SOAP, flat files, and custom data sources provide unlimited flexibility for data source integration and multiple data source access. A powerful meta-data layer provides report designers and end-users with the ability to create ad hoc queries for any report without having knowledge of the particular data source. Style Intelligence can integrate with any data source to produce any type of dashboard or report for all end-user populations.


 
Read Dashboard Reviews
View 2-min Dashboard Demo