SQLServerCentral Article

CData Connect: Derived Views and Query Federation


Big data plays a key role in promoting both manufacturing and scientific development through industrial digitization and emerging research. Integrating big data demands scalable tools for enabling not only big data ingestion and curation but also efficient large-scale exploration and discovery. There is a solution to scale up to large volumes of data distributed across multiple data sources.

In this article, we explore the Derived Views and Query Federation features of CData Connect, allowing users to customize their interface with data and even query data across different data sources.

Derived Views

Getting a database model of your SaaS data, where each entity (Account, Contact, etc.) is represented by a database table is useful, but creating views of your enterprise data can help in a variety of ways:

  • Simplified access — Views provide access to a specific subset of the data available, and can feature built-in filtering, column selections, and more to create easier access to commonly used queries

The Derived View feature of CData Connect lets you do just that — create a “view” of your SaaS, Big Data, & NoSQL sources. The derived view behaves exactly like a user-defined view, but the definition of the view resides directly within the CData Connect instance. To create a view, you first need to create a virtual database for your data.

  1. Navigate to the Data Model tab and click Data Sources

3. Configure the connection using the data source-specific properties

Once connected to a data source, you can explore the virtual database in the Data Explorer. You can select your newly created database and drag tables into the query pane.

You can also create custom SQL queries based on the data model to request specific data. Click the Create Derived View button to save the query as a view. With the view saved, users will be able to easily access the results of the specific query saved. For example, you can use the view to visualize the results in popular BI and reporting tools.

Query Federation

In addition to creating Derived Views, you can also leverage the Query Federation capabilities of CData Connect to query related data across different sources. For example, you would be able to join data from your CRM solution and your accounting solution. The feature in CData Connect is built using our existing query federation technology. To query data across different sources, you will need multiple virtual databases in your CData Connect instance.

Once the databases are created, you can query data from both of them simultaneously just like you would be able to query data from two related databases. For example, you can connect to CData Connect from Power BI and visualize your data.

Query Federation in Derived Views

The Query Federation functionality can be used to create Derived Views across different data sources. This grants users and administrators all of the benefits of views on top of the benefits of querying data from multiple sources at the same time. Simply construct a query joining data from multiple virtual databases and save it as a derived view.

More Information

In order to transform the enormous amount of data into knowledge from where actions can be taken, fundamental problems, such as data integration and query processing, must be solved.

CData Connect is a consolidated connectivity platform that lets you easily connect any application, on-premises or in the cloud, with real-time data from anywhere.