Enterprise-scale data integration demands scalable tools to not only centralize and curate data at scale, but also efficiently explore and analyze data at large-scale. CData Connect solves the challenge of data exploration and discovery through virtualized connectivity to data distributed across many data sources.
In this article, we explore the Derived Views and Query Federation features of CData Connect, allowing users to create custom interfaces, surfacing combined data from many sources and even simultaneously query data across 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.
- Navigate to the Data Model tab and click Data Sources
- Select a source from the available Sources
- 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 and actionable insights, teams must solve fundamental problems, such as data integration and query processing.
CData Connect is a consolidated connectivity platform that lets you easily connect any application, on-prem or in the cloud, with real-time data from anywhere. Now, analysts can finally gain unified access to vital data and deliver hard, data-driven insights for their teams.