Dealing with Disruptive Data: Advancing BI Connectors and Integrating SQL and NoSQL Databases

Don’t count on databases to have uniform data models. Far from it, you’ll find different databases storing data in a plethora of shapes and sizes. This leads to a number of challenges when it comes time for business intelligence (BI) professionals to pull useful data from non-uniform database structures. The developers building applications versus those building intelligence have vastly different preferences in how to access data.

Don’t count on databases to have uniform data models. Far from it, you’ll find different databases storing data in a plethora of shapes and sizes. This leads to a number of challenges when it comes time for business intelligence (BI) professionals to pull useful data from non-uniform database structures. The developers building applications versus those building intelligence have vastly different preferences in how to access data.

Being able to manage data in whatever form is the heart of a modern challenge that companies of all sizes can expect to face, if they haven’t already. Enterprises need to have the BI tools that can access a full range of databases from structured to unstructured data models. This is especially true when applications become mission critical within the organization and the choice of technologies is often dictated by enterprise BI requirements for the executive leadership.

For example, BI connectors and enhanced query languages have been developed for MongoDB and Cassandra to help address this. This query capability is layered on top of these NoSQL databases to build standard-based connectivity, processing non-tabular and unusually-shaped data and drawing valuable intelligence from it. BI connectors for NoSQL are hugely advantageous when dealing with different database systems, enabling BI professionals to retrieve data across diverse environments, but there is still room for improvement – especially around integrating data from NoSQL databases.

While SQL databases are relatively more established data storage systems, alternative NoSQL databases have recently been increasing in their popularity thanks to their scalability and flexible data models. MongoDB is currently the most widely downloaded NoSQL database, but it’s not alone. Among the other well-engineered NoSQL databases worth noting are Cassandra, MarkLogic and Couchbase. These feature sophisticated data intelligence interfaces: Couchbase supports the N1QL data access layer, a solution to the problem of accessing and processing JSON data, while MarkLogic supports XQuery, a very powerful and composable layer; Cassandra offers CQL, which is similar to SQL in that data is organized in tables containing rows of columns and supports prepared statements that allow developers to parse a query once and execute it multiple times with different concrete values.

With this trend, there arises a central need to improve NoSQL query languages and BI connectors across all NoSQL databases. The bottom line is that SQL and NoSQL databases both function to store data, while their divergent approaches make each suitable for different types of projects.

NoSQL has been getting more advanced support for query languages, and there has been a correlated increase in NoSQL technologies. Some vendors, like Progress Software, are building vendor-agnostic BI connectors that provide the ability to work reliably across all NoSQL technologies. And here’s the beauty behind it: In putting logical schema on top of unstructured and semi-structured databases, BI connectors act as a data source for SQL based platforms while actually being an open source NoSQL database.

These BI connectors provide normalized SQL access for NoSQL data models to certain collections for compatibility with existing BI applications, and their flexible data models handle data that lacks uniformity from row to row, storing data in JSON-like documents that are able to meet the latest demands for modern application development. The NoSQL database BI connectors provide compatibility with BI ecosystems that are expecting SQL, structuring with deeply nested data that does not have equivalents in the relational models. Before the concept of normalization for NoSQL databases, select third party vendors were building unreliable BI connectors that flattened the data model.

The industry demands better, and so is heading toward improving queries and complex analytics through standards based SQL connectivity in complex data environments. And in these cases, NoSQL BI connectors are used with different tools to achieve more elegant analytics. This includes data visualization (pulling data out of the connector, then running the visualization and using predictive analytics tools layered on to garner sophisticated data) and data federation (technologies such as Microsoft SQL Server Linked Server that allows NoSQL standards to look like SQL server databases).

These disruptive data sources indicate that BI is getting more complex. However, in a world of non-uniform data, BI connectors that layer on new databases to establish much-needed standard connections enable valuable enterprise data to be pulled from varying database structures. New BI tools keep advancing the state of the industry, making unstructured and semi-structured data seem much less threatening when it comes to integrating SQL and NoSQL databases.