SQL Access to Analytical Data APIs

CData Drivers provide standards-based (JDBC, ODBC, etc.) SQL access to analytical data sources, such as Bing Ads, Google AdWords, SQL Server Analysis Services, YouTube Analytics and many more, providing a single interface for all of your data. However, because the data underlying these services is natively multi-dimensional, most SQL aggregations are not relevant.

Working with Analytical Data

The data behind analytical data services is not relational like most of the other sources CData supports. Instead, the data is multi-dimensional, exposed as metrics and dimensions.

• Metrics are the meaningful number values for the service
• Dimensions define how the metrics are grouped

A query without a metric would return an empty response, therefore this is not generally possible with the CData drivers and results in an error. Dimensions give insight into what the raw numbers mean (these are essentially aggregations). Consider the two queries below:

1. SELECT Month, Sessions FROM Traffic WHERE StartDate='2015-01-01' AND EndDate='2015-12-31'

   This query groups the number of Sessions (metric) by Month (dimension) and returns 12 rows (one per month).

2. SELECT Month, DeviceCategory, Sessions FROM Traffic WHERE StartDate='2015-01-01' AND EndDate='2015-12-31'

   This query groups by Month (dimension 1) and breaks each month down by DeviceCategory (dimension 2, i.e. mobile, tablet, or desktop) and returns 36 rows (three rows per month).

Note: Analytical data APIs present aggregations of data. Any SQL queries that include aggregations will not be supported by the drivers, as such aggregations do not provide further meaning on the data exposed by the API.

Use Metrics & Dimensions to Get Data

In general, all queries will be a set of metrics (i.e. numbers) of interest grouped by the chosen dimensions (i.e. categories). As long as the query contains at least 1 metric, the service will return meaningful data from any query submitted.