ADO.NET Provider for Adobe Analytics

Build 26.0.9655

Data Model

The CData ADO.NET Provider for Adobe Analytics models Adobe Analytics objects as an easy-to-use SQL database, using views and stored procedures. Some of these entities are defined in schema files, which are simple, easy-to-read text files that define the structure and organization of data.

Views

Reports are shown as views, which are tables that cannot be modified. There are five types of report views:

Workspace Reports

Workspace project visualizations are exposed as report views. Unlike traditional database views, it is not very helpful to select all metrics and dimensions in a given table. Date is the default dimension for every report, so the query: 

SELECT * FROM MyReport
becomes: 
SELECT Date, {all metrics} FROM MyReport
If the query has criteria, the default dimensions are the dimensions used in the criteria. For example: 
SELECT * FROM MyReport WHERE Country = 'England'
becomes: 
SELECT Country, {all metrics} FROM MyReport WHERE Country = 'England'

Multi-Dimension Breakdown

The Adobe Analytics API limits the number of dimensions per REST API call to one. To generate reports with multiple dimensions, the provider breaks them down in stages.

For example, given the query: 

SELECT Country, City, Visits FROM MyReport
  • First, the provider requests all values of the first dimension, Country. In this example they are England and Germany.
  • Second, the provider breaks down England using the dimension City, and gets London and Liverpool.
  • Finally, it breaks down Germany using City and gets Berlin and Frankfurt.

If there is a third dimension, the provider breaks down every city with that dimension (one request per city). As the number of dimensions and values grows, the number of API calls increases significantly.

Server-Side Filters

The Adobe Analytics API supports the logical operators AND, OR, and NOT, as well as the MATCH, CONTAINS, BEGINS-WITH, and ENDS-WITH operators. The following criteria are handled server-side: 

... WHERE Dimension = 'Value'
... WHERE Dimension LIKE '%value%'
... WHERE Dimension1 LIKE '%value' AND Dimension2 = 'Value2'
... WHERE Dimension LIKE 'value%'
... WHERE Dimension1 = 'Value1' AND Dimension2 = 'Value2'
... WHERE Dimension = 'Value1' OR Dimension = 'Value2' OR Dimension = 'Value3'
... WHERE Dimension IN ('Value1', 'Value2', 'Value3', 'Value4')
... WHERE (Dimension1 = 'value1' OR Dimension1 = 'Value2') AND (Dimension2 = 'value3' OR Dimension2 = 'value4')

CData strongly recommends using as many filters as possible; otherwise queries with many dimensions can take a long time.

Mobile Scorecard Reports

Adobe Analytics Mobile Scorecard projects are also exposed as report views. Each tile on a scorecard's home screen becomes a separate view, named {ProjectName}_{TileName}. The columns of each view reflect the metrics and dimensions configured for that tile, along with other components in the tile's detail screens and slides.

Mobile Scorecard report views differ from Workspace report views in several ways:

  • Single-dimension queries only. Multi-level dimension breakdowns for Mobile Scorecard report views are not supported. Only 1 dimension can be specified per query; specifying more than one will result in an error. 
    -- Valid: single dimension
SELECT Date, Visits FROM MyScorecard_Revenue

-- Invalid: multiple dimensions, will fail
SELECT Date, Country, Visits FROM MyScorecard_Revenue
  • Connection properties ignored. The IncludeSummaryData, SkipEmptyRows, and SupportEnhancedBreakDown connection properties do not apply to Mobile Scorecard report views.

  • Standard columns. The SegmentId, DateRangeId, date range dimension columns (such as Day or Month) are included for every mobile scorecard report view.

    The SegmentId and DateRangeId columns can be specified in the WHERE to filter the report data. Only the = operator is supported for them. If they are not specified in the WHERE clause, the provider will filter the data using their default values (the first value in the sys_tablecolumns.Values metadata column).

    The date range dimension columns can be used to break down the data, with the Day column being the default dimension used in SELECT * queries.

Refer to the following query examples: 

SELECT * FROM [Mobile ScoreCard Project 1_Occurrences];
SELECT [Browser], [Occurrences], [Bot Occurrences] FROM [Mobile ScoreCard Project 1_Occurrences] WHERE [SegmentId] = [First_Time_Visits] AND [DateRangeId] = '69a00a0fadffc4351d023b45';

Defining Custom Reports

To create a report view, use the CreateReport stored procedure. It generates a new view definition with a custom combination of dimensions and metrics. Calling it creates a schema file that you can query like any other view.

The stored procedure takes a view name, comma-separated lists of metric names and Ids, comma-separated lists of dimension names and Ids, and an output folder as inputs.

Set the Location connection property to the folder containing the new script files in order to access them (you can set Location instead of the output folder).

Get the metric and dimension Ids by querying the Metrics and Dimensions views: 

SELECT Id, Name FROM Dimensions
SELECT Id, Name FROM Metrics

For example, to use a new schema along with the default schemas, set the Location property to the db subfolder in the installation folder and make the following call: 

EXEC CreateReport Dimensions = 'Country, City', Metrics = 'PageViews, Visits', TableName = 'MyCustomReport'

Predefined Reports

The following views are included with the application:

View Description
CalculatedMetrics Retrieve a list of all calculated metrics defined within your Adobe Analytics environment, including custom formulas built on top of standard metrics.
CollectionSuites Return the list of data collection suites configured in Adobe Analytics, which represent logical groupings of tracking data within a report suite.
Dimensions List all available dimensions that can be used to segment and break down Adobe Analytics data, such as page name, device type, or campaign.
KeyMetrics Display high-level engagement metrics like page views and visits for a specified time range (defaults to the past 30 days).
LastTouchChannel Identify the last marketing channel attributed to each visitor during their session, based on Adobe's last-touch attribution model.
LastTouchChannelDetail View detailed attributes of the last-touch marketing channel, such as referring domain, tracking code, or campaign ID.
Metrics Retrieve all available standard and custom metrics that measure user behavior and site performance in Adobe Analytics.
Orders Summarize the total number of purchase events recorded across all tracked orders within a specified time period (defaults to 30 days).
PageOccurrences Display the number of times a page was involved in a tracked interaction or persisted as a dimension value within the selected reporting window.
Pages Identify pages on your site ranked by popularity, based on views and interactions during a selected reporting period (defaults to 30 days).
PageViews Show the total number of page views recorded, helping you understand overall traffic volume over a specified date range.
Products Return order counts grouped by product name or SKU, allowing analysis of top-performing items sold within the last 30 days.
Revenue Aggregate the total revenue generated from all completed orders within the reporting period (defaults to 30 days).
Segments Retrieve the list of predefined and custom segments available in your Adobe Analytics account, including shared and curated segments.
SiteSections Identify the most trafficked or highest-converting sections of your site, based on page grouping and content hierarchy.
TrackingCode View which marketing or campaign tracking codes drove the most traffic to your site over a given period.
Units Show the total number of individual product units purchased across all orders in the selected reporting window.
UniversalReport Run a unified report that includes all configured dimensions and metrics for a broad view of user behavior and performance.
Users List the users who have access to Adobe Analytics, including login details, roles, and account status.
Visitors Display the total number of unique individuals who accessed your site during the reporting period (defaults to the past 30 days).
Visits Show the number of individual sessions recorded during the specified date range, regardless of visitor identity.

Stored Procedures

Stored Procedures are SQL scripts that extend beyond standard CRUD operations. They can be used to access additional capabilities of the Adobe Analytics API.

Copyright (c) 2026 CData Software, Inc. - All rights reserved.
Build 26.0.9655