CData Connect: Derived Views and Query Federation

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
• Improved security - Views can select specific columns/rows and set the permissions on the view instead of the underlying table, giving data administrators more granular control over which data points are accessible by each user
• Complex, real-time data - Views can roll up operations (summations, aggregations, etc.), JOINs, and more all while provide real-time access to data by performing live queries against the underlying table(s) each time the view itself is queried

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
2. Select a source from the available 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 & Free Trials

The features described above are available in both CData Connect Cloud and CData Connect On-Premise. Learn more about both products from our CData Connect page. Download or sign up for a free trial and see the CData difference in accessing your data today.